JAVA Tutorials

Hibernate Criteria/Projection

There are three different ways provided by Hibernate to retrieve data from a database:HQL,native SQL queries, and hibernate criteria queries. With the criteria query API you can build nested, structured expressions in Java language, that allows a compile-time syntax checking that is not possible with a query language like HQL or SQL.

The Criteria API also includes query by example (QBE) functionality. This lets you supply example objects that contain the properties you would like to retrieve instead of having to step-by-step spell out the components of the query. It also includes projection and aggregation methods, including count(). Let’s explore it’s different features in detail.

The org.hibernate.Criteria interface allows you to build up a criteria query object.  You can create criteria with the help of one of the overloaded createCriteria() methods from Hibernate Session interface.

You need to pass to the createCriteria() method the persistent object class. After the Criteria object will be able to return the persistence object class entities after you execute a criteria query.

Criteria criteria = session.createCriteria(UserEntity.class);

List results = criteria.list();

In the new version of Hibernate 5 all createCriteria methods are marked as deprecated. So, if you are working on a new project you have to use:

javax.persistence.criteria.CriteriaBuilder

javax.persistence.criteria.CriteriaQuery.

To work on the Criteria from starting from Hibernate 5, you have to perform the following steps:

1. We need to create an instance of Session with the use of the SessionFactory object.

2.  After, we need to call the getCriteriaBuilder() method and create an instance of CriteriaBuilder.

3. We call the CriteriaBuilder createQuery() method to get an instance of CriteriaQuery.

4. We create an instance of Query by calling the Session createQuery() method

5. To get results we need to call the getResultList() method of the query object.

Let’s look at the simplest example of a criteria query without optional parameters or restrictions.

Here is our UserEntity class:

@Entity
@Table(name = "User")
public class UserEntity implements Serializable {
@Id
@Column(name = "id", unique = true, nullable = false)
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
@Column(name = "first_name", unique = false, nullable = false, length = 100)
private String firstName;
@Column(name = "last_name", unique = false, nullable = false, length = 100)
private String lastName;
//getters and setters are skipped
}

The criteria query will return all object that corresponds to the class UserEntity:

public class HibernateExample {

public static void main(String[] args) {
        Configuration configuration = new Configuration();
        configuration.addAnnotatedClass(edu.hibernate.example.UserEntity.class);
        Session session = configuration.configure().buildSessionFactory().openSession();
        Transaction transaction = session.beginTransaction();
        // Create CriteriaBuilder
        CriteriaBuilder builder = session.getCriteriaBuilder();
        // Create CriteriaQuery
        CriteriaQuery<UserEntity> criteriaQuery = builder.createQuery(UserEntity.class);
        criteriaQuery.from(UserEntity.class);
        List<UserEntity> usersList = session.createQuery(criteriaQuery).getResultList();
        transaction.commit();
    }
}

This code will do the same as SQL query:
select * from User;

Let’s modify our code that it will work like query:
select first_name from User;

// Create CriteriaBuilder
CriteriaBuilder builder = session.getCriteriaBuilder();
// Create CriteriaQuery
CriteriaQuery<String> criteriaQuery = builder.createQuery(String.class);
Root<UserEntity> root = criteriaQuery.from(UserEntity.class);
criteriaQuery.multiselect(root.get("first_name"));
List<String> users_names= session.createQuery(criteriaQuery).getResultList();

We can perform selection by criteria. Let's create code that will do the same as SQL query:
select first_name from User where last_name=”Gagne”.

// Create CriteriaBuilder
CriteriaBuilder builder = session.getCriteriaBuilder();
// Create CriteriaQuery
CriteriaQuery<String> criteriaQuery = builder.createQuery(String.class);
Root<UserEntity> root = criteriaQuery.from(UserEntity.class);  criteriaQuery.multiselect(root.get("first_name")).where(builder.equal(root.get("last_name"), "Gagne"));
List<String> users= session.createQuery(criteriaQuery).getResultList();

It is possible to get several columns, the same as:

select first_name, last_name from User;

