Dynamic Query in Liferay

Dynamic Queries are another useful Liferay feature. They allow you to generate database queries programmatically. You can build more flexible queries that adapt to various criteria and conditions. In Liferay, Dynamic Query is accessible through the Dynamic Query class API and allows data manipulation in a dynamic manner.

Note :

1. Dynamic Queries is used only to Retrieve Data.
2. If you know Hibernate it is similar to Criteria API.
3. No need to build services again and again.
4. You can perform IN, AND, OR, LIKE etc. operation easily.

Now let’s write some basic queries :
First we decide on which table we need to write query. There are two cases:-

1) Liferay Generated Table
Liferay automatically generated tables like user_, usergroup etc.
In this case we create dynamic query object as:-

DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass(Entity_Name.class, Alias,PortalClassLoaderUtil.getClassLoader());

Ex:-

DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass (User.class, "user", PortalClassLoaderUtil.getClassLoader ());

2) Custom Tables
Tables you create for your requirement .In this case we create dynamic query as object:-

DynamicQuery query = DynamicQueryFactoryUtil.forClass (Entity_Name.class, "Alias", PortletClassLoaderUtil.getClassLoader ());

Ex:
DynamicQuery countryQuery = DynamicQueryFactoryUtil.forClass (Country. Class, "ct", PortletClassLoaderUtil.getClassLoader ());

The basic difference between two statements is Class loader. For Liferay tables we use PortalClassLoaderUtil and for custom tables we use PortletClassLoaderUtil.

In this blog we will write query on user_ table i.e. User entity.

Dynamic Query has some of these Hibernate features

• Restrictions: Similar to where clauses of an SQL query, restrictions limit results based on criteria.
• Projections: Modify the kind of results the query returns.
• Orders: Organize results.

RESTRICTION CRITERIA

• Restrictions in Hibernate’s Criteria API roughly correspond to the where clause of an SQL query: they offer a variety of ways to limit the results returned by the query. You can use restrictions, for example, to cause a query to return only results where a certain field has a particular value, or a value in a certain range, or a non-null value, etc.
• When you need to add restrictions to a dynamic query, don’t call Hibernate’s Restrictions class directly. Instead, use the RestrictionsFactoryUtil service. RestrictionsFactoryUtil has the same methods that you’re used to from Hibernate’s Restrictions class: in, between, like, eq, ne, gt, ge, lt, le, etc.

 

Thus, instead of using this call to specify that a user must have a certain name,

entryCriteria.add (Restrictions.eq ("name", firstName));

You use

entryQuery.add (RestrictionsFactoryUtil.eq ("name", firstName));

The restriction above limits the results to guestbook entries whose name attribute matches the value of the variable firstName. Add the restrictions you need to get the results you want.

PROJECTION CRITERIA

Projections in Hibernate’s Criteria API let you modify the kind of results returned by a query. For example, if you don’t want your query to return a list of entity objects (the default), you can set a projection on a query to return only a list of the values of a certain entity field, or fields. You can also use projections on a query to return the maximum or minimum value of an entity field, or the sum of all the values of a field, or the average, etc. For more information on restrictions and projections, please refer to Hibernate’s documentation.

Similarly, to set projections, create properties via Liferay’s PropertyFactoryUtil service instead of through Hibernate’s Property class. Thus, instead of

entryCriteria.setProjection (Property.forName ("userId"));

You use

entryQuery.setProjection(PropertyFactoryUtil.forName("userId"));

The projection above specifies the userId entity field to changes the result set to a list of those field values. If you want to return a specific field type from your entities, add a projection for it.

ORDER CRITERIA

Orders in Hibernate’s Criteria API let you control the order of the elements in the list a query returns. You can choose the property or properties to which an order applies as well as whether they’re in ascending or descending order.

This code creates an order by the entity’s modifiedDate attribute:

Order order = OrderFactoryUtil.desc("modifiedDate");

When you apply this order, the results are arranged in descending order of the query entity’s modifiedDate attribute. Thus the most recently modified entities (entries, in our example) appear first and the least recently modified entities appear last.

 

3. Now we will use dynamic query to retrieve record from User_. Let’s retrieve    all records from USER_ table. (SQL – SELECT * FROM user_ user)

Output

4. Let’s retrieve records from USER_ table using LIKE on EMAILADDRESS. (SQL – SELECT * FROM user_ user WHERE user.emailAddress like ‘%test %’)

 

 

---

---