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;

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>