Hibernate Query Language (HQL) is an object-oriented query language similar to SQL, but not operations on tables and columns. HQL is suitable for persistent objects and their properties. HQL queries are converted from Hibernate to traditional SQL queries, which perform operations on the database on the circle.
Although it is possible to use native SQL directly with SQL statements and Hibernate, it is recommended to use HQL to avoid the hassle of database portability as much as possible and adopt the advantages of Hibernate's SQL generation and caching strategies.
Keywords like SELECT, FROM and WHERE are case-sensitive, but properties such as table names and column names are distinguished in HQL sensitive.
FROM statement
Use the FROM clause if you want to load a complete persistent object into memory. Here is a simple syntax using the FROM clause:
String hql = "FROM Employee";Query query = session.createQuery(hql);List results = query.list();If you need to fully qualified a class name in HQL, just specify the following package and class name:
String hql = "FROM com.hibernatebook.criteria.Employee";Query query = session.createQuery(hql);List results = query.list();
AS statement
The AS clause can be used to alias allocate to HQL queries in a class, especially when there are very long queries. For example, our brief examples above are the following:
String hql = "FROM Employee AS E";Query query = session.createQuery(hql);List results = query.list();
The AS keyword is optional, and you can also specify an alias directly in the subsequent class name, as shown below:
String hql = "FROM Employee E";Query query = session.createQuery(hql);List results = query.list();
SELECT clause
The SELECT clause provides more control than the result set of the from clause. If you want to get several properties of the object instead of the entire object, use the SELECT clause. Here is a simple syntax to use a SELECT statement to get Employee object just FIRST_NAME field:
String hql = "SELECT E.firstName FROM Employee E";Query query = session.createQuery(hql);List results = query.list();
It is worth noting that here, Employee.firstName is an attribute of the Employee object, not a field of the EMPLOYEE table.
WHERE clause
If you want to narrow down the specific object returned from the storage, you can use the WHERE clause. Here is a simple syntax using the WHERE clause:
String hql = "FROM Employee E WHERE E.id = 10";Query query = session.createQuery(hql);List results = query.list();
ORDER BY clause
To sort the results of an HQL query, you will need to use the ORDER BY clause. You can sort the results in ascending (ASC) or descending (DESC) in the result set by sorting the results in any attribute of the object. Here is a simple syntax using the ORDER BY clause:
String hql = "FROM Employee E WHERE E.id > 10 ORDER BY E.salary DESC";Query query = session.createQuery(hql);List results = query.list();
If you want to sort by more than one property, you will just add additional properties to the end of the command separated by a clause with commas, as shown below:
String hql = "FROM Employee E WHERE E.id > 10 " + "ORDER BY E.firstName DESC, E.salary DESC ";Query query = session.createQuery(hql);List results = query.list();
GROUP BY clause
This clause allows the extraction of information from the database and group of Hibernate's attribute-based values, and usually uses the result to include the total value. Here is a simple syntax using the GROUP BY clause:
String hql = "SELECT SUM(E.salary), E.firtName FROM Employee E " + "GROUP BY E.firstName";Query query = session.createQuery(hql);List results = query.list();
Use named parameters
Hibernate is supported in its HQL query parameters. This makes it easy to write and accept input from users without having to defend against HQL queries on SQL injection attacks. Here is a simple syntax using named parameters:
String hql = "FROM Employee E WHERE E.id = :employee_id";Query query = session.createQuery(hql);query.setParameter("employee_id",10);List results = query.list(); UPDATE clause
Batch updates are new HQL with Hibernate3, and different deletion work, the same as Hibernate 3 and Hibernate2. The Query interface now contains a method called executeUpdate() for executing HQL UPDATE or DELETE statement.
The UPDATE clause can be used to update one or more properties in one or more objects. Here is a simple syntax using the UPDATE clause:
String hql = "UPDATE Employee set salary = :salary " + "WHERE id = :employee_id";Query query = session.createQuery(hql);query.setParameter("salary", 1000);query.setParameter("employee_id", 10);int result = query.executeUpdate();System.out.println("Rows affected: " + result); DELETE clause
The DELETE clause can be used to delete one or more objects. Here is a simple syntax using the DELETE clause:
String hql = "DELETE FROM Employee " + "WHERE id = :employee_id";Query query = session.createQuery(hql);query.setParameter("employee_id", 10);int result = query.executeUpdate();System.out.println("Rows affected: " + result); INSERT clause
HQL supports INSERT INTO clauses where only records can be inserted from one object to another. Here is a simple syntax using the INSERT INTO clause:
String hql = "INSERT INTO Employee(firstName, lastName, salary)" + "SELECT firstName, lastName, salary FROM old_employee";Query query = session.createQuery(hql);int result = query.executeUpdate();System.out.println("Rows affected: " + result); Aggregation method
HQL supports multiple aggregation methods, similar to SQL. They work in HQL the same way in SQL and the following list of available features:
The DISTINCT keyword only calculates unique values set on this line. The following query will return only a unique count:
String hql = "SELECT count(distinct E.firstName) FROM Employee E";Query query = session.createQuery(hql);List results = query.list();
Using query pagination
There are two methods for pagination query interfaces.
Together, you can build a paging component on a website or Swing application. Here is an example, you can extend it to get 10 lines:
String hql = "FROM Employee";Query query = session.createQuery(hql);query.setFirstResult(1);query.setMaxResults(10);List results = query.list();
Query criteria
Hibernate provides an alternative way to operate objects and sequentially data available in the RDBMS table. One of the methods is the standard API, which allows you to establish a standard query object programming that can apply filtering rules and logical conditions.
Hibernate's Session interface provides an instance of a class that can be used to create a returned persistent object, and the application executes a conditional query to createCriteria() method.
The following is an example of a simplest conditional query that will simply return each object corresponding to the Employee class.
Criteria cr = session.createCriteria(Employee.class);List results = cr.list();
Limitations and standards:
You can use the add() method to add restriction query using the Criteria object. Here is an example of adding a limit with a salary return record equal to 2000:
Criteria cr = session.createCriteria(Employee.class);cr.add(Restrictions.eq("salary", 2000));List results = cr.list();Here are a few examples covering different scenarios and can be used as required:
Criteria cr = session.createCriteria(Employee.class);// To get records having salary more than 2000cr.add(Restrictions.gt("salary", 2000));// To get records having salary less than 2000cr.add(Restrictions.lt("salary", 2000));// To get records having fistName starting with zaracr.add(Restrictions.like("firstName", "zara%"));// Case sensitive form of the above restriction.cr.add(Restrictions.ilike("firstName", "zara%"));// To get records having salary in between 1000 and 2000cr.add(Restrictions.between("salary", 1000, 2000));// To check if the given property is nullcr.add(Restrictions.isNull("salary"));// To check if the given property is not nullcr.add(Restrictions.isNotNull("salary"));// To check if the given property is emptycr.add(Restrictions.isEmpty("salary"));// To check if the given property is not emptycr.add(Restrictions.isNotEmpty("salary")); You can create AND or ORs to use LogicalExpression to restrict the following conditions: Criteria cr = session.createCriteria(Employee.class); Criterion salary = Restrictions.gt("salary", 2000); Criterion name = Restrictions.ilike("firstNname","zara%");// To get records matching with OR conditionionsLogicalExpression orExp = Restrictions.or(salary, name);cr.add( orExp );// To get records matching with AND conditionsLogicalExpression andExp = Restrictions.and(salary, name);cr.add( andExp );List results = cr.list();Although all the above conditions can be directly introduced using HQL in the previous tutorial.
Paging usage standards:
There are also standard interfaces, two methods for pagination.
Together with the above two methods, we can build a paging component on our website or Swing application. Here is an example, which can be extended to get 10 rows at a time:
Criteria cr = session.createCriteria(Employee.class);cr.setFirstResult(1);cr.setMaxResults(10);List results = cr.list();
Sort results:
The standard API provides the org.hibernate.criterion.Order class sorting your result set in ascending or descending order, according to the properties of the object. This example demonstrates how to sort using the result set of the Order class:
Criteria cr = session.createCriteria(Employee.class);// To get records having sales more than 2000cr.add(Restrictions.gt("salary", 2000));// To sort records in descending ordercrit.addOrder(Order.desc("salary"));// To sort records in ascending ordercrit.addOrder(Order.asc("salary"));List results = cr.list(); Prediction and Aggregation:
The Criteria API provides an org.hibernate.criterion.Projections class that can be used to get the average, maximum or minimum attribute values. The Projections class is similar to the class limitation because it provides several static factory methods for obtaining Projection instances. Provides the
Here are some examples involving different schemes that can be used as prescribed:
Criteria cr = session.createCriteria(Employee.class);// To get total row count.cr.setProjection(Projections.rowCount());// To get average of a property.cr.setProjection(Projections.avg("salary"));// To get distinct count of a property.cr.setProjection(Projections.countDistinct("firstName"));// To get maximum of a property.cr.setProjection(Projections.max("salary"));// To get minimum of a property.cr.setProjection(Projections.min("salary"));// To get sum of a property.cr.setProjection(Projections.sum("salary")); Criteria Queries Example:
Consider the following POJO class:
public class Employee { private int id; private String firstName; private String lastName; private int salary; public Employee() {} public Employee(String fname, String lname, int salary) { this.firstName = fname; this.lastName = lname; this.salary = salary; } 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; }}Let's create the following EMPLOYEE table to store the Employee object:
create table EMPLOYEE ( id INT NOT NULL auto_increment, first_name VARCHAR(20) default NULL, last_name VARCHAR(20) default NULL, salary INT default NULL, PRIMARY KEY (id));
The following will be mapped files.
<?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="Employee" table="EMPLOYEE"> <meta attribute="class-description"> This class contains the employee detail. </meta> <id name="id" type="int" column="id"> <generator/> </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>
Finally, we will create the main() method of the application class to run, and we will use the Criteria query for the application:
import java.util.List; import java.util.Date;import java.util.Iterator; import org.hibernate.HibernateException; import org.hibernate.Session; import org.hibernate.Transaction;import org.hibernate.SessionFactory;import org.hibernate.Criteria;import org.hibernate.criterion.Restrictions;import org.hibernate.criterion.Projections;import org.hibernate.cfg.Configuration;public class ManageEmployee { private static SessionFactory factory; public static void main(String[] args) { try{ factory = new Configuration().configure().buildSessionFactory(); } catch (Throwable ex) { System.err.println("Failed to create sessionFactory object." + ex); throw new ExceptionInInitializerError(ex); } ManageEmployee ME = new ManageEmployee(); /* Add few employee records in database */ Integer empID1 = ME.addEmployee("Zara", "Ali", 2000); Integer empID2 = ME.addEmployee("Daisy", "Das", 5000); Integer empID3 = ME.addEmployee("John", "Paul", 5000); Integer empID4 = ME.addEmployee("Mohd", "Yasee", 3000); /* List down all the employees */ ME.listEmployee(); /* Print Total employee's count */ ME.countEmployee(); /* Print Toatl salary */ ME.totalSalary(); } /* Method to CREATE an employee in the database */ public Integer addEmployee(String fname, String lname, int salary){ Session session = factory.openSession(); Transaction tx = null; Integer employeeID = null; try{ tx = session.beginTransaction(); Employee employee = new Employee(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; } /* Method to READ all the employees having salary more than 2000 */ public void listEmployees( ){ Session session = factory.openSession(); Transaction tx = null; try{ tx = session.beginTransaction(); Criteria cr = session.createCriteria(Employee.class); // Add restriction. cr.add(Restrictions.gt("salary", 2000)); List employees = cr.list(); for (Iterator iterator = employees.iterator(); iterator.hasNext();){ Employee employee = (Employee) 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 print total number of records */ public void countEmployee(){ Session session = factory.openSession(); Transaction tx = null; try{ tx = session.beginTransaction(); Criteria cr = session.createCriteria(Employee.class); // To get total row count. cr.setProjection(Projections.rowCount()); List rowCount = cr.list(); System.out.println("Total Coint: " + rowCount.get(0) ); tx.commit(); }catch (HibernateException e) { if (tx!=null) tx.rollback(); e.printStackTrace(); } finally { session.close(); } } /* Method to print sum of salaries */ public void totalSalary(){ Session session = factory.openSession(); Transaction tx = null; try{ tx = session.beginTransaction(); Criteria cr = session.createCriteria(Employee.class); // To get total salary. cr.setProjection(Projections.sum("salary")); List totalSalary = cr.list(); System.out.println("Total Salary: " + totalSalary.get(0) ); tx.commit(); }catch (HibernateException e) { if (tx!=null) tx.rollback(); e.printStackTrace(); } finally { session.close(); } }} Compile and execute:
Here are the steps to compile and run the above application. Please make sure you have set PATH and CLASSPATH appropriately before compiling and executing.
The following results will be obtained and the record will be created in the EMPLOYEE table.
$java ManageEmployee
.....VARIOUS LOG MESSAGES WILL DISPLAY HERE.........First Name: Daisy Last Name: Das Salary: 5000First Name: John Last Name: Paul Salary: 5000First Name: Mohd Last Name: Yasee Salary: 3000Total Coint: 4Total Salary: 15000
If you check the EMPLOYEE table, it should be recorded as follows:
mysql> select * from EMPLOYEE;
+-----------------------------------+| id | first_name | last_name | salary |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------