Hibernate Query Language – HQL + Java 8 + MySQL

I. Technologies

– Java 1.8
– Maven 3.3.9
– Spring Tool Suite – Version 3.8.1.RELEASE
– Hibernate: 5.2.10.Final
– MySql database

II. Hibernate Query Language

HQL is an Object-Oriented Query language, simillar to database SQL. But instead of working with tables and columns, HQL uses Java Objects to manipulate queries.

How it work?
-> Hibernate converts HQL queries into appropriate SQL queries before processing on database.

Why uses HQL?
– Database Independent
– Take advantage of Hibernate caching.

HQL supports:
– FROM Clause
– AS Clause
– SELECT Clause
– WHERE Clause
– ORDER BY Clause
– GROUP BY Clause
– Using Named Paramters
– UPDATE Clause
– DELETE Clause
– INSERT Clause
– Aggregate Methods
– Pagination using Query

In the tutorial, we will practice with FROM, AS, SELECT, WHERE, ORDER BY, GROUP BY, Using Named Parameters, UPDATE Clause, DELETE.

1. FROM Clause

FROM clause is used to load all objects into memory.
Example:

String hql = "FROM Customer";
Query query = session.createQuery(hql, Customer.class);
List custList = query.list();

2. AS Clause

AS clause is an optional keyword, used to create an aliases for a classes in HQL queries.

String hql = "FROM Customer AS C";
// Above statement is the same with statement: String hql = "FROM Customer C";
Query query = session.createQuery(hql, Customer.class);
List custList = query.list();

3. SELECT Clause

SELECT clause is used to obtain few properties of returned objects.

String hql = "SELECT C.firstName, C.age FROM Customer C";
Query query = session.createQuery(hql);
List objectList= query.list();

4. WHERE Clause

WHERE clause is used to narrow selected objects which must meet some conditions.

String hql = "FROM Customer C WHERE C.age = 20";
Query query = session.createQuery(hql, Customer.class);
List custList = query.list();

5. ORDER BY Clause

ORDER BY clause is used to sort HQL query’s results.

String hql = "FROM Customer C WHERE C.age > 25 ORDER BY C.id DESC";
Query query = session.createQuery(hql, Customer.class);
List custList = query.list();

6. GROUP BY Clause

GROUP BY clause is used with aggregate functions.

String hql = "SELECT COUNT(*), C.firstName FROM Customer C " +
         "GROUP BY C.firstName";
Query query = session.createQuery(hql);
List objLst = query.list();

7. Named Parameters

Named Parameters is used to get inputs from users and helpful to defend against SQL injection attacks.

String hql = "FROM Customer C WHERE C.id = :customerId";
Query query = session.createQuery(hql, Customer.class);
query.setParameter("customerId",1);

List custList = query.list();

8. UPDATE Clause

UPDATE clause is used to modify properties of objects.

String hql = "UPDATE Customer set age = :custAge WHERE id = :customerId";
Query query = session.createQuery(hql);
query.setParameter("custAge",26);
query.setParameter("customerId",3);

int affectedRows = query.executeUpdate();

9. DELETE Clause

DELETE clause is used to delete objects.

String hql = "DELETE FROM Customer WHERE id = :customerId";
Query query = session.createQuery(hql);
query.setParameter("customerId", 2);

int affectedRows = query.executeUpdate();

III. Practice

Step to do:
– Create Spring Maven project
– Create persistent class
– Setup Hibernate configuration
– Setup MySql Database
– Implement HQL Application
– Run & Check Results

1. Create Spring Maven project

Using Spring Tool Suite, create a Simple Spring Maven project.
Modified pom.xml file with Hibernate & MySql dependencies:

  4.0.0
  org.springframework.samples
  HQLSample
  0.0.1-SNAPSHOT
  
	
		
		
			org.hibernate
			hibernate-core
			5.2.10.Final
		
		
		
		
			mysql
			mysql-connector-java
			6.0.6
		
	

2. Create persistent class

Create a Customer.java:

package com.javasampleapproach.hibernate.hql.model;

public class Customer {
	private int id;
	private String firstName;
	private String lastName;
	private int age;
	
	public Customer(){};
	
	public Customer(int id, String firstName, String lastName, int age){
		this.id = id;
		this.firstName = firstName;
		this.lastName = lastName;
		this.age = age;
	}
	
	public void setId(int id){
		this.id = id;
	}
	
	public int getId(){
		return this.id;
	}
	
	public void setFirstName(String firstName){
		this.firstName = firstName;
	}
	
	public String getFirstName(){
		return this.firstName;
	}
	
