dynamic queries in liferay

This blog is basically for the developers who are new to liferay and they find it difficult to use the dynamic query.
Many a times we may require a data which cannot be accessed by Liferay API.
Liferay provides several ways to define complex queries used in retrieving database data.
The solution is to use the dynamic query.Liferay provides access to Hibernate’s Dynamic Query API.
Advantages of using dynamic query

To perform operation like add,or,max, min,like. etc.

Optimising the query.

Minimal coding.

Accessing the complex data.

A sample queries.

Returning the whole list without restriction.

List results = new ArrayList();

DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(Library.class,PortalClassLoaderUtil.getClassLoader());

results.addAll(LibraryLocalServiceUtil.dynamicQuery(dynamicQuery));

The above query returns list of all Library objects.

Now to add restriction to our dynamic query.

Liferay provides util class called RestrictionsFactoryUtil where in it provides many api to add restictions to our query.

few API are   like, eq, ne , isNull, isNotnull etc.

Restriction is like where attribute what we write in SQL query eg: “select * from library where authorName=’james’;”.

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

dynamicQuery.add(RestrictionsFactoryUtil.like(“bookName”, “java”));

dynamicQuery.add(RestrictionsFactoryUtil.eq(“author”, “james”));

List results = LibraryLocalServiceUtil.dynamicQuery(dynamicQuery);

The above query returns list of library objects whose author name is james and bookname is like “java”.

By using ProjectionFactoryUtil for dynamic query

The operations that can be performed using ProjectionFactoryUtil are avg,max,min, distinct etc.

DynamicQuery dynamicQuery=DynamicQueryFactoryUtil.forClass(Library.class,PortalClassLoaderUtil.getClassLoader());

dynamicQuery.setProjection(ProjectionFactoryUtil.max(“price”));

List results = LibraryLocalServiceUtil.dynamicQuery(dynamicQuery);

By using DetachedCriteria for the dynamic query.

The operations that can be performed using DetachedCriteria are add,addorder etc.

DetachedCriteria dCriteria = DetachedCriteria.forClass(Library.class);

dCriteria.add(Restrictions.eq(“bookname”, “java”));

dCriteria.addOrder(Order.desc(“price”));

DynamicQuery dynamicQuery = new DynamicQueryImpl(dCriteria);

List results = LibraryLocalServiceUtil.dynamicQuery(dynamicQuery);

The above query returns list of library objects whose bookname is java in a desc order of price.

The packages imports are given below use it appropriately

import com.liferay.portal.kernel.dao.orm.DynamicQuery;

import com.liferay.portal.kernel.dao.orm.DynamicQueryFactoryUtil;

import com.liferay.portal.kernel.util.PortalClassLoaderUtil;

import com.liferay.portal.kernel.dao.orm.RestrictionsFactoryUtil;

import com.liferay.portal.kernel.dao.orm.Projection;

import org.hibernate.criterion.Restrictions;

import org.hibernate.criterion.DetachedCriteria;

import com.liferay.portal.dao.orm.hibernate.DynamicQueryImpl;