Sometimes Liferay’s basic finder methods generated after running service builder may not be sufficient to fetch required data from tables.
Liferay provides Dynamic Query API, which is useful while retrieving data dynamically and in an easy way compared to custom SQL queries which requires to build service every time there is any change in SQL query.

Dynamic query can be used in JSP pages, action classes or inside service implementation class. Following are the details to use Dynamic Query in Liferay.

  • Create a Dynamic query object using DynamicQueryFactoryUtil class for a model class.
DynamicQuery query = DynamicQueryFactoryUtil.forClass(EmployeeDetails.class, PortletClassLoaderUtil.getClassLoader());
  • Here, class loader object can be created based on the the model class. If we want to use portal level model class, then we can use PortalClassLoaderUtil.getClassLoader(). If we are using portlet level model classes then we can use PortletClassLoaderUtil.getClassLoader(). We can also use XXXBeanLocatorUtil to create class loaderobject as shown below:
ClassLoader classLoader = (ClassLoader) PortletClassLoader classLoader = 
(ClassLoader) PortletBeanLocatorUtil.locate("ServiceBuilder-Advanced-portlet", "portletClassLoader");
  • To add conditions(like equals, >, =. etc) to a particular column of an entity in dynamic query, we use PropertyFactoryUtil Class or RestrictionsFactoryUtil class to set conditions to the query:
query.add(PropertyFactoryUtil.forName("empid").eq(new Long(1)));
query.add(RestrictionsFactoryUtil.eq("empid", new Long(1)));
  • We can also create separate criterion and later join them to form a query, as shown below:
Criterion criterion = RestrictionsFactoryUtil.eq("pincode", "110011"); 
Criterion criterion2 = RestrictionsFactoryUtil.and(criterion, RestrictionsFactoryUtil.eq("name", "Arun")); 
query.add(criterion2);
  • We can also use ProjectionFactoryUtil class to get the required result set from a query. In below example, we are setting the projection property for column “empName“, which will be returned as result. We can also add multiple columns as shown below:
Projection projection = ProjectionFactoryUtil.property("empName"); 

//Adding multiple projection properties: 
ProjectionList projectionList = ProjectionFactoryUtil.projectionList();
Projection projection1 = ProjectionFactoryUtil.groupProperty("empName");
Projection projection2 = ProjectionFactoryUtil.property("pincode");
projectionList.add(projection1); projectionList.add(projection2);
query.setProjection(projectionList);
  • We can use Junction objects to add AND and OR conditions to our query. Following is an example for conjunction(AND) and dis-junction(OR). First we have to create a Junction object from RestrictionFactoryUtil class; add columns to the created entity and finally, add it to the query as shown below:
///For AND operation: 
Junction conjunction = RestrictionsFactoryUtil.conjunction(); 

//For OR operation: 
Junction disjunction = RestrictionsFactoryUtil.disjunction();
conjunction.add(PropertyFactoryUtil.forName("empid").eq(new Long(1)));
conjunction.add(PropertyFactoryUtil.forName("empName").eq("John"));
query.add(conjunction);
  • We can use OrderFactoryUtil class to add order to particular column of the result set as shown below:
//For descending order:
query.addOrder(OrderFactoryUtil.desc("empName"));
//For ascending order:
query.addOrder(OrderFactoryUtil.asc("empName"));
  • If we are fetching data based on the conditions which includes data from another table,  we can create separate query object with projection of that entity and use it in main query as shown below:
//Subquery to fetch pincode:
DynamicQuery pinQuery = DynamicQueryFactoryUtil.forClass(Location.class, PortletClassLoaderUtil.getClassLoader());

//Set projection for column pincode which we are using in main query:
pinQuery.setProjection(ProjectionFactoryUtil.property("pincode"));

//Dynamic Query to Fetch records by passing sub query 
DynamicQuery query = DynamicQueryFactoryUtil.forClass (EmployeeDetails.class, PortletClassLoaderUtil.getClassLoader());
DynamicQuery query = query.add(PropertyFactoryUtil.forName("pincode").in(pinQuery)); 
List a =(List) EmployeeDetailsLocalServiceUtil.dynamicQuery(query);

Hence, Dynamic query is very useful if want to fetch data from JSP pages, action classes or service layer implementation class.

Here are the all Liferay related posts.