Custom Query in Liferay 6.1

The following will explain how to work with custom-sql in liferay 6.1

1. Create a portlet
2. In a service.xml (Under docroot/WEB-INF) add the following lines

<?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.books">
    <author>koteswara.rao</author>
    <namespace>library</namespace>


    <entity name="Books" local-service="true" remote-service="false">
        <column name="bookId" type="long" primary="true"></column>
        <column name="title" type="String"></column>
        <column name="author" type="String"></column>
               
        <order by="asc">
            <order-column name="bookId"></order-column>
        </order>
       
        <finder return-type="Collection" name="Author">
            <finder-column name="author"></finder-column>
        </finder>
    </entity>
</service-builder>

3. Build the service (you can use ant build-service or directly from IDE)
4. Create custom-sql folder under src folder
5. Create default.xml file under custom-sql and add the following lines of code

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



6. Create book.xml file under the same custom-sql folder mentioned in step 4 and add the following lines of code

<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
<sql id="findBooks">
<![CDATA[
SELECT * FROM library_books
WHERE (library_books.title like ?)
]]>
</sql>
</custom-sql>


7. Create the file BooksFinderImpl.java under persistence (persistence will get after the first time build service) and add the following lines of code

public class BooksFinderImpl extends BasePersistenceImpl implements BooksFinder {
    }

8. Now run ant build-service to generate necessary files

9. Add the following lines of code in BooksFinderImpl class

 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", BooksImpl.class);
         QueryPos qPos = QueryPos.getInstance(query);
         qPos.add(name);
         return (List)query.list();
         }catch (Exception e) {
         }
         return null;
         }
         public static String FIND_BOOKS = "findBooks";

10. Add the following lines of code under BooksLocalServiceImpl 

public class BooksLocalServiceImpl extends BooksLocalServiceBaseImpl {
    /*
     * NOTE FOR DEVELOPERS:
     *
     * Never reference this interface directly. Always use {@link com.cignex.books.service.BooksLocalServiceUtil} to access the books local service.
     */
     public List<Books> findBook(String name) throws PortalException,
     SystemException, RemoteException {
     return BooksFinderUtil.findBooks("%" + name + "%");
     }
}

11. Again run "ant build-service" again passing the service.xml file as parameter.
     This will update the corresponding interface with the new method defined.

12. Now go ahead and call BookLocalServiceImpl method from your jsp or java
      normally how    you      call other methods. For testing purpose add the following
     lines of code   in    Portlet under doView() method

public void doView(RenderRequest renderRequest,
            RenderResponse renderResponse) throws IOException, PortletException {
         try {
            List<Books> books = BooksLocalServiceUtil.findBook("es");
            System.out.println("::::::::::"+books.size());
            for(Books bookName : books){
                String book = bookName.getTitle();
                System.out.println("####"+book);

            }
        } catch (PortalException e) {
            e.printStackTrace();
        } catch (SystemException e) {
            e.printStackTrace();
        }

        super.doView(renderRequest, renderResponse);
       
    }

That's it enjoy!!!