10 - Hibernate Query Language (HQL)

10.1 Overview

As Hibernate is all about persisting java objects, Hibernate Query Language is a similar to SQL but in terms of Object. We may want queries with where clauses, conditions , aggregate functions etc in our application and HQL does support all these features. One of the important different between SQL and HQL is , class names are used in place of tables and object properties names are used in place of table column names. In this chapter we will discuss HQL in detail. Hibernate also supports native SQL which we will discuss later.

HQL are case insensitive.Using HQL keeps our application code database independent.

10.2 Query Object

There are several methods available in Query API ( refer Chapter 6 [ section 6.4] ) for more details on Query API.

We can create a query object, using session object. Session Object does provide a createQuery() method which takes a query as an argument and returns a Query object.

Refer below code snippet for reference

Query query = session.createQuery(“ from Object ”);

10.3 from Clause

From is the only mandatory part of HQL.. It tells hibernate from where to pull the data. For example if we have a BOOK-INFO table corresponding to Book object  and we want to fire a query  on Book object .

Query query = session.createQuery(“ from Book ”);

10.4 Aliases

We can assign an aliases to the objects in HQL by just adding an alias next to object OR using “as” keyword.

To assign an alias for Book object we can use any one of below syntax

Query query = session.createQuery(“ from Book  as bookAlias ”);

Query query = session.createQuery(“ from Book  bookAlias ”);

10.5 select Clause

To get all the columns of table we can simple use “from Object” . There is no need of select clause but if we want to get selected properties  only we need to use select clause providing the list  of comma separated name of the object properties .

If select statement has multiple properties to fetch then  result will be an List of  Object array (List<Object[] >. If select has one property then result will be list of property data type

Assuming name and author are the two properties of Book object then we can use below queries.

Query query = session.createQuery(“select name from Book ”);

Query query = session.createQuery(“ select alias.name from Book  as alias ”);

To get a list of an object  using select statement , we can use constructor . For example

Query query = session.createQuery(“ select new Book(name, price)  from Book  ”);

List<Book> books = query.list();

10.6 where Clause

We can get the filtered objects using where clause.

Assuming isbn , name and author are the properties of Book object then we can use below queries.

Query query = session.createQuery(“select name from Book where isbn=’123’ ”);

10.7 Supported Operators

a)Logical Operators - AND , OR , NOT  

Query query = session.createQuery(“ select name from Book  where isbn=’123’ and author=’xyz’ ”);

b) Equality Operators -   =, !=

Query query = session.createQuery(“ select name from Book  where isbn=’123’ and author !=’xyz’ ”);

 c) Comparison Operators - <  , >  ,<= ,>=, LIKE, NOT LIKE , BETWEEN , NOT BETWEEN ,  IS NULL , IS NOT NULL  , IN

Query query = session.createQuery(“ select name from Book  where isbn=’123’ and author like ’%xyz’ ”);

