Introduction:
Custom SQL is the way to write native SQL queries in liferay portlet development. Generally we need requirement that might not be fulfill by service builder generated services such scenarios we will use Custom SQL.
Based on our requirement we write native SQL and will use it in liferay portlet development.
Generally when data comes from one table or one service entity then will get List of same type of objects. This is simple scenarios we can handle it very easy.
Assume another scenario where data comes from multiple tables then the list of data belongs to different object types.
Before continue this article please follow below article
Generally when we use joins in Native SQL query then data comes from multiple tables.
In the custom method implementation we need to add Model Object Implementation class name to SQL Query object.
Similarly if the data comes from multiple tables then we need to add Multiple Model Object Implementation class name to SQL Query object and this scenario we can say list of data with complex object type.
Example Custom SQL method on One Entity/Table
SQL Query SELECT LS_Address.* FROM LS_Address WHERE LS_Address.employeeId=? Custom Method Implementation in Finder Implementation Class. public List<Address> getEmployeeAddresses(int employeeId) { Session session = null; try { session = openSession(); String sql = CustomSQLUtil.get("employeeSQLQuery"); SQLQuery queryObject = session.createSQLQuery(sql); queryObject.setCacheable(false); queryObject.addEntity("Address",AddressImpl.class); QueryPos qPos = QueryPos.getInstance(queryObject); qPos.add(employeeId); return (List<Address>)queryObject.list(); } catch (Exception e) { e.printStackTrace(); } finally { closeSession(session); } return null; } Access the Data List<Address> addressList =AddressLocalServiceUtil.getEmployeeAddresses(1,29); for(Address address: addressList){ System.out.println(address.getCity()); } |
In the query object we have added one Entity Model Object implementation class so here it will return the data list of Address Objects.
public List<Address> getEmployeeAddresses(--) |
When we observe the return type its List of Address Model Objects Type and here we don’t have any problem when we iterate list.
Example Custom SQL method on Multiple Entities/Tables
Assume we have requirement there we need to get the data from multiple tables and we need to use SQL JOIN in the sql query.
If the data comes from multiple tables then we need to add multiple Entity Model Objects to Query object.
When we add multiple Model Objects to Query Object then the List of data is Complex object. Means its list of object array Type and Array object contains the data of multiple tables.
public List<Object[]> getEmployeANDAddress(--) |
When we observe the return type its List of Object Array Type and here we have problem when we iterate list because in the object array there are multiple object types.
Here Object array each index element is one respective mole object.
Assume we have Employee and each employee have multiple addresses.
The following are tables in database.
LS_Employee
LS_Address
Here we will use employeeId in Address Tables so that we can have relation between Employee and Address tables.
Now we want get the data all employees and their address. We will use inner joins in SQL to achieve our requirement.
The following is SQL Query
SELECT emp.*,addr.* FROM ls_employee AS emp INNER JOIN ls_address AS addr ON emp.employeeId=addr.employeeId; |
The result is as follows
Now we will implement Custom SQL Method for above Query
When we observe the above data its comes for two tables so when we implement Custom SQL implementation we need to add tow Model Objects to the Query Object and Return Type is List of Object array because data is from multiple tables.
We already know custom SQL implementation in Liferay portlet development.
Custom SQL java method Implementation.
We already know we need create XXXFinderImpl.java class in persistence package. But here we have two tables which one we should choose?
We can choose any of the two tables either Employee or Address.
Assume we will choose Employee then out finder Implementation class is EmployeeFinderImpl.java
The following is Method implementation
public class EmployeeFinderImpl extends BasePersistenceImpl<Employee> implements EmployeeFinder { public List getAllEmployeeAndAddresses() { Session session = null; try { session = openSession(); String sql = CustomSQLUtil.get("employeesAddressesSQLQuery"); SQLQuery queryObject = session.createSQLQuery(sql); queryObject.setCacheable(false); queryObject.addEntity("Employee", EmployeeImpl.class); queryObject.addEntity("Address", AddressImpl.class); QueryPos qPos = QueryPos.getInstance(queryObject); // qPos.add(studentGender); return (List) queryObject.list(); } catch (Exception e) { e.printStackTrace(); } finally { closeSession(session); } return null; } } |
Note:
When we observe above method we have added two entities model implementation objects to query object i.e. EmployeeImpl.class and AddressImp.class.
The above methods return list of array objects and each object contains two entity model objects and the sequence is which we have added in the query object. Means array first index is Employee Model object and Second index is Address Model Object.
When we work with multiple tables then we need select all columns in SQL query otherwise we will get exceptions so use * on table so that we can select all columns in the table.
Use alias in the SQL query it will help us to distinguish the table names this is especially when we use JOINs.
The model implementation classes we have used in Custom SQL method should be available in current portlet class path or portal global class path.
When we join tables which are in current portlet context then we don’t have any problem. Some time we may join tables which are other portlet context then we will get problem loading other portlet Entity Model Implementation class.
When we add entity to query object and that is not available in current class path then it throw exception Unknown Entity Exception.
When we work with Plugin portlet each Plugin portlet have its own session factory and all entities model objects which we defined in service.xml will be loaded in respective portlet session factory.
When we use opensession()method it will get session from current session factory that is current Plugin portlet session factory.
So when we join table which is not in current Plugin portlet service.xml those entities may not load into query object then we will get Unknown Entity Exception.
More details Follow the below article
Access the data from Object Array
When access the custom SQL service method then it will return list of Object Array Type.
In each object array there are multiple model objects that we added to query object.
When iterate list we need to convert it to object array and need to access model object with array index.
The following is Example Code
<% Employee employee=null; Address address=null; List list=EmployeeLocalServiceUtil.getAllEmployeeAndAddresses(); for(Object object:list){ Object[] arrayobject=(Object[])object; employee=(Employee)arrayobject[0]; address=(Address)arrayobject[1]; out.println(employee.getEmployeeName()); out.println(address.getCity()); } %> |
When we observe above code we have accessed model object from Array and we have done type cast.
The sequence of model object will be decided the order we added model implementation classes to Query Object.
Complete Code Example
SQL Query in xml file (/WEB-INF/src/custom-sql/employee-custom-sql.xml)
<?xml version="1.0" encoding="UTF-8"?> <custom-sql> <sql id="employeesAddressesSQLQuery" > <![CDATA[ SELECT emp.*,addr.* FROM ls_employee AS emp INNER JOIN ls_address AS addr ON emp.employeeId=addr.employeeId ]]> </sql> </custom-sql> |
Custom SQL Finder Implementation Class and Method (EmployeeFinderImpl.java)
package com.meera.customesql.dbservice.service.persistence; import java.util.List; 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 com.meera.customesql.dbservice.model.Employee; import com.meera.customesql.dbservice.model.impl.AddressImpl; import com.meera.customesql.dbservice.model.impl.EmployeeImpl; public class EmployeeFinderImpl extends BasePersistenceImpl<Employee> implements EmployeeFinder { public List getAllEmployeeAndAddresses() { Session session = null; try { session = openSession(); String sql = CustomSQLUtil.get("employeesAddressesSQLQuery"); SQLQuery queryObject = session.createSQLQuery(sql); queryObject.setCacheable(false); queryObject.addEntity("Employee", EmployeeImpl.class); queryObject.addEntity("Address", AddressImpl.class); QueryPos qPos = QueryPos.getInstance(queryObject); return (List) queryObject.list(); } catch (Exception e) { e.printStackTrace(); } finally { closeSession(session); } return null; } } |
Custom SQL Service Method in XXXLocalServiceImpl.java(EmployeeLocalServiceImpl.java)
public class EmployeeLocalServiceImpl extends EmployeeLocalServiceBaseImpl { public java.util.List getAllEmployeeAndAddresses() { return EmployeeFinderUtil.getAllEmployeeAndAddresses(); } } |
Note:
When we write any method in XXXLocalServiceImpl.java respective method signature will be created in XXXLocalServiceutil.java after run service builder.
Access Data in JSP Page(/view.jsp)
<%@page import="com.meera.customesql.dbservice.model.Address"%> <%@page import="com.meera.customesql.dbservice.model.Employee"%> <%@page import="java.util.List"%> <%@page import="com.meera.customesql.dbservice.service.EmployeeLocalServiceUtil"%> <%@ taglib uri="http://java.sun.com/portlet_2_0" prefix="portlet" %> <portlet:defineObjects /> <h2>Liferay Custom SQL on Multiple Tables</h2> <% Employee employee=null; Address address=null; List list=EmployeeLocalServiceUtil.getAllEmployeeAndAddresses(); %> <table border="1" width="700"> <tr> <th>EmployeeId</th> <th>Employee Name</th> <th>Employee Designation</th> <th>Street</th> <th>City</th> <th>State</th> </tr> <%for(Object object:list){ Object[] arrayobject=(Object[])object; employee=(Employee)arrayobject[0]; address=(Address)arrayobject[1];%> <tr> <td><%=employee.getEmployeeId()%></td> <td><%=employee.getEmployeeName()%></td> <td><%=employee.getEmployeeDesignation()%></td> <td><%=address.getStreet()%></td> <td><%=address.getCity()%></td> <td><%=address.getState()%></td> </tr> <%} %> </table> |
Download Liferay Custom SQL on Multiple Tables Portlet
Environment:
Liferay IDE 2.x+Eclipse (Kepler) +Liferay Plugins SDK 6.2+Tomcat 7.x Liferay Portal Bundle
Deployment and its Working.
Download portlet you can source or war file to deploy into liferay portal as your convenient.
After deployment please insert some data in LS_Employee and LS_Address tables.
When insert data in Address table address should map to employeeId.
Once portlet successfully deployed drag the portlet in any desired page. Portlet is available in sample category.
Once you drop the portlet in the page you can see the data which is comes from two tables.
Note:
The portlet does not have any real functionality but I just showed using custom SQL service methods in portlet page.
Portlet Screens:
Default Page
Reference Links
Author
0 comments:
Post a Comment