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 #

• How To's
• How to create a custom query in liferay