Introduction
Custom SQL is the way to use native SQL queries in portlet
development. Liferay Service Builder have created service layer with all possible
CRUD operation services.
Some time we may get specific requirement that might
not fulfill with service builder created services then we need to use some
native SQL in development.
Whenever ever we need to use native SQL in portlet development
then liferay have given mechanism called Custom SQL. Custom SQL is the way to
use Native SQL queries in portlet development.
Before implement this we need to have basic knowledge
with respect SQL and writing SQL quires for you favorite RDBMS database.
Before
start this article please follow the below articles.
Basic
Knowledge on SQL
Following
are the steps to implement Custom SQL
- Create Simple Liferay MVC Portlet
- Define entity in service.xml
- Run the Service Builder
- Create SQL queries configuration XML file (Custom SQL XML file)
- Write Native SQL in Custom SQL xml file
- Create Finder Implementation class
- Run the Service Builder
- Implement Required Custom SQL Method in Finder Implementation Java Class
- Run the Service Builder
- Write Custom Service Method in respective Local Service Implementation Class
- Run the Service Builder
- Use Respective Local Service Util class to call service method
Create
Simple Liferay MVC Portlet
Create simple Liferay MVC Portlet and this is start
point for your custom SQL implementation. You can use Liferay IDE and create
simple Liferay MVC Portlet
Define
Service Entities in portlet service.xml
We are already aware of Liferay Service Builder. We need
service.xml file to defined service entity so that service builder will create
all necessary service classes and methods to defined entities in service.xml. We
need create service.xml file in portlet /WEB-INF/
directory.
Assume we are going to defined Student Entity
The
following is entity configuration in service.xml (/WEB-INF/service.xml)
<service-builder package-path="com.meera.dbservice">
<author>LiferaySavvy</author>
<namespace>LS</namespace>
<entity name="Student"
local-service="true" remote-service="true">
<!-- PK fields
-->
<column name="studentId"
type="long" primary="true"
/>
<column name="firstName"
type="String" />
<column name="lastName"
type="String" />
<column name="studentAge"
type="int" />
<column name="studentGender"
type="int" />
<column name="studentAddress"
type="String" />
<!-- Finder
methods -->
<finder name="Gender"
return-type="Collection">
<finder-column name="studentGender"/>
</finder>
</entity>
</service-builder>
|
Note:
We have defined student entity and service builder
base package is com.meera.dbservice.
The actual table which going to create in database
is LS_Student and remember this we
will use table in SQL queries.
Run
the Service Builder
Once we defined the required service entities in
service.xml we need to run service builder. Use ant build-service from eclipse
ANT view and run the target.
As soon as we run the ant target all necessary java classes
and required service methods and data base script will be created.
More
details read Liferay Service Builder Articles
Create
SQL queries configuration XML file (Custom SQL XML file)
Before two steps we have done minimum required
things. Now custom SQL steps will be start.
To write our SQL queries we need some configuration
file where we will define all native SQL queries.
Now we need to create default.xml file in portlet
Src/custom-sql directory.
Here cutom-sql
directory not available so we need create that and we need create default.xml
in custom-sql directory.
We can directly write queries in default.xml file but for easy maintenance
we just write all sql queries in other xml file and we will include other xml
file in default.xml file
Example
configuration in custom-sql.xml files (src/custom-sql/default.xml)
<?xml version="1.0"
encoding="UTF-8"?>
<custom-sql >
<sql file="custom-sql/student-custom-sql.xml"/>
</custom-sql >
|
Now create stundent-custom-sql.xml file in same
location that is src/custom-sql/
Note:
If we have multiple portlet in single Plugin we will
create multiple xml files and we will include all files in default.xml file.
Write
Native SQL in Custom SQL xml file
Now we have SQL queries configuration file i.e. stundent-custom-sql.xml now we will
write all Native SQL quires in the xml file.
Note we already know when we write SQL queries it
should use exact table name which will be created in RDBMS database.
We already know as for service builder the table
name will be created in database follow the logic
[Name
Space]_[Entity Name in Service.xml]
Our Table Name:
LS_Stundent
Note:
If
you want know the table name which is going create will be available in
table.sql file(/WEB-INF/sql/tables.sql)
Example:
create table LS_Student (
studentId LONG not null primary key,
firstName VARCHAR(75) null,
lastName VARCHAR(75) null,
studentAge INTEGER,
studentGender INTEGER,
studentAddress VARCHAR(75) null
);
|
Assume
the following is our Native SQL query
SELECT
LS_Student.* FROM LS_Student WHERE LS_Student.studentGender=?
AND LS_Student.studentAge=?
|
Note:
For simplification I just used simple SQL query. We have
to decide input paramteres for query and we make it as ? Mark and these values
will be passed dynamically from the java method.
Finally
following is SQL configuration in stundent-custom-sql.xml file
<?xml version="1.0"
encoding="UTF-8"?>
<custom-sql>
<sql id="studentSQLQuery"
>
<![CDATA[
SELECT LS_Student.*
FROM LS_Student WHERE LS_Student.studentGender=? AND LS_Student.studentAge=?
]]>
</sql>
</custom-sql>
|
Note:
No need to use semi-colon to terminating SQL
statement in xml file.
We will use SQL tag and inside we have to write SQL
query and we need use id attribute for each query so that it will be used to identify
the query. We will use this id in java custom SQL implementation method.
Note:
Before use SQL query in xml file please test the
query in database sql query execution client and make sure it should work
properly there.
Create
Finder Implementation class
This is core step I Custom SQL implementation. Any
custom SQL implementation we need one java implementation class and this java implementation
class we will call it as Finder Implementation class.
We will use class as XXXFinderImpl.java and XXX is our entity
name i.e. Student.
If write custom SQL for Student entity then our finder
will be StundentFinderImpl.java
The finder implementation class should be create in
service builder base package Name.service.persistence
We can say it should be available in persistence
directory
(com.meera.dbservice.service.persistence)
Properties
of Custom SQL Implementation class
- Name should be XXXFinderImpl.java
- Should be create in persistence directory
- Should implement XXXFinder.java interface
- Should Extends BasePersistenceImpl.java
Now create XXXFinderImpl.java in persistence directory.
In our case StudentFinderImpl.java is our custom SQL implementation class.
The
following is Example Code
package
com.meera.dbservice.service.persistence;
import
com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
import
com.meera.dbservice.model.Student;
public class StudentFinderImpl extends
BasePersistenceImpl<Student>
implements StudentFinder{
}
|
Note:
XXXFinder interface initially not available once we
run service builder then XXXFinder will be created.
So initially when write code it will show some
syntax error in eclipse once we run service builder then it will be resolved.
Run
the Service Builder
Once we created Finder Implementation java class we
need to run service builder with ant build-service target from ANT view.
As soon as we run service builder it will create
some additional classes and interfaces.
Generally it will create XXXFinder.java interface and XXXFinderUtil.java
class. This will happened after run service builder.
In our case StudenFinder.java
and StudentFinderUtil.java classes
will be created.
StudentFinderUtil
class will be used to call custom SQL service methods and we can’t use StudentFinderUtil
class directly in JSP pages or Portlet Action class it will throw Hibernate
Exception.
We need to implement custom SQL service method in XXXLocalServiceImpl.java and there we
need use StudentFinderUtil.java class
to call custom SQL service method.
Note:
All custom SQL service methods available in
XXXFinderUtil.java and we will use this class to call custom SQL service
methods.
Implement
Required Custom SQL Method in Finder Implementation Java Class
Now need to implement custom SQL implementation
method in XXXFinderImple.java class.
We need to deicide method return type and also need
to decide how many parameters use in implementation method and this should be
depends on SQL query positional parameters we used in native SQL query and also
make sure parameter data types.
Steps
in implementing custom SQL method
- Open Session Portlet Session
- Create query object by passing SQL query as a String
- Add entities Model Impl class for query object
- Create Query Position instance to pass positional parameter for the query.
- Use list () method over query object.
The following is Custom SQL Implementation method
package
com.meera.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.dbservice.model.impl.StudentImpl;
public class StudentFinderImpl extends
BasePersistenceImpl<Student> implements
StudentFinder {
public
List<Student> getStudentByGenderAndAge(int studentGende,
int studentAge) {
Session session = null;
try {
session
= openSession();
String
sql = CustomSQLUtil.get("studentSQLQuery");
SQLQuery
queryObject = session.createSQLQuery(sql);
queryObject.setCacheable(false);
queryObject.addEntity("Student",
StudentImpl.class);
QueryPos
qPos = QueryPos.getInstance(queryObject);
qPos.add(studentGende);
qPos.add(studentAge);
return (List<Student>) queryObject.list();
} catch (Exception e) {
e.printStackTrace();
} finally {
closeSession(session);
}
return null;
}
}
|
If the method has start end parameter then use as
follows generally we need it in pagination.
return
(List<Event>) QueryUtil.list(queryObject,getDialect(),begin, end);
|
Note:
CustomSQLUtil.get ("studentSQLQuery") will
give the SQL query as Sting by passes the Query Id which we have used in SQL
queries xml configuration file.
How many tables we have used in SQL query those
tables Model Entities we need to add to Query object. If multiple tables data
then it will return array of objects list.
Write
Custom Service Method in respective Local Service Implementation Class
We already know we can’t call custom SQL service
method directly using XXXFinderUtil.java from JSP or portlet action class. Now
we need to make Custom SQL service method as Local Service Method.
Custom
SQL service as Local Service
Define some custom service method in
XXXLocalServiceImpl.java and there we will use StudentFinderUtil.java to call
custom SQL service method.
When we write any method in XXXLocalServiceImpl.java
respective method signature will be created in XXXLocalServiceUtil.java and we
will use XXXLocalSertviceUtil.java to call local service methods.
The following is Local Service Method Implementation
in XXXLocalServiceImpl.java
public class
StudentLocalServiceImpl extends StudentLocalServiceBaseImpl {
public
java.util.List<com.meera.dbservice.model.Student> getStudentByGenderAndAgeASLocal(
int studentGende, int
studentAge) {
return StudentFinderUtil.getStudentByGenderAndAge(studentGende,
studentAge);
}
}
|
Note:
In the Local Service Implementation method we have
used XXXFinderUtil.java to call custom SQL service.
All service methods in implementation class should
not be static methods
Run
Service Builder
Once we implemented custom service method in
XXXLocalServiceImpl.java then we need to run service builder.
As soon as we run the service builder then
respective custom method signature will be created in XXXLocalServiceUtil.java.
Whenever we need custom service then we will use
XXXLocalServiceUtil.java class to call local service methods.
This XXXLocalServiceUtil.java we will use in Portlet
Action Class or JSP pages to obtain the service.
Method
Signature in XXXLocalServiceUtil.java
public static
java.util.List<com.meera.dbservice.model.Student>
getStudentByGenderAndAgeASLocal(
int studentGende, int studentAge) {
return getService()
.getStudentByGenderAndAgeASLocal(studentGende,
studentAge);
}
|
Note:
All generated methods in XXXLocalServiceUtil.java always
static methods.
Use
Respective Local Service Util class to call service method
Now our custom SQL service became Local Service so
we can use XXXLocalServiceUtil.java class to call Custom SQL service. We can
use these local services in anywhere in JSP pages or Portlet Action class.
Calling
Custom SQL Service Method Example
<%
List
studentList=StudentLocalServiceUtil.getStudentByGenderAndAgeASLocal(studentGender,
studentAge);
%>
|
Complete
code for Example
Portlet
service.xml file (WEB-INF/service.xml)
<service-builder package-path="com.meera.dbservice">
<author>LiferaySavvy</author>
<namespace>LS</namespace>
<entity name="Student"
local-service="true" remote-service="true">
<!-- PK fields
-->
<column name="studentId"
type="long" primary="true"
/>
<column name="firstName"
type="String" />
<column name="lastName"
type="String" />
<column name="studentAge"
type="int" />
<column name="studentGender"
type="int" />
<column name="studentAddress"
type="String" />
<!-- Order
-->
<order by="asc">
<order-column name="studentId"
/>
</order>
<!-- Finder
methods -->
<finder name="Gender"
return-type="Collection">
<finder-column name="studentGender"/>
</finder>
</entity>
</service-builder>
|
Portlet
default.xml file (/WEB-INF/src/custom-sql/default.xml)
<?xml version="1.0"
encoding="UTF-8"?>
<custom-sql >
<sql file="custom-sql/student-custom-sql.xml"/>
</custom-sql >
|
SQL
Queries configuration files (/WEB-INF/src/custom-sql/student-custom-sql.xml)
<?xml version="1.0"
encoding="UTF-8"?>
<custom-sql>
<sql id="studentSQLQuery"
>
<![CDATA[
SELECT * FROM
LS_Student Where studentGender=? AND
studentAge=?
]]>
</sql>
</custom-sql>
|
Custom
SQL Finder Implementation Java Class (StudentFinderImpl.java)
package
com.meera.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.dbservice.model.impl.StudentImpl;
public class StudentFinderImpl extends
BasePersistenceImpl<Student> implements
StudentFinder {
public
List<Student> getStudentByGenderAndAge(int studentGender,
int studentAge) {
Session session = null;
try {
session =
openSession();
String sql =
CustomSQLUtil.get("studentSQLQuery");
SQLQuery
queryObject = session.createSQLQuery(sql);
queryObject.setCacheable(false);
queryObject.addEntity("Student", StudentImpl.class);
QueryPos qPos =
QueryPos.getInstance(queryObject);
qPos.add(studentGender);
qPos.add(studentAge);
return (List<Student>)
queryObject.list();
// if the method
have begin end parameter then use as follows
// generally we
need it in pagination.
// return
(List<Event>) QueryUtil.list(queryObject,getDialect(),
// begin, end);
} catch (Exception e) {
e.printStackTrace();
} finally {
closeSession(session);
}
return null;
}
}
|
Custom
SQL Local Service Implementation class (StudentLocalServiceImpl.java)
public class
StudentLocalServiceImpl extends StudentLocalServiceBaseImpl {
public
java.util.List<Student> getStudentByGenderAndAgeASLocal(
int studentGender, int studentAge) {
return StudentFinderUtil.getStudentByGenderAndAge(studentGender,
studentAge);
}
}
|
Custom
SQL Service Util class (StudentLocalServiceUtil.java)
public class
StudentLocalServiceUtil {
public static
java.util.List<com.meera.dbservice.model.Student>
getStudentByGenderAndAgeASLocal(
int studentGender, int studentAge) {
return getService()
.getStudentByGenderAndAgeASLocal(studentGender,
studentAge);
}}
|
Note:
Local Service methods have many other methods but
for sake of explanation I just showed only custom SQL service method.
Using
Custom SQL service method in JSP page
<%@page import="com.meera.dbservice.model.Student"%>
<%@page import="java.util.List"%>
<%@page import="com.meera.dbservice.service.StudentLocalServiceUtil"%>
<%@ include
file="init.jsp"%>
<a href="<portlet:renderURL
/>">«Home</a>
<div class="separator"></div>
<h2>Custom SQL Service
Call</h2><br/>
<b>StudentLocalServiceUtil.usingUniqueAttributeforFindertag(1)</b><br/>
<h2>The following
method is example for Calling Custom SQL service in JSP page</h2><br/><br/>
<b>StudentLocalServiceUtil.getStudentByGenderAndAgeASLocal(1,27)</b><br/>
<%="Number of
Males Age 27 years:"+StudentLocalServiceUtil.getStudentByGenderAndAgeASLocal(1,
27)+"<br/>"%>
<br/>
|
Note:
Entire article we have used only one table but in
real time we need to use Custom SQL on multiple tables in the coming post we
will see in detail.
Download
Liferay Custom SQL 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.
Once portlet successfully deployed drag the portlet
in any desired page. Portlet is available in sample category.
In the portlet page you can click on Liferay Custom SQL Example link
then you can see the search container with search inputs.
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
Custom SQL Service Method Example
Reference
Links
Author
Posts
0 comments :
Post a Comment