Blogs

Using the Dynamic Query API

Minhchau Dang

August 17, 2009

A Minute Read

Assuming that indexes have already been created against the fields you're querying against, the Dynamic Query API is a great way to create custom queries against Liferay objects without having to create custom finders and services.

However, there are some "gotchas" that I've bumped into when using them, and I'm hoping that sharing these experiences will help someone out there if they're banging their head against a wall.

One gotcha was getting empty results, even though the equivalent SQL query was definitely returning something.

By default, the dynamic query API uses the current thread's class loader rather than the portal class loader. Because the Impl classes can only be found in Liferay's class loader, when you try to utilize the Dynamic Query API for plugins that are located in a different class loader, Hibernate silently returns nothing instead.

A solution is to pass in the portal class loader when you're initializing your dynamic query object, and Hibernate will know to use the portal class loader when looking for classes.

DynamicQuery query =
	DynamicQueryFactoryUtil.forClass(
		UserGroupRole.class, PortalClassLoaderUtil.getClassLoader());

A second gotcha was figuring out how to use SQL projections (more commonly known as the stuff you put in the SELECT clause), the most common cases being to select a specific column or to get a row count.

The gotcha was that the sample documentation on the Liferay Wiki uses Hibernate's DetachedCriteria classes, and trying to add Projections.rowCount() to a DynamicQuery object gave me a compile error, because the Liferay DynamicQuery object requires using Liferay's version of the Projection class, rather than the Hibernate version.

To resolve this gotcha, you can use ProjectionFactoryUtil to get the appropriate object.

query.setProjection(ProjectionFactoryUtil.rowCount());

A third gotcha was a "could not resolve property" error when I tried to add a restriction via RestrictionsFactoryUtil. Even though it looked like the bean class definitely had that attribute defined in portal-hbm.xml, Hibernate wasn't able to figure out what property needed to be used.

The gotcha is that some of Liferay's objects use composite keys. When using composite keys with Hibernate's detached criteria and Liferay's dynamic queries, the name of the property must include the name of the composite key. In the case of the Hibernate definitions created by Liferay's Service Builder, composite keys are always named primaryKey.

Therefore, the solution is to use primaryKey.userId instead of userId.

Criterion criterion =
	RestrictionsFactoryUtil.eq("primaryKey.userId", userId);

A fourth gotcha is that even if you don't specify a projection (thus resulting in a default of selecting all columns and implied "give me the entity"), casting directly to a List<T> won't work as it does in custom finders, because you're getting back a List<Object>, not a List.

The quick and dirty solution is to either (a) use addAll on a List and typecast (simulating what happens in a custom finder), or (b) add each result to a List<T> (cleaner to read).

5 Comments

Please sign in to comment.

Great article, thanks a lot for sharing this knowledge.

One insignificant remark:

> A fourth gotcha is that even if you don't specify a projection
> (thus resulting in a default of selecting all columns and implied "give me the entity"),
> casting directly to a List<T> won't work as it does in custom finders,
> because you're getting back a List<Object>, not a List.

> The quick and dirty solution is to either (a) use addAll on a List
> and typecast (simulating what happens in a custom finder),
> or (b) add each result to a List<T> (cleaner to read).

Adding each result one by one is unnecessary overhead, as is creation as another List implementation instance (a quite small overhead, but still unnecessary).
This can be easily avoided by using code like this:
List<T> listOfT = (List<T>) (List) listOfObject;

Please sign in to reply.

How to use dynamic query for (NOT EQUAL TO) in liferay..,
Eg..,
SELECT * FROM table where userid!='10';

Please sign in to reply.

Hi Umer Sayeed

It's similar to equals condition described in article. See there is:
Criterion criterion = RestrictionsFactoryUtil.eq("primaryKey.userId", userId);
Here RestrictionsFactoryUtil creates "equal" condition on eq method call.

Same way RestrictionsFactoryUtil method ne can be used:
Criterion criterion = RestrictionsFactoryUtil.ne("primaryKey.userId", userId);
This will create "not equals" condition.

Regards,
Mykola Makhin.

Please sign in to reply.

P.S. Note also that you can negate any condition using RestrictionFactoryUtil.not(Criterion criterion) method.

I.e. same thing could be done this way (same result, just 2 lines of code instead of 1):
Criterion eqCriterion = RestrictionsFactoryUtil.eq("primaryKey.userId", userId);
Criterion notEqCriterion = RestrictionFactoryUtil.not(eqCriterion);

P.P.S. I also recommend you to look up RestrictionsFactoryImpl class source code when you have questions like this - you'll see what the methods actually do, as this factory is essentially proxying calls to Hibernate's org.hibernate.criterion.Restrictions util (documentation on which you can fine on Hibernate site - in case you're not familiar with it already), and wraps hibernate Criterion into Liferay's wrapper Criterion (see CriterionImpl).

Please sign in to reply.