Hibernate – Criteria Queries

Hibernate is a framework that provides some abstraction layer, meaning that the programmer does not have to worry about the implementations, Hibernate does the implementations for you internally like Establishing a connection with the database, writing queries to perform CRUD operations, etc.

To get the data available in RDBMS tables, Hibernate follows one method via Criteria API. This will help you to filter the resultset as desired. i.e. exactly how we write a “WHERE” clause for a SQL, the same way it can be handled here by means of Criteria Query. The logical operation, pagination concepts, sorting concepts, aggregation concepts too supported with Criteria Query.

Implementation: Let us take a sample table in MySQL to proceed further

-- Here name of the database is geeksforgeeks
-- Name of the table is geekEmployee 
create table geeksforgeeks.geekEmployee (
   id INT NOT NULL auto_increment,
   firstName VARCHAR(20) default NULL,
   lastName  VARCHAR(20) default NULL,
   salary     INT  default NULL,
   PRIMARY KEY (id)
);

A: File: GeekEmployee.java

Primarily, let us define a “GeekEmployee” POJO class, mapping file(mapping between POJO class and geekEmployee table), configuration file(informs about MySQL as the database is taken, credentials and the mapping file that needs to be looked. 

Example:

Java

// Java Program to Illustrate GeekEmployee Class

// CLass
public class GeekEmployee {

    // Class data members
    private int id;
    private String firstName;
    private String lastName;
    private int salary;

    // All the four attributes must match with geekEmployee
    // table and datatypes also should match

    // Constructor
    public GeekEmployee() {}

    // Constructor
    public GeekEmployee(String firstName, String lastName,
                        int salary)
    {

        // This keyword refers to current instance itself
        this.firstName = firstName;
        this.lastName = lastName;
        this.salary = salary;
    }

    // Getters and Setters

    public int getId() { return id; }
    public void setId(int id) { this.id = id; }
    public String getFirstName() { return firstName; }
    public void setFirstName(String firstName)
    {
        this.firstName = firstName;
    }
    public String getLastName() { return lastName; }
    public void setLastName(String lastName)
    {
        this.lastName = lastName;
    }
    public int getSalary() { return salary; }
    public void setSalary(int salary)
    {
        this.salary = salary;
    }
}

B: File: geekEmployee.hbm.xml (Mapping file that connects POJO class and MySQL table)

XML

<?xml version = "1.0" encoding = "utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
  <!-- This is the place where GeekEmployee POJO class  and geekEmployee table mapping -->
   <class name = "com.geeksforgeeks.GeekEmployee" table = "geekEmployee">
      <meta attribute = "class-description">
         This class contains the geekEmployee detail. This is optional
      </meta>

      <id name = "id" type = "int" column = "id">
         <generator class="native"/>
      </id>

      <property name = "firstName" column = "first_name" type = "string"/>
      <property name = "lastName" column = "last_name" type = "string"/>
      <property name = "salary" column = "salary" type = "int"/>

   </class>
</hibernate-mapping>

C: File: hibernate.cfg.xml (Hibernate configuration file)

XML

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/geeksforgeeks</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password">XXX</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="show_sql">true</property><!-- For criteria query to display , always make it as true -->
        <property name="format_sql">true</property>
        <property name="hbm2ddl.auto">update </property>
        <!-- We need to specify which hbm file we are going to use here -->
        <mapping resource="geekEmployee.hbm.xml" />
    </session-factory>
</hibernate-configuration>

Implementation: Let us add a few records to the table so that we can perform criteria operations on it. For adding records, let us do from hibernate itself.

Example 1:

Java

public class GeekEmployeeCriteriaExample {
    private static SessionFactory sessionFactory;

    public static void main(String[] args)
    {
        //
        try {
            sessionFactory = new Configuration()
                                 .configure()
                                 .buildSessionFactory();
        }
        catch (Throwable ex) {
            System.err.println(
                "Failed to create sessionFactory object."
                + ex);
            throw new ExceptionInInitializerError(ex);
        }

        GeekEmployeeCriteriaExample
            geekEmployeeCriteriaObject
            = new GeekEmployeeCriteriaExample();

        /* As a sample let us add some 10 records so that we
         * can see criteria example */
        Integer empID1
            = geekEmployeeCriteriaObject.addEmployee(
                "GeekA", "GeekA", 1000);
        Integer empID2
            = geekEmployeeCriteriaObject.addEmployee(
                "GeekB", "GeekB", 5000);
        Integer empID3
            = geekEmployeeCriteriaObject.addEmployee(
                "GeekC", "GeekC", 10000);
        Integer empID4
            = geekEmployeeCriteriaObject.addEmployee(
                "GeekD", "GeekD", 20000);
        Integer empID5
            = geekEmployeeCriteriaObject.addEmployee(
                "GeekE", "GeekE", 25000);

        Integer empID6
            = geekEmployeeCriteriaObject.addEmployee(
                "GeekF", "GeekF", 30000);
        Integer empID7
            = geekEmployeeCriteriaObject.addEmployee(
                "GeekG", "GeekG", 40000);
        Integer empID8
            = geekEmployeeCriteriaObject.addEmployee(
                "GeekH", "GeekH", 50000);
        Integer empID9
            = geekEmployeeCriteriaObject.addEmployee(
                "GeekI", "GeekI", 35000);
        Integer empID10
            = geekEmployeeCriteriaObject.addEmployee(
                "GeekJ", "GeekJ", 85000);
        * /

            System.out.println(
                "Listing the data via criteria");
        System.out.println("-----------------------------");
        geekEmployeeCriteriaObject
            .listGeekEmployeesByCriteria();
    }

    // This method  List the geekEmployee data whose salary
    // greater than 50000
    public void listGeekEmployeesByCriteria()
    {
        Session session = sessionFactory.openSession();
        Transaction tx = null;

        try {
            tx = session.beginTransaction();
            // This will simply return every object that
            // corresponds to the GeekEmployee class.
            Criteria geekEmployeeCriteria
                = session.createCriteria(
                    GeekEmployee.class);
            // As a list we can collect them and can iterate
            List geekEmployeeList
                = geekEmployeeCriteria.list();
            for (Iterator iterator
                 = geekEmployeeList.iterator();
                 iterator.hasNext();) {
                GeekEmployee employee
                    = (GeekEmployee)iterator.next();
                System.out.print("First Name: "
                                 + employee.getFirstName());
                System.out.print("  Last Name: "
                                 + employee.getLastName());
                System.out.println("  Salary: "
                                   + employee.getSalary());
            }
            tx.commit();
        }
        catch (HibernateException e) {
            if (tx != null)
                tx.rollback();
            e.printStackTrace();
        }
        finally {
            session.close();
        }
    }
    /* Method to CREATE an employee in the database */
    public Integer addEmployee(String fname, String lname,
                               int salary)
    {
        Session session = sessionFactory.openSession();
        Transaction tx = null;
        Integer employeeID = null;

        try {
            tx = session.beginTransaction();
            GeekEmployee employee
                = new GeekEmployee(fname, lname, salary);
            employeeID = (Integer)session.save(employee);
            tx.commit();
        }
        catch (HibernateException e) {
            if (tx != null)
                tx.rollback();
            e.printStackTrace();
        }
        finally {
            session.close();
        }
        return employeeID;
    }
}

Output: On executing the above code, we can able to see the output in the console as follows:

For all the 10 records, the data is inserted

On console: And also as we have displayed the records via criteria

Displaying data via criteria way

At the same time, we can see the data got inserted in MySQL table also

Using the criteria, we can manipulate the data in different ways.

Filtering the data based on Salary. We need to write the below methods to do that. In SQL, we will filter the day by means of adding the “WHERE” clause. Here in hibernate, we need to use add() method available for the Criteria object and it is helped to add restrictions for a criteria query. It will have all the comparison operations such as >,<,=, between, etc.

Operator	Restrictions
>	Restrictions.gt
<	Restrictions.lt
=	Restrictions.eq
between	Restrictions.between
Wildcard pattern(like)	Restrictions.like

Example 2:

Java

// List out all geekEmployees based on
// the filtering condition with salary
public void listGeekEmployeesBySalary(int salaryRange,
                                      String conditionCheck)
{

    Session session = sessionFactory.openSession();
    Transaction tx = null;

    // Try block to check for exceptions
    try {

        tx = session.beginTransaction();
        // This will simply return every object that
        // corresponds to the GeekEmployee class
        Criteria geekEmployeeCriteria
            = session.createCriteria(GeekEmployee.class);

        // Depends upon the condition check, Restrictions
        // are added
        if (conditionCheck != null) {
            if (conditionCheck.equals(">")) {
                geekEmployeeCriteria.add(
                    Restrictions.gt("salary", salaryRange));
            }
            if (conditionCheck.equals("<")) {
                geekEmployeeCriteria.add(
                    Restrictions.lt("salary", salaryRange));
            }
            if (conditionCheck.equals("=")) {
                geekEmployeeCriteria.add(
                    Restrictions.eq("salary", salaryRange));
            }
            if (conditionCheck.equalsIgnoreCase(
                    "between")) {
                geekEmployeeCriteria.add(
                    Restrictions.between("salary", 10000,
                                         30000));
            }
        }

        // As a list we can collect them and can iterate
        List geekEmployeeList = geekEmployeeCriteria.list();

        for (Iterator iterator
             = geekEmployeeList.iterator();
             iterator.hasNext();) {
            GeekEmployee employee
                = (GeekEmployee)iterator.next();
            System.out.print("First Name: "
                             + employee.getFirstName());
            System.out.print("  Last Name: "
                             + employee.getLastName());
            System.out.println("  Salary: "
                               + employee.getSalary());
        }

        tx.commit();
    }

    // Catch block to handle the exceptions
    catch (HibernateException e) {

        if (tx != null)
            tx.rollback();
        e.printStackTrace();
    }

    // finally block that will execute for sure
    finally {

        // Closing sessions using close() method
        session.close();
    }
}

We can execute the same by calling in different ways

Java

System.out.println(
    "Listing the geekEmployee data whose salary greater than 50000");
System.out.println(
    "--------------------------------------------------------------------");
// Here in the place of "salary" parameter, 50000 is passed
// and in the place of "conditionCheck" , ">" is passed
geekEmployeeCriteriaObject.listGeekEmployeesBySalary(50000,
                                                     ">");

Output: On console

salary > 50000

Java

System.out.println("Listing the geekEmployee data whose salary lesser than 50000");
System.out.println("--------------------------------------------------------------------");
geekEmployeeCriteriaObject.listGeekEmployeesBySalary(50000,"<");

Output: On console

Java

System.out.println("Listing the geekEmployee data whose salary equal to 30000");
System.out.println("----------------------------------------------------------------");
geekEmployeeCriteriaObject.listGeekEmployeesBySalary(30000,"=");

Output: On console

Java

System.out.println("Listing the geekEmployee data whose salary between 10000 and 30000");
System.out.println("-----------------------------------------------------------------------------");
geekEmployeeCriteriaObject.listGeekEmployeesBySalary(30000,"between");

Output: On console

For between

We can combine the queries with the “And”/”Or” condition also.

Example 3:

Java

// Java Program to Illustrate Combining Queries
// With And/Or

// Method
// List the geekEmployee data whose firstname like
// certain name and salary > certain value
// We can combine expressions using 'And','Or'
public void listGeekEmployeesByNameAndSalaryCriteria()
{
    Session session = sessionFactory.openSession();
    Transaction tx = null;

    // Try block to check for exceptions
    try {

        tx = session.beginTransaction();

        // This will simply return every object that
        // corresponds to the GeekEmployee class.
        Criteria geekEmployeeCriteria
            = session.createCriteria(GeekEmployee.class);

        // Here 2 expectations are there one with salary and
        // second one is name. Both are expected to be
        // present. Let us see how to do that
        Criterion salaryExpectation
            = Restrictions.gt("salary", 40000);

        Criterion nameExpectation
            = Restrictions.ilike("firstName", "Geek%");
        // As we are combining 2 conditions and that two
        // logically And, we need to add as Restrictions.and
        // To get records matching with AND conditions we
        // need to give below way
        LogicalExpression logicalAndExpression
            = Restrictions.and(salaryExpectation,
                               nameExpectation);
        geekEmployeeCriteria.add(logicalAndExpression);

        // As a list we can collect them and can iterate
        List geekEmployeeList = geekEmployeeCriteria.list();

        for (Iterator iterator
             = geekEmployeeList.iterator();
             iterator.hasNext();) {
            GeekEmployee employee
                = (GeekEmployee)iterator.next();
            System.out.print("First Name: "
                             + employee.getFirstName());
            System.out.print("  Last Name: "
                             + employee.getLastName());
            System.out.println("  Salary: "
                               + employee.getSalary());
        }
        tx.commit();
    }

    // Catch block to handle exceptions
    catch (HibernateException e) {

        if (tx != null)
            tx.rollback();
        e.printStackTrace();
    }

    // Finally block which will execute for sure
    finally {

        // Closing sessions using close() method
        session.close();
    }
}

Java

System.out.println(
    "Listing the geekEmployee data By Name and Salary With Certain conditions");
System.out.println(
    "-----------------------------------------------------------------------------");
geekEmployeeCriteriaObject
    .listGeekEmployeesByNameAndSalaryCriteria();

Output: On console

Example 4:

Java

// Java Program to Illustrate Pagination Concept

// Method
public void listPaginatedResultsUsingCriteria()
{

    Session session = sessionFactory.openSession();
    Transaction tx = null;

    // Try block to check for exceptions
    try {

        tx = session.beginTransaction();

        // This will simply return every object that
        // corresponds to the GeekEmployee class.
        Criteria geekEmployeeCriteria
            = session.createCriteria(GeekEmployee.class);
        // setFirstResult-> It takes an integer and it is
        // represented as the first row in your result set,
        // starting with row 0.

        geekEmployeeCriteria.setFirstResult(1);
        // setMaxResults->fixed number maxResults of objects
        // are returned here
        geekEmployeeCriteria.setMaxResults(3);

        // As a list we can collect them and can iterate
        List geekEmployeeList = geekEmployeeCriteria.list();

        for (Iterator iterator
             = geekEmployeeList.iterator();
             iterator.hasNext();) {
            GeekEmployee employee
                = (GeekEmployee)iterator.next();
            System.out.print("First Name: "
                             + employee.getFirstName());
            System.out.print("  Last Name: "
                             + employee.getLastName());
            System.out.println("  Salary: "
                               + employee.getSalary());
        }

        tx.commit();
    }

    // Catch block to handle exceptions
    catch (HibernateException e) {

        if (tx != null)
            tx.rollback();
        e.printStackTrace();
    }

    // Finally block which will execute for sure
    finally {

        // Closing the connections
        // using close() methods
        session.close();
    }
}

Java

System.out.println("Displaying Paginated results");
System.out.println("-------------------------------");
geekEmployeeCriteriaObject.listPaginatedResultsUsingCriteria();

Output: On console

Example 5:

Java

// Java Program to Sort Records using Criteria

// Method
public void listSortedResultsUsingCriteria()
{

    Session session = sessionFactory.openSession();
    Transaction tx = null;

    // try block to check for exceptions
    try {
        tx = session.beginTransaction();
        // This will simply return every object that
        // corresponds to the GeekEmployee class.

        Criteria geekEmployeeCriteria
            = session.createCriteria(GeekEmployee.class);
        geekEmployeeCriteria.add(
            Restrictions.gt("salary", 20000));

        // Display the results in descending order
        geekEmployeeCriteria.addOrder(Order.desc("salary"));

        // As a list we can collect them and can iterate
        List geekEmployeeList = geekEmployeeCriteria.list();

        for (Iterator iterator
             = geekEmployeeList.iterator();
             iterator.hasNext();) {
            GeekEmployee employee
                = (GeekEmployee)iterator.next();
            System.out.print("First Name: "
                             + employee.getFirstName());
            System.out.print("  Last Name: "
                             + employee.getLastName());
            System.out.println("  Salary: "
                               + employee.getSalary());
        }

        tx.commit();
    }

    // Catch block to handle exceptions
    catch (HibernateException e) {
        if (tx != null)
            tx.rollback();

        // Display exceptions with line numbers
        // using printStackTrace() method
        e.printStackTrace();
    }

    // Finally block
    // It will execute for sure
    finally {
        session.close();
    }
}

System.out.println("Displaying sorted results");
System.out.println("---------------------------");
geekEmployeeCriteriaObject.listSortedResultsUsingCriteria();

Output: On console

descending order listing of data

Aggregations are a very useful part of report preparations. In Hibernate, it can be possible by means of Projections

Aggregation	Hibernate way with Projections
Get RowCount	Projections.rowCount()
Get sum of salary	Projections.sum(“salary”)
Get average of salary	Projections.avg(“salary”)
Get maximum salary	Projections.max(“salary”)
Get minimum salary	Projections.min(“salary”)

Example 6:

Java

// Java Program to Illustrate Aggregations

// Method
// to get total count, sum(salary),
// max(salary),min(salary),avg(salary)
public void displayAggregatedValuesUsingCriteria()
{

    Session session = sessionFactory.openSession();
    Transaction tx = null;

    // Try block to check for exceptions
    try {

        tx = session.beginTransaction();

        // This will simply return every object that
        // corresponds to the GeekEmployee class.
        Criteria geekEmployeeCriteria
            = session.createCriteria(GeekEmployee.class);

        // Get total number of records by using rowcount
        geekEmployeeCriteria.setProjection(
            Projections.rowCount());
        List employeeRowCount = geekEmployeeCriteria.list();

        System.out.println("Total row Count: "
                           + employeeRowCount.get(0));

        // Getting sum(salary)
        geekEmployeeCriteria.setProjection(
            Projections.sum("salary"));
        List totalSalary = geekEmployeeCriteria.list();

        System.out.println("Total Salary of GeekEmployees: "
                           + totalSalary.get(0));

        // Getting average(salary)
        geekEmployeeCriteria.setProjection(
            Projections.avg("salary"));
        List averageSalary = geekEmployeeCriteria.list();
        System.out.println(
            "Average Salary of GeekEmployees: "
            + averageSalary.get(0));

        // Getting max(salary)
        geekEmployeeCriteria.setProjection(
            Projections.max("salary"));
        List maxSalary = geekEmployeeCriteria.list();
        System.out.println(
            "Maximum Salary among GeekEmployees: "
            + maxSalary.get(0));

        // Getting min(salary)
        geekEmployeeCriteria.setProjection(
            Projections.min("salary"));
        List minSalary = geekEmployeeCriteria.list();
        System.out.println(
            "Minimum salary among GeekEmployees: "
            + minSalary.get(0));

        tx.commit();
    }

    // Catch block to handle exceptions
    catch (HibernateException e) {
        if (tx != null)
            tx.rollback();

        // Printing exceptions with line number
        // using printStackTrace() method
        e.printStackTrace();
    }

    // Finally block
    finally {

        // Closing connections
        session.close();
    }
}

// Display message only
System.out.println("Displaying Aggregated results");

// Display command for better readability of output
System.out.println("--------------------------------");

geekEmployeeCriteriaObject
    .displayAggregatedValuesUsingCriteria();

Output: On console

Video explanation of the concepts explained for criteria queries are as follows:

Conclusion: As explained in the above examples, we can perform different criteria and they will help to filter out, paginate and sort the results as per our needs. Hence they are much useful in programming.