Liferay service builder’s finder method helps us to invoke data from the entities by their column attributes. But this may not be sufficient for real-world use cases. Liferay Service Builder provides an option to write custom SQL as Liferay custom SQL and invoking it in the service layer through the custom finder method.
In this article, we will learn how to leverage the custom SQL query support of the Liferay service builder.
Version used: Liferay 6.2 + Apache Tomcat 7.
Table of Contents
Using custom SQL queries in Liferay
Below are the steps to create Liferay custom SQL.
- Specify custom SQL.
- Implement a finder method.
- Access finder method from the service layer.
The following example shows each step to create Liferay custom SQL with a simple select query from the “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 build to generate the necessary service layer code.
<?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:
<?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. We can use <custom-sql> tag to specify each SQL query.
We also use <sql> tag with a separate with a unique id attribute, and the value is set as the fully qualified finder class name and finder method name as shown above.
All SQL queries are wrapped inside <![CDATA[]]> with no terminating semicolon.
Implement Finder Method
The persistence layer contains the finder class implementation. The name of the Implementation class should be XXXFinderImpl.
The finder implementation class will extend the BasePersistenceImpl of a particular entity and implements Finder Interface.
Finally, add the finder method to the implementation class as shown below.
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 the employee name as query input to fetch the records from the Employee_Details table. We also have created a session, retrieved the SQL query from the default.xml file. Then we created an SQLQuery object for the Employee_Details entity.
Finally, we are adding the Query position(QueryPos) to the custom SQL query and then adding the “name” column, and then finally returning the matching list of records.
We can use QueryUtil.list(queryObject, getDialect(), begin, end); method to return result list, if we need pagination support.
Run the service builder so that the required finder Interface files get created in the project’s source directory.
Access the finder method from service
Create a service method in implementation class XXXLocalServiceImpl and call the finder method by using XXXFinderUtil class. Execute the service builder again to make this method available in the service layer.
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);
}
}
Update the view.jsp file as shown below. Here we are invoking the service method to fetch the employee details with the name “Arun”
<%@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>
<%} %>
Testing the output
Run the application, and once the portlet application is ready, we can get the query results on the UI screen, as shown below.
Below is the SQL query result of our database for our custom SQL query.
Conclusion
In this article, we learned how we can use customized SQL queries with the Liferay service builder.
