Hibernate 5 - Criteria query example

Posted on June 27, 2017


From Hibernate 5.2 documentation - This appendix covers the legacy Hibernate org.hibernate.Criteria API, which should be considered deprecated. Read more…

Most of the methods of the org.hibernate.Criteria API are deprecated and new development is focused on the JPA javax.persistence.criteria.CriteriaQuery API. In this post, I will show you how to use the JPA CriteriaQuery API for retrieving the entities based on specific criteria in Hibernate application.

The basic steps to create a Criteria query are –

1 - Create a CriteriaBuilder instance by calling the Session.getCriteriaBuilder() method.

CriteriaBuilder builder = session.getCriteriaBuilder();

2 - Create a query object by creating an instance of the CriteriaQuery interface.

CriteriaQuery<T> query = builder.createQuery(T.class);

3 - Set the query Root by calling the from() method on the CriteriaQuery object to define a range variable in FROM clause.

Root<T> root = query.from(T.class);

4 - Specify what the type of the query result will be by calling the select() method of the CriteriaQuery object.

query.select(root);

5 - Prepare the query for execution by creating a org.hibernate.query.Query instance by calling the Session.createQuery() method, specifying the type of the query result.

Query<T> q = session.createQuery(query);

6 - Execute the query by calling the getResultList() or getSingleResult() method on the org.hibernate.query.Query object.

List<T> list = q.getResultList();

Jar Dependencies

Open pom.xml file of your maven project and add the following dependencies in it.

  <dependencies>
    <!-- Mysql Connector -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>6.0.5</version>
    </dependency>
    <!-- Hibernate 5.2.10 Final -->
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-core</artifactId>
      <version>5.2.10.Final</version>
    </dependency>
  </dependencies>

Entity classes

In this example, we are considering the following entities classes.

Department.java

package com.boraji.tutorial.hibernate.entity;

import java.util.*;
import javax.persistence.*;

@Entity
@Table(name = "DEPARTMENT")
public class Department {

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   @Column(name = "DPT_ID")
   private long id;

   @Column(name = "NAME", nullable = false, unique = true)
   private String name;

   @OneToMany(cascade = CascadeType.ALL, mappedBy = "department")
   private List<Employee> employees = new ArrayList<>();

   //Setter and Getter methods
   
}

Employee.java

package com.boraji.tutorial.hibernate.entity;

import javax.persistence.*;

@Entity
@Table(name = "EMPLOYEE")
public class Employee {

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   @Column(name = "EMP_ID")
   private long id;

   @Column(name = "NAME", nullable = false)
   private String name;

   @Column(name = "DESIGNATION")
   private String designation;

   @Column(name="SALARY")
   private int salary;
   
   @ManyToOne
   @JoinColumn(name = "DPT_ID")
   private Department department;

   // Getter and Setter methods
}

CriteriaQuery examples

Here is an example of using the CriteriaQuery.select()  method for a list of objects -

package com.boraji.tutorial.hibernate;

import java.util.List;
import javax.persistence.criteria.*;
import org.hibernate.*;
import org.hibernate.query.Query;
import com.boraji.tutorial.hibernate.entity.Employee;

public class CriteriaQueryExample {

   public static void main(String[] args) {

      Transaction transaction = null;
      try (Session session = HibernateUtil.getSessionFactory().openSession()) {
         transaction = session.beginTransaction();

         CriteriaBuilder builder = session.getCriteriaBuilder();
         CriteriaQuery<Employee> query = builder.createQuery(Employee.class);
         Root<Employee> root = query.from(Employee.class);
         query.select(root);
         Query<Employee> q=session.createQuery(query);
         List<Employee> employees=q.getResultList();
         for (Employee employee : employees) {
            System.out.println(employee.getName());
         }
         transaction.commit();
      } catch (Exception e) {
         e.printStackTrace();
         if (transaction != null) {
            transaction.rollback();
         }
      }
      
   }
}

An equivalent SQL statement-

select * from employee;

Here is an example of using the CriteriaQuery.select()  method a single object -

package com.boraji.tutorial.hibernate;

import javax.persistence.criteria.*;
import org.hibernate.*;
import org.hibernate.query.Query;

import com.boraji.tutorial.hibernate.entity.Department;

public class CriteriaQueryExample2 {

   public static void main(String[] args) {

      Transaction transaction = null;
      try (Session session = HibernateUtil.getSessionFactory().openSession()) {
         transaction = session.beginTransaction();

         CriteriaBuilder builder = session.getCriteriaBuilder();
         CriteriaQuery<Department> query = builder.createQuery(Department.class);
         Root<Department> root = query.from(Department.class);
         query.select(root).where(builder.equal(root.get("id"), 1l));
         Query<Department> q=session.createQuery(query);
         Department department=q.getSingleResult();
         System.out.println(department.getName());
         
         transaction.commit();
      } catch (Exception e) {
         e.printStackTrace();
         if (transaction != null) {
            transaction.rollback();
         }
      }
   }
}

