[Liferay] Writing Custom Sql for multiple tables in Liferay

Hi Friends,

There were a number of times you might have faced a situation when you need data from multiple tables. Sometimes you would have got the data from one service, extract some data and then call another service, extract some more data and do the processing by writing some logic. Well, this has a major impact on performance and that's not good.

That's not good choice because you can write your own custom SQL query and get the desired result. The query we are writing, we always have a chance to optimize it and boost/improve performance. Alright, enough of the lecture.. lets get to work.



We're going to take a very simple example of a Library system. We have an entity Book as below.

	<entity name="Book" uuid="true" local-service="true" remote-service="true">
	<!-- PK fields -->
	<column name="bookId" type="long" primary="true" />
	<!-- Group instance -->
	<column name="groupId" type="long" />
	<!-- Audit fields -->
	<column name="companyId" type="long" />
	<column name="userId" type="long" />
	<column name="userName" type="String" />
	<column name="createDate" type="Date" />
	<column name="modifiedDate" type="Date" />
	<!-- Other fields -->
	<column name="title" type="String" />
	<column name="pages" type="int" />
	<column name="authorId" type="long" />
	</entity>

view raw Book.xml hosted with ❤ by GitHub

We have another entity Author as well.

	<entity name="Author" uuid="true" local-service="true" remote-service="true">
	<!-- PK fields -->
	<column name="authorId" type="long" primary="true" />
	<!-- Group instance -->
	<column name="groupId" type="long" />
	<!-- Audit fields -->
	<column name="companyId" type="long" />
	<column name="userId" type="long" />
	<column name="userName" type="String" />
	<column name="createDate" type="Date" />
	<column name="modifiedDate" type="Date" />
	<!-- Other fields -->
	<column name="firstName" type="String" />
	<column name="lastName" type="String" />
	</entity>

view raw Author.xml hosted with ❤ by GitHub

Now we need all the books and authors (combined) by author name and title of the book. There can be multiple authors for multiple books. Many to many relationships. Don't you worry about relationships for now, that's not our topic of concentration here.. is that? So the SQL we want is like this.

	SELECT
	*
	FROM
	RKG_Book, RKG_Author
	WHERE
	( RKG_Author.firstName LIKE ? OR RKG_Author.lastName LIKE ? ) AND RKG_Book.title Like ?

view raw book_author.sql hosted with ❤ by GitHub

RKG_Book, RKG_Author are our table names generated by Liferay. For reference, complete service.xml file is as below.

	<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.1.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_6_1_0.dtd">
	<service-builder package-path="me.rkg.plugins">
	<namespace>RKG</namespace>
	<entity name="Book" uuid="true" local-service="true" remote-service="true">
	<!-- PK fields -->
	<column name="bookId" type="long" primary="true" />
	<!-- Group instance -->
	<column name="groupId" type="long" />
	<!-- Audit fields -->
	<column name="companyId" type="long" />
	<column name="userId" type="long" />
	<column name="userName" type="String" />
	<column name="createDate" type="Date" />
	<column name="modifiedDate" type="Date" />
	<!-- Other fields -->
	<column name="title" type="String" />
	<column name="pages" type="int" />
	<column name="authorId" type="long" />
	</entity>
	<entity name="Author"uuid="true" local-service="true" remote-service="true">
	<!-- PK fields -->
	<column name="authorId" type="long" primary="true" />
	<!-- Group instance -->
	<column name="groupId" type="long" />
	<!-- Audit fields -->
	<column name="companyId" type="long" />
	<column name="userId" type="long" />
	<column name="userName" type="String" />
	<column name="createDate" type="Date" />
	<column name="modifiedDate" type="Date" />
	<!-- Other fields -->
	<column name="firstName" type="String" />
	<column name="lastName" type="String" />
	</entity>
	</service-builder>

view raw service.xml hosted with ❤ by GitHub

Now when we have required things in place, lets build-service. If you're using maven, run
mvn clean liferay:build-service

Or if you're using plugins-sdk, run
ant clean build-service

Now when you're finished building your service, lets go step by step for writing the other classes and code required to perform custom SQL.

Step 1. Add one more entity to your service.xml
<entity name="Library" local-service="true" remote-service="true"></entity>

This is a blank entity, this will create service classes for us but there won't be any fields, methods etc. We will use this service to provide our finder methods. After adding the entity, build service again.

Step 2. Create a LibraryFinderImpl class in service/persistence. If you used the service xml mentioned above, the package for this class would be me.rkg.plugins.service.persistence.

Your class would be like this.

	package me.rkg.plugins.service.persistence;
	import java.util.List;
	import com.liferay.portal.kernel.log.Log;
	import com.liferay.portal.kernel.log.LogFactoryUtil;
	import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
	public class LibraryFinderImpl extends BasePersistenceImpl{
	public List<Object[]> findByAuthorAndTitle(String authorName, String title, int begin, int end){
	return null;
	}
	private static final Log LOGGER = LogFactoryUtil.getLog(LibraryFinderImpl.class);
	}

view raw LibraryFinderImpl.java hosted with ❤ by GitHub

Now run build-service again. Remember if you do not have a method in finderImpl, a finder interface would not be created. A finder would be created only for entities. We created an empty entity which we are using for our custom finders.

After the service is built, check in LibraryLocalServiceImpl, libraryFinder variable must be present. If yes, lets move to next step.

