Hibernate Queries

Hibernate provides three different ways to retrieve data:

• The Hibernate Query Language.
• The Criteria API, which we'll discuss in this session later.
• Native SQL queries.

Hibernate Query Language

• Hibernate Query Language (HQL) is an object-oriented query language, similar to SQL, but instead of operating on tables and columns, HQL works with persistent objects and their properties.
• HQL queries are translated by Hibernate into conventional SQL queries which in turns perform action on database.
• Understands inheritance. It can issue a query using a superclass or interface.
• Keywords like SELECT, FROM, and WHERE, etc., are not case sensitive, but properties like table and column names are case sensitive in HQL.

Working in HQL

• To execute a HQL query on database, we need to create query object.
• ” Query ” is an interface given in org.hibernate package.
• In order to get query object, we need to call createQuery() method in the session Interface.

// Getting Query  object.

Query query = session.createQuery("--- HQL command ---");

//Executing the object content (which is HQL command)

List list = query.list(); 

Query Interface Methods

The query interface provides many methods. Some commonly used methods are:

• public int executeUpdate() is used to execute the update or delete query.
• public list() returns the result as a list.
• public Query setFirstResult(int rowno) specifies the row number from where record will be retrieved.
• public Query setMaxResult(int rowno) specifies the no. of records to be retrieved from the relation (table).
• public Query setParameter(int position, Object value) it sets the value to the JDBC style query parameter.

Clauses in HQL

•  FROM Clause -   FROM clause if you want to load a complete persistent objects into memory.

String hql = "FROM Employee";
Query query = session.createQuery(hql);
List<Employee> results = (List<Employee>)query.list(); 

• AS Clause - The AS clause can be used to assign aliases to the classes in your HQL queries, especially when you have the long queries.

   

String hql="select E.name from Employee As E";
Query query=session.createQuery(hql);
List<String> employeeList = query.list();

• WHERE Clause.

• ORDER BY Clause

String hql="select E.name from Employee As E where E.age > 40 order by E.age";
Query query=session.createQuery(hql);
List<String> employeeList = query.list();

Selecting more than one columns from table

String hql = "Select name,age from Employee";
Query query = session.createQuery(hql);
List<Object[]> list= (List<Object[]>)query.list();
list.forEach((Object[] element)->{
     System.out.println(element[0]);
     System.out.println(element[1]);
 });

Binding  Query Parameters

 String hql = "select name from Employee where age > ?";
 Query query = session.createQuery(hql)
                .setParameter(0,40);
 List<String> employeeList = query.list();

(b) Name-based : Use names as placeholders and Set parameters by name..

String hql="select name from Employee where age > :age";
Query query=session.createQuery(hql).setParameter("age",40);
List<String> employeeList = query.list();

Binding by Object

Object-based binding  accepts an entire object for setting query parameters.

Placeholder names must match object attribute names.

public class QueryParam {
    Integer age;

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
}

QueryParam queryParam = new QueryParam();
queryParam.setAge(23);

String hql = "select name from Employee where age > :age";
Query query = session.createQuery(hql)
              .setProperties(queryParam);

Binding by Map

Map-based binding  accepts a map for setting query parameters.

Placeholder names must match map key.

Map map= new HashMap();
map.put("age",60);

String hql = "select name from Employee where age > :age";
Query query = session.createQuery(hql)
              .setProperties(map);

Exercise 1

• Create a hibernate entity Author and place  instance variables  Name, Age and Gender in it and Do some enteries for this hibernate entity.
• Using HQL print all the records of Author  
• Use As and Select Clause to Just print the name of the authors
• Find All the male authors with age above 30 in ascending order.
• Select and print only name and age of the Authors.
• Use Position Based, Name based, Object based and Map based query binding.

Clauses in HQL

3) UPDATE & DELETE Clause : delete work differently in Hibernate .executeUpdate() for executing HQL INSERT/UPDATE/DELETE statements.

String hql = "Update Employee set age=100 where id=3 ";
Query query = session.createQuery(hql);
query.executeUpdate();

INSERT Clause : HQL supports INSERT INTO clause only where records can be inserted from one object to another object.

      HQL supports only the INSERT INTO...SELECT....; not as  conventional      way  INSERT INTO…VALUES.

