« Back to Queries

Dynamic Query API

(Redirected from Queries 2: DynamicQuery API)

Introduction #

Liferay provides several ways to define complex queries used in retrieving database data. Each service Entity typically defines several 'finder' methods which form the basis for the default views used throughout the portal.

There are several reasons/use cases for wanting to move beyond those existing 'finder' queries:

  • the level of complexity allowed by the service generation is not sufficient for your purposes and/or
  • you need queries which implement aggregate SQL operations such as max, min, avg, etc.
  • you want to return composite objects or tuples rather than the mapped object types
  • you want to access the data in way not originally conceived for whatever reason
  • query optimization
  • complex data access, like reporting

This is done mainly through Liferay providing access to Hibernate's Dynamic Query API. Within the earlier 4.3 version, there was a direct dependency on Hibernate and hence it was only usable within the core or ext environments. However, This has been changed in 5.1+ whereby all these classes are now placed in wrappers and can be more easily executed.

Complete documentation from Hibernate can be found here

5.1+ Example#

Here is a simple query to find bookmarks associated to a given userId and folderId, returning a list of BookmarksEntry (the start/end are optional):

DynamicQuery query = DynamicQueryFactoryUtil.forClass(BookmarksEntry.class)
	.add(PropertyFactoryUtil.forName("folderId").eq(new Long(folderId)))
	.add(PropertyFactoryUtil.forName("userId").eq(new Long(userId)))
	.addOrder(OrderFactoryUtil.asc("createDate"));

List results = BookmarksEntryLocalServiceUtil.dynamicQuery(query, start, end);

4.3+ Example#

For our example, let's consider the BookmarksEntry entity that belongs to the Bookmarks portlet/service.

By default the Bookmarks portlet displays it's content on a per group basis, that is to say it shows all the Bookmarks in a Community (subject to individual permissions of course) all at once. On the other hand, when Bookmarks are created they reference the user who created them. As such, it would be entirely possible to query for the Bookmarks of a given user. If one wanted to create an extended or modified Bookmarks portlet which took the user into account, then this would require queries involving the userId as a parameter.

Assuming we know the folderId in question, the following DetachedCriteria query does just what we want:

  DetachedCriteria query = 
    DetachedCriteria.forClass(BookmarksEntry.class)
        .add(Property.forName("folderId").eq(new Long(folderId)))
        .add(Property.forName("userId").eq(new Long(userId)));

If we wanted to order results, for example on createDate:

  DetachedCriteria query = 
    DetachedCriteria.forClass(BookmarksEntry.class)
        .add(Property.forName("folderId").eq(new Long(folderId)))
        .add(Property.forName("userId").eq(new Long(userId)))
        .addOrder(Order.asc("createDate"));

If we wanted to get the user's most visited bookmark:

  DetachedCriteria query = 
    DetachedCriteria.forClass(BookmarksEntry.class)
        .add(Property.forName("folderId").eq(new Long(folderId)))
        .add(Property.forName("userId").eq(new Long(userId)))
        .setProjection(Projections.max("visits"));

If we wanted to get the number of bookmarks:

  DetachedCriteria query = 
    DetachedCriteria.forClass(BookmarksEntry.class)
        .add(Property.forName("folderId").eq(new Long(folderId)))
        .add(Property.forName("userId").eq(new Long(userId)))
        .setProjection(Projections.rowCount());

Subqueries, Associations, Projections, Aliases are all features available through the DetachedCriteria API.

To continue with our example, we should consider the fact that all of the portal/portlet services, having been created through service generation, automatically implement the following two methods:

  public static java.util.List dynamicQuery(
    com.liferay.portal.kernel.dao.DynamicQueryInitializer queryInitializer)
    throws com.liferay.portal.SystemException {
    ...
  }
  
  public static java.util.List dynamicQuery(
    com.liferay.portal.kernel.dao.DynamicQueryInitializer queryInitializer,
    int begin, int end) throws com.liferay.portal.SystemException {
    ...
  }

i.e.

  BookmarksEntryLocalServiceUtil.dynamicQuery(DynamicQueryInitializer queryInitializer);
  BookmarksEntryLocalServiceUtil.dynamicQuery(DynamicQueryInitializer queryInitializer, int begin, int end);

They allow us to define and pass along a DetachedCriteria through the DynamicQueryInitializer on to the db session. The basic logic is as follows:

  • Define the DetachedCriteria query
  • Create a DynamicQueryInitializer (dqi) using the query
  • Pass the dqi into the service's dynamicQuery() methods

Let's create a helper class to contain all this logic and our new query methods.

  public class BookmarksQueryUtil {
  }

To fulfill our contract with the default business logic of the portal, we usually require three types of methods, one providing a count of the objects, a second for retrieving all the objects, and one for paginating through the objects.

First the object count:

  public class BookmarksQueryUtil {
  
    public static int getEntryCount(long folderId, long userId) {
      
      DetachedCriteria query = 
        DetachedCriteria.forClass(BookmarksEntry.class)
          .add(Property.forName("folderId").eq(new Long(folderId)))
          .add(Property.forName("userId").eq(new Long(userId)))
          .setProjection(Projections.rowCount());
    
      DynamicQueryInitializer dqi = new DynamicQueryInitializerImpl(query);
      
      int count = 0;
      
      try {
        Iterator resultsItr = 
          BookmarksEntryLocalServiceUtil.dynamicQuery(dqi).iterator();
        
        if (resultsItr.hasNext()) {
          count = ((Integer)resultsItr.next()).intValue();
        }
      }
      catch (SystemException se) {
        _log.error(se.getMessage(), se);
      }
      
      return count;
    }
    
    ...
  
  }

