• Liferay Custom SQL Example

    Posted on June 14, 2015 by Hamidul Islam in Liferay.

    Liferay-Custom-Query-Example


    aim

    We know Liferay service builder tool which generate all our necessary stuffs to interact with database. By Liferay Custom SQL we can query database by writing raw SQL query. You might be thinking of Liferay Finder to fetch data. But it has some limitations. If we need to aggregate data from from different tables then Liferay Custom Query would be very handy. Liferay Custom Query is nothing but native query. For example we are writing custom query for oracle but it may not work for MySQL.  Liferay Custom Query is very easy to use by means of using XML file where we write our SQL Query. This article explains in details about how to create Liferay Custom Query in our custom portlet.


    NoteBefore starting the topic I am assuming that you have basic knowledge of Liferay Service builder tool. If you are new to Liferay Service builder then it would be better to understand the concept of Liferay Service Builder first and then learn how to build Lifery Custom SQL. Follow the below links to know Liferay Service Builder

    http://proliferay.com/an-introduction-to-liferay-service-builder/ 

    http://proliferay.com/crud-operations-in-liferay-portlet/


    I am considering the below service.xml file for explaining Liferay Custom SQL

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.2.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_6_2_0.dtd">
    <service-builder package-path="com.proliferay.sbuilder.example.customquery">
    	<author>Hamidul Islam</author>
    	<namespace>Customquery</namespace>
    	<entity name="Book" table="CUSTOMQUERY_BOOK_PORTLET" local-service="true" remote-service="true">
    		<column name="bookId" type="long" primary="true" id-type="increment"/>
    		<column name="bookName" type="String" />
    		<column name="description" type="String" />
    		<column name="authorName" type="String" />
    		<column name="isbn" type="int" />
    		<column name="price" type="int" />
    	</entity>
    </service-builder>
    

    Note:

    The main purpose of Liferay Custom SQL is to execute our own hand written query to fetch data from one table or many tables. 


    Now follow some few steps to implement Liferay Custom Query...

    1. First inform Liferay from where to pick Custom SQL

    Liferay should know the location of our custom query. In src folder of our portlet we need to create a new directory called custom-sql. Under custom-sql folder we should create one new file called default.xml file. In default.xml file we can write our all the custom query. But its better to write custom queries in separate files if number of custom queries are more. The content of default.xml is 

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

    Which means that book.xml is another file where our custom queries are available. Download the source code at the end of this article. 

    2. Write the actual finder class 

    Have a look into the service.xml shown in the above. We write finder class based on service.xml. Initially BookFinderImpl class is created with below content.  

    package com.proliferay.sbuilder.example.customquery.service.persistence;
    
    import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
    import com.proliferay.sbuilder.example.customquery.model.Book;
    
    public class BookFinderImpl extends BasePersistenceImpl<Book> implements
    		BookFinder {
    
    }
    

    Note: BookFinderImpl class implements BookFinder. BookFinder interface will be generated only after building the service.  

    liferay-custom-query-file-creation

    3Write  some SQL in book.xml file 

    <?xml version="1.0" encoding="UTF-8"?>
    <custom-sql>
    	<sql id="com.proliferay.sbuilder.example.customquery.service.persistence.BookFinderImpl.findBooksByPrice">
    
    			<![CDATA[
    				SELECT
    				    *
    				FROM
    				    CUSTOMQUERY_BOOK_PORTLET
    				WHERE
    				    price = ?
    			]]>
    	</sql>
    </custom-sql>
    

    Note: The ? mark in the above SQL is dynamic. We can execute this query from BookFinderImpl class passing dynamic value for the ? mark.

    4. Write  a new method in BookFinderImpl to execute the query

    package com.proliferay.sbuilder.example.customquery.service.persistence;
    
    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.service.persistence.impl.BasePersistenceImpl;
    import com.liferay.util.dao.orm.CustomSQLUtil;
    import com.proliferay.sbuilder.example.customquery.model.Book;
    import com.proliferay.sbuilder.example.customquery.model.impl.BookImpl;
    
    public class BookFinderImpl extends BasePersistenceImpl<Book> implements
    		BookFinder {
    
    	private String FIND_BOOKS = BookFinderImpl.class.getName()
    			+ ".findBooksByPrice";
    
    	public List<Book> findBooksByPrice(int price) {
    
    		// 1. Open an ORM session
    		Session session = openSession();
    
    		/**
    		 * 2. Get SQL statement from XML file with its name
    		 *
    		 * FIND_BOOKS is actually equal to
    		 * com.proliferay.sbuilder.example.customquery
    		 * .service.persistence.BookFinderImpl.findBooksByPrice
    		 */
    
    		String sql = CustomSQLUtil.get(FIND_BOOKS);
    
    		// 3. Transform the normal query to HQL query
    		SQLQuery query = session.createSQLQuery(sql);
    
    		// 4. Add the actual entity to be searched
    		query.addEntity("Book", BookImpl.class);
    
    		// 5. Replace positional parameters in the query
    		QueryPos qPos = QueryPos.getInstance(query);
    		qPos.add(price);
    
    		// 6. Execute query and return results.
    		return (List<Book>) query.list();
    	}
    }
    

    5. Write  a new method in BookLocalServiceImpl  and call the above method. Hit ant build-service to generate the service again

    package com.proliferay.sbuilder.example.customquery.service.impl;
    
    import java.util.List;
    
    import com.proliferay.sbuilder.example.customquery.model.Book;
    import com.proliferay.sbuilder.example.customquery.service.base.BookLocalServiceBaseImpl;
    import com.proliferay.sbuilder.example.customquery.service.persistence.BookFinderUtil;
    
    /**
     * The implementation of the book local service.
     *
     * <p>
     * All custom service methods should be put in this class. Whenever methods are
     * added, rerun ServiceBuilder to copy their definitions into the
     * {@link com.proliferay.sbuilder.example.customquery.service.BookLocalService}
     * interface.
     *
     * <p>
     * This is a local service. Methods of this service will not have security
     * checks based on the propagated JAAS credentials because this service can only
     * be accessed from within the same VM.
     * </p>
     *
     * @author Hamidul Islam
     * @see com.proliferay.sbuilder.example.customquery.service.base.BookLocalServiceBaseImpl
     * @see com.proliferay.sbuilder.example.customquery.service.BookLocalServiceUtil
     */
    public class BookLocalServiceImpl extends BookLocalServiceBaseImpl {
    	/*
    	 * NOTE FOR DEVELOPERS:
    	 *
    	 * Never reference this interface directly. Always use {@link
    	 * com.proliferay.sbuilder.example.customquery.service.BookLocalServiceUtil}
    	 * to access the book local service.
    	 */
    
    	public List<Book> getBooksByPrice(int price) {
    		return BookFinderUtil.findBooksByPrice(price);
    	}
    }
    

    6. Now we are ready to call getBooksByPrice method like 

    List<Book> books = BookLocalServiceUtil.getBooksByPrice(123);
    

    Check the method using it in your any JSP of the portlet. 

    Summery: 

    In this article we have discussed only the basic concept of Liferay Custom SQL. We will discuss some advance topic in another article. 

    Download Source Code

One Response so far.

  1. Alexandre Mélard says:

    Hi, thanks for that nice tutorial, I would like to call the **BookLocalServiceUtil.getBooksByPrice(123);** service from another portlet, I tried to add the following to the portlet using the service:

    required-deployment-contexts=mf3-plugin-service-portlet

    The jar file of the portlet is added to the WEB_INF/lib directory, but when calling the methode, I get a NoSuchBean Exception.

    Woould be glad if you could assit me on this

Leave a Reply

Your email address will not be published. Required fields are marked *

Are you human? * Time limit is exhausted. Please reload CAPTCHA.

Top
%d bloggers like this:

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close