Liferay Dynamic Query API

Liferay Dynamic Query API


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

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


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

About The Author

9 thoughts on “Liferay Dynamic Query API”

  1. Sir how to execute this query in liferay

    SELECT qualityId FROM `quality_quality`
    ORDER BY qualityId DESC
    LIMIT 1;

    Thanks
    Bharamani

  2. Hi I want to retrieve lifray cms content in a jsp page.Please mention which tables handle cms in liferay…also is it possible using Dynamic query API?

  3. select * from table_name where latitude like ‘37.774929%’ and longitude like -122.41941550000001;

    How to make dynamic query for above ?

    Thank you in advance !

    1. DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(Table_name.class,PortletClassLoaderUtil.getClassLoader());
      Junction junction = RestrictionsFactoryUtil.conjunction(); junction.add(RestrictionsFactoryUtil.like(“latitude”, “37.774929%”));
      junction.add(RestrictionsFactoryUtil.like(“longitude”, “-122.41941550000001”));
      dynamicQuery.add(junction); List entitylist = Entity_nameLocalServiceUtil.dynamicQuery(dynamicQuery);

  4. Thanks for this post! It’s been very clear and helpful for me.
    My question is: Should I implement this in any specific class? if yes, where?

  5. how to solve this
    long folderId=Folder.getFolderId();
    folderQuery2.add(PropertyFactoryUtil.forName(“parentFolderId”).eq(folderId));
    System.out.println(folderQuery2);

    List subList=DLFolderLocalServiceUtil.dynamicQuery(folderQuery2);//inside of Config/RegularExp
    System.out.println(DLFolderLocalServiceUtil.dynamicQuery(folderQuery2));
    System.out.println(“subList–“+subList);

    result is
    1st iteration is correctly Working but 2 iteration gives
    “CriteriaImpl(com.liferay.portlet.documentlibrary.model.impl.DLFolderImpl:this[][groupId=20181, status8, parentFolderId=38260, parentFolderId=38352])”

    so please help me, who to know this
    Thank you in advance

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top