Query query = session.createQuery(“ select name from Book  where author in ( ‘xyz’ , ‘abc’ ”);

10.8 Pagination

HQL supports pagination where we can specify the starting record number and number of records to fetch. To do so we  can use setFirstResult and setMaxResults methods.

Below query will fetch 15 records starting from 5th record.

Query query = session.createQuery(“select name from Book  ”);



10.9 Parameters Binding

Instead of hard coding the variables values , we can bind the values at run time. We can bind the parameters using

  1. Name – also known as named parameter binding
  2. Position  - also known as positional parameter binding.

10.9.1 Named Parameter Binding 

- Using named parameter binding , query becomes more readable and same parameter can be reused multiple times. To use named parameter we need to use “ :name ” syntax

Query query = session.createQuery(“select name from Book where name = : bookName ”);

query.setString(“bookName”, “XYZ”);

10.9.2 Positional Parameter Binding.

 – Similar to traditional JDBC Prepared Statement style , we can use ? as a place holder ( 0 based) 

Query query = session.createQuery(“select name from Book where name = ? ”);

query.setString(0, “XYZ”);

10.10 Functions

We can use several functions of SQL in HQL as well. Functions like avg() , sum(), count() , max() , min() , distinct are supported.

String hql = “ select sum(book.price) from Book as book”;

Query query = session.createQuery(hql);

10.11 CRUD Operations

HQL supports INSERT, DELETE, UPDATE ,SELECT clauses.  For INSERT we cannot  provide arbitrary values , instead we can only use select which means select the record from database and insert it. To insert a new record , we can simply use save() method

10.12 Sorting

We can use ORDER BY clause to sort the results

Query query = session.createQuery(“select name from Book order by name desc ”);

10.13 Example

Lets create one example of Product to use HQL .

a. Product.java

package com.tutorial.hibernate;
import java.util.Date;

public class Product {

     private int productId;
     private String productName;
     private double price;
     private String productCategory;
     private Date expiryDate;
     private String batchNumber;

public Product()

public Product(double price, String productCategory, String batchNumber) {
        this.price = price;
        this.productCategory = productCategory;
        this.batchNumber = batchNumber;

    public int getProductId() {
        return productId;

    public void setProductId(int productId) {
        this.productId = productId;

    public String getProductName() {
        return productName;

    public void setProductName(String productName) {
        this.productName = productName;

    public double getPrice() {
        return price;

    public void setPrice(double price) {
        this.price = price;

    public String getProductCategory() {
        return productCategory;
    public void setProductCategory(String productCategory) {
        this.productCategory = productCategory;
    public Date getExpiryDate() {
        return expiryDate;
    public void setExpiryDate(Date expiryDate) {
        this.expiryDate = expiryDate;
    public String getBatchNumber() {
        return batchNumber;
    public void setBatchNumber(String batchNumber) {
        this.batchNumber = batchNumber;
    public String toString() {
        return "Product [productId=" + productId + ", productName="
+ productName + ", price=" + price + ", productCategory="
                + productCategory + ", expiryDate=" + expiryDate
                + ", batchNumber=" + batchNumber + "]";

b. product.hbm.xml

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC 
 "-//Hibernate/Hibernate Mapping DTD//EN"

   <class name="com.tutorial.hibernate.Product" table="PRODUCT">
      <id name="productId" type="int" column="product_id">
         <generator class="native"/>
      <property name="productName" column="name" type="string"/>
      <property name="batchNumber" column="batch" type="string" /> 
      <property name="productCategory" column="category" type="string" /> 
      <property name="price" column="price" type="double" />
      <property name="expiryDate" column="expiry_date" type="date" />


c. hibernate.cfg.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD//EN"


       <property name="hibernate.connection.url">
        <property name="hibernate.connection.username">
        <property name="hibernate.connection.password">


<property name="hibernate.format_sql">true</property>    
    <property name="show_sql">true</property>
<property name="hibernate.connection.driver_class">

       <mapping resource="product.hbm.xml" />

d. create product table 

CREATE TABLE `product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `batch` varchar(255) DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `price` double DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  PRIMARY KEY (`product_id`)


import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

import com.tutorial.hibernate.Product;

public class HQLExample {

    private static SessionFactory sessionFactory;
    public static void main(String a[])

        //save some products in database         
        Product product1= getProductObject("Product A", "SOAP", "AB-23-F",23.4,new Date() );        
        Product product2= getProductObject("Product B", "TOOTH PASTE", "XXAB-23-F",13.4,new Date() );        
        Session session = sessionFactory.openSession();        
        Transaction tx = session.beginTransaction();        

        getProductByName("Product B");        

        updateProduct(2,"Product C");

    private static void  getSessionFactory()
        Configuration cfg = new Configuration().configure();        
        sessionFactory = cfg.buildSessionFactory();        
    private static void updateProduct(int productId, String name )
        Session session = sessionFactory.openSession();        
        Transaction tx = session.beginTransaction();
        String queryString = "update Product set productName = ? where productId =?  " ;
        Query query = session.createQuery(queryString);

    private static void deleteProduct(int productId)
        Session session = sessionFactory.openSession();        
        Transaction tx = session.beginTransaction();
        String queryString = "delete from Product where productId =?  " ;

        Query query = session.createQuery(queryString);

    private static void getAllProducts()

      Session session = sessionFactory.openSession();        
      String queryString = "from Product " ;        
      Query query = session.createQuery(queryString);        
      List <Product> products = query.list();    

      for(int i=0;i<products.size();i++)

    private static void getProductByName(String name )
        Session session = sessionFactory.openSession();    

        String queryString = "from Product where productName = :name" ;        
        Query query = session.createQuery(queryString);        
        query.setString("name", name);        
        Product product = (Product)query.uniqueResult();        

    private static void getSpecificDetailsOfProduct()

        Session session = sessionFactory.openSession();        
        // return list of objects (2) and each will be an array of 3
        String queryString = "select productCategory, price, batchNumber from Product " ;        
        Query query = session.createQuery(queryString);        
        List<Object[]> product  = query.list();

        for(int i=0;i<product.size();i++)
            Object[] data = product.get(i);            
            System.out.println(data[0] + "," + data[1] + "," + data[2]);            

        // Another way

        queryString = "select new Product( price, productCategory,batchNumber ) from Product " ;        
        query = session.createQuery(queryString);        
        List<Product> products  = query.list();        
        for(int i=0;i<products.size();i++)

    private static Product getProductObject(String name , String category, String batch, double price,
            Date expiry)
        Product product = new Product();


        return product;



Like us on Facebook