Custom Query

If you are thinking to use custom query feature of liferay in your plugin portlet,
Just follow below steps :



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

In the $PLUGINS_SDK/portlets(Windows)
run this command create library-portlet "Library"

Step 2 :- in portlet.xml
=================================================================================

< portlet-class > com.liferay.util.bridges.mvc.MVCPortlet < /portlet-class >

replace with 

< portlet-class > com.cignex.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.cignex" >
< author > Jigs < /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 :- Custom SQL
=================================================================================
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 FinderImpl
=================================================================================
Create the file "BookFinderImpl.java" under src/persistence

public class BookFinderImpl extends BasePersistenceImpl implements
BookFinder {
}

Note : Now Run ant build-service to generate necessary files.

Step 6 :- Logic part
=================================================================================

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;

Step 7 :- Create LocalServiceImpl class
=================================================================================
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 + "%");
}

Step 8 :- Final step for result
=================================================================================

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.cignex.service.BookLocalServiceUtil" % >
< %@ page import="com.cignex.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.cignex.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/cignex/action

package com.cignex.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.cignex.model.Book;
import com.cignex.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.

If still you are not succeed please put your comments over here.
 so that we can get quick solution !!!