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 neveropen -- Name of the table is geekEmployee create table neveropen.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// CLasspublic 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"<hibernate-mapping> <!-- This is the place where GeekEmployee POJO class and geekEmployee table mapping --> <class name = "com.neveropen.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"<hibernate-configuration> <session-factory> <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</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 salarypublic 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 passedgeekEmployeeCriteriaObject.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// Methodpublic 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// Methodpublic 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 onlySystem.out.println("Displaying Aggregated results");// Display command for better readability of outputSystem.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.
