18 - Hibernate Criteria Queries

18.1 Overview

Hibernate comes with a feature of Criteria Query which allows us to fetch the data from databased in object oriented style. Criteria Queries can only be used to fetch the records only as they do not support updates or deletes to the database. Criteria API uses entity names and properties not table names and table columns.

18.2 Criteria Queries API

Criteria object needs to be grabbed by calling createCriteria(Entity.class) method of Session object.

add() – is used to apply restrictions on criteria object. It is equivalent to where clause in SQL.

Convenient class Restrictions should be used to apply the restrictions in add() method.

Use Restrictions.isNotNull("property") to check if property is not null.

Use Restrictions.isEmpty("property") to check if property is empty.

Use Restrictions.isNotEmpty("property") to check if property is not empty.

To apply logical expressions (AND / OR ) conditions Hibernate provides LogicalExpression class which needs to be added as restrictions.

Use LogicalExpression or = Restrictions.or(Restriction1, Restriction2) to apply OR condition. “In this case restriction1 OR restriction2 will be applied “

Use LogicalExpression and = Restrictions.and(Restriction1, Restriction2) to apply AND condition. “In this case restriction1 AND restriction2 will be applied “

To apply ordering similar to order by clause in SQL, use addOrder() method. Hibernate provides class org.hibernate.criterion.Order which has static methods desc and asc.

Use Order.asc(“property”) to sort the result in ascending order of property.

Use Order.desc(“property”) to sort the result in descending order of property.

To use aggregate functions like sum(), avg() etc. we need to use Projections class (org.hibernate.criterion.Projections )defined by Hibernate and apply it using setProjection() method. Commonly used Projection API are

Use Projections.rowCount() to get the total number of rows count

Use Projections.avg("property") to get the average of property.

Use Projections.countDistinct("property") to get the distinct count of a property

Use Projections.max("property") to get the maximum value of property.

Use Projections.min("property") to get the minimum value of property.

Use Projections.sum("property") to get the sum of property.

To get the specific properties in result (like select col1 from table) we need to use ProjectionsList. To do so we need to add the property in projectionList using Projections.property like below

ProjectionList columns = Projections.projectionList();

columns .add(Projections.property("property1"));

columns.add(Projections.property("property2"));

criteria.setProjection(columns);

Use dot (.) for nested property like instance.property. If Person has Address instance so to get the address property use address.city