Introduction

If you've used any Liferay entities or ServiceBuilder custom entities, you've likely run into a situation where you've needed to perform a custom query to get a set of results that are outside of what the normal methods provide. So you've had to use a DynamicQuery in order to make that happen.

Or perhaps you've needed to perform batch operations on entities and needed to build out an ActionableDynamicQuery or specifically you're handling exports with an ExportActionableDynamicQuery or index updates using an IndexableActionableDynamicQuery.

For those who are not familiar with the DynamicQuery, it is a tool to build a custom query against your entity using Java code. So you're not writing native SQL or HQL, but you're building an equivalent using the DynamicQuery and other interfaces. While DynamicQuery is Liferay's set of APIs, they are wrappers on top of Hibernate's Criteria API. Definitions are going to change, class names and packages are going to be different, but many of the concepts will feel the same.

The rest of this post is going to cover how to instantiate a DQ instance, how to build out a query, and how to get a list of results too. Oh, and I'll also show an example of how to do batch updates to entities using the ActionableDynamicQuery.

Instantiating a DQ Instance

If you come from the Liferay 6.x or earlier days, we used to primarily use a factory class, DynamicQueryFactoryUtil to get a DQ instance for a particular entity. It has a method, forClass(), that takes the interface class name for the entity to act upon, and there was an overloaded option to pass in a ClassLoader for cases where you needed an instance for the portal's class loader or a class loader from another portlet WAR.

Since Liferay 7.0, as the Liferay entities have been moving out of core into OSGi modules, using the DQFU's forClass() method has been harder to use because there are many more possible class loaders in play, so I recommend not using DQFU at all.

Stop using DynamicQueryFactoryUtil's forClass() method.

Instead, every XxxLocalService interface includes a method, dynamicQuery(), that returns a new DQ instance for the entity ready to be used anywhere in the portal. No class loader headaches, no runtime exceptions due to CNFEs, etc.

So if we want a DQ instance for JournalArticle, for example, we would use the line:

DynamicQuery dq = journalArticleLocalService.dynamicQuery();

It's a similar method to get a DQ instance for other Liferay entities and even your custom entities.

DQ Queries

So a DQ is used to select entities. The return type from invoking the DQ is going to be a list of your entities. So you don't need to itemize fields to retrieve like you would in an SQL statement, so that part of the query is handled for you.

Restrictions

We do need to cover how to handle the WHERE portion of the query, and we do that using Restrictions.

We can create a Restrictions instance using the RestrictionsFactoryUtil class. The RFU class has a number of methods representing tests such as eq() for equals, ne() for not equals, etc. To get the full list of supported tests, check out the com.liferay.portal.kernel.dao.orm.RestrictionsFactoryUtil class.

Most of the tests are used to match against field values. So for our JournalArticle DQ, we might want to find a particular Article ID:

dq.add(RestrictionsFactoryUtil.eq("articleId", articleIdParameter));

The first argument is going to be the field name, the same name for the column as you'd find in the service.xml <column  /> tag.

The second argument is, in this particular case, the value to test for equality. You can either use a constant or, in this case, I'd be using a parameter value to compare to.

The DQ's add() method can be called to repeatedly add a restriction to the clause. Each add() effectively is an AND operator between the restrictions. The following code:

dq.add(RestrictionsFactoryUtil.eq("articleId", articleIdParameter));
dq.add(RestrictionsFactoryUtil.eq("groupId", 12345));

This code is equivalent to a WHERE clause like WHERE articleId=? AND groupId=?

If you need to use an OR clause or you want to build a more complex query with AND, OR, and NOT including groups, the RestrictionsFactory has and(), or() and not() methods that take Criterion as arguments.

To convert the above clause to WHERE articleId=? OR groupId=?, we would do the following:

Criterion articleId = RestrictionsFactoryUtil.eq("articleId", articleIdParameter);

Criterion groupId = RestrictionsFactoryUtil.eq("groupId", 12345);