Dummy dummy = new Dummy();
dummy.setAge(23);
dummy.setId(23);
dummy.setName("Emp 23");
session.save(dummy);
String hql = "INSERT INTO Employee (id,name,age) SELECT id,name,age FROM Dummy where id = 23";
Query query = session.createQuery(hql);
query.executeUpdate();

Pagination using Query

There are two methods of the Query interface for pagination.

1. Query setMaxResults(int maxResult)

    This method tells Hibernate to retrieve a fixed number maxResults of objects.

2. Query setFirstResult(int startPosition)

    This method takes an integer that represents the first row in your result set, starting with row 0.

String hql = "FROM Employee";
Query query = session.createQuery(hql);
query.setFirstResult(1);
query.setMaxResults(10);
List<Employee> list = query.list() 

Named Query

@Entity
@NamedQuery(name = "user.byId",query = "from Employee where id = :id")
public class Employee {

    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String name;
    private Integer age;

    //getters and setters
}

Query query= session.createNamedQuery("user.byId")
                .setParameter("id",3);
System.out.println(query.uniqueResult());

@NamedQuery Annotation is used to specify query at the top of an entity 

Exercise 2

• Update a Author name with id 3 using HQL
• Delete Author with id 4 using HQL.
• Create a Demo class with same instance variables as in Author class.
• Save one record for demo entity now insert using HQL from Demo to Author Entity.
• Print few records of Author using pagination.
• Perform one query in HQL using Named Query.

 Criteria Queries

• The Criteria API allows queries to be built at runtime without direct string manipulations.
•  Criteria also includes projection and aggregation methods.
• Since criteria queries are built from library objects, the queries are parsed and validated at compile time, unlike Hibernate Query Language strings.

 Criteria Interface

• The Criteria interface is in the org.hibernate package.

• Since Criteria is an interface, it can’t be instantiated. Instead, the Session class has a factory method called createCriteria, which takes a reference of type Class.

  Eg: Criteria crit = session.createCriteria(User.class);

• As with most Hibernate classes, the methods in Criteria return the this reference, so additional calls can be chained.

• We cant perform non-select operations using this criteria.

Restriction Class

• In Restrictions class, we have all static methods and each method of this class returns Criterion object.
• Restrictions class is also given in “org.hibernate.criterion” package.

Criteria criteria = session.createCriteria(Employee.class);
criteria.add(Restrictions.gt("age",40));
List<Employee> employeeList = criteria.list();

• If we want to put more conditions on the data (multiple conditions)  then we can use and method , or method give by the Restrictions class.

 Criteria criteria = session.createCriteria(Employee.class);
 criteria.add(Restrictions.and(Restrictions.gt("age",23),Restrictions.lt("age",50)));
 List<Employee> employeeList = criteria.list();

AddOrder

• The addOrder method can be used to order the results.

Criteria criteria = session.createCriteria(Employee.class);
criteria.addOrder(Order.asc("age"));
List<Employee> employeeList = criteria.list();

Conjunction and Disjunction

Criteria criteria = session.createCriteria(Employee.class);
        Criterion criteria1=Restrictions.gt("age",23);
        Criterion criteria2=Restrictions.lt("age",25);

        //it performs or operation
        Conjunction conjunction = Restrictions.conjunction();
        conjunction.add(criteria1);
        conjunction.add(criteria2);
        criteria.add(conjunction);

        //it performs and operation
//        Disjunction disjunction = Restrictions.disjunction();
//        disjunction.add(criteria1);
//        disjunction.add(criteria2);
//        criteria.add(disjunction);

         List<Employee> employeeList = criteria.list();

Projection

Criteria criteria = session.createCriteria(Employee.class).
                setProjection(Projections.rowCount());
System.out.println(criteria.uniqueResult());

Count number of rows

Criteria criteria = session.createCriteria(Employee.class).
                setProjection(Projections.projectionList()
                        .add(Projections.avg("age"))
                        .add(Projections.max("age"))
                        .add(Projections.min("age")));

        Object[] objectArray = ((Object[]) criteria.uniqueResult());
        System.out.println("avg::::"+objectArray[0]);
        System.out.println("max::::"+objectArray[1]);
        System.out.println("min::::"+objectArray[2]);

Avg, Min, Max age

Exercise 3

• Get all the Authors between age 30 to 50 using criteria.
• Apply Conjunction and Disjunction using criteria.
• Find Average, Min and Max age among authors.
• Project only name and age of the employee using criteria.

