Custom Queries in Liferay #

Service Builder and service.xml will take care of many of our most basic needs in querying the database.

• .get[FieldName]() and .set[FieldName](..) methods for each field
• .findByPrimaryKey(..), .fetchByPrimaryKey(...) and other methods for other fields marked as primary keys
• .findBy[FieldName](...) methods when we write [finder tags]

Sometimes that still isnt enough and we need a custom query. To learn how this is done, we will look at the custom queries in the Message Board Portlet.

Note for users of Liferay v4.2: in 4.2, the portal-impl directory was called portal-ejb. Whenever you see a patch from now on in this article be aware of that when looking for the files mentioned.

There are 3 files that we will be working with

• portal/portal-impl/classes/custom-sql/MessageBoards.xml
• portal/portal-impl/src/com/liferay/portlet/messageboards/service/persistence/MBMessageFinder.java
• portal/portal-impl/classes/custom-sql/Default.xml

Defining your custom query #

It is a good practice to separate your queries from your code. It also just makes your queries easier to find and edit. But if you wanted, you could skip this step and directly write your query into your code.

When not to create a custom query #

Starting with Liferay version 4.3 there is a new mechanism which allows creating dynamic criteria. In many situation this avoids the need to create a custom query completely. For more information read Queries 2: DynamicQuery API

Step 1) Understand Liferay's query finders #

The default.xml (portal/portal-impl/classes/custom-sql/default.xml) file lets Liferay know where to look for custom queries. This is simply a listing of all XML files which contain custom queries. If you are creating a new portlet, make sure you add an entry in this file pointing to your custom query xml file so that Liferay knows to load your custom query file.

<?xml version="1.0"?>

<custom-sql>
	<sql file="custom-sql/portal.xml" />
	<sql file="custom-sql/blogs.xml" />
	<sql file="custom-sql/bookmarks.xml" />
	<sql file="custom-sql/calendar.xml" />
	<sql file="custom-sql/documentlibrary.xml" />
	<sql file="custom-sql/imagegallery.xml" />
	<sql file="custom-sql/journal.xml" />
	<sql file="custom-sql/messageboards.xml" />
	<sql file="custom-sql/permission.xml" />
	<sql file="custom-sql/shopping.xml" />
	<sql file="custom-sql/wiki.xml" />
	<sql file="custom-sql/workflow.xml" />
</custom-sql>

For development in the EXT environment, instead of directly editing the default.xml file, create or edit a default-ext.xml file in the ext environment (ext/ext-ejb/classes/custom-sql/default-ext.xml), and then add an entry in your portal-ext.properties file:

portal-ext.properties (ext/ext-ejb/classes/portal-ext.properties)

custom.sql.configs=custom-sql/default.xml,custom-sql/default-ext.xml

Step 2) Find the XML files for the queries of the desired portelt #

The messageBoards.xml (portal/portal-impl/classes/custom-sql/messageBoards.xml) file stores a listing of your custom SQL queries. These queries will be pulled out as strings using the CustomSQLUtil. To pull out this particular query, our code will look like:

String sql = CustomSQLUtil.get(FIND_BY_GROUP_ID);

<?xml version="1.0"?>

<custom-sql>
	<sql id="com.liferay.portlet.messageboards.service.persistence.MBMessageFinder.findByGroupId">
		<![CDATA[
			SELECT
				{MBMessage.*}
			FROM
				MBMessage
			INNER JOIN
				MBCategory ON
					(MBCategory.categoryId = MBMessage.categoryId)
			WHERE
				(MBCategory.groupId = ?)
		]]>
	</sql>
</custom-sql>

Step 4) Update the Finder Java class #

In the MBMessageFinder.java (portlet/messageboards/service/persistence/MBMessageFinder.java) class the query is executed and the return data retrieved.

package com.liferay.portlet.messageboards.service.persistence;

import com.liferay.portal.SystemException;
import com.liferay.portal.spring.hibernate.CustomSQLUtil;
import com.liferay.portal.spring.hibernate.HibernateUtil;
import com.liferay.portlet.messageboards.model.impl.MBMessageImpl;
import com.liferay.util.StringUtil;
import com.liferay.util.dao.hibernate.QueryPos;
import com.liferay.util.dao.hibernate.QueryUtil;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.Session;

public class MBMessageFinder {

	public static String FIND_BY_GROUP_ID =
		MBMessageFinder.class.getName() + ".findByGroupId";

	public static List findByGroupId(String groupId, int begin, int end)
		throws SystemException {

		Session session = null;

		try {
			//open a new session
			session = HibernateUtil.openSession();

			//pull out our query from MessageBoards.xml, make sure you have added an entry in the default.xml file
			String sql = CustomSQLUtil.get(FIND_BY_GROUP_ID);

			//create a SQLQuery object
			SQLQuery q = session.createSQLQuery(sql);

			q.setCacheable(false);

			//replace the "MBMessage" in the query string with the fully qualified java class.. this has to be the hibernate table name
			q.addEntity("MBMessage", MBMessageImpl.class);

			QueryPos qPos = QueryPos.getInstance(q);

			//fill in the "?" value of the custom query
			qPos.add(groupId);

			//execute the query and return a list from the db
			return QueryUtil.list(q, HibernateUtil.getDialect(), begin, end);
		}
		catch (Exception e) {
			throw new SystemException(e);
		}
		finally {
			//must have this to close the hibernate session.. if you fail to do this.. you will have alot of open session.. 
			HibernateUtil.closeSession(session);
		}
	}

If you are Connecting to Another Datasource/Database. Need two extra steps:

1. Go to ext-spring-training.xml and get the id trainingSessionFactory or what you name it.
2. Whith this id, open a new hibernate session on the code above, refering to the another database, like this:

                        //open a new session
			session = HibernateUtil.openSession(&trainingSessionFactory);

Using the custom query #

When these files have been set up, you can then use the function "findByGroupId(...)" in your [EntityName]LocalServiceImpl file.

MBMessageLocalServiceImpl.java (portal/portal-impl/src/com/liferay/portlet/messageboards/service/impl/**MBMessageLocalServiceImpl.java**)#

public List getGroupMessages(String groupId, int begin, int end) throws SystemException {

return MBMessageFinder.findByGroupId(groupId, begin, end); }

Related Articles #

How To's