Liferay Custom SQL Query on multiple tables

Sometimes we may want to write complex SQL queries, which contain data from multiple tables. These tables can be inside the same plugin portlet, outside plugin portlet, or at the portal level(like User_, Group_ tables). Check out Liferay custom SQL for details about implementing custom SQL queries on Liferay.

If we are fetching data from multiple tables, the result might be a list of complex objects with data from many tables.

In this article, we will learn how to retrieve data from many tables.

Version used: Liferay 6.2 + Apache Tomcat 7.

Implementing custom SQL query

Let’s begin with the implementaion.

Adding the configuration

Create a service.xml with two entities as below. Here we have created two entities StudentDetails and Department

The Column departmentId of the StudentDetails table is having a foreign reference to the Department table.

<?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">
 <author>ASB</author>
 <namespace>ASB</namespace>

 <entity name="StudentDetails" local-service="true" 
remote-service="false">
 <column name="studentId" type="long" primary="true" />
 <column name="name" type="String" />
 <column name="age" type="String" />
 <column name="departmentId" type="long" />
 </entity>
 
 <entity name="Department" local-service="true" remote-service="false">
 <column name="departmentId" type="long" primary="true" />
 <column name="departmentName" type="String" />
 </entity>
</service-builder>

Add a folder named custom-sql inside WEB-INF/src folder and add a default.xml file. This file will contain our custom SQL queries.

Instead of a single file, we can use separate XML file for every SQL query and provide a link from the default.xml file based on our convenience.

Below is the code to link to the student.xml file from the default.xml file.

default.xml:

<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
 <sql file="custom-sql/student.xml"/>
</custom-sql>

student.xml

<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
 <sql id="com.asb.service.persistence.StudentDetailsFinder.findStudentDetails">
 <![CDATA[
 SELECT a.*,b.* FROM ASB_STUDENTDETAILS a, ASB_DEPARTMENT b WHERE a.departmentId = b.departmentId
 ]]>
 </sql>
</custom-sql>

Implementing the service

Create a FinderImpl class and add the following code to implement the finder method. Here we are adding two defined entities(StudentDetails and Department) to the query.

The return type of the finder method will be an object array list.

package com.asb.service.persistence;
import com.asb.model.StudentDetails;
import com.asb.model.impl.DepartmentImpl;
import com.asb.model.impl.StudentDetailsImpl;
import com.liferay.portal.kernel.bean.PortalBeanLocatorUtil;
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.kernel.dao.orm.SessionFactory;
import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
import com.liferay.util.dao.orm.CustomSQLUtil;
import java.util.List;

public class StudentDetailsFinderImpl extends BasePersistenceImpl implements StudentDetailsFinder {

 public List findStudentDetails() {
  Session session = openSession();
  String sqlQuery = CustomSQLUtil.get(sql);
  SQLQuery query = session.createSQLQuery(sqlQuery);
 
  query.addEntity("StudentDetails", StudentDetailsImpl.class);
  query.addEntity("Department", DepartmentImpl.class);
 
  QueryPos queryPos = QueryPos.getInstance(query);
 
  return (List) query.list();
 }
 private static String sql = StudentDetailsFinder.class.getName() +".findStudentDetails";
}

If we are trying to invoke tables that are inside available in portal level(Ex: User_, Group_, etc.), we can invoke as shown below.

Here, we are opening a session from liferaySessionFactory. Then add the entity to query by using PortalClassLoader().loadClass(“fully qualified portal bean Impl class name”).

We can also use separate sessions to invoke portal and portlet level entities within a finder method and combine the results.

SessionFactory sessionFactory = (SessionFactory)PortalBeanLocatorUtil.locate("liferaySessionFactory");
 Session session = sessionFactory.openSession();

//Add User_ entity to the query:
query.addEntity("User_", PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.UserImpl"));

We can invoke the tables from another plugin portlet context. For this, we have to use “liferaySessionFatory” to open a database session. 

Also, we have to add the following property and copy the service implementation jar of that plugin portlet to WEB-INF/lib/ directory.

required-deployment-contexts=\name-of-Portlet-portlet

If we use the IDE, it automatically copies the service impl jar is to the WEB-INF/lib/ directory. Then we can add use that entity in our custom SQL query using created session.

Finally, add service layer implementation by adding finder method to XXXLocalServiceImpl as shown below.

package com.asb.service.impl;
import com.asb.service.base.StudentDetailsLocalServiceBaseImpl;
import com.asb.service.persistence.StudentDetailsFinderUtil;
import com.liferay.portal.kernel.exception.SystemException;
import java.util.List;

public class StudentDetailsLocalServiceImpl extends StudentDetailsLocalServiceBaseImpl {
 
 public List findCompleteStudentDetails()throws SystemException {
 return StudentDetailsFinderUtil.findStudentDetails();
 }
}

Displaying the result

In the JSP file, we can access the finder method, as shown below. Here we will be receiving an object array list. This array contains entities in the order same as that we have added in the finder method.

<%@page import="com.asb.model.StudentDetails"%>
<%@page import="com.liferay.portal.kernel.util.Validator"%>
<%@page import="com.asb.service.StudentDetailsLocalServiceUtil"%>
<%@ taglib uri="http://java.sun.com/portlet_2_0" prefix="portlet" %>
<%@page import="java.util.List"%>
<%@page import="com.asb.model.Department"%>
<portlet:defineObjects />

<table border="1">
<tr>
 <th>Student ID</th> 
 <th>Student Name</th> 
 <th>Student age</th> 
 <th>Department ID</th> 
 <th>Department ID</th> 
 <th>Department Name</th>
</tr>
<%
List<Object[]> stdList = 
StudentDetailsLocalServiceUtil.findCompleteStudentDetails();

 for(Object obj : stdList)
 {
 Object[] arrayobject=(Object[])obj;
 StudentDetails details =(StudentDetails) arrayobject[0];
 Department department = (Department)arrayobject[1];
 %> 
 <tr> 
 <td><%=details.getStudentId() %></td> 
 <td><%=details.getName() %></td>
 <td><%=details.getAge() %></td> 
 <td><%=details.getDepartmentId() %></td> 
 <td><%=department.getDepartmentId() %></td> 
 <td><%=department.getDepartmentName() %></td> 
 </tr> 
 <% 
 }
%>
</table>

Testing the output

Below is the out put of above code, containing both student and department entity details.

multipleTables

Conclusion

In this article, we learned how to retrieve data from many tables using Liferay’s service builder.

2 comments

  1. Great post! Your blog addresses many of the “below the surface” questions most have. Have you tried using tables outside those defined with service builder?

    1. Hi Phil,

      I am glad that you liked it 😊
      For tables which already exists in database, use entity mapping in service.xml as normal entities. Then you can run service builder and use generated services.

      Make sure to take complete backup of table along with data, as any mismatch in entity mapping may create a new table in database.

Comments are closed.