Native SQL

• Native SQL means using the direct SQL command specific to the particular database and executing it with using hibernate.
• To execute Native SQL Queries on the database then, we need to construct an object of SQLQuery,  this SQLQuery is an interface extended from Query and it is given in ” org.hibernate package ”.

Query query= session.createNativeQuery("Select id,name,age from Employee");

List<Object []> list =(List<Object []>) query.list();

list.forEach((Object [] element)->{
            System.out.println(element[0]);
            System.out.println(element[1]);
            System.out.println(element[2]);
});

Query query= session
                .createNativeQuery("Select * from Employee")
                .addEntity(Employee.class);

        List<Employee> list =(List<Employee>) query.list();

        System.out.println(list);
        list.forEach((Employee employee)->{
            System.out.println(employee.getId());
            System.out.println(employee.getName());
            System.out.println(employee.getAge());
        });

Using Native sql with Array Object Result

Specifying type of result in Native SQL

   Query query= session
                .createNativeQuery("Delete from Employee where id=1")
                .addEntity(Employee.class);

        System.out.println(query.executeUpdate());

Performing deletion using native SQL

Exercise 4

• Perform select, delete and update operation using Native SQL.
• Use addEntity to cast the result into Hibernate entity object.

Hibernate Interceptor

•   Hibernate is all about Entity persistence and quite often we would want to intercept the request or perform some tasks when state of an object changes.
• Hibernate provides a Interface and a implementation of Interface. org.hibernate.Interceptor .

import org.hibernate.EmptyInterceptor;
import org.hibernate.type.Type;

import java.io.Serializable;
import java.util.Arrays;


public class LoggerInterceptor extends EmptyInterceptor {
    @Override
    public boolean onFlushDirty(
            Object entity,
            Serializable id,
            Object[] currentState,
            Object[] previousState,
            String[] propertyNames,
            Type[] types) {
        System.out.println( "------------------Entity "+entity.getClass().getSimpleName()+
                        "#"+id+
                " changed from "+Arrays.toString( previousState )+" to "+
                Arrays.toString( currentState )
        );
        return super.onFlushDirty( entity, id, currentState,
                previousState, propertyNames, types
        );
    }
}

In Order to use interceptor you need to extend EmptyInterface Class

SessionFactory sessionFactory= new Configuration().configure().buildSessionFactory();
LoggerInterceptor loggerInterceptor=new LoggerInterceptor();
Session session = sessionFactory.withOptions().interceptor(loggerInterceptor).openSession();
session.beginTransaction();
Employee employee = session.get(Employee.class,2);
employee.setName("Updated name");
System.out.println(employee);
session.getTransaction().commit();
session.close();
sessionFactory.close();

Exercise 5

• Implement an interceptor for onFlushDirty using EmptyInterceptor

Transaction and locking

Locking: In a relational database, locking refers to actions taken to prevent data from changing between the time it is read and the time is used.

Why locking: The ACID properties

Locking strategies: Pessimistic and Optimistic

Pessimistic locking assumes that concurrent transactions will conflict with each other, and requires resources to be locked after they are read and only unlocked after the application has finished using the data.

Pessimistic lock can be of two types, shared and exclusive

Share Lock

queryString = "select e " +
  "from Employee e " +
  "where e.deptId = :deptId";

/****************Reader 1*****************/
query = session.createQuery(
          queryString, Employee.class)
  .setParameter("deptId", "JVM")
  .setLockMode(LockModeType.PESSIMISTIC_READ); 

session.beginTransaction();
		
Employee employee = query.getSingleResult();
		
sleep(...)
		
session.getTransaction().commit();



/****************Reader 2*****************/
query = session.createQuery(
          queryString, Employee.class)
  .setParameter("deptId", "JVM")
  .setLockMode(LockModeType.PESSIMISTIC_READ); 

session.beginTransaction();
		
Employee employee = query.getSingleResult();
		
session.getTransaction().commit();

/*****************Output**********/

Reader 1: begin transaction

Hibernate: // reading from database
    select
        employee0_.empId as empId1_1_,
        employee0_.deptId as deptId2_1_,
        employee0_.gender as gender3_1_,
        employee0_.name as name4_1_
    from
        employee employee0_ 
    where
       employee0_.deptId=? lock in share mode

Reader 1: Employee [empId=1, deptId=JVM, 
                    name=Kavita,gender=F]
