Dynamic Query power in liferay

Navigate to the following URL to know how the dynamic query works, it clearly explains.

http://liferayjavafunwithjay.blogspot.in/2012/01/power-of-dynamic-query-in-liferay.html



Lately I came across a scenario while developing portlet in Liferay in which simple queries were not enough & I was required to fetch data on many different conditions. To simply the things, consider following scenario:

There is one model (In Liferay's terms Entity / POJO) called "Request" generated by service builder having fields like request Id, status, requestor Id, department, shipment address, date, updator Id, next Approver Id etc.
Now at run-time, I was required to show data by combining various conditions based on user’s actions like when
((requestorId == 1000 || nextApproverId == 1000) && (status == “PENDING”) )
((address ==”NJ”)  &&  (status == “PENDING” || status == “NEW” || status == “INPROCESS”) )

      In above 2 conditions mentioned in pseudo code, consider “||” as “OR” operator & “&&” as “AND” operator.


Now this kind of queries is hard to write without use of Dynamic Query as conditions are constructed at run-time. This can be resolved with use of Dynamic-Query as entities generated by Liferay’s service builder always have 2 methods related to Dynamic Query with following signature in LocalServiceUtil class:

public static java.util.List<Object> dynamicQuery(com.liferay.portal.kernel.dao.orm.DynamicQuery dynamicQuery);
public static java.util.List<Object> dynamicQuery(com.liferay.portal.kernel.dao.orm.DynamicQuery dynamicQuery, int start,int end);
We will now use this to create our Dynamic Query.

Steps:
·
Create reference of “DynamicQuery” class using following syntax, here Request is the entity generated by service-builder:
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(Request.class);

Create criterion based on our restriction conditions. For example for query ( (requestorId == 1000 || nextApproverId == 1000) && (status == “PENDING”) ), following syntax will be used to create criterion:

// means "requestorId == 1000"
Criterion criterionRequestor = RestrictionsFactoryUtil.eq("requestorId", Long.valueOf(1000));
// means "nextApproverId == 1000"
Criterion criterionNextApprover = RestrictionsFactoryUtil.eq("nextApproverId", Long.valueOf(1000));
// following achieves "(requestorId == 1000 || nextApproverId == 1000)"
Criterion criterionFirstAnd = RestrictionsFactoryUtil.or(criterionRequestor, criterionNextApprover);
         
//means '(status == “PENDING”)'
Criterion criterionStatus = RestrictionsFactoryUtil.eq("status", String.valueOf("PENDING"));
         
//following achieves "( (requestorId == 1000 || nextApproverId == 1000)&&(status == “PENDING”))"
Criterion criterionFinal = RestrictionsFactoryUtil.and(criterionFirstAnd, criterionStatus);

// finally add criterion to Dynamic Query
dynamicQuery.add(criterion);

// finally execute dynamic query to get the results
requestList = RequestLocalServiceUtil.dynamicQuery(dynamicQuery);


That’s it; we are good to go to get results dynamically. Not only that, we can create these criterion dynamically based on user’s actions as well as we can dynamically add order to get our results sorted on multiple fields.