Wednesday, June 11, 2014

Liferay Dynamic Query API

  
Introduction:

Liferay Dynamic Query API is an elegant feature in Liferay portlet development. We already know liferay service builder tool will create all required database services in the portlet but some time we need some specific requirement where we need some custom services there we will use dynamic query API to meet those requirements. Dynamic Query API can only perform read operations against database. These we will use specially to provide search operations against database.

We already have Custom SQL to meet the same requirements but Custom SQL is Service Builder Dependent feature and we need to follow many steps to implement custom SQL. If we want change or modify service each time we need to rerun service builder.

Dynamic Query API is independent feature and we can implement anywhere in the development like we can implement in JSP pages, Portlet Action Classes and service implementation classes.

The following are Steps
  1. Create Dynamic Query Object against Entity Model Class
  2. Create Criteria and Add criteria to Dynamic Query Object
  3. Call Dynamic Query using respective XXXLocalServiceUtil class

Create Dynamic Query Object against Entity Model Class

We need to create Dynamic Query Object on behalf of specific model class and it represent the data is fetching from respective table.

Assume I want fetch data from Student table then the following is way to create Dynamic Query Object.


DynamicQuery userQuery4 = DynamicQueryFactoryUtil.forClass(
Student.class, PortletClassLoaderUtil.getClassLoader());

Here very important thing is we need to pass respective class loader where our model class is available.

We have two class loaders
  1. Portal Class Loader
  2. Portlet Class Loader

Portal Class Loader

If any class or model class in portal level then we need to use Portal Class Loader in Dynamic Query Object.

The following is Util class and it’s have methods to get Portal Class Loader Object


ClassLoader portalClassLoader=PortalClassLoaderUtil.getClassLoader();

When we implement dynamic Query against portal level tables or model classes then we need portal class loader.

Example Classes


User, Group, Role, Organization, Layout and Layoutset

Portlet Class Loader

Portlet Class Loader is related to each portlet. In the Plugin portlet environment each Plugin portlet context has its own class loader that is portlet class loader and this class loader responsible to load respective classes with in the portlet context.

We have another Util class which has some methods to get Portlet Class Loader


ClassLoader portletClassLoader=PortletClassLoaderUtil.getClassLoader();

Portal Level Dynamic Query Implementation


DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass(
User.class, PortalClassLoaderUtil.getClassLoader());

Note:

When we execute above dynamic query it will return list User Type Objects.

Plugin Portlet Level Dynamic Query Implementation


DynamicQuery studentQuery = DynamicQueryFactoryUtil.forClass(
Student.class, PortletClassLoaderUtil.getClassLoader());

Note:

When we execute above dynamic query it will return list of Student Type Objects.

Create Criteria and Add criteria to Dynamic Query Object

Criteria represent the reading policies against table or database. We already know when we write the SQL query we will use different operators and condition.

Similarly in dynamic query we can apply all condition and operators. Criteria consist of all operators and condition.

All these will be managed by only two Util classes


RestrictionsFactoryUtil

PropertyFactoryUtil

We can create criteria in different ways using above classes it’s based in developer convenient.

Example:

Assume following is our required SQL query


Select * from Student Where studentAge=? And studentGender=?

The following are Different Types of Dynamic Query Implementation.

Dynamic Query Type 1


DynamicQuery studentQuery = DynamicQueryFactoryUtil.forClass(
Student.class, PortletClassLoaderUtil.getClassLoader());

studentQuery.add(PropertyFactoryUtil.forName("studentAge ").eq(new Integer ("23")));

studentQuery.add(PropertyFactoryUtil.forName("studentGender ").eq(new Integer("1")));

In the above query we will add each criteria to Dynamic Query Object when we use like above it will take always AND operator in Where condition.

PropertyFactoryUtil contained all possible operators against columns and PropertyFactoryUtil contains all methods which represent the columns related operators like =,>, <, in and like

Note:

If we see the PropertyFactoryUtil API then you can see all the methods and its usage.

Dynamic Query Type 2

Here we will create separate Criteria Object and after completing our criteria finally we will add these criteria Object to Dynamic Query


