Some times we may want to write complex SQL queries, which contains data from multiple tables. These tables can be inside the same plugin portlet, outside plugin portlet or can be in 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, result might be a list of complex object with data from multiple tables. following is an example of how to retrieve data from multiple tables.

  • Create a service.xml with two entities as below. Here we have created two entities “StudentDetails” and “Department“. Column “departmentId” of StudentDetails table is having a foreign reference to 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. add a “default.xml” file. This file will contain our custom SQL queries. We can also keep our SQL queries in separate xml files and provide link from default.xml file based on our convenience.
<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
 <sql file="custom-sql/student.xml"/>
</custom-sql>
  • Following is the code to link to student.xml file from default.xml file:
<?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>
  • 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.  Return type of the finder method will be a list of object array.
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 which 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”) .

Note that we can 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"));

If we are invoking the tables from another plugin portlet context, 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. If you are using IDE, service impl jar will be automatically copied to the WEB-INF/lib/ directory. Then we can add use that entity in our custom SQL query using created session.

required-deployment-contexts=\name-of-Portlet-portlet
  • 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();
 }
}
  • In JSP file, we can access the finder method as shown below. Here we will be receiving list of object array. This array contains entities in the order same as that of we have added in 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>

Below is the out put of above code, containing both entity details:

multipleTables

Check out all Liferay related posts.

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