Liferay Dynamic Query API

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 to solve this problem.

The Dynamic Query API is helpful while retrieving data from the database. Implementation of dynamic query API is simple than the custom SQL queries, which requires building the service every time there is any change in SQL query.

Implementing the dynamic query API in Liferay

We can use the dynamic query in JSP pages, action classes, or also within the service implementation classes.

Following are the details to use Dynamic Query in Liferay.

Creating dynamic query

Create a Dynamic query object using the DynamicQueryFactoryUtil class for a model class.

DynamicQuery query = DynamicQueryFactoryUtil.forClass(EmployeeDetails.class, PortletClassLoaderUtil.getClassLoader());

Here, We can create the class loader object based on 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 the class loader object, 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 a dynamic query, we use PropertyFactoryUtil Class or RestrictionsFactoryUtilclass.

query.add(PropertyFactoryUtil.forName("empid").eq(new Long(1)));
query.add(RestrictionsFactoryUtil.eq("empid", new Long(1)));

Using Criterion

We can create many criterion instances 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);

Using Projections

We can also use ProjectionFactoryUtil class to get the required result set from a query.

In the below example, we are setting the projection property for column empName. The query returns the employee object having a matching name as a query 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);

Using Junctions

We can use Junction objects to add AND and OR conditions to our query. Following is an example for conjunction(AND) and disjunction(OR).

First, we have to create a Junction object from RestrictionFactoryUtil class and 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);

Using Orders

We can also use the OrderFactoryUtil class to add resultset order to a 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"));

Fetching data from multiple tables

If we are fetching data based on the conditions, which include data from another table, we can create a separate query object with the projection of that entity and use it in the 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.

Conclusion

In this article, we learned about Liferay dynamic query APIs.

We also learned how to use them to dynamically fetch the data from database tables.