Hibernate Criteria Query

Hibernate criteria API is a type of method which permits to create a criteria query object programmatically where you can apply filtration rules and logical conditions. The criteria query with examples listed below will give a better insight on how to use criteria queries in hibernate .The Hibernate Session interface uses createCriteria() method which can be helped to create a Criteria object that returns instances of the persistence object’s class when your application executes a criteria query.

Example:-

Criteria cr = session.createCriteria(Person.class);
List results = cr.list();

Hibernate support different mechanism by using criteria queries. They are,

  1. Restrictions with criteria
  2. Pagination using criteria
  3. Sorting the result
  4. Projection and aggregations

Restrictions with criteria

This mechanism can be done in two ways.

a)     Add restriction

Here we are using add() method for Criteria object to add restriction for a criteria query.

Eg:-

Criteria cr = session.createCriteria(Person.class);
cr.add(Restrictions.eq("salary", 2000));
List results = cr.list();

a)     Logical expression restriction

Here we are creating AND or OR operations using logical expression restrictions.

Example:-

Criteria cr = session.createCriteria(Person.class);
Criterion salary = Restrictions.gt("salary", 2000);
Criterion name = Restrictions.ilike("firstNname","zara%");
// To get records matching with OR condistions
LogicalExpression orExp = Restrictions.or(salary, name);
cr.add( orExp );
// To get records matching with AND condistions
LogicalExpression andExp = Restrictions.and(salary, name);
cr.add( andExp );
List results = cr.list();

