Liferay Dynamic Query API is one of the best API to fetch data from database in object oriented fashion.Liferay provides many API to fetch data from database. Dynamic Query is one of them. In this article we will demonstrate how to build different types of dynamic query and execute them.
About Dynamic Query:
I am assuming that you are aware of Liferay Service Builder and you have basic understanding of persisting data by service builder tool. Consider that you have developed your portlet and you have persisted a lot many records in the database. You are a java developer and you don’t have much knowledge on queering database. Liferay Dynamic Query is the rescue in this case. Liferay provides API to query the database just like writing java program. You don’t need to bother about low level database query. One point to remember here that Dynamic Query is only to fetch data.
Below are the API we will be working with to write dynamic query.
com.liferay.portal.kernel.dao.orm.DynamicQueryFactoryUtil com.liferay.portal.kernel.dao.orm.DynamicQuery com.liferay.portal.kernel.dao.orm.Criterion com.liferay.portal.kernel.dao.orm.RestrictionsFactoryUtil com.liferay.portal.kernel.dao.orm.PropertyFactoryUtil com.liferay.portal.kernel.dao.orm.Projection com.liferay.portal.kernel.dao.orm.ProjectionFactoryUtil com.liferay.portal.kernel.dao.orm.ProjectionList com.liferay.portal.kernel.dao.orm.OrderFactoryUtil
How to build Dynamic Query:
The basic syntax of building dynamic query is
DynamicQuery dynamicQuery= DynamicQueryFactoryUtil.forClass(Entity_Name.class, PortalClassLoaderUtil.getClassLoader());
OR
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(Entity_Name.class, PortletClassLoaderUtil.getClassLoader());
Note 1: In the above syntax you can use any Entity which is generated by Liferay Service builder.
Note 2: If you working with dynamic query inside your custom portlet and trying to access liferay core entity (i.e, database) then use Portal Class Loader.
Note 3: If you are accessing the custom portlet level entity then use Portlet Class Loader.
Lets start exploring dynamic query:
Before writing this article I have created a sample jsp portlet and I tested all the query inside the JSP. And off course you can use dynamic query in your portlet class without any problem.For this demo I have considered the below entity class
com.liferay.portal.model.User
This entity is persisted in User_ table of Liferay database. In the below examples first I have shown the SQL query then I have shown the corresponding dynamic query.
Note : In the below examples I have used hard coded value in the dynamic query. Those hard coded value can be replaced by dynamic variable and that’s what its meaningful to call as dynamic Query.
1.SELECT * FROM User_ WHERE lastName=’Bloggs’
Dynamic Query:
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(User.class, PortalClassLoaderUtil.getClassLoader());
dynamicQuery.add(PropertyFactoryUtil.forName("lastName").eq("Bloggs"));
List<User> userList = UserLocalServiceUtil.dynamicQuery(dynamicQuery);
2. SELECT * FROM User_ WHERE lastName like ‘ord%’
Dynamic Query:
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(User.class, PortalClassLoaderUtil.getClassLoader());
dynamicQuery.add(PropertyFactoryUtil.forName("lastName").like("ord%"));
List<User> userList = UserLocalServiceUtil.dynamicQuery(dynamicQuery);
3.SELECT * FROM User_ WHERE userId BETWEEN 10931 AND 10945
Dynamic Query:
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(User.class, PortalClassLoaderUtil.getClassLoader());
dynamicQuery.add(PropertyFactoryUtil.forName("userId").between(new Long(10931), new Long(10945)));
List<User> userList = UserLocalServiceUtil.dynamicQuery(dynamicQuery);
Note : The userId in service.xml is long. That’s why I have used Long .
4.SELECT * FROM User_ WHERE userId < 11376
Dynamic Query:
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(User.class, PortalClassLoaderUtil.getClassLoader());
dynamicQuery.add(PropertyFactoryUtil.forName("userId").lt(new Long(11376)));
List<User> userList = UserLocalServiceUtil.dynamicQuery(dynamicQuery);
5.SELECT * FROM User_ WHERE userId < = 11376
Dynamic Query:
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(User.class, PortalClassLoaderUtil.getClassLoader());
dynamicQuery.add(PropertyFactoryUtil.forName("userId").le(new Long(11376)));
List<User> userList = UserLocalServiceUtil.dynamicQuery(dynamicQuery);
5.SELECT * FROM User_ WHERE userId > 14015
Dynamic Query:
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(User.class, PortalClassLoaderUtil.getClassLoader());
dynamicQuery.add(PropertyFactoryUtil.forName("userId").gt(new Long(14015)));
List<User> userList = UserLocalServiceUtil.dynamicQuery(dynamicQuery);
6.SELECT * FROM User_ WHERE userId > = 14015
Dynamic Query:
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(User.class, PortalClassLoaderUtil.getClassLoader());
dynamicQuery.add(PropertyFactoryUtil.forName("userId").ge(new Long(14015)));
List<User> userList = UserLocalServiceUtil.dynamicQuery(dynamicQuery);
7.SELECT DISTINCT firstName from User_
Dynamic Query:
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(User.class, PortalClassLoaderUtil.getClassLoader());
Projection projection = ProjectionFactoryUtil.distinct(ProjectionFactoryUtil.property("firstName"));
dynamicQuery.setProjection(projection);
List<Object> userList = UserLocalServiceUtil.dynamicQuery(dynamicQuery);
Note 1: Use projection when you need only specific column
Note 2: When you use projection the query result is List of Object for single column
8.SELECT userId, firstName from User_
Dynamic Query:
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(User.class, PortalClassLoaderUtil.getClassLoader());
ProjectionList projectionList = ProjectionFactoryUtil.projectionList();
projectionList.add(ProjectionFactoryUtil.property("userId"));
projectionList.add(ProjectionFactoryUtil.property("firstName"));
dynamicQuery.setProjection(projectionList);
List<Object[]> userList = UserLocalServiceUtil.dynamicQuery(dynamicQuery);
Note 1: If you need to select multiple column use ProjectionList
Note 2: In this case we are queering 2 (i.e., multiple) columns. Therefore the result of this query is List<Object[]>. Each object is consist of 2 objects. You can access each object like this
for (Object[] obj: userList) {
//obj[0];
//obj[1];
}
9.SELECT * from User_ WHERE firstName = ‘Test’ AND userId = 10663
Dynamic Query:
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(User.class, PortalClassLoaderUtil.getClassLoader());
Criterion criterion = null;
criterion = RestrictionsFactoryUtil.eq("firstName", "Test");
criterion = RestrictionsFactoryUtil.and(criterion, RestrictionsFactoryUtil.eq("userId", new Long(10663)));
dynamicQuery.add(criterion);
List<User> userList = UserLocalServiceUtil.dynamicQuery(dynamicQuery);
Alternative way (Using RestrictionsFactoryUtil.conjunction):
The same query can be generated by Using Conjunction
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(User.class, PortalClassLoaderUtil.getClassLoader());
Junction junction = RestrictionsFactoryUtil.conjunction();
junction.add(PropertyFactoryUtil.forName("firstName").eq("Test"));
junction.add(PropertyFactoryUtil.forName("userId").eq(new Long(10663)));
dynamicQuery.add(junction);
List<User> userList = UserLocalServiceUtil.dynamicQuery(dynamicQuery);
Note1: RestrictionsFactoryUtil.and is used to build AND query. Similarly for OR query we can use RestrictionsFactoryUtil.or
Note 2: If in your query you have all AND operator then you can use Junction
Note 3: If you have all OR operator in your query then you can use RestrictionsFactoryUtil.disjunction the same way as shown above.
10.SELECT * from User_ WHERE lastName = ‘Bloggs’ order by lastName asc
Dynamic Query:
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(User.class, PortalClassLoaderUtil.getClassLoader());
dynamicQuery.add(PropertyFactoryUtil.forName("lastName").eq("Bloggs"));
dynamicQuery.addOrder(OrderFactoryUtil.asc("lastName"));
List<User> userList = UserLocalServiceUtil.dynamicQuery(dynamicQuery);
Note : For desc use OrderFactoryUtil.desc