An equivalent SQL statement-

select * from department where DPT_ID=1;

 

Here is an example of using the CriteriaQuery.select()  method for a list of data elements -

package com.boraji.tutorial.hibernate;

import java.util.List;
import javax.persistence.criteria.*;
import org.hibernate.*;
import org.hibernate.query.Query;

import com.boraji.tutorial.hibernate.entity.Employee;

public class CriteriaQueryExample3 {

   public static void main(String[] args) {

      Transaction transaction = null;
      try (Session session = HibernateUtil.getSessionFactory().openSession()) {
         transaction = session.beginTransaction();

         CriteriaBuilder builder = session.getCriteriaBuilder();
         CriteriaQuery<String> query = builder.createQuery(String.class);
         Root<Employee> root = query.from(Employee.class);
         query.select(root.get("name"));
         Query<String> q=session.createQuery(query);
         List<String> list=q.getResultList();
         for (String name : list) {
            System.out.println(name);
         }

         transaction.commit();
      } catch (Exception e) {
         e.printStackTrace();
         if (transaction != null) {
            transaction.rollback();
         }
      }
   }
}

An equivalent SQL statement-

select name from employee;

Here is an example of using the CriteriaQuery.select()  method for a list of element array -

package com.boraji.tutorial.hibernate;

import java.util.List;
import javax.persistence.criteria.*;
import org.hibernate.*;
import org.hibernate.query.Query;

import com.boraji.tutorial.hibernate.entity.Employee;

public class CriteriaQueryExample3 {

   public static void main(String[] args) {

      Transaction transaction = null;
      try (Session session = HibernateUtil.getSessionFactory().openSession()) {
         transaction = session.beginTransaction();

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

         transaction.commit();
      } catch (Exception e) {
         e.printStackTrace();
         if (transaction != null) {
            transaction.rollback();
         }
      }
   }
}

An equivalent SQL statement-

select NAME, DESIGNATION from employee;

Aggregate functions examples

Aggregate functions such as max(), min(), count(), avg(), countDistinct() etc are defined in CriteriaBuilder and can be used to return a single result.

Here is an example of using aggregation functions in Hibernate-

package com.boraji.tutorial.hibernate;

import javax.persistence.criteria.*;
import org.hibernate.*;
import org.hibernate.query.Query;

import com.boraji.tutorial.hibernate.entity.Employee;

public class CriteriaQueryExample5 {

   public static void main(String[] args) {

      Transaction transaction = null;
      try (Session session = HibernateUtil.getSessionFactory().openSession()) {
         transaction = session.beginTransaction();

         CriteriaBuilder builder = session.getCriteriaBuilder();

         // Count number of employees
         CriteriaQuery<Long> criteriaQuery = builder.createQuery(Long.class);
         Root<Employee> root = criteriaQuery.from(Employee.class);
         criteriaQuery.select(builder.count(root));
         Query<Long> query = session.createQuery(criteriaQuery);
         long count = query.getSingleResult();
         System.out.println("Count = " + count);

         // Get max salary
         CriteriaQuery<Integer> criteriaQuery2 = builder.createQuery(Integer.class);
         Root<Employee> root2 = criteriaQuery2.from(Employee.class);
         criteriaQuery2.select(builder.max(root2.get("salary")));
         Query<Integer> query2 = session.createQuery(criteriaQuery2);
         int maxSalary = query2.getSingleResult();
         System.out.println("Max Salary = " + maxSalary);

         // Get Average Salary
         CriteriaQuery<Double> criteriaQuery3 = builder.createQuery(Double.class);
         Root<Employee> root3 = criteriaQuery3.from(Employee.class);
         criteriaQuery3.select(builder.avg(root3.get("salary")));
         Query<Double> query3 = session.createQuery(criteriaQuery3);
         double avgSalary = query3.getSingleResult();
         System.out.println("Average Salary = " + avgSalary);

         // Count distinct employees
         CriteriaQuery<Long> criteriaQuery4 = builder.createQuery(Long.class);
         Root<Employee> root4 = criteriaQuery4.from(Employee.class);
         criteriaQuery4.select(builder.countDistinct(root4));
         Query<Long> query4 = session.createQuery(criteriaQuery4);
         long distinct = query4.getSingleResult();
         System.out.println("Distinct count = " + distinct);

         transaction.commit();
      } catch (Exception e) {
         e.printStackTrace();
         if (transaction != null) {
            transaction.rollback();
         }
      }
   }
}

CriteriaQuery - FROM and JOIN example

The CriteriaQuery.from() method returns an element in the query's from clause for the entity class. A Root object is returned from from clause, which represent the object in the context of the query and allows joins.

The following example demonstrates how to use the CriteriaQuery.from() and CriteriaQuery.where() methods to join two objects.

