
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>