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-sqldirectory 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.javainterface
- 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.javaclass. This will happened after run service builder.
In our case StudenFinder.javaand StudentFinderUtil.java classes will be created.
StudentFinderUtilclass 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 linkthen 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
0 comments:
Post a Comment