package com.boraji.tutorial.hibernate;

import java.util.List;

import javax.persistence.criteria.*;

import org.hibernate.*;
import org.hibernate.query.Query;

import com.boraji.tutorial.hibernate.entity.Department;
import com.boraji.tutorial.hibernate.entity.Employee;

public class CriteriaQueryExample6 {

   public static void main(String[] args) {

      Transaction transaction = null;
      try (Session session = HibernateUtil.getSessionFactory().openSession()) {
         transaction = session.beginTransaction();

         CriteriaBuilder builder = session.getCriteriaBuilder();

         // Using FROM and JOIN
         CriteriaQuery<Object[]> criteriaQuery = builder.createQuery(Object[].class);
         Root<Employee> empRoot = criteriaQuery.from(Employee.class);
         Root<Department> deptRoot = criteriaQuery.from(Department.class);
         criteriaQuery.multiselect(empRoot, deptRoot);
         criteriaQuery.where(builder.equal(empRoot.get("department"), deptRoot.get("id")));

         Query<Object[]> query=session.createQuery(criteriaQuery);
         List<Object[]> list=query.getResultList();
         for (Object[] objects : list) {
            Employee employee=(Employee)objects[0];
            Department department=(Department)objects[1];
            System.out.println("EMP NAME="+employee.getName()+"\t DEPT NAME="+department.getName());
         }
         transaction.commit();
      } catch (Exception e) {
         e.printStackTrace();
         if (transaction != null) {
            transaction.rollback();
         }
      }
   }
}

 

CriteriaQuery - GROUP BY and HAVING example

The group by and having clauses are defined in the CriteriaQuery and can be used for summary information to be computed on a set of objects.

The following example demonstrates the use of the CriteriaQuery.groupBy() and CriteriaQuery.having() methods.

package com.boraji.tutorial.hibernate;

import java.util.List;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;

import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.query.Query;

import com.boraji.tutorial.hibernate.entity.Department;
import com.boraji.tutorial.hibernate.entity.Employee;

public class CriteriaQueryExample7 {

   public static void main(String[] args) {

      Transaction transaction = null;
      try (Session session = HibernateUtil.getSessionFactory().openSession()) {
         transaction = session.beginTransaction();

         CriteriaBuilder builder = session.getCriteriaBuilder();

         CriteriaQuery<Object[]> criteriaQuery = builder.createQuery(Object[].class);
         Root<Employee> root = criteriaQuery.from(Employee.class);
         criteriaQuery.multiselect(builder.count(root.get("name")), root.get("salary"),
               root.get("department"));
         criteriaQuery.groupBy(root.get("salary"), root.get("department"));
         criteriaQuery.having(builder.greaterThan(root.get("salary"), 30000));

         Query<Object[]> query = session.createQuery(criteriaQuery);
         List<Object[]> list = query.getResultList();
         for (Object[] objects : list) {
            long count = (Long) objects[0];
            int salary = (Integer) objects[1];
            Department department = (Department) objects[2];
            System.out.println("Number of Employee = " + count + "\t SALARY=" + salary
                  + "\t DEPT NAME=" + department.getName());
         }

         transaction.commit();
      } catch (Exception e) {
         e.printStackTrace();
         if (transaction != null) {
            transaction.rollback();
         }
      }
   }
}

An equivalent SQL statement-

select count(name),salary,dpt_id from employee group by salary,dpt_id having salary>30000;

CriteriaQuery – ORDER BY example

The order by clause is defined in the CriteriaQuery and can be used to define the order of a query result.

The following example demonstrates the use of the CriteriaQuery.orderBy() method.

package com.boraji.tutorial.hibernate;

import java.util.List;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;

import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.query.Query;

import com.boraji.tutorial.hibernate.entity.Employee;

public class CriteriaQueryExample8 {

   public static void main(String[] args) {

      Transaction transaction = null;
      try (Session session = HibernateUtil.getSessionFactory().openSession()) {
         transaction = session.beginTransaction();

         CriteriaBuilder builder = session.getCriteriaBuilder();

         CriteriaQuery<Employee> criteriaQuery = builder.createQuery(Employee.class);
         Root<Employee> root = criteriaQuery.from(Employee.class);
         criteriaQuery.select(root);
         criteriaQuery.orderBy(builder.asc(root.get("salary")));
         Query<Employee> query = session.createQuery(criteriaQuery);
         List<Employee> list = query.getResultList();
         for (Employee employee : list) {
            System.out.println("EMP NAME="+employee.getName()+"\t SALARY="+employee.getSalary());
         }

         transaction.commit();
      } catch (Exception e) {
         e.printStackTrace();
         if (transaction != null) {
            transaction.rollback();
         }
      }
   }
}

An equivalent SQL statement-

select * from employee order by salary asc;
Download Sources