We will get result as the list of Objects.

CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<Object[]> query = builder.createQuery(Object[].class);
Root<UserEntity> root = query.from(UserEntity.class);
query.multiselect(root.get("first_name"),root.get("last_name"));
Query<Object[]> q=session.createQuery(query);
List<Object[]> list=q.getResultList();
for (Object[] objects : list) {
System.out.println("First name: "+objects[0]);
       System.out.println("Last name: "+objects[1]);
}

It is possible to use aggregate functions with CriteriaBuilder (max(), min(), count(), avg(), countDistinct(), etc).

Let’s create one Integer field for our entity and try some aggregate functions:

@Entity
@Table(name = "User")
public class UserEntity implements Serializable {
@Id
@Column(name = "id", unique = true, nullable = false)
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
@Column(name = "first_name", unique = false, nullable = false, length = 100)
private String firstName;
@Column(name = "last_name", unique = false, nullable = false, length = 100)
private String lastName;
@Column(name = "age", unique = false, nullable = false)
private Integer age;
}

Here we will count a number of selected rows from User table. IN the our case it will be the number of all users:

public class HibernateExample {

public static void main(String[] args) {

        Configuration configuration = new Configuration();
        configuration.addAnnotatedClass(edu.hibernate.example.UserEntity.class);
        Session session = configuration.configure().buildSessionFactory().openSession();
        Transaction transaction = session.beginTransaction();
        // getting criteria builder
        CriteriaBuilder builder = session.getCriteriaBuilder();
        // Count number of users
        CriteriaQuery<Long> criteriaQuery = builder.createQuery(Long.class);
        Root<UserEntity> root = criteriaQuery.from(UserEntity.class);
        criteriaQuery.select(builder.count(root));
        Query<Long> query = session.createQuery(criteriaQuery);
        long count = query.getSingleResult();
        System.out.println("Count = " + count);       
        transaction.commit();
    }
}

Here is code to get the maximum age of the Users:

CriteriaBuilder builder = session.getCriteriaBuilder();
// Get max age
CriteriaQuery<Integer> criteriaQuery = builder.createQuery(Integer.class);
Root<UserEntity> root = criteriaQuery.from(UserEntity.class);
criteriaQuery.select(builder.max(root.get("age")));
Query<Integer> query = session.createQuery(criteriaQuery);
int maxAge = query.getSingleResult();
System.out.println("Max Age = " + maxAge); 

The code to get average age:

CriteriaBuilder builder = session.getCriteriaBuilder();
// Average Age
CriteriaQuery<Double> criteriaQuery = builder.createQuery(Double.class);
Root<UserEntity> root = criteriaQuery.from(UserEntity.class);
criteriaQuery.select(builder.avg(root.get("age")));
Query<Double> query = session.createQuery(criteriaQuery);
double avgAge = query.getSingleResult();
System.out.println("Average Age = " + avgAge);

There is the possibility to order the users. Let’s order them by age:

public class HibernateExample {

public static void main(String[] args) 
    {

        Configuration configuration = new Configuration();
        configuration.addAnnotatedClass(edu.hibernate.example.UserEntity.class);
        Session session = configuration.configure().buildSessionFactory().openSession();
        Transaction transaction = session.beginTransaction();
        // Create CriteriaBuilder
        CriteriaBuilder builder = session.getCriteriaBuilder();

        CriteriaQuery<UserEntity> criteriaQuery = builder.createQuery(UserEntity.class);
        Root<UserEntity> root = criteriaQuery.from(UserEntity.class);
        criteriaQuery.select(root);
        criteriaQuery.orderBy(builder.asc(root.get("age")));
        Query<UserEntity> query = session.createQuery(criteriaQuery);
        List<UserEntity> list = query.getResultList();
        for (UserEntity user : list) {
           System.out.println("NAME="+ user.getFirstName()+"\t Age="+user.getAge());
        }
        transaction.commit();
    }
}

Projection