	public void setLastName(String lastName){
		this.lastName = lastName;
	}
	
	public String getLastName(){
		return this.lastName;
	}
	
	public void setAge(int age){
		this.age = age;
	}
	
	public int getAge(){
		return this.age;
	}
	
	public String toString(){
		String info = String.format("Customer info: id = %d, firstname = %s, lastname = %s, age = %d", 
														id, firstName, lastName, age);
		return info;
	}
	
}

3. Setup Hibernate configuration

Under /src/main/resources, create Hibernate Configuration file – hibernate.cfg.xml:

    
    	jdbc:mysql://localhost:3306/testdb?useSSL=false
        com.mysql.jdbc.Driver
        root
        12345
        org.hibernate.dialect.MySQLDialect
        true
        
    

– Create a mapping file for Customer class – Customer.hbm.xml:

 


   
      
         Mapping between persistent class - Customer with MySQL table - customer 
      
      
         
      
      
      
      
   

4. Setup MySql Database

Create a customer table and insert data to it with below SQL scripts:

CREATE TABLE customer(
   id INT NOT NULL AUTO_INCREMENT,
   first_name VARCHAR(20) NOT NULL,
   last_name VARCHAR(20) NOT NULL,
   age INT NOT NULL,
   PRIMARY KEY (id)
);

INSERT INTO testdb.customer (first_name, last_name, age) VALUES ("Peter", "Smith", 20);
INSERT INTO testdb.customer (first_name, last_name, age) VALUES ("Mary", "Taylor", 25);
INSERT INTO testdb.customer (first_name, last_name, age) VALUES ("Lauren", "Taylor", 30);
INSERT INTO testdb.customer (first_name, last_name, age) VALUES ("Peter", "Johnson ", 22);
INSERT INTO testdb.customer (first_name, last_name, age) VALUES ("Mary", "Harris  ", 27);

5. Implement HQL Application

Implement 11 functions:
– setUpSessionFactory(): setup Hibernate Session Factory
– fromClause()
– asClause()
– selectClause()
– whereClause()
– orderByClause()
– groupByClause()
– namedParameters()
– updateClause()
– deleteClause()
– shutdown(): close Hiberbate Session Factory Object

Details:

package com.javasampleapproach.hibernate.hql;

import java.util.List;

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

import com.javasampleapproach.hibernate.hql.model.Customer;


public class Application {
	private static SessionFactory factory = null;
	
	public static void main(String[] args) {
		
		System.out.println("============Set up Session Factory============");
			setUpSessionFactory();
			// Show all customer tables
			
			System.out.println("==============FROM CLAUSE: hql = 'FROM Customer'==============");
			fromClause();
			
			System.out.println("==============AS CLAUSE: hql = FROM Customer AS C==============");
			asClause();
			
			System.out.println("==============SELECT CLAUSE: hql = SELECT C.firstName, C.age FROM Customer C==============");
			selectClause();
			
			System.out.println("==============WHERE CLAUSE: hql = FROM Customer C WHERE C.age = 20==============");
			whereClause();
			
			System.out.println("==============ORDER BY CLAUSE: hql = FROM Customer C WHERE C.age > 25 ORDER BY C.id DESC==============");
			orderByClause();
			
			System.out.println("==============GROUP BY CLAUSE: hql = SELECT COUNT(*), C.firtName FROM Customer C ==============");
			groupByClause();
			
			System.out.println("==============NAMED PARAMETERS CLAUSE: hql = FROM Customer C WHERE C.id = 1==============");
			namedParameters();
			
			System.out.println("==============UPDATE CLAUSE: UPDATE Customer set age = :custAge WHERE id = :customerId==============");
			updateClause();
			
			System.out.println("==============DELETE CLAUSE: hql = DELETE FROM Customer WHERE id = 2==============");
			deleteClause();
			
			shutdown();
	}
	
	/**
	 * Set SessionFactory
	 */
	public static void setUpSessionFactory() {
		// create sessionFactory
		try {
			factory = new Configuration().configure().buildSessionFactory();
		} catch (Throwable ex) {
			System.err.println("Failed to create sessionFactory object." + ex);
			throw new ExceptionInInitializerError(ex);
		}
	}
	
	/**
	 * Shutdown
	 */
	public static void shutdown(){
		factory.close();
	}
	
	/**
	 * From Clause
	 */
	public static void fromClause(){
		Session session = factory.openSession();
		Transaction tx = null;
		try{
			tx = session.beginTransaction();
			
			String hql = "FROM Customer";
			Query query = session.createQuery(hql, Customer.class);
			List custList = query.list();
			
			custList.forEach(System.out::println);
			
			tx.commit();
		}catch(Exception e){
			if(null != tx){
				tx.rollback();
			}
		}finally{
			session.close();
		}
	}
	
