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
DetachedCriteriaclasses, and trying to add
DynamicQueryobject gave me a compile error, because the Liferay
DynamicQueryobject requires using Liferay's version of the
Projectionclass, rather than the Hibernate version.
To resolve this gotcha, you can use
ProjectionFactoryUtil to get the appropriate object.
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
Therefore, the solution is to use
primaryKey.userId instead of
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
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).