• Liferay Dynamic Query API

    Posted on September 23, 2014 by Hamidul Islam in Liferay.

    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

    Post Tagged with ,

9 Responses so far.

  1. kartik shiroya says:

    Thanks hamidul
    your blog is very use full and explanation is to good

  2. Bharamani says:

    Sir how to execute this query in liferay

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

    Thanks
    Bharamani

  3. Vini says:

    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?

  4. khaja says:

    Sir can u plz show how to get data from multiple entities using dynamic query.

  5. Abhishek says:

    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 !

    • Edwin Lobo says:

      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);

  6. Edwin Lobo says:

    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?

  7. Praveenraj says:

    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

  8. michael.wang says:

    how is the inner join query?

Leave a Reply

Your email address will not be published. Required fields are marked *

Are you human? * Time limit is exhausted. Please reload CAPTCHA.

Top
%d bloggers like this:

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close