Step 3. Create a folder named custom-sql in src folder and create a default.xml file inside. We need to put our sql in this file and Finder will use it to get data later on. So this is how our default.xml file looks like.

	<?xml version="1.0" encoding="UTF-8"?>
	<custom-sql>
	<sql id="me.rkg.plugins.service.persistence.LibraryFinder.findByAuthorAndTitle">
	<![CDATA[
	SELECT
	*
	FROM
	RKG_Book, RKG_Author
	WHERE
	( RKG_Author.firstName LIKE ? OR RKG_Author.lastName LIKE ? ) AND RKG_Book.title Like ?
	]]>
	</sql>
	</custom-sql>

view raw default.xml hosted with ❤ by GitHub

Step 4. Update the finderImpl to implement LibraryFinder interface generated by Liferay service builder. Also add the required code to run the query. See the final finderImpl below.

	package me.rkg.plugins.service.persistence;
	import java.util.List;
	import me.rkg.plugins.model.impl.AuthorImpl;
	import me.rkg.plugins.model.impl.BookImpl;
	import com.liferay.portal.kernel.dao.orm.QueryPos;
	import com.liferay.portal.kernel.dao.orm.QueryUtil;
	import com.liferay.portal.kernel.dao.orm.SQLQuery;
	import com.liferay.portal.kernel.dao.orm.Session;
	import com.liferay.portal.kernel.log.Log;
	import com.liferay.portal.kernel.log.LogFactoryUtil;
	import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
	import com.liferay.util.dao.orm.CustomSQLUtil;
	public class LibraryFinderImpl extends BasePersistenceImpl implements LibraryFinder{
	public List<Object[]> findByAuthorAndTitle(String authorName, String title, int begin, int end){
	Session session = null;
	try {
	session = openSession();
	String sql = CustomSQLUtil.get(
	FIND_BY_AUTHOR_TITLE);
	LOGGER.info("Query Is : "+ sql);
	SQLQuery q = session.createSQLQuery(sql);
	q.setCacheable(false);
	// add only those entities from where you need the final data.
	q.addEntity("RKG_Book", BookImpl.class);
	q.addEntity("RKG_Author", AuthorImpl.class);
	QueryPos qPos = QueryPos.getInstance(q);
	qPos.add("%"+authorName+"%");
	qPos.add("%"+authorName+"%");
	qPos.add("%"+title+"%");
	LOGGER.info("authorName : "+ authorName);
	LOGGER.info("title : "+ title);
	// QueryUtil will return a list of Objects in case of data is of only one table.
	// In case you are trying to get data from multiple tables, array of Objects will be returned.
	List<Object[]> bookdetails = (List<Object[]>) QueryUtil.list(q, getDialect(), begin, end);
	LOGGER.info("size of bookdetails : "+bookdetails.size());
	return bookdetails;
	} catch (Exception e) {
	LOGGER.error(e.getMessage());
	} finally {
	closeSession(session);
	}
	return null;
	}
	public static final String FIND_BY_AUTHOR_TITLE=
	LibraryFinder.class.getName() +
	".findByAuthorAndTitle";
	private static final Log LOGGER = LogFactoryUtil.getLog(LibraryFinderImpl.class);
	}

view raw LibraryFinderImpl.java hosted with ❤ by GitHub

Step 5. CustomSQLUtil will get the right query by id provided. This is the same id we used in default.xml for our sql query. This is needed, because there can be multiple sql queries.

Step 6. Now after all this, you can call the finder in your serviceImpl. See one sample method below in LibraryLocalServiceImpl to get details. Remember to use libraryFinder variable only to access finder in your service.

	package me.rkg.plugins.service.impl;
	import java.util.List;
	import me.rkg.plugins.model.Author;
	import me.rkg.plugins.model.Book;
	import me.rkg.plugins.service.base.LibraryLocalServiceBaseImpl;
	/**
	* The implementation of the library 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 me.rkg.plugins.service.LibraryLocalService} 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 Ravi Kumar Gupta
	* @see me.rkg.plugins.service.base.LibraryLocalServiceBaseImpl
	* @see me.rkg.plugins.service.LibraryLocalServiceUtil
	*/
	public class LibraryLocalServiceImpl extends LibraryLocalServiceBaseImpl {
	/*
	* NOTE FOR DEVELOPERS:
	*
	* Never reference this interface directly. Always use {@link me.rkg.plugins.service.LibraryLocalServiceUtil} to access the library local service.
	*/
	public List<Object> getBookDetailsByAuthorAndTitle(String authorName, String title){
	List<Object[]> objects = libraryFinder.findByAuthorAndTitle(authorName, title, -1, -1);
	for(Object[] objs : objects){
	System.out.println(objs.length);
	for(int i=0;i<objs.length;i++){
	if(objs[i] instanceof Book){
	System.out.println("Object is instance of Book");
	}
	if(objs[i] instanceof Author){
	System.out.println("Object is instance of Author");
	}
	}
	}
	return null;
	}
	}

view raw LibraryLocalServiceImpl.java hosted with ❤ by GitHub

Some Explanations:

• We use q.addEntity(..) method for all the entities which will participate in output after running query. In our case, firstName, lastName of author and title from book. This makes us adding both Book and Author entities in the SQLQuery. If we had more entities participating, we would have added them as well. 
• We have added, % in QueryPos.add(..) methods because we need a like search on the fields. Simple SQL, right?
• QueryUtil.list(..) will always return the List<Object[]> or List<Object>. When we have more than one entity participating in output, we will get List<Object[]> and the Object[] array will have length exactly as the number of entities. In case there was only one entity, the List<Object> will be returned. 
• We can loop through the List<Object[]> and check which object is of what type and extract the desired result. Just like we did in LocalServiceImpl.

Hope you enjoyed the long post.. :)

Until next time..