« Back to Queries

Custom Queries in EXT

What’s custom query?#

There is a new mechanism which allows creating dynamic criteria – dynamic query API. In many situations this avoids the need to create a custom query completely. But in some cases, custom query (or called custom-sql) is still useful – where there is complex JOIN query and it is hard to do so in Dynamic query API.

When do you need custom query?#

You should not use custom query when Dynamic query API can do. That is, sometimes Dynamic query API still isn’t enough and thus we need a custom query.

How to build custom query in the Ext?#

Considering a scenario: getting count of Reports Entries by user ID.

Of course, you can use Dynamic query API. Here we use this request as an example – show how to build custom query in the Ext.

In ten steps, you should be able to build custom query in the Ext.

1) Preparation (Using MySQL as an example):

  • Update service.xml, set:

local-service="true"; 

  • Add following lines in porta-ext.properties:

## MySQL

jdbc.default.driverClassName=com.mysql.jdbc.Driver jdbc.default.url=jdbc:mysql://localhost/liferay?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false jdbc.default.username=liferay jdbc.default.password=liferay}}}

  • create an account in database:

drop database if exists liferay;
create database liferay character set utf8; grant all on liferay. to 'liferay'@'localhost' identified by 'liferay' with grant option; grant all on liferay. to 'liferay'@'localhost.localdomain' identified by }}}

2) Create a file default-ext.xml under $EXT_HOME/ext-impl/src/custom-sql (Cerate custom-sql folder if it is not there) with following lines

<?xml version="1.0"?>

<custom-sql> <sql file="custom-sql/custom.xml" /> </custom-sql>}}}

3) Create a file custom.xml under $EXT_HOME/ext-impl/src/custom-sql with following lines:

<?xml version="1.0"?>

<custom-sql> <sql id="com.ext.portlet.reports.service.persistence.ReportsEntryFinder.countByUserId"> <![CDATA[ SELECT COUNT() AS COUNT_VALUE FROM ReportsEntry WHERE ReportsEntry.userId = ? ]]> </sql> </custom-sql>}}} Note that the above code is just sample code. You should write your own custom query.

4) In portal-ext.properties, add following lines:

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

5) Create the file com.ext.portlet.reports.service.persistence.ReportsEntryFinderImpl.java under the folder $EXT_HOME/ext-impl/src and add following lines.

public class ReportsEntryFinderImpl
	extends BasePersistenceImpl implements ReportsEntryFinder {
}

6) Do ant build-service-reports-portlet, so that the portal will generate the necessary interface.

7) Add actual logic to access the custom query on com.ext.portlet.reports.service.persistence.ReportsEntryFinderImpl.java.

package com.ext.portlet.reports.service.persistence;

import com.liferay.portal.SystemException;
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 java.util.LinkedHashMap;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class ReportsEntryFinderImpl
	extends BasePersistenceImpl implements ReportsEntryFinder {
	
	public static String COUNT_BY_USER_ID =
		ReportsEntryFinder.class.getName() + ".countByUserId";
	
	public int countByUserId(long userId) throws SystemException {
		LinkedHashMap<String, Object> params1 =
			new LinkedHashMap<String, Object>();
		
		Session session = null;

		try {
			session = openSession();

			int count = countByUserId(session, userId, params1);

			return count;
		}
		catch (Exception e) {
			throw new SystemException(e);
		}
		finally {
			closeSession(session);
		}
	}
	
	protected int countByUserId(
		Session session, long userId, LinkedHashMap<String, Object> params) {

		String sql = CustomSQLUtil.get(COUNT_BY_USER_ID);
		_log.info("SQL: " + sql);
		SQLQuery q = session.createSQLQuery(sql);

		QueryPos qPos = QueryPos.getInstance(q);
		
		qPos.add(userId);
		
		int count = q.list().size();
		
		return count;
	}
	
	private static Log _log = LogFactory.getLog(ReportsEntryFinderImpl.class);
}

Note that the above code is just sample code. You should write your own logic.

8) Create com.ext.portlet.reports.service.impl.RerportEntryLocalServiceImpl.java with a method under $EXT_HOME/ext-impl/src as follows

public int getCountByUserId(long userId) throws com.liferay.portal.SystemException {
	return ReportsEntryFinderUtil.countByUserId(userId);
}

9) Do ant build-service-reports-portlet again, so that the portal will generate the necessary interface with parameters.

10) Add one line in view.jsp under $ext-web/docroot/html/portlet/ext/reports (for testing purpose).

<p>Count: <%= ReportsEntryLocalServiceUtil.getCountByUserId(themeDisplay.getUserId()) %></p>

0 Attachments
50142 Views
Average (0 Votes)
The average rating is 0.0 stars out of 5.
Comments
Threaded Replies Author Date
Xlent feature... zaheer mohammed saddapalli September 2, 2009 4:30 AM
Good Article but could not make it work.... Nikhil Kumar Gupta September 16, 2009 9:33 PM
I too could not get this to work du to error: ... Greg Smith November 19, 2009 9:01 PM
I eventually got this to work. Originally I... Greg Smith December 6, 2009 2:14 PM
6) Do ant build-service-reports-portlet, so... kan kan August 22, 2010 1:22 AM
from the build file under ext-impl, there is a... Maged Makled August 26, 2010 8:51 AM

Xlent feature...
Posted on 9/2/09 4:30 AM.
Good Article but could not make it work.
Dynamicquery is working
Posted on 9/16/09 9:33 PM.
I too could not get this to work du to error:

04:52:00,651 ERROR [PortletBeanLocatorUtil:49] BeanLocator is null
com.liferay.portal.kernel.bean.BeanLocatorException: BeanLocator has not been set
at com.liferay.portal.kernel.bean.PortletBeanLocatorUtil.locate(PortletBeanLocatorU­til.java:51)
Posted on 11/19/09 9:01 PM.
I eventually got this to work. Originally I added my finder to a new entity created with no columns. This was because I wanted a custom query to do Oracle queries that Hibernate couldn't, and do them on data not persisted via Hibernate. I couldn't get the finder to work when the custom query was via a finder on this entity. So I went back to the start and instead tacked the finder onto a existing service-builder entity , and it worked. The data being returned via the finder is unrelated to the service-builder entity that I use to drive it, but at least it works now.
Posted on 12/6/09 2:14 PM in reply to Greg Smith.
6) Do ant build-service-reports-portlet, so that the portal will generate the necessary interface.
how to do?
Posted on 8/22/10 1:22 AM.
from the build file under ext-impl, there is a target with that name
Posted on 8/26/10 8:51 AM in reply to kan kan.