Dynamic Query in Liferay
Liferay provides several ways by which we can retrieve data from database. One of them is dynamic query. You can easily fire complex query using dynamic query and it will reduce overhead of creating custom finder methods. Lets go step by step with easy example. If you want to fire simple AND query then here is the example.

DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(MyCustomTable.class);
dynamicQuery.add(PropertyFactoryUtil.forName("status").eq("Pending")
dynamicQuery.add(PropertyFactoryUtil.forName("userId").eq(10122);

e.g.:-

<%
DynamicQuery subQuery = DynamicQueryFactoryUtil.forClass(ProductGradeDailyPrice.class);
Projection projection=ProjectionFactoryUtil.min("primaryKey.priceDate");
subQuery.setProjection(projection);
List l=ProductGradeDailyPriceLocalServiceUtil.dynamicQuery(subQuery);
Calendar mindate = (CalendarFactoryUtil.getCalendar(TimeZone.getDefault(), request.getLocale()));
mindate.setTime((Date)l.get(0));
System.out.print("min date = "+mindate);
%>



Returning the whole list without restriction.

List results = new ArrayList();
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(Library.class);
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.