DynamicQuery studentQuery = DynamicQueryFactoryUtil.forClass(
User.class, PortletClassLoaderUtil.getClassLoader());

Criterion criterion = null;

criterion = RestrictionsFactoryUtil.eq("studentAge ",new Integer ("23"));

criterion=RestrictionsFactoryUtil.and(criterion,
RestrictionsFactoryUtil.eq("studentGender ",new Integer ("1")));

studentQuery.add(criterion);

RestrictionsFactoryUtil is Util class contains WHERE condition operators like (AND, OR, IN).When we want apply WHERE condition related operators then we will RestrictionsFactoryUtil

Dynamic Query Type 3


DynamicQuery dynamicQuery = 
DynamicQueryFactoryUtil.forClass(Student.class,
PortletClassLoaderUtil.getClassLoader());

Junction junction = RestrictionsFactoryUtil.conjunction();

Property property = PropertyFactoryUtil.forName("studentAge");
junction.add(property.eq(Integer.valueOf(studentAge)));

Property property = PropertyFactoryUtil.forName("studentGender");
junction.add(property.eq(Integer.valueOf(studentGender)));

dynamicQuery.add(junction)

If the WHERE condition have all AND/OR operator between columns then we can use Junction

AND Operator


Junction conjunction = RestrictionsFactoryUtil.conjunction();

OR Operator


Junction disjunction = RestrictionsFactoryUtil.disjunction();

Note:

Above all Dynamic Queries return same result but the way of writing is different. When we build query we need to append each criteria to its previous criteria object then only it can build complete query.

Once we will build like above finally the query will be converted into data base dependent Native SQL query and these will be take care by API level classes.

Call Dynamic Query using respective XXXLocalServiceUtil class

Once we build the Dynamic Query Object then we will use respective XXXLolaserviceUtil class to call the query and it will return list of records.

Example:


List<User> studentList=
StudentLocalServiceUtil.dynamicQuery(studentQuery);

Note:

We can implement dynamic query anywhere in development like we can write in JSP pages, Portlet Action Class and XXXServiceImpl.java classes.

Best Practices to implement Dynamic Query API

If we want perform search operation then we can use Dynamic Query API

Implement Dynamic Query in XXXLocalServiceImpl.java so that we can use it anywhere in the application.

If we want fetch the data from Portal Level Tables then we can use Dynamic Query.

If we want fetch from other Plugin portlet level tables then we can use Dynamic Query

AND Condition Dynamic Query


DynamicQuery studentQuery = DynamicQueryFactoryUtil.forClass(
User.class, PortletClassLoaderUtil.getClassLoader());

Criterion criterion = null;

criterion = RestrictionsFactoryUtil.eq("studentAge ",new Integer ("23"));

criterion=RestrictionsFactoryUtil.and(criterion1,
RestrictionsFactoryUtil.eq("studentGender ",new Integer ("1")));

studentQuery.add(criterion);

OR Condition Dynamic Query


DynamicQuery studentQuery = DynamicQueryFactoryUtil.forClass(
User.class, PortletClassLoaderUtil.getClassLoader());

Criterion criterion = null;

criterion = RestrictionsFactoryUtil.eq("studentAge ",new Integer ("23"));

criterion=RestrictionsFactoryUtil.or(criterion1,
RestrictionsFactoryUtil.eq("studentGender ",new Integer ("1")));

studentQuery.add(criterion);

Important Imports for Dynamic Query


<%@page import="com.liferay.portal.kernel.util.PortalClassLoaderUtil"%>
<%@page import="com.liferay.portal.kernel.dao.orm.Property"%>
<%@page import="com.liferay.portal.kernel.dao.orm.Junction"%>
<%@page import="com.liferay.portal.kernel.dao.orm.RestrictionsFactoryUtil"%>
<%@page import="com.liferay.portal.kernel.dao.orm.Criterion"%>
<%@page import="com.liferay.portal.kernel.dao.orm.PropertyFactoryUtil"%>
<%@page import="com.liferay.portal.kernel.portlet.PortletClassLoaderUtil"%>
<%@page import="com.liferay.portal.kernel.dao.orm.DynamicQueryFactoryUtil"%>
<%@page import="com.liferay.portal.kernel.dao.orm.DynamicQuery"%>

