Liferay service builder’s finder method helps us to invoke data from the entities by their column attributes. Some times we may need to do complicated queries which involve attributes from different entities. Liferay Service builder provides us an option write custom SQL as Liferay custom SQL and invoking it in service layer through custom finder method.

Following are the steps to create liferay custom SQL:

  1. Specify custom SQL.
  2. Implement a finder method.
  3. Access finder method from service layer.

Following example shows each step to create liferay custom SQL with a simple select query from “Employee_Details” table by employee name.

Specify custom SQL:

Create an entity by creating a serive.xml file as shown below and run the service builder so that required service layer code will be created.

service.xml:

<?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="com.asb.sba">
 <author>ASB</author>
 <namespace>ASB</namespace>

 <entity name="EmployeeDetails" local-service="true" 
remote-service="false" table="Employee_Details">

<!-- PK fields -->
 <column name="empid" type="long" primary="true" />
 <column name="empName" type="String"></column>
 <column name="age" type="int"></column>
 <column name="salaryDtls" type="long"></column>
 <column name="pincode" type="long"></column>
<!-- Group instance -->
 <column name="groupId" type="long" />

<!-- Audit fields -->
 <column name="companyId" type="long" />
 <column name="createDate" type="Date" />
 <column name="modifiedDate" type="Date" />

<!-- Order -->
 <order by="asc">
 <order-column name="empName" />
 </order>
 </entity>
</service-builder>

Create a folder called “custom-sql” inside WEB-INF/src/ folder.  Add an xml file named “default.xml” with the content as shown below:

default.xml:

<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
 <sql id="com.asb.sba.service.persistence.EmployeeDetailsFinder
.findEmployeeDetails">
 <![CDATA[
 SELECT * FROM Employee_Details WHERE Employee_Details.empname LIKE ?
 ]]>
 </sql>
</custom-sql>

Here, We can add multiple custom SQL queries and each query can have a separate tag with unique sql ID.  tag will have a unique id attribute, with a fully qualified finder class name + “.” and finder method name as shown above. All SQL queries should be wrapped inside <![CDATA[]]> with no terminating semi colon.

Implement Finder Method:

Finder class implementation will be done in persistence layer. Name of Implementation class should be XXXFinderImpl.  This implementation class will extend BasePersistenceImpl of  particular entity and implements Finder Interface. Now add finder method to the implementation class as shown below:

EmployeeDetailsFinderImpl:

package com.asb.sba.service.persistence;

import com.asb.sba.model.EmployeeDetails;
import com.asb.sba.model.impl.EmployeeDetailsImpl;
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.List;

public class EmployeeDetailsFinderImpl extends BasePersistenceImpl implements EmployeeDetailsFinder {
  @SuppressWarnings("unchecked")
  public List findEmployeeDetailsByName(String name) {
     Session session = null;
     try {
       session = openSession();
       String sql = CustomSQLUtil.get(FIND_BY_EMPNAME);
       SQLQuery query = session.createSQLQuery(sql);
       query.addEntity("EmployeeDetails", EmployeeDetailsImpl.class);
 
       QueryPos qPos = QueryPos.getInstance(query);
       qPos.add(name);
 
       return (List) query.list();
    }
    catch(Exception e){}
    finally {
      closeSession(session);
    }
   return null;
 }
 public static final String FIND_BY_EMPNAME = EmployeeDetailsFinder.class.getName() + ".findEmployeeDetails";
}

Here, we have a finder method, which takes employee name as input to fetch the records from Employee_Details table. We have created a session, fetched SQL query from the default.xml file. Then we created a SQLQuery object for the Employee_Details entity. Finally added the Query position(QueryPos) for the custom SQL query and added “name” column and returned the matching list of records.

We can use QueryUtil.list(queryObject, getDialect(), begin, end); method to return result list, if we are using pagination. Run the service builder, so that required finder Interface will be created.

Access finder method from service:

Now create a service method in implementation class XXXLocalServiceImpl and call the finder method by using XXXFinderUtil class. Run service builder again to make this method available in service layer. Check the following code:

EmployeeDetailsLocalServiceImpl:

package com.asb.sba.service.impl;

import com.asb.sba.model.EmployeeDetails;
import com.asb.sba.service.base.EmployeeDetailsLocalServiceBaseImpl;
import com.asb.sba.service.persistence.EmployeeDetailsFinderUtil;
import com.liferay.portal.kernel.exception.SystemException;
import java.util.List;

public class EmployeeDetailsLocalServiceImpl extends EmployeeDetailsLocalServiceBaseImpl {

  public List findempbyname(String name) throws SystemException {
      return EmployeeDetailsFinderUtil.findEmployeeDetailsByName(name);
  }
}

And from view.jsp , we are invoking the service method to fetch the employee details named “Arun”.

view.jsp:

<%@page import="com.asb.sba.service.EmployeeDetailsLocalServiceUtil"%>
<%@page import="com.asb.sba.model.EmployeeDetails"%>
<%@page import="java.util.List" %>
<%@ taglib uri="http://java.sun.com/portlet_2_0" prefix="portlet" %>
<portlet:defineObjects />

This is the <b>Service Builder Advanced</b> portlet in View mode.
<%
 List<EmployeeDetails> empDtls = EmployeeDetailsLocalServiceUtil.findempbyname("Arun"); 
 %> 
<h1>Employee Details:</h1>
<%for(EmployeeDetails em : empDtls){ %>
<h3><%=em.getEmpName() %></h3>
<h3><%=em.getAge() %></h3>
<h3><%=em.getSalaryDtls() %></h3>
<%} %>

Here are the outputs:

customSql1

customSql2

 

Check out all Liferay related posts.

Note: Version used: Liferay 6.2 + Apache Tomcat 7.