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