Simple Dynamic Query


<%
//Simple Dynamic Query
DynamicQuery studentQuery1 = DynamicQueryFactoryUtil.forClass(
Student.class, PortletClassLoaderUtil.getClassLoader());
List<Student> studentList1=StudentLocalServiceUtil.dynamicQuery(studentQuery1);
for(Student student:studentList1){
out.println(student.getFirstName()+"<br/>");
}
%>

 Dynamic Query for Portal Level Table/Entity


Native SQL Query

Select * from Student;

Dynamic Query

<%
//simple query to get data from user table which from portal
DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass(
User.class, PortalClassLoaderUtil.getClassLoader());
List<User> userList=UserLocalServiceUtil.dynamicQuery(userQuery);
for(User user:userList){
out.println(user.getEmailAddress()+"<br/>");
}
%>

In the above query we have used User table which is in portal level. So we need to use Portal Class Loader.

WHERE condition on single column


Native SQL Query

Select * from Student WHERE firstName=’meera’;

Dynamic Query

<%
//use where condition on single column
DynamicQuery studentQuery1 = DynamicQueryFactoryUtil.forClass(
Student.class, PortletClassLoaderUtil.getClassLoader());
studentQuery1.add(PropertyFactoryUtil.forName("firstName").eq(new String("meera")));
List<Student> studentList1=StudentLocalServiceUtil.dynamicQuery(studentQuery1);
for(Student student:studentList1){
out.println(student.getFirstName()+"<br/>");

}
%>

AND operator in WHERE Clause


Native SQL Query

Select * from Student WHERE firstName=’meera’ AND stundetGender=1;

Dynamic Query

<%
//use where condition on two  columns with AND operation
DynamicQuery studentQuery2 = DynamicQueryFactoryUtil.forClass(
Student.class, PortletClassLoaderUtil.getClassLoader());
studentQuery2.add(PropertyFactoryUtil.forName("firstName").eq(new String("meera")));
studentQuery2.add(PropertyFactoryUtil.forName("studentGender").eq(new Integer("1")));
List<User> userList2=UserLocalServiceUtil.dynamicQuery(studentQuery2);
List<Student> studentList2=StudentLocalServiceUtil.dynamicQuery(studentQuery2);
for(Student student:studentList2){
out.println(student.getFirstName()+"<br/>");
}
%>

Criterion with AND operator


Native SQL Query

Select * from Student WHERE firstName=’meera’ AND stundetGender=1;

Dynamic Query

<%
//use Cretierian to apply where condition condition on two  columns with AND operation
DynamicQuery studentQuery4 = DynamicQueryFactoryUtil.forClass(
Student.class, PortletClassLoaderUtil.getClassLoader());
Criterion criterion1 = null;
criterion1 = RestrictionsFactoryUtil.eq("firstName",new String("meera"));
criterion1=RestrictionsFactoryUtil.and(criterion1,
RestrictionsFactoryUtil.eq("studentGender",new Integer("1")));
studentQuery4.add(criterion1);
List<Student> studentList4=StudentLocalServiceUtil.dynamicQuery(studentQuery4);
for(Student student:studentList4){
out.println(student.getFirstName()+"<br/>");
}
%>

In the above Dynamic Query we have used Criterion to prepare Query.

Criterion with OR operator


Native SQL Query

Select * from Student WHERE firstName=’meera’ OR stundetGender=1;

Dynamic Query

<%
//use Cretierian to apply where condition condition on two  columns with OR operation
DynamicQuery studentORCriteriaQuery = DynamicQueryFactoryUtil.forClass(
Student.class, PortletClassLoaderUtil.getClassLoader());
Criterion criterion8 = null;
criterion8 = RestrictionsFactoryUtil.eq("firstName",new String("meera"));
criterion8=RestrictionsFactoryUtil.or(criterion8,
RestrictionsFactoryUtil.eq("studentGender",new Integer("1")));
studentORCriteriaQuery.add(criterion8);
List<Student> studentList9=StudentLocalServiceUtil.dynamicQuery(studentORCriteriaQuery);
for(Student student:studentList9){
out.println(student.getFirstName()+"<br/>");
}
%>

