Hibernate Queries
Hibernate provides three different ways to retrieve data:
Hibernate Query Language
Working in HQL
// 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:
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.
SELECT Clause -The SELECT clause provides more control over the result set then the from clause. To obtain few properties of objects instead of the complete object, use the SELECT clause.
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
(a) Position-based : Set parameters in an ordered fashion, starting with zero, just like JDBC.
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
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
Criteria Queries
Criteria Interface
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
Criteria criteria = session.createCriteria(Employee.class);
criteria.add(Restrictions.gt("age",40));
List<Employee> employeeList = criteria.list();
Criteria criteria = session.createCriteria(Employee.class);
criteria.add(Restrictions.and(Restrictions.gt("age",23),Restrictions.lt("age",50)));
List<Employee> employeeList = criteria.list();
AddOrder
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
Native SQL
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
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
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;
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.
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
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.