Reader 1: sleep ...

Reader 2: begin transaction

Hibernate: // reading from database
    same query...

Reader 2: Employee [empId=1, deptId=JVM, 
                    name=Kavita,gender=F]

Reader 2: closing transaction

Reader 1: closing transaction

Exclusive Lock

queryString = "select e " +
  "from Employee e " +
  "where e.deptId = :deptId";
/****************Reader *****************/
query = session.createQuery(
          queryString, Employee.class)
  .setParameter("deptId", "JVM")
  .setLockMode(LockModeType.PESSIMISTIC_WRITE); 
session.beginTransaction();
		
Employee employee = query.getSingleResult();
		
sleep(...)
		
session.getTransaction().commit();
/****************Writer *****************/
query = session.createQuery(
          queryString, Employee.class)
  .setParameter("deptId", "JVM")
  .setLockMode(LockModeType.PESSIMISTIC_WRITE); 
session.beginTransaction();
		
Employee employee = query.getSingleResult();

employee.setName("changed");
session.save(employee);
		
session.getTransaction().commit();

/*****************Output**********/
Reader: begin transaction
Hibernate: // reading from database
    select
        employee0_.empId as empId1_1_,
        employee0_.deptId as deptId2_1_,
        employee0_.gender as gender3_1_,
        employee0_.name as name4_1_
    from
        employee employee0_ 
    where
        employee0_.deptId=? for update
            
Reader: Employee [empId=1, deptId=JVM, 
                    name=Kavita,gender=F]
Reader: sleep ...

Writer: begin transaction
Hibernate: // reading from database
    same query...
Reader: closing transaction

Writer: Employee [empId=1, deptId=JVM, 
                    name=Kavita,gender=F]
Writer: update employee.name= 'changed'
Writer: Employee [empId=1, deptId=JVM, 
                    name=changed, gender=F]
Writer: closing transaction

Exclusive lock: Hands on

/****************Reader *****************/

// begin transaction
// fetch employee by deptId = "JVM"
// with PESSIMISTIC_WRITE lock
Employee employee = (...);
		
sleep(20000)
		
// close transaction

/****************Writer *****************/

// begin transaction
// fetch employee by gender = 'F'
// with PESSIMISTIC_WRITE lock
		
Employee employee = (...);

// update employee.name= 'changed'
		
// close transaction

/********Employee entity**********/

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

    @Id
    @GeneratedValue(
        strategy = GenerationType.AUTO, 
        generator = "native"
    )
    @GenericGenerator(
        name = "native", 
        strategy = "native"
    )
    private long empId;
    private String deptId;
    private String name;
    private char gender;
}

/***************** DB script for employee**********/
https://gist.github.com/bjpaul/ee8b56be0a065372a75d7a56b7a19714

/*****************Output**********/

????????

Optimistic locking

When the application uses long transactions or conversations that span several database transactions, you can store versioning data so that if the same entity is updated by two conversations, the last to commit changes is informed of the conflict, and does not override the other conversation’s work.

This approach guarantees some isolation, but scales well and works particularly well in read-often-write-sometimes situations.

Hibernate provides two different mechanisms for storing versioning information, a dedicated version number or a timestamp.

Version: Hands on

/****************************************/

// fetch employee
// make no changes, save and close transaction
// check Assert.equals(employee.version, 0)

// again fetch employee
// now make some changes, 
// save and close transaction

// now check the output

/********Employee entity**********/
@Entity
@Table(name="employee")
public class Employee {

    @Id
    @GeneratedValue(
        strategy = GenerationType.AUTO, 
        generator = "native"
    )
    @GenericGenerator(
        name = "native", 
        strategy = "native"
    )
    private long empId;
    private String deptId;
    private String name;
    private char gender;
    
    @Version
    @Temporal(TemporalType.DATE)
    private Date version;
    
}

/***************** Run query for employee**********/
update employee set version= current_timestamp;

• Physical clock: Timestamp
• Logical clock: short, int, long
• Logical v/s physical clocking optimistic locking

Optimistic lock: Hands on

/****************Writer 1 *****************/

// begin transaction
// fetch address by id = 1
// without any explicit lock
Address address = (...);
		
sleep(10000)
		
//update address.city = 'changed 1'

// close transaction

/****************Writer 2 *****************/

// begin transaction
// fetch address by id = 1
// without any explicit lock
Address address = (...);
		
