Creating a Custom Query in Plugin Portlet

Creating a Custom Query in Plugin Portlet for Liferay Models(Liferay 6.0.x).

Hibernate3 can use custom SQL statements for create, read, update, and delete operations.
The SQL is directly executed in your database, so you can use any dialect you like.
This will reduce the portability of your mapping if you use database specific SQL.

Follow the steps below to write the custom SQL in liferay:

Step 1 :- Creating a library portlet
          ===========================

In the $PLUGINS_SDK/portlets

Run the following command on the command prompt:

create library-portlet "Library" (windows)

./create.sh library-portlet "Library" (linux/unix)


Step 2 :-
 Once the build is successfull the portlet will be in the following folder structure.
$PLUGINS_SDK/portlets/library-portlet
$PLUGINS_SDK/portlets/library-portlet/docroot
$PLUGINS_SDK/portlets/library-portlet/docroot/WEB-INF
$PLUGINS_SDK/portlets/library-portlet/docroot/css
$PLUGINS_SDK/portlets/library-portlet/docroot/js

Now find the portlet.xml under library-portlet/docroot/WEB-INF and do the following changes
Replace
 <portlet-class>com.liferay.util.bridges.mvc.MVCPortlet</portlet-class>
  with
 <portlet-class>com.mpower.action.LibraryPortlet</portlet-class>

Step 3 :-


Create service.xml under library-portlet/docroot/WEB-INF with following contents:


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.0.0//EN"
    "http://www.liferay.com/dtd/liferay-service-builder_6_0_0.dtd">
<service-builder package-path="com.mpower">
    <author>Arun Kumar</author>
    <namespace>library</namespace>
    <entity name="Book" local-service="true" remote-service="false">
        <column name="bookId" type="long" primary="true" />
        <column name="name" type="String" />
        <column name="author" type="String" />
        <order by="asc">
            <order-column name="name" case-sensitive="false" />
        </order>      
    </entity>
</service-builder>



Step 4 :-

a. Create the folder custom-sql under library-portlet/docroot/WEB-INF/src

b. Create a file default.xml under library-portlet/docroot/WEB-INF/src/custom-sql with following contents:

<?xml version="1.0"?>
<custom-sql>
    <sql file="custom-sql/book.xml" />
</custom-sql>

c. Create a file book.xml, under library-portlet/docroot/WEB-INF/src/custom-sql with following contents:

<?xml version="1.0"?>
<custom-sql>
    <sql id="findBooks">
        <![CDATA[
            SELECT
                *
            FROM
                library_book
            WHERE
                (library_book.name like ?)
        ]]>
    </sql>
</custom-sql>

Step 5 :-

Create the file "BookFinderImpl.java" under library-portlet/docroot/WEB-INF/src/com/mpower/service/persistence

public class BookFinderImpl extends BasePersistenceImpl implements
BookFinder {

}


Now run the command ant build-service from command prompt $PLUGINS_SDK/portlets/library-portlet to generate necessary files.



Step 6 :-

Now write the logic to access the custom sql in BookFinderImpl.java which we created in the above step:


    public List<Book> findBooks(String name) throws SystemException {
        Session session = null;
        try {
            session = openSession();
            String sql = CustomSQLUtil.get(FIND_BOOKS);
            SQLQuery query = session.createSQLQuery(sql);
            query.addEntity("Book", BookImpl.class);
            QueryPos qPos = QueryPos.getInstance(query);
            qPos.add(name);
            return (List)query.list();
        }catch (Exception e) {
        }
        return null;
    }
  
    public static String FIND_BOOKS = "findBooks";



*** Make the necessary imports.


import java.util.List;

import com.liferay.portal.kernel.dao.orm.QueryPos;
import com.liferay.portal.kernel.dao.orm.SQLQuery;
import com.liferay.portal.kernel.dao.orm.Session;
import com.liferay.portal.kernel.exception.SystemException;
import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
import com.liferay.util.dao.orm.CustomSQLUtil;


Step 7 :-

Now write the method to search books from the list in library-portlet/docroot/WEB-INF/src/com/mpower/service/impl/BookLocalServiceImpl.java


public class BookLocalServiceImpl extends BookLocalServiceBaseImpl {
  
    public List<Book> findBook(String name) throws PortalException,
    SystemException, RemoteException {

        return BookFinderUtil.findBooks("%" + name + "%");
}


run ant build-service from command prompt $PLUGINS_SDK/portlets/library-portlet to update the corresponding api with new method defined above.

Step 8 :-

a. Create init.jsp under library-portlet/docroot and add the below contents


<%@ taglib uri="http://java.sun.com/portlet_2_0" prefix="portlet" %>
<%@ taglib uri="http://liferay.com/tld/ui" prefix="liferay-ui" %>
<portlet:defineObjects />
<%@ page import="com.mpower.service.BookLocalServiceUtil" %>
<%@ page import="com.mpower.model.Book" %>
<%@ page import="java.util.*" %>

b. Create result.jsp under library-portlet/docroot to display the results


<%@page import="com.liferay.portal.kernel.util.Validator"%>
<%@ include file="init.jsp" %>

<%
List<Book> books = (List) request.getAttribute("result");
if(Validator.isNull(books))books = new ArrayList<Book>();

%>

<liferay-ui:search-container delta="10" emptyResultsMessage="no-books-were-found">
    <liferay-ui:search-container-results
        results="<%= books %>"
        total="<%= books.size( )%>"
    />

    <liferay-ui:search-container-row className="com.mpower.model.Book" modelVar="book">
  
        <liferay-ui:search-container-column-text
            name="Book Title"
            property="name"
        />
    
        <liferay-ui:search-container-column-text
            name="Author"
            property="author"
        />  
    
    </liferay-ui:search-container-row>
  
    <liferay-ui:search-iterator />

</liferay-ui:search-container>


C. Update the library-portlet/docroot/view.jsp to display the book search form.

<%@ include file="init.jsp" %>
<portlet:actionURL var="findURL" name="findBooks" />

<form action="<%= findURL.toString() %>" name="fm" method="post">


<label>Book Title</label><input name="title" value=""/><input type="submit" value="Search"/>

</form>


d. Create the portlet class LibraryPortlet.java under library-portlet/docroot/WEB-INF/src/com/mpower/action to write the search the books:


package com.mpower.action;

import java.io.IOException;
import java.util.List;

import javax.portlet.ActionRequest;
import javax.portlet.ActionResponse;
import javax.portlet.PortletException;

import com.liferay.portal.kernel.exception.PortalException;
import com.liferay.portal.kernel.exception.SystemException;
import com.liferay.portal.kernel.util.ParamUtil;
import com.liferay.util.bridges.mvc.MVCPortlet;
import com.mpower.model.Book;
import com.mpower.service.BookLocalServiceUtil;

public class LibraryPortlet extends MVCPortlet{

    public void findBooks(ActionRequest actionRequest,
            ActionResponse actionResponse) throws IOException, PortletException {
  
        String name = ParamUtil.getString(actionRequest, "title");
    
        try {
            List<Book> books = BookLocalServiceUtil.findBook(name);
            actionRequest.setAttribute("result", books);
            actionResponse.setRenderParameter("jspPage", "/result.jsp");
        } catch (PortalException e) {
            e.printStackTrace();
        } catch (SystemException e) {
            e.printStackTrace();
        }
  
  
    }
}


run "ant deploy" from command prompt $PLUGINS_SDK/portlets/library-portlet to deploy the portlet on the server.