[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.

We have another entity Author as well.

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.

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

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.

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.

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.

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.

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