	/**
	 * As Clause
	 */
	public static void asClause(){
		Session session = factory.openSession();
		Transaction tx = null;
		try{
			tx = session.beginTransaction();
			
			String hql = "FROM Customer AS C";
			// Above statement is the same with statement: String hql = "FROM Customer C";
			Query query = session.createQuery(hql, Customer.class);
			List custList = query.list();
			
			custList.forEach(System.out::println);
			
			tx.commit();
		}catch(Exception e){
			if(null != tx){
				tx.rollback();
			}
		}finally{
			session.close();
		}
	}
	
	/**
	 * Select Clause
	 */
	public static void selectClause(){
		Session session = factory.openSession();
		Transaction tx = null;
		try{
			tx = session.beginTransaction();
			
			String hql = "SELECT C.firstName, C.age FROM Customer C";
			Query query = session.createQuery(hql);
			List objectList= query.list();
			objectList.forEach(obj->{
				String info = String.format("Customer's Info has firstname = %s, age = %d", (String)obj[0], (Integer)obj[1]);
				System.out.println(info);
			});
			
			tx.commit();
		}catch(Exception e){
			if(null != tx){
				tx.rollback();
			}
		}finally{
			session.close();
		}
	}
	
	/**
	 * 
	 * WHERE Clause 
	 */
	
	public static void whereClause(){
		Session session = factory.openSession();
		Transaction tx = null;
		try{
			tx = session.beginTransaction();
			
			String hql = "FROM Customer C WHERE C.age = 20";
			Query query = session.createQuery(hql, Customer.class);
			List custList = query.list();

			custList.forEach(System.out::println);
			
			tx.commit();
		}catch(Exception e){
			if(null != tx){
				tx.rollback();
			}
		}finally{
			session.close();
		}
	}
	
	/**
	 * 
	 * OrderBy Clause
	 */
	public static void orderByClause(){
		Session session = factory.openSession();
		Transaction tx = null;
		try{
			tx = session.beginTransaction();
			
			String hql = "FROM Customer C WHERE C.age > 25 ORDER BY C.id DESC";
			Query query = session.createQuery(hql, Customer.class);
			List custList = query.list();
			
			custList.forEach(System.out::println);
			
			tx.commit();
		}catch(Exception e){
			if(null != tx){
				tx.rollback();
			}
		}finally{
			session.close();
		}
	}
	
	/**
	 * 
	 * Group By Clause
	 */
	public static void groupByClause(){
		Session session = factory.openSession();
		Transaction tx = null;
		try{
			tx = session.beginTransaction();
			
			String hql = "SELECT COUNT(*), C.firstName FROM Customer C " +
			         "GROUP BY C.firstName";
			Query query = session.createQuery(hql);
			List objLst = query.list();
			objLst.forEach(obj->{
				String info = String.format("Having %d Customers has name is %s", (Long)obj[0], (String)obj[1]);
				System.out.println(info);
			});
			
			tx.commit();
		}catch(Exception e){
			if(null != tx){
				tx.rollback();
			}
		}finally{
			session.close();
		}
	}
	
	/**
	 * 
	 * Using Named Parameter
	 */
	public static void namedParameters(){
		Session session = factory.openSession();
		Transaction tx = null;
		try{
			tx = session.beginTransaction();
			
			String hql = "FROM Customer C WHERE C.id = :customerId";
			Query query = session.createQuery(hql, Customer.class);
			query.setParameter("customerId",1);
			
			List custList = query.list();
			
			custList.forEach(System.out::println);
			
			tx.commit();
		}catch(Exception e){
			if(null != tx){
				tx.rollback();
			}
		}finally{
			session.close();
		}
	}
	
	/**
	 * 
	 * Update Clause
	 */
	public static void updateClause(){
		Session session = factory.openSession();
		Transaction tx = null;
		try{
			tx = session.beginTransaction();
			
			String hql = "UPDATE Customer set age = :custAge WHERE id = :customerId";
			Query query = session.createQuery(hql);
			query.setParameter("custAge",26);
			query.setParameter("customerId",3);
			
			int affectedRows = query.executeUpdate();
			System.out.println("Rows affected: " + affectedRows);
			
			tx.commit();
		}catch(Exception e){
			if(null != tx){
				tx.rollback();
			}
		}finally{
			session.close();
		}
	}
	
