Site icon Pro Liferay

Liferay Custom SQL Example

Liferay-Custom-Query-Example


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.


Before 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

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

https://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.  

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

Exit mobile version