Liferay custom sql example

Liferay out of the box, provides below ways to access Database:

  • Service Builder Finder methods
  • Database Access API : It provides JDBC  mechanism to write SQL queries but extra burden to write POJO mappings
  • Dynamic Query : Dynamic queries are recommended for simple queries
  • Custom SQL Queries : Liferay custom SQL queries are used to write some complex queries  such joins for multiple tables etc.

Custom SQL in liferay is robust and easy to use and below are steps to write Custom SQL queries.

  1. Define custom SQL queries in default.xml
  2. Create custom  Finder Util with {EntityName}FinderImpl.java in persistence package
  3.  Access Custom Finder Util in LocaServiceImpl class

In this tutorial, we will see from scratch on creating leave system portlet

Step1:  Create New Portlet

In Liferay IDE, create new plugin “leave-system-portlet” and uncheck “include sample code” and create portlet separate portlet liferay-portlet-example1

new-portlet-creation

Step2: Create Service Builder In Portlet plugin

  • Create service.xml file in File->New->ServiceBuilder
  • Run the Service Builder Target
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.2.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_6_2_0.dtd">
<service-builder package-path="org.javasavvy.leave">
 <author>jay</author>
 <namespace>js</namespace>
 <entity name="Leave" local-service="true" remote-service="false">
 <!-- PK fields -->
 <column name="leaveId" type="long" primary="true" />
 <!-- Group instance -->
 <column name="groupId" type="long" />
 <!-- Audit fields -->
 <column name="companyId" type="long" />
 <column name="userName" type="String" />
 <column name="createDate" type="Date" />
 <column name="modifiedDate" type="Date" />
 <!-- Other fields -->
 <column name="leaveName" type="String" />
 <column name="leaveTypeId" type="long" />
 <column name="startDate" type="Date" />
 <column name="endDate" type="Date" />
 <column name="userId" type="long" />
  <!-- Order -->
 <order by="asc">
 <order-column name="createDate" />
 </order>
 <!-- Finder methods -->
 <finder name="UserId" return-type="Collection">
 <finder-column name="userId" />
 </finder>
 <finder name="CompanyId" return-type="Collection">
 <finder-column name="companyId" />
 </finder>
 <finder name="GroupId" return-type="Collection">
 <finder-column name="groupId" />
 </finder>
 </entity>
 <entity name="LeaveType" remote-service="false" local-service="false">
 <column name="leaveTypeId" type="long" primary="true" />
 <column name="tyeName" type="String" />
 </entity>
</service-builder>

Step3:Create custom-sql under src folder

  • To create custom sqls, need to create custom-sql folder under src
  • create default.xml file in custom-sql as shown in below image

custom-sql

Step4:Create CustomFinderImpl class

  • Create CustomerFinderImpl class with pattern {EntityName}FinderImpl.java in persistence package
  • FinderImpl class must extend BasePersistence class to access Database access below methods
    •  Session session=openSession()
    • clearCache()
    • getDataSource()
    • getDialect()
    • openNewSession()
    • closeSession()
  • Run the build-service ant target and it will generate {EntitName}Finder interface. {EntityName}FinderImpl.java implements {EntityName}Finder. In this example, LeaveFinderImpl class that extends BasePersistence<Leave> and implements LeaveFinder
  • public class LeaveFinderImpl extends BasePersistenceImpl<Leave> implements LeaveFinder{
    
     public static final String GET_LEAVES_BY_USER_LEAVETYPE = LeaveFinderImpl.class.getName() + ".getLeaves"; 
     
     }
  • CustomSQLUtil class liferay utility method to access SQL defined in default.xml file

customer-finder

Step5: Add SQL query in default.xml

<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
     <sql id="org.javasavvy.leave.service.persistence.LeaveFinderImpl.getLeaves">
         SELECT js_Leave.*  FROM js_Leave
        INNER JOIN   js_LeaveType ON js_Leave.leaveTypeId = js_LeaveType.leaveTypeId
      WHERE  js_Leave.userId = ? 
 </sql>
</custom-sql>

Step6:Create method in Finder Impl

  • Now create method in LeaveFinderImpl class
  • open session with  Session session = openSession()
  • Get SQL by Id using CustomSQLUtil.get(GET_LEAVES_BY_USER_LEAVETYPE);
  • Set the query.addEntity(“js_Leave”, Leave.class), so that Liferay will take care POJO Mapping converion
  • Use the QueryPos qPos = QueryPos.getInstance(query); to set positional parameters like leaveTypeId and userId
  • Now use query.list() or QueryUtil to execute queries
  • session must be closed to avoid performance issues
public List<Leave> getLeaves(long leaveTypeId,long userId) throws SystemException {

 Session session = null;
 try {
             session = openSession();
             String sql = CustomSQLUtil.get(GET_LEAVES_BY_USER_LEAVETYPE);
             SQLQuery query = session.createSQLQuery(sql);
             query.setCacheable(false);
             query.addEntity("js_Leave", Leave.class);

             QueryPos qPos = QueryPos.getInstance(query);
             qPos.add(leaveTypeId);
             qPos.add(userId);
             return query.list();
 }
 catch (Exception e) {
           e.printStackTrace();
 }
 finally {
           closeSession(session);
 }
 return null;
 }
One thought on “Liferay custom sql example”

Leave a Reply