The most commonly used restrictions are given below.All these restriction returns  value types implement the interface org.hibernate.criterion.Criterion. Furthermore, the package org.hibernate.criterion provides even more useful Criterion implementers.

  • allEq(Map propertyNameValues):- It returns Criterion. It providesequality constraints on all entries of the corresponding map. The map keys are the property names its values the values to match against.
  • and(Criterion lhs, Criterion rhs):- It returns LogicalExpression. It is a type of Logical conjunction of two Criterion instances. If the requirement is that two restrictions must be true this method can be used for their conjunction. I.e., and( isNotNull(“property”), ge(“property”, new Integer(555));

One may also nest multiple calls to this method (theoretically indefinitely): and( and( isNotNull(“prop1”), ge(“prop1”, new Integer(78) ), like(“name”, “Chap%”));

  • between(String propertyName, Object lo, Object hi): It returns  Criterion. Include all entities in result set that have the value of the property propertyName between lo and hi. The method works inclusively referring to the specified lo and hi values. I.e., if you check an int field with values Integer(10) and Integer(50) entities matching one of these values exactly, will be included in the result set. The lo and hi values can be used with numeric instances but also, for example, with Date objects.

So the following code snippet covers all Actors.

// imports and other stuff left out
DateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
Date start = (Date) format.parse("2006-01-01 00:00:00");
Date end = (Date) format.parse("2006-12-31 23:59:59");
// session and transaction setup left out
session.createCriteria(Actor.class)
.add( Restrictions.between("registered", start, end) ).list();
// commiting transaction, exception handling left out
  • conjunction(): It returns the Conjunction. This method group several expressions into one conjunction. The use of a Conjunction should be preferred over the use of and(Criterion lhs, Criterion rhs) if several restrictions are required to be true at the same time.
// session and transaction setup left out
session.createCriteria(ContentElement.class)
.add(
Restrictions.conjunction()
.add( Restrictions.eq("namespaceId", new Integer(0)) )
.add( Restrictions.like("name", "Chap%" )
.add( Restrictions.gt("externalId", new Integer(100)))
.add( Restrictions.isNotNull("created") )
)
.list();
// commiting transaction, exception handling left out
  • disjunction():- It returns disjunction. Group several expressions into one disjunction. If several restrictions are to be or-ed together using this method is preferred over nesting a bunch of or(Criterion lhs, Criterion rhs) statements.
<code>// session and transaction setup left out
</code><code>session.createCriteria(ContentElement.class)
</code><code> .add( Restrictions.isNotNull("namespaceId") )
</code><code> .add(
</code><code> Restrictions.disjunction()
</code><code> .add( Restrictions.eq("namespaceId", new Integer(0)) )
</code><code> .add( Restrictions.eq("namespaceId", new Integer(10) )
</code><code> .add( Restrictions.eq("namespaceId", new Integer(14) )
</code><code> .add( Restrictions.ge("namespaceId", new Integer(100) )
</code><code> )
</code><code> .list();
</code><code>// commiting transaction, exception handling left out</code>
  • eq(String propertyName, Object value):- :- It returns the SimpleExpression.
  • eqProperty(String propertyName, String otherPropertyName) :- It returns the PropertyExpression.
  • ge(String propertyName, Object value) :- It returns the SimpleExpression.
  • geProperty(String propertyName, String otherPropertyName) :- It returns the PropertyExpression.
  • gt(String propertyName, Object value) :- It returns the SimpleExpression.
  • gtProperty(String propertyName, String otherPropertyName) :- It returns the PropertyExpression.
  • idEq(Object value) :- It returns the Criterion.
  • ilike(String propertyName, Object value) :- It returns the Criterion.
  • ilike(String propertyName, Object value, MatchMode matchMode) :- It returns the Criterion.
  • in(String propertyName, Collection values) :- It returns the Criterion.
  • in(String propertyName, Object[] values) :- It returns the Criterion.
  • isEmpty(String propertyName) :- It returns the Criterion.
  • isNotEmpty(String propertyName) :- It returns the Criterion.
  • isNotnull(String propertyName) :- It returns the Criterion.
  • isNull(String propertyName) :- It returns the Criterion.
  • le(String propertyName, Object value) :- It returns the SimpleExpression.
  • leProperty(String propertyName, String otherPropertyName):- It returns the PropertyExpression.
  • like(String propertyName, Object value) :- It returns the SimpleExpression.
  • like(String propertyName, Object value, MatchMode matchMode) :- It returns the SimpleExpression.
  • lt(String propertyName, Object value) :- It returns the SimpleExpression.
  • ltProperty(String propertyName, String otherPropertyName:- It returns the PropertyExpression.
  • naturalId():- It returns the NaturalIdentifier.
  • ne(String propertyName, Object value) :- It returns the SimpleExpression.
  • neProperty(String propertyName, String otherPropertyName) :- It returns the PropertyExpression.
  • not(Criterion expression) :- It returns the Criterion.
  • or(Criterion lhs, Criterion rhs) :- It returns the LogicalExpression.
  • sizeEq(String propertyName, int size) :- It returns the Criterion.
  • sizeGe(String propertyName, int size) :- It returns the Criterion.
  • sizeGt(String propertyName, int size) :- It returns the Criterion.
  • sizeLe(String propertyName, int size) :- It returns the Criterion.
  • sizeGt(String propertyName, int size) :- It returns the Criterion.
  • sizeNe(String propertyName, int size) :- It returns the Criterion.
  • sqlRestriction(String sql) :- It returns the Criterion.
  • sqlRestriction(String sql, Object[] values, Type[] types) :- It returns the Criterion.
  • sqlRestriction(String sql, Object value, Type type):- It returns the Criterion.

Pagination using criteria

Hibernate provide two different methods of the criteria interface for pagination. By using these two methods hibernate to retrieve a fixed number maxResult of objects.

1)      public Criteria setFirstResult(int firstResult)

In this method it takes an integer that represents the first row in your result set, starting with row 0.

2)      public Criteria setMaxResults(int maxResults)

This method shows Hibernate to retrieve a fixed number maxResults of objects.

Example:-

Criteria cr = session.createCriteria(Person.class);
cr.setFirstResult(1);
cr.setMaxResults(10);
List results = cr.list();

Sorting the result

The org.hibernate.criterion.order class is a type of criteria API that helps to sort our result set in either ascending or descending order depending upon our object’s properties.

Example:-

Criteria cr = session.createCriteria(Person.class);
// To get records having salary more than 2000
cr.add(Restrictions.gt("salary", 2000));
// To sort records in descening order
crit.addOrder(Order.desc("salary"));
// To sort records in ascending order
crit.addOrder(Order.asc("salary"));
List results = cr.list();

Projection and aggregations

The org.hibernate.criterion.Projections class of the criteria API provides a mechanism to get average, maximum, minimum of the property values. The projection class and restriction class are almost same and these classes provides several static factory methods for obtaining projection instances.

Example:-

Criteria cr = session.createCriteria(Person.class);
// To get total row count.
cr.setProjection(Projections.rowCount());
// To get average of a property.
cr.setProjection(Projections.avg("salary"));
// To get distinct count of a property.
cr.setProjection(Projections.countDistinct("firstName"));
// To get maximum of a property.
cr.setProjection(Projections.max("salary"));
// To get minimum of a property.
cr.setProjection(Projections.min("salary"));
// To get sum of a property.
cr.setProjection(Projections.sum("salary"));

Hibernate uses different methods to get the result set. They are,

  1. list() – Returns a java.util.List
  2. scroll() – Returns a org.hibernate.ScrollableResults :- It can be worked with like an iterator with extended functionality, i.e. for navigating the result set.
  3. scroll(ScrollMode scrollMode) – Returns a org.hibernate.ScrollableResult
  4. uniqueResult() – Returns a java.lang.Object. Will throw an exception if the result is not unique.
  5. get() – Returns a unique result set, i.e. just one row. The criteria has to be formed that way, that it only queries one row. This method is not to be confused with a limit to just the first row.
  6. Count() – Returns the number of matching rows.
  7. listDistinct –  If subqueries or associations are used, one may end up with the same row multiple times in the result set, this allows listing only distinct entities and is equivalent to DISTINCT_ROOT_ENTITY of the CriteriaSpecification class.