Custom Sql in Liferay Plugin Portlet

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

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

In the $PLUGINS_SDK/portlets(Windows)

create library-portlet "Library"

Step 2 :-

in portlet.xml

< portlet-class > com.liferay.util.bridges.mvc.MVCPortlet < /portlet-class >
replace with
< portlet-class > com.mpower.action.LibraryPortlet < /portlet-class >


Step 3 :-


Create service.xml under docroot/WEB-INF


< ?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 a folder custom-sql under src

b. Create a file default.xml under src/custom-sql

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

c. Create a file book.xml, under src/custom-sql

< ?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 service/persistence

public class BookFinderImpl extends BasePersistenceImpl implements
BookFinder {

}


Do ant build-service to generate necessary files.



Step 6 :-

Now write the logic to access the custom sql


public List 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 in BookLocalServiceImpl.java


public class BookLocalServiceImpl extends BookLocalServiceBaseImpl {

public List findBook(String name) throws PortalException,
SystemException, RemoteException {

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


run ant build-service , this will update the corresponding api with new method defined.

Step 8 :-

a. Create init.jsp under docroot and add the below content


< %@ 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 docroot/


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

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

%>

< 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 view.jsp

< %@ 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 src/com/mpower/action


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 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" and check the search functionality in Library portlet.