	/**
	 * Delete Clause
	 */
	public static void deleteClause(){
		Session session = factory.openSession();
		Transaction tx = null;
		try{
			tx = session.beginTransaction();
			
			String hql = "DELETE FROM Customer WHERE id = :customerId";
			Query query = session.createQuery(hql);
			query.setParameter("customerId", 2);
			
			int affectedRows = query.executeUpdate();
			System.out.println("Rows affected: " + affectedRows);
			
			tx.commit();
		}catch(Exception e){
			if(null != tx){
				tx.rollback();
			}
		}finally{
			session.close();
		}
	}
}

6. Run & Check Results

– Initial customer table:

– Run the main class Application.java, Logs:

==============FROM CLAUSE: hql = 'FROM Customer'==============
May 13, 2017 2:34:00 PM org.hibernate.hql.internal.QueryTranslatorFactoryInitiator initiateService
INFO: HHH000397: Using ASTQueryTranslatorFactory
Hibernate: select customer0_.id as id1_0_, customer0_.first_name as first_na2_0_, customer0_.last_name as last_nam3_0_, customer0_.age as age4_0_ from customer customer0_
Customer info: id = 1, firstname = Peter, lastname = Smith, age = 20
Customer info: id = 2, firstname = Mary, lastname = Taylor, age = 25
Customer info: id = 3, firstname = Lauren, lastname = Taylor, age = 30
Customer info: id = 4, firstname = Peter, lastname = Johnson , age = 22
Customer info: id = 5, firstname = Mary, lastname = Harris  , age = 27
==============AS CLAUSE: hql = FROM Customer AS C==============
Hibernate: select customer0_.id as id1_0_, customer0_.first_name as first_na2_0_, customer0_.last_name as last_nam3_0_, customer0_.age as age4_0_ from customer customer0_
Customer info: id = 1, firstname = Peter, lastname = Smith, age = 20
Customer info: id = 2, firstname = Mary, lastname = Taylor, age = 25
Customer info: id = 3, firstname = Lauren, lastname = Taylor, age = 30
Customer info: id = 4, firstname = Peter, lastname = Johnson , age = 22
Customer info: id = 5, firstname = Mary, lastname = Harris  , age = 27
==============SELECT CLAUSE: hql = SELECT C.firstName, C.age FROM Customer C==============
Hibernate: select customer0_.first_name as col_0_0_, customer0_.age as col_1_0_ from customer customer0_
Customer's Info has firstname = Peter, age = 20
Customer's Info has firstname = Mary, age = 25
Customer's Info has firstname = Lauren, age = 30
Customer's Info has firstname = Peter, age = 22
Customer's Info has firstname = Mary, age = 27
==============WHERE CLAUSE: hql = FROM Customer C WHERE C.age = 20==============
Hibernate: select customer0_.id as id1_0_, customer0_.first_name as first_na2_0_, customer0_.last_name as last_nam3_0_, customer0_.age as age4_0_ from customer customer0_ where customer0_.age=20
Customer info: id = 1, firstname = Peter, lastname = Smith, age = 20
==============ORDER BY CLAUSE: hql = FROM Customer C WHERE C.age > 25 ORDER BY C.id DESC==============
Hibernate: select customer0_.id as id1_0_, customer0_.first_name as first_na2_0_, customer0_.last_name as last_nam3_0_, customer0_.age as age4_0_ from customer customer0_ where customer0_.age>25 order by customer0_.id DESC
Customer info: id = 5, firstname = Mary, lastname = Harris  , age = 27
Customer info: id = 3, firstname = Lauren, lastname = Taylor, age = 30
==============GROUP BY CLAUSE: hql = SELECT COUNT(*), C.firtName FROM Customer C ==============
Hibernate: select count(*) as col_0_0_, customer0_.first_name as col_1_0_ from customer customer0_ group by customer0_.first_name
Having 1 Customers has name is Lauren
Having 2 Customers has name is Mary
Having 2 Customers has name is Peter
==============NAMED PARAMETERS CLAUSE: hql = FROM Customer C WHERE C.id = 1==============
Hibernate: select customer0_.id as id1_0_, customer0_.first_name as first_na2_0_, customer0_.last_name as last_nam3_0_, customer0_.age as age4_0_ from customer customer0_ where customer0_.id=?
Customer info: id = 1, firstname = Peter, lastname = Smith, age = 20
==============UPDATE CLAUSE: UPDATE Customer set age = :custAge WHERE id = :customerId==============
Hibernate: update customer set age=? where id=?
Rows affected: 1
==============DELETE CLAUSE: hql = DELETE FROM Customer WHERE id = 2==============
Hibernate: delete from customer where id=?
Rows affected: 1

– Database records after executing:

IV. Sourcecode

HibernateQueryLanguage