Junction With AND Operator in Where Clause


Native SQL Query

Select * from Student WHERE studentAge=28 AND stundetGender=1;

Dynamic Query

<%
//Use AND Junction
DynamicQuery dynamicjunctionANDQuery = DynamicQueryFactoryUtil.forClass(Student.class,
PortletClassLoaderUtil.getClassLoader());
Junction junctionAND = RestrictionsFactoryUtil.conjunction();
Property property = PropertyFactoryUtil.forName("studentAge");
junctionAND.add(property.eq(Integer.valueOf(28)));
property = PropertyFactoryUtil.forName("studentGender");
junctionAND.add(property.eq(Integer.valueOf(1)));
dynamicjunctionANDQuery.add(junctionAND);
List<Student> studentList5=StudentLocalServiceUtil.dynamicQuery(dynamicjunctionANDQuery);
for(Student student:studentList5){
out.println(student.getFirstName()+"<br/>");
}
%>

When the query consist of all AND operators between columns then we can use Junction and that’s Conjunction.

Junction With OR Operator in Where Clause


Native SQL Query

Select * from Student WHERE studentAge=28 OR stundetGender=1;

Dynamic Query

<%
//Use OR Junction
DynamicQuery studentjunctionORQuery = DynamicQueryFactoryUtil.forClass(Student.class,
PortletClassLoaderUtil.getClassLoader());
Junction junctionOR = RestrictionsFactoryUtil.disjunction();
Property property= PropertyFactoryUtil.forName("studentAge");
junctionOR.add(property.eq(Integer.valueOf(28)));
property = PropertyFactoryUtil.forName("studentGender");
junctionOR.add(property.eq(Integer.valueOf(1)));
studentjunctionORQuery.add(junctionOR);
List<Student> studentList10=StudentLocalServiceUtil.dynamicQuery(studentjunctionORQuery);
for(Student student:studentList10){
out.println(student.getFirstName()+"<br/>");
}
%>

When the query consist of all OR operators between columns then we can use Junction and that’s Disjunction

Equal Operator in Criterion


Native SQL Query

Select * from Student WHERE firstName=’meera’;

Dynamic Query

<%
//use EQUAL Operator against Column
DynamicQuery studentQuery1 = DynamicQueryFactoryUtil.forClass(
Student.class, PortletClassLoaderUtil.getClassLoader());
studentQuery1.add(PropertyFactoryUtil.forName("firstName").eq(new String("meera")));
List<Student> studentList1=StudentLocalServiceUtil.dynamicQuery(studentQuery1);
for(Student student:studentList1){
out.println(student.getFirstName());

}
%>

Like Operator in Criterion


Native SQL Query

Select * from Student WHERE firstName like %me% AND stundetGender=1;

Dynamic Query

<%
//Use Like Operator in Criterain
DynamicQuery studentLikeOperatorQuery = DynamicQueryFactoryUtil.forClass(
Student.class, PortletClassLoaderUtil.getClassLoader());
Criterion criterion3 = null;
criterion3 = RestrictionsFactoryUtil.like("firstName",new StringBuilder("%").append("me").append("%").toString());
criterion3=RestrictionsFactoryUtil.and(criterion3,
RestrictionsFactoryUtil.eq("studentGender",new Integer("1")));
studentLikeOperatorQuery.add(criterion3);
List<Student> studentList6=StudentLocalServiceUtil.dynamicQuery(studentLikeOperatorQuery);
for(Student student:studentList6){
out.println(student.getFirstName()+"<br/>");
}
%>

Greater Than Operator in Criterion


Native SQL Query

Select * from Student WHERE firstName like %me% AND stundetAge > 23;

Dynamic Query

