18.1 Overview
Hibernate comes with a feature of Criteria Query which allows us to fetch the data from databased in object oriented style. Criteria Queries can only be used to fetch the records only as they do not support updates or deletes to the database. Criteria API uses entity names and properties not table names and table columns.
18.2 Criteria Queries API
- Criteria object needs to be grabbed by calling createCriteria(Entity.class) method of Session object.
- add() – is used to apply restrictions on criteria object. It is equivalent to where clause in SQL.
- Convenient class Restrictions should be used to apply the restrictions in add() method.
Restrictions class provides static method and commonly used Restrictions methods are
- Use Restrictions.gt("property", value)) to apply greater than restriction.
- Use Restrictions.lt("property", value)) to apply less than restriction.
- Use Restrictions.like("property", “pattern”)) to apply pattern restrictions.
- Use Restrictions.between("property", initial range, end range ) to apply range restrictions.
- Use Restrictions.isNull("property") to check if property is null.
- Use Restrictions.isNotNull("property") to check if property is not null.
- Use Restrictions.isEmpty("property") to check if property is empty.
- Use Restrictions.isNotEmpty("property") to check if property is not empty.
- To apply logical expressions (AND / OR ) conditions Hibernate provides LogicalExpression class which needs to be added as restrictions.
- Use LogicalExpression or = Restrictions.or(Restriction1, Restriction2) to apply OR condition. “In this case restriction1 OR restriction2 will be applied “
- Use LogicalExpression and = Restrictions.and(Restriction1, Restriction2) to apply AND condition. “In this case restriction1 AND restriction2 will be applied “
- To apply ordering similar to order by clause in SQL, use addOrder() method. Hibernate provides class org.hibernate.criterion.Order which has static methods desc and asc.
- Use Order.asc(“property”) to sort the result in ascending order of property.
- Use Order.desc(“property”) to sort the result in descending order of property.
- To use aggregate functions like sum(), avg() etc. we need to use Projections class (org.hibernate.criterion.Projections )defined by Hibernate and apply it using setProjection() method. Commonly used Projection API are
- Use Projections.rowCount() to get the total number of rows count
- Use Projections.avg("property") to get the average of property.
- Use Projections.countDistinct("property") to get the distinct count of a property
- Use Projections.max("property") to get the maximum value of property.
- Use Projections.min("property") to get the minimum value of property.
- Use Projections.sum("property") to get the sum of property.
- To get the specific properties in result (like select col1 from table) we need to use ProjectionsList. To do so we need to add the property in projectionList using Projections.property like below
ProjectionList columns = Projections.projectionList();
columns .add(Projections.property("property1"));
columns.add(Projections.property("property2"));
criteria.setProjection(columns);
Use dot (.) for nested property like instance.property. If Person has Address instance so to get the address property use address.city
18.3 Example
Let's take an example of Employee entity and try Criteria Queries.
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.url">
jdbc:mysql://localhost:3306/tutorial
</property>
<property name="hibernate.connection.username">
root
</property>
<property name="hibernate.connection.password">
password
</property>
<property name="dialect">
org.hibernate.dialect.MySQLDialect
</property>
<property name="hibernate.format_sql">true</property>
<property name="show_sql">true</property>
<property name="hibernate.connection.driver_class">
com.mysql.jdbc.Driver
</property>
<mapping resource="employee.hbm.xml" />
</session-factory>
</hibernate-configuration>
18.3.2 Create Employee Table
create table employee ( employee_id integer not null auto_increment, first_name varchar(255), last_name varchar(255), email_address varchar(255), salary integer, primary key (employee_id) );
18.3.3 Employee.java
package com.tutorial.hibernate;
public class Employee {
private int id;
private String firstName;
private String lastName;
private int salary;
private String emailAddress;
public int getId() {
return id;
}
public void setId( int id ) {
this.id = id;
}
public String getFirstName() {
return firstName;
}
public void setFirstName( String first_name ) {
this.firstName = first_name;
}
public String getLastName() {
return lastName;
}
public void setLastName( String last_name ) {
this.lastName = last_name;
}
public int getSalary() {
return salary;
}
public void setSalary( int salary ) {
this.salary = salary;
}
public String getEmailAddress() {
return emailAddress;
}
public void setEmailAddress(String emailAddress) {
this.emailAddress = emailAddress;
}
}
18.3.4 employee.hbm.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>
<class name="com.tutorial.hibernate.Employee" table="employee">
<id name="id" type="int" column="employee_id">
<generator class="native"/>
</id>
<property name="firstName" column="first_name" type="string"/>
<property name="lastName" column="last_name" type="string"/>
<property name="emailAddress" column="email_address" type="string"/>
<property name="salary" column="salary" type="int"/>
</class>
</hibernate-mapping>
18.3.5 Insert Test Data in Employee Table using below program
Session session = factory.openSession();
Transaction tx = session.beginTransaction();
Employee emp1 = new Employee();
emp1.setEmailAddress("emp1@company.com");
emp1.setFirstName("Employee1 First Name ");
emp1.setLastName("Employee1 Last name ");
emp1.setSalary(5000);
Employee emp2 = new Employee();
emp2.setEmailAddress("emp2@company.com");
emp2.setFirstName("Employee2 First Name ");
emp2.setLastName("Employee2 Last name ");
emp2.setSalary(3000);
Employee emp3 = new Employee();
emp3.setFirstName("Employee3 First Name ");
emp3.setLastName("Employee3 Last name ");
emp3.setSalary(8000);
session.save(emp1);
session.save(emp2);
session.save(emp3);
tx.commit();
session.close();
Below is the employee table state after inserting test data.
18.3.6 Test Program
import com.tutorial.hibernate.Employee;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.LogicalExpression;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
public class Test {
private static SessionFactory factory;
public static void main(String args[])
{
Configuration cfg = new Configuration().configure();
factory = cfg.buildSessionFactory();
getAllEmployees();
getAllEmployeeById(1);
getAllEmployeesByConditions();
getTotalSalofAllEmployees();
getOrderedListOfEmployees();
getSelectedColumnsOfEmployees();
testLogicalExpressions();
factory.close();
}
private static void getAllEmployees()
{
Session session = factory.openSession();
Criteria criteria = session.createCriteria(Employee.class);
List<Employee> employee = criteria.list();
for(int i=0;i<employee.size();i++)
{
System.out.println("Total Employees in Database = " + employee.size());
Employee emp = employee.get(i);
System.out.println(" Employee First Name = " + emp.getFirstName() );
System.out.println(" Employee Last Name = " + emp.getLastName() );
System.out.println(" Employee Email Address = " + emp.getEmailAddress());
System.out.println(" Employee Salary = " + emp.getSalary());
System.out.println("=============================");
}
}
private static void testLogicalExpressions()
{
Session session = factory.openSession();
Criteria criteria = session.createCriteria(Employee.class);
LogicalExpression andCondition = Restrictions.or(Restrictions.isNull("emailAddress"), Restrictions.like("firstName", "Employee2%"));
criteria.add(andCondition);
List<Employee> employee = criteria.list();
for(int i=0;i<employee.size();i++)
{
System.out.println("Total Employees in Database with email address as null OR firstname starts with Employee2 = " + employee.size());
Employee emp = employee.get(i);
System.out.println(" Employee First Name = " + emp.getFirstName() );
System.out.println(" Employee Last Name = " + emp.getLastName() );
System.out.println(" Employee Email Address = " + emp.getEmailAddress());
System.out.println(" Employee Salary = " + emp.getSalary());
System.out.println("=============================");
}
}
private static void getSelectedColumnsOfEmployees()
{
Session session = factory.openSession();
Criteria criteria = session.createCriteria(Employee.class);
ProjectionList columns = Projections.projectionList();
columns .add(Projections.property("firstName"));
columns.add(Projections.property("salary"));
criteria.setProjection(columns);
List<Object[]> employee = criteria.list();
for(int i=0;i<employee.size();i++)
{
System.out.println(" Employees First Name and Salary are" );
Object[] emp = employee.get(i);
System.out.println(" Employee First Name = " + emp[0] );
System.out.println(" Employee Salary = " + emp[1]);
System.out.println("=============================");
}
}
private static void getOrderedListOfEmployees()
{
Session session = factory.openSession();
Criteria criteria = session.createCriteria(Employee.class);
criteria.addOrder(Order.asc("firstName"));
List<Employee> employee = criteria.list();
for(int i=0;i<employee.size();i++)
{
System.out.println("Total Employees in Database in ascending order of First Name= " + employee.size());
Employee emp = employee.get(i);
System.out.println(" Employee First Name = " + emp.getFirstName() );
System.out.println(" Employee Last Name = " + emp.getLastName() );
System.out.println(" Employee Email Address = " + emp.getEmailAddress());
System.out.println(" Employee Salary = " + emp.getSalary());
System.out.println("=============================");
}
}
private static void getTotalSalofAllEmployees()
{
Session session = factory.openSession();
Criteria criteria = session.createCriteria(Employee.class);
Projection sum= Projections.sum("salary");
criteria.setProjection(sum);
long totalSal = (Long)criteria.uniqueResult();
System.out.println("Total Salary = " + totalSal);
}
private static void getAllEmployeesByConditions()
{
Session session = factory.openSession();
Criteria criteria = session.createCriteria(Employee.class);
criteria.add(Restrictions.isNull("emailAddress"));
List<Employee> employee = criteria.list();
for(int i=0;i<employee.size();i++)
{
System.out.println("Total Employees in Database with Null Email Address = " + employee.size());
Employee emp = employee.get(i);
System.out.println(" Employee First Name = " + emp.getFirstName() );
System.out.println(" Employee Last Name = " + emp.getLastName() );
System.out.println(" Employee Email Address = " + emp.getEmailAddress());
System.out.println(" Employee Salary = " + emp.getSalary());
System.out.println("=============================");
}
criteria = session.createCriteria(Employee.class);
criteria.add(Restrictions.gt("salary",3000));
employee = criteria.list();
for(int i=0;i<employee.size();i++)
{
System.out.println("Total Employees in Database with Salary > 3000 = " + employee.size());
Employee emp = employee.get(i);
System.out.println(" Employee First Name = " + emp.getFirstName() );
System.out.println(" Employee Last Name = " + emp.getLastName() );
System.out.println(" Employee Email Address = " + emp.getEmailAddress());
System.out.println(" Employee Salary = " + emp.getSalary());
System.out.println("=============================");
}
}
private static void getAllEmployeeById(int id)
{
Session session = factory.openSession();
Criteria criteria = session.createCriteria(Employee.class);
criteria.add(Restrictions.eq("id", id));
List<Employee> employee = criteria.list();
for(int i=0;i<employee.size();i++)
{
System.out.println("Total Employees in Database = " + employee.size());
Employee emp = employee.get(i);
System.out.println(" Employee First Name = " + emp.getFirstName() );
System.out.println(" Employee Last Name = " + emp.getLastName() );
System.out.println(" Employee Email Address = " + emp.getEmailAddress());
System.out.println(" Employee Salary = " + emp.getSalary());
System.out.println("=============================");
}
}
}
Test Program Output