The package org.hibernate.criterion contains the class Projection. It is the way to show in the object-oriented representation a query result set of a Criteria query. There are built-in projections in the Projections factory class. This interface might be implemented to define custom projections.  Hibernate Projection is used to get the particular columns from the database table. The Projection class produces projection objects. This class contains just static methods and they return the projection interface object. Projection usually is used for the Criteria query object.

The Projections class contains some in-built functions like sum, max, min, rowCount, etc, therefore you can perform the aggregation operations in Hibernate. You can add a projection object to the criteria object by calling the criteria.setProjection(projectionObj) method.

If you need to read multiple columns from the database, then you need to pass the ProjectionList object to the setProjection() method: criteria.setProjection(projection_list_obj)

For our example we will use UserEntity class.

Let’s look at the following snippet that illustrates how to use the projection together with hibernate criteria.

package edu.hibernate.example;

import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.Projections;

public class HibernateExample {

public static void main(String[] args) 
    {
        Configuration configuration = new Configuration();
        configuration.addAnnotatedClass(edu.hibernate.example.UserEntity.class);
// opening new session 
        Session session = configuration.configure().buildSessionFactory().openSession();
        Transaction transaction = session.beginTransaction();
        // creating the criteria object.
        @SuppressWarnings("deprecation")
Criteria myCriteria = session.createCriteria(UserEntity.class);        
        // creating the projection object.
        Projection myProjection = Projections.property("first_name");       
        // adding the projection object to the criteria object.
        myCriteria.setProjection(myProjection);        
        List list = myCriteria.list();
        transaction.commit();
    }
}

Let’s look closer at methods that are available in the Projections class to support the property aggregation:

  • sum(String property_name) – method calculates the sum of total of the property values.
  • avg(String property_name) – method is dedicated to the calculation of the average of the property’s value.
  • count(String property_name) – method counts the number of times a property appears in the database table.
  • countDistinct(String property_name) – method counts the number of unique values the property contains.
  • max(String property_name) – the method calculates the maximum value.
  • min(String property_name) – the method returns the minimum value.
  • rowCount()  – calculates the total number of records in the database table.

Let’s use build-in function of the class Projections to count a number of users:

package edu.hibernate.example;

import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Projections;

public class HibernateExample {

public static void main(String[] args) 
    {
        Configuration configuration = new Configuration();
        configuration.addAnnotatedClass(edu.hibernate.example.UserEntity.class);
        Session session = configuration.configure().buildSessionFactory().openSession();
        Transaction transaction = session.beginTransaction();
        @SuppressWarnings("deprecation")
        Criteria criteria = session.createCriteria(UserEntity.class); 
        criteria.setProjection(Projections.rowCount()); 
        List users = criteria.list(); 
        System.out.println(users);
        transaction.commit();
    }
}

It is possible for Hibernate Criteria API to use an alias for a projection.

This is very useful when you use an aggregate function. Let’s group our users by first_name and count them. We will put the number of users in the column with the alias “count”:

public class HibernateExample {

public static void main(String[] args) 
    {
        Configuration configuration = new Configuration();
        configuration.addAnnotatedClass(edu.hibernate.example.UserEntity.class);
        Session session = configuration.configure().buildSessionFactory().openSession();    
        @SuppressWarnings("deprecation")
Criteria criteria = session.createCriteria(UserEntity.class);
        criteria = criteria.setProjection(Projections.projectionList()
                     .add(Projections.groupProperty("first_name"))
                     .add(Projections.alias(Projections.rowCount(), "count")));
        criteria.addOrder(Order.asc("count"));        
    }
}

It is valuable to mention that in the new version of Hibernate 5 all createCriteria methods are marked as deprecated. So, if you are working on a new project you have to use:

  • javax.persistence.criteria.CriteriaBuilder
  • javax.persistence.criteria.CriteriaQuery

They have all functionality that you had with the combination of Criteria and Projection combination.

The Hibernate framework made deprecated Criteria API from version 5.2 but supports CriteriaQuery API. This is because there is no sense to keep two different APIs, that have the pretty much same functionality.

Facebook Comments
Tags

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Check Also
Close
Back to top button
Close
Close