« Back to Development (Legacy)

Custom Queries in Liferay (Legacy)

(Redirected from How to create a custom query in liferay)

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

0 Attachments
66066 Views
Average (0 Votes)
The average rating is 0.0 stars out of 5.
Comments
Threaded Replies Author Date
HibernatenateUtil.openSession() is no more in... Ashish kumar sinha February 16, 2009 11:29 PM
You may not need this anymore.... But just in... Alex Wallace June 2, 2009 7:19 AM
Don't we need to update MBMessageFinder.java,... Secret Developer September 22, 2009 8:16 AM
Hi, I am new to Liferay. I want to call and... Sunetra Chavan December 13, 2010 3:45 AM
Sunetra: Did you ever get this working? Scott Rabon January 31, 2011 4:48 PM
You can have more details about custom query on... Jignesh Vachhani March 19, 2012 4:27 AM

HibernatenateUtil.openSession() is no more in Liferay 5.1.

HOw to get session object in liferay 5.1? executing my custom query throws

<exception>
Caused by: org.hibernate.HibernateException: No Hibernate Session bound to thread, and configuration does not allow creation of non-transactional one here
</exception>

I tried to reuse "AnnouncmentsEntryFinderImpl" to execute, but error is same.

Tried to create my own "ExtFinderLocalServiceImpl" using blank entity "ExtFinder" has no success.

please suggest
Posted on 2/16/09 11:29 PM.
You may not need this anymore.... But just in case:

I make my finders extend a custom class that in turn extends BasePersistenceImpl ... In the constructor of my custom class, i do:

setSessionFactory((SessionFactory)PortalBeanLocatorUtil.getBeanLocator().loca­te(LayoutModelImpl.SESSION_FACTORY));

From then on you can do, in your finder:

myFinder.openSession();

and use it as usual.

Be aware that this only works if there is a bound session in your context so you must calll your finder from a *LocalServiceImpl or *ServiceImpl.

Hope this helps.
Posted on 6/2/09 7:19 AM in reply to Ashish kumar sinha.
Don't we need to update MBMessageFinder.java, MBMessageFinderUtil.java, and MBMessageFinderImpl.java ?

When I look into source code, that's the structure.
Posted on 9/22/09 8:16 AM.
Hi,

I am new to Liferay. I want to call and execute the custom query in my liferay application.

I am using Liferay 6.0 with MySQL 5.2 version. Also I am not using hibernet in my application.

I have done following step to implement the same.

1. Added custom.sql.configs=custom-sql/default.xml,custom-sql/default-ext.xml line in portal-ext.properties file.

2. Created custom-sql folder in src. Created default-ext.xml file in that folder with below code.
<custom-sql>
<sql file="custom-sql/test_custom.xml" />
</custom-sql>

3. Created test_custom.xml file in custom-sql folder in src. Added below code in that.
<?xml version="1.0"?>
<custom-sql>
<sql
id="com.test.service.persistence.QuestionEntryFinder.getCountByUserId"> <![CDATA[
SELECT
COUNT(*) AS COUNT_VALUE
FROM
elsevier_question
WHERE
elsevier_question.user = ?
]]>
</sql>
</custom-sql>

4. Entity Question is already created, so all java files are generated for Question Entity.

<entity name="Question" local-service="true" remote-service="false">
<column name="id" type="long" primary="true"></column>
<column name="question" type="String"></column>
<column name="date" type="Date"></column>
<column name="user" type="long"></column>
</entity>

I have manually created QuestionEntryFinder.java interface and QuestionEntryFinderImpl.java implementation class with method getCountByUserId() in com.test.service.persistence package. This package is already created when the service is build for Question entity.

5. Java code for Interface:

package com.test.service.persistence;

import com.liferay.portal.kernel.exception.SystemException;

public interface QuestionEntryFinder {
public int getCountByUserId(long userId) throws SystemException;
}


6. Java code for implementation class:

package com.test.service.persistence;

import java.util.LinkedHashMap;


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.kernel.exception.SystemException;
import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
import com.liferay.util.dao.orm.CustomSQLUtil;
import com.test.model.Question;

public class QuestionEntryFinderImpl extends BasePersistenceImpl<Question> implements QuestionEntryFinder {

public static String COUNT_BY_USER_ID = QuestionEntryFinder.class.getName() + ".getCountByUserId";


public int getCountByUserId(long userId) throws SystemException {

System.out.println("******** inside getCountByUserId *************");

System.out.println("COUNT_BY_USER_ID = "+COUNT_BY_USER_ID);

LinkedHashMap<String, Object> params1 = new LinkedHashMap<String, Object>();
System.out.println("param1 = "+params1);


//System.out.println("open = "+openSession());

System.out.println("Test2*******");

Session session = null;
try {
session = openSession();

System.out.println("session = "+session);

String sql = CustomSQLUtil.get(COUNT_BY_USER_ID);
System.out.println("sql = "+sql);

SQLQuery q = session.createSQLQuery(sql);
System.out.println("sqlQurey = "+q);

QueryPos qPos = QueryPos.getInstance(q);
System.out.println("qPos = "+qPos);

qPos.add(userId);

int count = q.list().size();
System.out.println("count *** "+count);

return count;
}
catch (Exception e) {
throw new SystemException(e);
}
finally {
closeSession(session);
}
}

}


If I instancetiate the impl class and trying to call getCountByUserId(), then openSession() method of BasePersistenceImpl class gives me the NullPointerException.

I have tried call it like this. QuestionLocalServiceUtil.getCountByUserId(1);

But not able to get the method under QuestionLocalServiceUtil class.

So how I am suppose to call my function getCountByUserId() from my QuestionEntryFinderImpl class. Please help me in that.

Thanks in advance.

Sunetra
Posted on 12/13/10 3:45 AM.
Sunetra:
Did you ever get this working?
Posted on 1/31/11 4:48 PM in reply to Sunetra Chavan.
You can have more details about custom query on http://www.liferaysolution.com/2012/03/custome-query.html as well
Posted on 3/19/12 4:27 AM.