//update address.city = 'changed 2'

// close transaction

/********Address entity**********/
@Entity
@Table(name="address")
public class Address {
	
    @Id
    @GeneratedValue(...)
    @GenericGenerator(...)
    private long id;
    private String city;
    private String country;
    @Version
    private short version;
	
    @ManyToOne(
        cascade=CascadeType.ALL, 
        fetch=FetchType.LAZY
    )
    @JoinColumn(name="employee_id")
    private Employee employee;
    
}

/***************** Run query for employee**********/
INSERT INTO `address` 
(`country`, `city`, `employee_id`, `version`)
 VALUES ('India', 'New Delhi',  1, 0);

Optimistic lock: with dedicated version property

/****************Writer 1 *****************/
session.beginTransaction();
Address address = session.get(Address.class, 1l);
		
sleep(10000)
		
address.setCity("changed ");
session.save(address);

session.getTransaction().commit();

/****************Writer 2 *****************/
session.beginTransaction();
Address address = session.get(Address.class, 1l);
		
address.setCity("changed ");
session.save(address);

session.getTransaction().commit();

/********Outout**********/
Writer 1: begin transaction 
Hibernate: //fetching address
Writer 1: Address [id=1, city=New Delhi, 
            country=India, version=0]
Writer 1: sleep ...

Writer 2: begin transaction 
Hibernate: //fetching address
Writer 2: Address [id=1, city=New Delhi, 
            country=India, version=0]
Writer 2: update address.city = 'changed' 
Hibernate: 
    update
        address 
    set
        city=?, country=?,
        employee_id=?, version=? 
    where
        id=? 
        and version=?

Writer 2: Address [id=1, city=changed, 
            country=India, version=0]
Writer 2: closing transaction

Writer 1: update address.city = 'changed' 
Hibernate: 
    same update query as Writer 2

Exception in thread "Writer 1" 
javax.persistence.OptimisticLockException: 
Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1

OptimisticLockException: Thrown by the persistence provider when an optimistic locking conflict occurs. This exception may be thrown as part of an API call, a flush or at commit time. The current transaction, if one is active, will be marked for rollback.

• OptimisticLocking: This annotation is used to specify if the currently annotated attribute will trigger an entity version increment upon being modified.
• OptimisticLockType: The four possible strategies are defined by the OptimisticLockType enumeration

Versionless optimistic locking: Hibernate supports a form of optimistic locking that does not require a dedicated "version attribute". This is also useful for use with modeling legacy schemas.

/********Employee entity**********/
@Entity
@Table(name="address")
@OptimisticLocking(
        type= OptimisticLockType.ALL
)
public class Address {
	
    -------------------
    -------------------
    
}

/****check the generated update query *******/

/********Employee entity**********/
@Entity
@Table(name="address")
@OptimisticLocking(
        type= OptimisticLockType.DIRTY
)
public class Address {
	
    -------------------
    -------------------
    
}

/****check the generated update query *******/

/********Employee entity**********/
@Entity
@Table(name="address")
@OptimisticLocking(
        type= OptimisticLockType.ALL
)
@DynamicUpdate
public class Address {
	
    -------------------
    //@Version
    private short version;
    -------------------
    
}

/****generated update query *******/

Hibernate: 
    update
        address 
    set
        city=? 
    where
        id=? 
        and city=? and country=? 
        and employee_id=? and version=?

/********Employee entity**********/
@Entity
@Table(name="address")
@OptimisticLocking(
        type= OptimisticLockType.DIRTY
)
@DynamicUpdate
public class Address {
	
    -------------------
    //@Version
    private short version;
    -------------------
    
}

/****check the generated update query *******/

Hibernate: 
    update
        address 
    set
        city=? 
    where
        id=? 
        and city=?

@DynamicUpdate: ​For updating, the entity uses dynamic sql generation where only changed columns ( for OptimisticLockType.DIRTY) or all columns ( for OptimisticLockType.ALL)  get referenced in the where clause

Comparisons

1. Optimistic locking does not cause transactions to wait for each other like Pessimistic locking. if a transaction fails because of optimistic locking, the user is required to start all over again.

2. Optimistic locking is generally faster because there is actually no locking from the database point of view. Pessimistic locking occurs on DB level, depends on RDMS we may not have control on what is locked, we need to take care of locking order manually.