dq.add(RestrictionsFactoryUtil.or(articleId, groupId);

Projections

As previously stated, normally a DQ is used to return a list of all entities and all fields of the entity.

But sometimes you don't need every field in the entity, maybe you only need one or a handful of the fields. For example, if you just needed the Article IDs for articles in the pending status, we could use a query like:

DynamicQuery dq = journalArticleLocalService.dynamicQuery();

dq.add(RestrictionsFactoryUtil.eq("status", WorkflowConstants.STATUS_PENDING));

dq.setProjection(PropertyFactoryUtil.forName("articleId"));

List<String> pendingArticleIds = journalArticleLocalService.dynamicQuery(dq);

The projection indicates that we want the article ids, and it also means our return list will be a list of Strings (the article ids), not the complete entity.

Projections can also be useful when you want to do a subquery. So the following:

DynamicQuery groupQuery = groupLocalService.dynamicQuery();

groupQuery.add(RestrictionsFactoryUtil.eq("companyId", 2345));

groupQuery.setProjection(PropertyFactoryUtil.forName("groupId"));

DynamicQuery dq = journalArticleLocalService.dynamicQuery();

dq.add(RestrictionsFactoryUtil.in("groupId", groupQuery));

List<JournalArticle> companyGroupArticles = journalArticleLocalService.dynamicQuery(dq);

Because we use the projection on the groupQuery for the groupId, our list is going to be a list of longs, the group ids for all groups in the company.

The main DQ will have an IN restriction to make sure the groupId is in the list returned from the subquery.

Projections can also be used to do calculations against the field values. For example, if you have an entity with a column named "votes", you could get the sum of all votes using:

DynamicQuery dq = xxxLocalService.dynamicQuery();

dq.setProjection(ProjectionFactoryUtil.sum("votes"));

List<Long> votes = xxxLocalService.dynamicQuery(dq);

return votes.get(0);

This method would return the sum of the votes field. To check out all of the available projection functions check out the com.liferay.portal.kernel.dao.orm.ProjectionFactoryUtil class.

Order

In your DQ, you can control the order of the returned results, ordering by one (or more) of the entry's fields.

To order based upon the modified date in descending order (so newest first, oldest last), we would do:

DynamicQuery dq = journalArticleLocalService.dynamicQuery();

dq.addOrder(OrderFactoryUtil.desc("modifiedDate"));

List<JournalArticle> = journalArticleLocalService.dynamicQuery(dq);

You can add multiple Order instances to get the kind of sorting that you need.

Executing Dynamic Queries

So we've seen how to build up the DQ instances, but how do we invoke them?

Well, there are two easy ways, using dynamicQueryCount(dq) and dynamicQuery(dq). These are two methods that will be part of your XxxLocalService interface and should be used to execute the queries.

dynamicQueryCount()

Yep, as you probably guessed, dynamicQueryCount() issues your dynamic query and returns the count of matches. It's kind of the equivalent of SELECT count(*) FROM (dq); in that it returns the count of objects the DQ will match.

When using the dynamicQueryCount() method, it is really inefficient to not use a projection. You don't really need the whole entity to count matches when, for example, the primary key (id) is more than enough.

So you should use the DQ's setProjection() method to apply a projection and simplify the count handling. This can be a pain if you want to use the same DQ instance for counts and selects, so theres an overloaded method that accepts a projection as the second argument. So you could use something like:

DynamicQuery dq = journalArticleLocalService.dynamicQuery();

long count = journalArticleLocalService.dynamicQueryCount(dq, PropertyFactoryUtil.forName("id"));

dynamicQuery()

The XxxLocalService's dynamicQuery() method without args creates a new DQ instance, but with args it is used to execute the query.

The dynamicQuery() method comes in three flavors:

dynamicQuery(dq) - Executes the dynamic query and returns all matches.

dynamicQuery(dq, startPos, endPos) - Executes the dynamic query but returns a subset of all results (a windowed retrieval).

dynamicQuery(dq, startPos, endPos, OrderByComparator) - This is one method I would not recommend using. This method uses a windowed retrieve, but also a code-based sort on the results. I can't see a reason to sort in code when DQ already supports ordering which is handled as part of the database query.

The DQ interface does have a list() method attached. Personally, I'm not a fan. Sure, I know that if my DQ instance comes from journalArticleLocalService.dynamicQuery() that the list() method is going to return JournalArticle entities. But I just feel like it is ambiguous to see a "dq.list();" line vs the more specific "journalArticleLocalService.dynamicQuery(dq);".

Actionable Dynamic Queries

Did you know that Liferay actually has support in place for batch operations?

It sure does, and those are handled in the form of an ActionableDynamicQuery interface.

An ADQ has two main sections to it, the criteria method used to specify criteria to select rows to operate on and the perform action method used to affect a change to a single entity instance.

Typically an ADQ is better than a "retrieve all, update individual and persist" sort of loop. First, it uses special transaction handling code to wrap the update of a configurable number of rows in a single transaction. Updating multiple entities within a single transaction is actually a huge performance gain versus updating each fetched entity in a loop.

To create an ADQ, the XxxLocalService interface has a getActionableDynamicQuery() method to return a properly constructed instance.

After you have the instance, you call the setAddCriteriaMethod() to provide a callback to populate a DQ instance with the criteria to match records for update. So if we wanted to change the status of journal articles that are pending to denied, our criteria portion is going to be like:

ActionableDynamicQuery adq = journalArticleLocalService.getActionableDynamicQuery();

adq.setAddCriteriaMethod(new ActionableDynamicQuery.AddCriteriaMethod () {

  @Override
  public void addCriteria(DynamicQuery dq) {
    dq.add(RestrictionsFactoryUtil.eq("status", WorkflowConstants.STATUS_PENDING));
  }
});

We have to implement the addCriteria() method. We're given a DQ instance to add all of our criteria into, so basically we can add any restrictions necessary to build out the appropriate WHERE clause for record selection.

To continue the example, we also need to assign the perform action method:

adq.setPerformActionMethod(new ActionableDynamicQuery.PerformActionMethod<JournalArticle>() {

  @Override
  public void performAction(JournalArticle article) {
    article.setStatus(WorkflowConstants.STATUS_DENIED);
    journalArticleLocalService.updateJournalArticle(article);
  }
});

We don't really have to test if the status is PENDING because our criteria is only going to select articles in that status. We just have to make the entity changes and update the change.

With our ADQ ready, we can go ahead and actually apply it:

try {
  adq.peformActions();
} catch (Exception e) {
  _log.error("Error changing journal article statuses: " + e.getMessage(), e);
  throw new PortalException("Failed denying all pending journal articles.", e)
}

Now if all of this seems just too easy, well I have to confess it is. I mean, even a change this simple is not going to be reflected in the index, so your search index can be out of sync with what is now in the database.

If you delete entities or otherwise update them, other pieces of the system may be affected. Index is one, but the asset framework may be another. Be careful when creating your performAction() methods and consider updating those other necessary pieces.

There are actually two additional variants of the ADQ to be aware of.

The first is the IndexableActionableDynamicQuery. The IADQ is a specialized version of ADQ whose primary use case is to support bulk entity reindexing. If you had your own entities, for example, and want to have a "Reindex All" button on your page, basically the implementation of the action handler for the button is going to get one of these IADQ instances and use it to reindex all of your entities. Since it is an extension of ADQ, you get control over the criteria used to select entities for reindexing.

The next one is the ExportActionableDynamicQuery. The EADQ is used when exporting data. Common use case for this is when you are exporting an entity that needs to include other entities in the export. For example, when exporting an Asset, it in turn uses two EADQ instances from categories and tags to include the necessary entries in the export of the Asset. This is important when the definition of your entity (in this case the Asset) is incomplete without the additional entities (categories and tags) but they are otherwise not selected to include for export (you export assets, but you are not required to export categories and tags separately).

Best Practices

The most important best practice I can share is to stop using the DynamicQueryFactoryUtil class and its nefarious forClass() method. It is nothing but a headache, and since all XxxLocalService interfaces has the no-arg dynamicQuery() method to create a proper DQ instance, there is simply no reason to use the DQFU class at all.

As a rule, any time I find myself building code using DQ for a custom entity, I'm going to encapsulate that in a method in my XxxLocalServiceImpl class. After all, it really is a data access method and therefore should be in the service layer.

The same applies in principle to the PerformAction method implementation. I want to put an updateStatus(Journal Article article, int status); method in my XxxLocalServiceImpl class since that is the class with the knowledge about the indexing, asset framework, etc. If I put the method in there, I'm likely going to remember to update all of those necessary pieces.

Then the performAction() method implementation passed to the ADQ is only going to invoke that updateStatus() method from the local service. So my ADQ gets to update what it wants, but the details of the update are encapsulated in the local service itself.

Conclusion

So DynamicQuery is really nothing new to Liferay. I haven't really exposed anything new about DQ and the ADQ interfaces.

So why was it important to write this blog now? Well, actually I had a couple of questions from the community Slack as well as in the forums about problems stemming from the use of DynamicQueryFactoryUtil's forClass() method and the CNFEs that sometimes come from constructing the DQ instances correctly.

So I knew I needed to blog about the importance of using XxxLocalService's dynamicQuery() method to create an instance, but I figured why stop there? Why not go all in and give DQ some love? :D

Anyway, I hope this helps!

References

The following links to Liferay documentation can be useful and provide additional information:

https://portal.liferay.dev/docs/7-2/appdev/-/knowledge_base/a/dynamic-query This is Liferay's page documenting DynamicQuery usage. Although the doco says to use the DynamicQueryFactoryUtil.forClass() method, you should not use that method to create a DQ instance. Otherwise, the information on this page is quite helpful.

https://portal.liferay.dev/docs/7-2/appdev/-/knowledge_base/a/actionable-dynamic-queries This is Liferay's documentation page for the ActionableDynamicQuery and how to use it.

Blogs

Does dynamic query support Entity Cache / Finder Cache?

Good question, Nader.

I don't really know for sure, but my gut tells me it doesn't. I mean, when you're talking about projections and groupings, I think the answer is clearly no because you're not talking about an entity and the group calculations are affected by indirect storage updates (i.e. sum on a column can change if a record is added or modified but falls outside of potential cache lookups).

If it is supported, I guess it might only be through a basic query for entities using a restriction; that's about as close to a regular kind of query that you can get that supports a predictable response set, so I could see it possible to cache these results.

But honestly I'd want to test before proclaiming there is any caching going on even for those kinds of simple queries. 

Great article David! 

 

Do you know if it is possible to do bulk insert too? 

When we want to import a flat file, the easiest is to do a cancel/replace on all the data of the table. (no logic, no sync problem, no bug) 

Bulk insert can be great in this case? 

 

Shouldn't it be

DynamicQuery dq = JournalArticleLocalServiceUtil.dynamicQuery();

at the beginning of the article?

 

I'm having problems to get an instance of *LocalService...

 

Absolutely not. Any of the *LocalServiceUtil classes are for legacy use only, we don't use them any more. To get an instance of JournalArticleLocalService, you just need to @Reference it in on your @Component class and it will be injected.

Yes, that's true for the Portlet Component. But I've implemented the custom dynamic Query in my [myServicePackage].persistence.impl.[myFinderImpl] class.

 

How do I get my hands on the [myEntitiy]LocalService instance in that situation?

 

I followed this tutorial: https://help.liferay.com/hc/en-us/articles/360030614252-Defining-a-Custom-Finder-Method

I just tried and clearly that was the link I was missing. Thinking about it, it's logical because the Service and API layers ar OSGI components just like the Portlet module!

 

Thank you! :-)

Thank you for this great article. I'm using 7.3.X when trying dq.add(RestrictionsFactoryUtil.in("groupId", groupQuery)); ​​​​​​​ ​​​​​​I got

error: no suitable method found for in(String,DynamicQuery)                                 dq.add(RestrictionsFactoryUtil.in("groupId", groupQuery));                                                               ^     method RestrictionsFactoryUtil.in(String,Collection<?>) is not applicable       (argument mismatch; DynamicQuery cannot be converted to Collection<?>)     method RestrictionsFactoryUtil.in(String,Object[]) is not applicable       (argument mismatch; DynamicQuery cannot be converted to Object[])