Hibernate Query Language

• Hibernate Query Language (HQL) is same as SQL (Structured Query Language) but it doesn't depends on the table of the database. 

• Instead of table name, we use class name in HQL. So it is database independent query language.

• Advantages

• database independent

• supports polymorphic queries

• easy to learn for Java Programmer

• Query Interface

Methods

public int executeUpdate() is used to execute the update or delete query.
public List list() returns the result of the ralation 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.
public Query setParameter(String name, Object value) it sets the value to a named query parameter.

Example of HQL to get all the records

Query query=session.createQuery("from Emp");//here persistent class name is Emp  

List list=query.list();  

Example of HQL to get records with pagination

Query query=session.createQuery("from Emp");  

query.setFirstResult(5);  

query.setMaxResult(10);  

List list=query.list();//will return the records from 5 to 10th number  

Example of HQL update query

Transaction tx=session.beginTransaction();  

Query q=session.createQuery("update User set name=:n where id=:i");  

q.setParameter("n","Udit Kumar");  

q.setParameter("i",111);  

int status=q.executeUpdate();  

System.out.println(status);  

tx.commit();  

Example of HQL delete query

Query query=session.createQuery("delete from Emp where id=100");  // //specifying class name (Emp) not tablename 

query.executeUpdate();  

HQL with Aggregate functions

Query q=session.createQuery("select sum(salary) from Emp");  

List<Integer> list=q.list();  

System.out.println(list.get(0));  

Query q=session.createQuery("select max(salary) from Emp");  //Example to get maximum salary of employee

Query q=session.createQuery("select min(salary) from Emp");  // Example to get minimum salary of employee

Query q=session.createQuery("select count(id) from Emp");  // Example to count total number of employee ID

Query q=session.createQuery("select avg(salary) from Emp");  // Example to get average salary of each employees

HQL Update Statement

Query query=session.createQuery("update Employee set age=:age where name=:name");

query.setInteger("age", 32);

query.setString("name", "Lokesh Gupta");

int modifications=query.executeUpdate();

HQL Delete Statement

Query query=session.createQuery("delete from Account where accountstatus=:status");

query.setString("status", "purged");

int rowsDeleted=query.executeUpdate();

HQL Insert Statement

Query query=session.createQuery("insert into purged_accounts(id, code, status) "+"select id, code, status from account where status=:status");

query.setString("status", "purged");

int rowsCopied=query.executeUpdate();

HQL Select Statement

An HQL SELECT is used to query the database for classes and their properties

HQL – from clause and aliases

from Product as p // or from Product as product