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.
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.
3. Write 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.