<%
//Use > operator
DynamicQuery studentGreaterThanOperatorQuery = DynamicQueryFactoryUtil.forClass(
Student.class, PortletClassLoaderUtil.getClassLoader());
Criterion criterion5 = null;
criterion5 = RestrictionsFactoryUtil.like("firstName",new StringBuilder("%").append("me").append("%").toString());
criterion5=RestrictionsFactoryUtil.and(criterion5,
RestrictionsFactoryUtil.gt("studentAge",new Integer("23")));
studentGreaterThanOperatorQuery.add(criterion5);
List<Student> studentList7=StudentLocalServiceUtil.dynamicQuery(studentGreaterThanOperatorQuery);
for(Student student:studentList7){
out.println(student.getFirstName());
}
%>

IN Operator in Criterion


<%
//Use in operator
DynamicQuery studentINOperator= DynamicQueryFactoryUtil.forClass(
Student.class, PortletClassLoaderUtil.getClassLoader());
Criterion criterion6 = null;
criterion6 = RestrictionsFactoryUtil.in("firstName",new String[]{"meera","prince","naga"});
criterion6=RestrictionsFactoryUtil.and(criterion6,
RestrictionsFactoryUtil.eq("studentGender",new Integer("1")));
studentINOperator.add(criterion6);
List<Student> studentList8=StudentLocalServiceUtil.dynamicQuery(studentINOperator);
for(Student student:studentList8){
out.println(student.getFirstName()+"<br/>");
}
%>

When we use IN operator we need pass Array of object values and object array may contains int, long and String based in our Column data Type.

Using Order in Dynamic Query


<%
//Use Order operator
DynamicQuery studentOrderCriteriaQuery = DynamicQueryFactoryUtil.forClass(
Student.class, PortletClassLoaderUtil.getClassLoader());
Criterion criterion9 = null;
criterion9 = RestrictionsFactoryUtil.eq("firstName",new String("meera"));
criterion9=RestrictionsFactoryUtil.or(criterion9,
RestrictionsFactoryUtil.eq("studentGender",new Integer("1")));
studentOrderCriteriaQuery.add(criterion9);
studentOrderCriteriaQuery.addOrder(OrderFactoryUtil.desc("firstName"));
//studentOrderCriteriaQuery.addOrder(OrderFactoryUtil.asc("firstName"));
List<Student> studentList11=StudentLocalServiceUtil.dynamicQuery(studentOrderCriteriaQuery);
for(Student student:studentList11){
out.println(student.getFirstName()+"<br/>");
}
%>

Note:

All Query implementation was done in JSP pages but you can implement anywhere.

In the Portlet Source Code I implemented Search Dynamic Query in StudentLocalServiceImpl.java class you can look into the source code then you can understand more.

Important Points:
  • We can replace the Liferay Custom SQL with Dynamic Query for all read operations.
  • Dynamic Query API is very easy to use and it can be more understandable to developer and developer need not concern about Native SQL queries.
  • In the article we have read the data from specific table and all objects are only one mode object type.
  • Dynamic Query API is inherited from Hibernate/JPA Criteria API.
  • RestrictionsFactoryUtil contains all Possible Operators which we have used in WHERE Clause and also have all Column level operators.
  • PropertyFactoryUtil contains all possible Operators which we have used for Column conditions.

Download Liferay Custom SQL on Multiple Tables Portlet

https://sourceforge.net/projects/meeralferay/files/DynamicQueryAPIPortlet/




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 name as StudentLiferayMVC.

Once you drop the portlet in the page you can see the link Liferay Dynamic Query Examples
Portlet Screens:

Default Page


Dynamic Query Examples Page View


Related Articles

http://www.liferaysavvy.com/2014/06/scalars-in-liferay-custom-sqlcustom-sql.html

http://www.liferaysavvy.com/2014/06/liferay-custom-sql-using-native-sql.html

http://www.liferaysavvy.com/2014/06/liferay-service-builder-custom-service.html

http://www.liferaysavvy.com/2014/06/liferay-service-builder-finder-methods.html

http://www.liferaysavvy.com/2014/05/liferay-mvc-portlet-database.html

http://www.liferaysavvy.com/2014/05/liferay-mvc-portlet-form-submission.html




Author
Meera Prince
Liferay Top Contributor Award Winner
http://www.liferaysavvy.com

0 comments :

Post a Comment

Subscribe to: Post Comments ( Atom )

Recent Posts

Recent Posts Widget

Popular Posts