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

Create Dynamic Query Object against Entity Model Class
Create Criteria and Add criteria to Dynamic Query Object
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

Portal Class Loader
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 PropertyFactoryUtilAPI 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);

RestrictionsFactoryUtilis 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()+" ");

}

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()+" ");

}

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()+" ");

}

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()+" ");

}

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()+" ");

}

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()+" ");

}

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()+" ");

}

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()+" ");

}

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()+" ");

}

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()+" ");

}

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()+" ");

}

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.javaclass 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.
RestrictionsFactoryUtilcontains all Possible Operators which we have used in WHERE Clause and also have all Column level operators.
PropertyFactoryUtilcontains 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

About Meera Prince

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.

Liferay Dynamic Query API

About Meera Prince

0 comments:

Post a Comment

Video Of Day

Liferay Dynamic Query API

About Meera Prince

RELATED POSTS

0 comments:

Post a Comment