Next, all the objects:

  public class BookmarksQueryUtil {
  
    ...
    
    public static List getEntries(long folderId, long userId) {
  
      DetachedCriteria query = 
        DetachedCriteria.forClass(BookmarksEntry.class)
            .add(Property.forName("folderId").eq(new Long(folderId)))
            .add(Property.forName("userId").eq(new Long(userId)));
  
      DynamicQueryInitializer dqi = new DynamicQueryInitializerImpl(query);
  
      List results = new ArrayList();
  
      try {
        results = BookmarksEntryLocalServiceUtil.dynamicQuery(dqi);
      }
      catch (SystemException se) {			
        _log.error(se.getMessage(), se);
      }
  	
      return results;
    }
    
    ...
  
  }

Last, the paginator:

  public class BookmarksQueryUtil {
  
    ...
    
    public static List getEntries(long folderId, long userId, int start, 
      int end) {
  
      DetachedCriteria query = 
        DetachedCriteria.forClass(BookmarksEntry.class)
            .add(Property.forName("folderId").eq(new Long(folderId)))
            .add(Property.forName("userId").eq(new Long(userId)));
  
      DynamicQueryInitializer dqi = new DynamicQueryInitializerImpl(query);
  
      List results = new ArrayList();
  
      try {
        results = 
          BookmarksEntryLocalServiceUtil.dynamicQuery(dqi, start, end);
      }
      catch (SystemException se) {			
        _log.error(se.getMessage(), se);
      }
  
      return results;
    }
  
    ...
  
  }

How to use a OR clause

Junction junction = RestrictionsFactoryUtil.disjunction();
junction.add(...);
junction.add(...);
dynamicQuery.add(junction)

Conclusion#

The 'DynamicQuery API' provides an elegant way to define complex queries without complex setup or a stiff and abstract learning curve. This abstracts away the SQL grammar, making it DB agnostic, without giving up all of the power. There are no configuration files and no abhorrent embedded SQL strings. And, since it creates the query without the immediate need of a db session the queries can be assembled through business logic, making them even more flexible.

Related Articles #

0 Attachments
110272 Views
Average (3 Votes)
The average rating is 4.66666666666667 stars out of 5.
Comments
Threaded Replies Author Date
Nice tutor, thx! I've spotted that the... Richard Oliver Legendi December 16, 2009 4:29 AM
This is a wiki: anybody can edit the page to... Pierpaolo Cira March 16, 2010 5:24 AM
This issue can be usefull if you are using it... Denis Signoretto April 19, 2010 1:44 AM
Hello, How the results of dynamicQuery is... Anonymous May 27, 2010 2:26 AM
I found this wiki interesting, and like to know... David Peng August 16, 2010 1:52 PM
Detached criteria doesn't seems to work in... Mohammed Azam December 20, 2010 9:46 AM
It would be great if anybody could update this... Jakub Liska January 18, 2011 4:54 PM
Nicely explained... According to the steps... Jay Patel March 23, 2011 12:05 AM
Hi All, How to create dynamic query were we... Satish Sapate September 27, 2011 5:24 AM
Hi All, How to create dynamic query were we... Satish Sapate September 27, 2011 5:23 AM
Working fine. Thanks for helping me out from... Jay Amrutkar December 28, 2011 11:32 PM
Nice tutorial - I think you could expand the... Robert Leo Smith February 15, 2012 8:41 AM

Nice tutor, thx!
I've spotted that the "Complete documentation from Hibernate can be found here" link is broken.
Posted on 12/16/09 4:29 AM.
This is a wiki: anybody can edit the page to adjust a link... emoticon
Posted on 3/16/10 5:24 AM in reply to Richard Oliver Legendi.
This issue can be usefull if you are using it in plugins SDK.

http://issues.liferay.com/browse/LPS-2534
Posted on 4/19/10 1:44 AM.
Hello,

How the results of dynamicQuery is casted?
Because I've a List of Object (setProjection is well defined), but I don't know how to access my attributes.

Thanks
Posted on 5/27/10 2:26 AM in reply to Denis Signoretto.
I found this wiki interesting, and like to know further about dynamic query API, does this API support some advanced query, such as join, outer join, etc.
Thanks.
Posted on 8/16/10 1:52 PM.
Detached criteria doesn't seems to work in plugin portlet
Posted on 12/20/10 9:46 AM.
It would be great if anybody could update this article. It is so important and 90% of it is deprecated ...
Posted on 1/18/11 4:54 PM.
Nicely explained...
According to the steps mentioned, when I tried similar query with Document Library, it works fine.
The version, I am using is LR 5.2 SP4 EE.
Posted on 3/23/11 12:05 AM.
Hi All,
How to create dynamic query were we have to select data from more than one tables,
like tables are Employee(EmpId is PK),Branch(branchId is PK, and EmpId is FK of Employee),
How to get data from both tables using dynamic query.
Posted on 9/27/11 5:23 AM.
Hi All,
How to create dynamic query were we have to select data from more than one tables,
like tables are Employee(EmpId is PK),Branch(branchId is PK, and EmpId is FK of Employee),
How to get data from both tables using dynamic query.
Posted on 9/27/11 5:24 AM in reply to Jay Patel.
Working fine.
Thanks for helping me out from executing DyanamicQuery.
Posted on 12/28/11 11:32 PM.
Nice tutorial - I think you could expand the 'or' syntax though. It seems out of context with the rest of the example.
Posted on 2/15/12 8:41 AM.