Submit Feedback

Using Dynamic Query

Liferay’s Dynamic Query API wraps Hibernate’s Criteria API. It helps you think in terms of objects and member variables instead of tables and columns. Complex queries can be significantly easier to understand and maintain than the equivalent custom SQL (or HQL) queries.

Note

In Liferay 7.4+, the recommend method of making custom queries for Service Builder entities is DSL Query.

Deploy an Example

Start a new Liferay instance by running

docker run -it -m 8g -p 8080:8080 liferay/portal:7.4.3.120-ga120

Sign in to Liferay at http://localhost:8080. Use the email address test@liferay.com and the password test. When prompted, change the password to learn.

Then, follow these steps to deploy the example:

Download and unzip the liferay-v3r9.zip example project.

curl https://resources.learn.liferay.com/dxp/latest/en/liferay-development/building-applications/data-frameworks/advanced-queries/using-dynamic-query/liferay-v3r9.zip -O

unzip liferay-v3r9.zip

Build and deploy the project module.
```
cd liferay-v3r9
```
```
./gradlew deploy -Ddeploy.docker.container.id=$(docker ps -lq)
```
Note

This command is the same as copying the deployed jars to /opt/liferay/osgi/modules on the Docker container.

Confirm the deployment in the Liferay Docker container console.

STARTED com.liferay.v3r9.service_1.0.0 [1462]
STARTED com.liferay.v3r9.web_1.0.0 [1463]
STARTED com.liferay.v3r9.api_1.0.0 [1461]

To verify the example module is working, open your browser to https://localhost:8080.
Add the V3R9 Portlet to a page. You can find the example portlet under Sample in Widgets.
Add an entry by entering a name and a description. Leave Hidden unchecked. Click Add, and the new entry appears under V3R9 Entries.
Add another entry with a different name and description. This time, check Hidden. The new entry doesn’t appear under V3R9 Entries.

This example uses Dynamic Query to retrieve only entries with a specified value in the database (hidden_ = false).

Creating Dynamic Queries

Open V3R9EntryLocalServiceImpl.java. The code for the Dynamic Query is defined in the getV3R9Entries method.

@Override
public List<V3R9Entry> getV3R9Entries(boolean hidden) {
	Session session = null;

	try {
		session = v3r9EntryPersistence.openSession();

		DynamicQuery entryQuery = DynamicQueryFactoryUtil.forClass(
			V3R9Entry.class, getClass().getClassLoader()
		).add(
			RestrictionsFactoryUtil.eq("hidden", hidden)
		);

		return dynamicQuery(entryQuery);
	}
	catch (Exception exception) {
		throw new SystemException(exception);
	}
	finally {
		v3r9EntryPersistence.closeSession(session);
	}
}

Create a DynamicQuery object. Like Hibernate criteria, Liferay’s dynamic queries are chainable. You can add criteria to, set projections on, and add orders to Liferay’s dynamic query objects by appending the appropriate method calls to the query object. In Liferay, you don’t create criteria objects directly from the Hibernate session. Instead, you create dynamic query objects using Liferay’s DynamicQueryFactoryUtil. Thus, use
```
DynamicQuery entryQuery = DynamicQueryFactoryUtil.forClass(Entry.class, classLoader);
```
instead of
```
Criteria entryCriteria = session.createCriteria(Entry.class);
```
Next, add the appropriate restrictions. 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. This example uses restrictions to cause the query to return only results where the hidden field has the desired value of false. To add restrictions to a dynamic query, don’t call Hibernate’s Restrictions class directly. Instead, use the RestrictionsFactoryUtil service which has all the same methods: in, between, like, eq, ne, gt, ge, lt, le, etc. Thus, to limit the results to entries whose hidden attribute matches the value of the variable hidden, use
```
entryQuery.add(RestrictionsFactoryUtil.eq("hidden", hidden))
```
instead of
```
entryCriteria.add(Restrictions.eq("hidden", hidden))
```
Modify the kind of results returned by the query using projections. 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. 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. Again, do not create properties directly through Hibernate’s Property class. Use PropertyFactoryUtil
```
entryQuery.setProjection(PropertyFactoryUtil.forName("hidden"));
```
instead of
```
entryCriteria.setProjection(Property.forName("hidden"));
```
Lastly, you can control the order of the elements in the list a query returns. Choose the 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 name attribute. When you apply this order, the results are arranged in alphabetical order:
```
Order order = OrderFactoryUtil.desc("name");
```
After finishing your dynamic query, execute the query by passing it in as an argument to the dynamicQuery() method.

return dynamicQuery(entryQuery);

Note

Service Builder generates multiple overloaded definitions of the dynamicQuery() method:

public List dynamicQuery(DynamicQuery dynamicQuery)
public List dynamicQuery(DynamicQuery dynamicQuery, int start, int end)
public List dynamicQuery(DynamicQuery dynamicQuery, int start, int end, OrderByComparator orderByComparator)

The start and end parameters are the lower and upper bound of the range of model entity instances.

Other Use Cases of Dynamic Query

When using Dynamic Query outside the *-service module (to build Dynamic Query instances outside out-of-the-box Liferay entities, for example), you need the class loader that contains the -EntityImpl class that Hibernate references. To do this, you must use the DynamicQueryFactoryUtil.forClass method instead of -LocalService.dynamicQuery.

Using Dynamic Query

Deploy an Example

Creating Dynamic Queries

Other Use Cases of Dynamic Query

Related Topics