19 March, 2012

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 !!!

Rate Me:

6 Discussion:

raghu said...

Nice Post.... i will try it....

Thanks

resmy said...

i want to write custom query for messageboard. how do we run ant build-service from ext?

asif aftab said...

hi
would any one like to let me how to use the custom sql for updating data I can do the same by using simple update function of service builder, but I want using custom sql and I am searching but lots of place I found example on fetching data but not single place of updating. Please help me I am new in liferay
thanks asif

Anonymous said...

How do you ensure this goes through LR's caching layer ?

Madhura Shetty said...

Hi, instead of "select * from table" I need to select only certain columns form the table. How can i do it. Please help

Madhura Shetty said...

"SELECT *
FROM pficontacts.FAQRAISEQUERY
WHERE
(pficontacts.FAQRAISEQUERY.sentbyregion LIKE ?)" is working fine but i want
"SELECT pficontacts.FAQRAISEQUERY.sentByName
FROM pficontacts.FAQRAISEQUERY
WHERE
(pficontacts.FAQRAISEQUERY.sentbyregion LIKE ?)" . How do i do it