hook

DynamicQuery in Liferay

Liferay provides very powerful tool service-builder in-order to deal with custom database entity.
It generates many methods to retrieve results from database just like you can have your own finder methods on entity.

Intro and Why?
But, this not suffice all we may need. Many times we would need complex result set from database table by performing aggregate SQL operations such as max,avg etc, and also in clause, and/or operations or we would need to fetch some of fields rather the whole entity object[database entity mapped object].

Here, DynamicQuery would be useful.

How?

Dealing with Liferay's built in entities like Users, Organizations, Assets, AssetCategories
As DynamicQuery API looks for current classloader, for access, but as you are dealing with Portal level class [User.class], you have to specify portalClassLoader as below.

 ClassLoader portalClassLoader = PortalClassLoaderUtil.getClassLoader();  
 DynamicQuery query = DynamicQueryFactoryUtil.forClass(User.class, portalClassLoader);  

Dealing with custom entity within portlet.

DynamicQuery dynamicQuery=DynamicQueryFactoryUtil.forClass(Application.class);

Here we have just got dynamicQuery object and having applied any query criteria.
Lets do that.

Following util classes would be useful for it.

PropertyFactoryUtil.java, RestrictionsFactoryUtil.java, ProjectionFactoryUtil.java, OrderFactoryUtil.java

Lets understand by example.

 DynamicQuery dynamicQuery=DynamicQueryFactoryUtil.forClass(CustomEntity.class);  
 dynamicQuery.add(RestrictionsFactoryUtil.eq("status", "active"));  
 dynamicQuery.addOrder(OrderFactoryUtil.asc("name"));  
 dynamicQuery.setLimit(0, 5) ;  
 List<CustomEntity> list=ApplicationLocalServiceUtil.dynamicQuery(dynamicQuery);  

Though above example is self-explanatory, let me tell that it will return list of 5 objects[of CustomEntity] who has status field as active and results will be ordered[ascending] by name field.

Above things can be easily achieved by finder on status field and having order on name in service.xml

Lets add some more complexity in our query.
Say, You need list of 5 CustomEntity having name starts with"ABC" and status as active and order by name.

Here you go.

 DynamicQuery dynamicQuery=DynamicQueryFactoryUtil.forClass(CustomEntity.class);  
 dynamicQuery.add(RestrictionsFactoryUtil.and(RestrictionsFactoryUtil.eq("status", "active"),RestrictionsFactoryUtil.ilike("name", "ABC")));  
 dynamicQuery.addOrder(OrderFactoryUtil.asc("name"));  
 dynamicQuery.setLimit(0, 5) ;  
 List<CustomEntity> list=ApplicationLocalServiceUtil.dynamicQuery(dynamicQuery);  

You can also add criteria like greater than, less than, in clause etc using RestrictionsFactoryUtil.

Let say now you need just some of tuples rather whole customEntity object.

dynamicQuery.setProjection(ProjectionFactoryUtil.projectionlist().add(ProjectionFactoryUtil.property("name")).add(ProjectionFactoryUtil.property("age")));

By setting projection as above, output result would be list of array of Objects.[with name,age values].


ProjectionFactoryUtil can be utilized to have aggregate functions like min,max,avg,distinct,count etc.