Back to Hibernate Hub

Nearby lessons

14 of 19
Hibernate - TransactionsHibernate - Connection PoolingHibernate - HQLHibernate - Native SQLHibernate - Criteria API

Hibernate - HQL (Hibernate Query Language)

📌 What You Will Learn
  • Understand Hibernate - HQL (Hibernate Query Language)
  • See working code examples
  • Learn from common mistakes and Q&A

Learn Hibernate - HQL (Hibernate Query Language) step by step — simple explanations, complete programs with their output, common beginner mistakes, and exam-style MCQs.

HQL (Hibernate Query Language) — Test.java

  • <mapping resource="Employee.hbm.xml"/>
  • <mapping resource="Employee2.hbm.xml"/>
  • <mapping class="com.durgasoft.pojo.Employee1"/>
  • <mapping class="com.durgasoft.pojo.Employee2"/>
  • </session-factory>
  • </hibernate-configuration>
Example01
JCode Cell
1 
2package com.durgasoft.test;
3 
4import org.hibernate.Query;
5import org.hibernate.Session;
6import org.hibernate.SessionFactory;
7import org.hibernate.Transaction;
8import org.hibernate.boot.registry.StandardServiceRegistry;
9import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
10import org.hibernate.cfg.Configuration;
11 
12public class Test {
13 
14public static void main(String[] args)throws Exception {
15Configuration config = new Configuration();
16config.configure();
17StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder();
18builder = builder.applySettings(config.getProperties());
19StandardServiceRegistry registry = builder.build();
20SessionFactory sessionFactory = config.buildSessionFactory(registry);
21Session session = sessionFactory.openSession();
22Query query = session.createQuery("insert into Employee2(eno,ename,esal,eaddr)select e.eno,e.ename,e.esal,e.eaddr from Employee1 as e");
23Transaction tx = session.beginTransaction();
24int rowCount = query.executeUpdate();
25tx.commit();
26System.out.println("Employee details are transfered from emp2 to emp1");
27session.close();
28sessionFactory.close();
29}
30}
31

Building Blocks for HQL queries

To prepare HQL queries what are the various elements we have to provide

  • Clauses
  • Aggregate Functions
  • Generic Expressions
  • Parameters
  • Subqueries

Clauses

These are building blocks to HQL queries, which are able to specify POJO classes property names, conditional expressions,.....

EX:

from select where order by group by having ---- ----

from

It can be used to specify POJO class names and their alias names in HQL queries.

Syntax:

From POJO_Class_Name [[AS] var_Name]

EX:

From Employee FROM Employee from Employee e From Employee AS e

select

This clause can be used to specify POJO class properties names inorder to retrive individual column values. If we provide select clause with individual POJO class properties in HQL query then results are generated in the form of Object[].

Syntax:

[select prop_Names] from POJO_Class_Name [[AS] var_Name]

Example05
JCode Cell
1 
2Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e");
3List<Object[]> list = query.list();
4System.out.println("ENO\tENAME\tESAL\tEADDR");
5System.out.println("-------------------------------");
6for(Object[] obj: list) {
7for(Object o: obj) {
8System.out.print(o+"\t");
9}
10System.out.println();
11}
12

Where clause

In HQL, where clause can be used to provide a particular conditional expression inorder to retrive the results as per the condition.

Syntax:

[select prop_Names] from POJO_Class_Name [[AS] var_Name][where condition]

Example06
JCode Cell
1 
2Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.esal<=7000");
3List<Object[]> list = query.list();
4System.out.println("ENO\tENAME\tESAL\tEADDR");
5System.out.println("-------------------------------");
6for(Object[] obj: list) {
7for(Object o: obj) {
8System.out.print(o+"\t");
9}
10System.out.println();
11}
12

Order by

It can be used to retrive all results either in ascending order or in descending order w.r.t a particular column. in HQL, bydefault, all results are generated in Ascending order only.

Syntax:

[select prop_Names] from POJO_Class_Name [[AS] var_Name][where condition][order by prop_Name asc/desc]

Example07
JCode Cell
1 
2Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.esal<=10000 order by e.ename desc");
3List<Object[]> list = query.list();
4System.out.println("ENO\tENAME\tESAL\tEADDR");
5System.out.println("-------------------------------");
6for(Object[] obj: list) {
7for(Object o: obj) {
8System.out.print(o+"\t");
9}
10System.out.println();
11}
12

group by

This clause can be used to specify groups over the retrived results w.r.t a particular column. Note: In HQL queries, we are able to implement group by clause with the combination of aggregate functions only.

Syntax:

[select prop_Names] from POJO_Class_Name [[AS] var_Name][where condition][order by prop_Name asc/desc] [group by column_Name]

Example08
JCode Cell
1 
2Query query = session.createQuery("select sum(e.esal) FROM Employee AS e group by e.ename");
3List<Double> list = query.list();
4for(Double val : list) {
5System.out.println(val);
6}
7having:
8

group by

The main intention of having clause is to implement a conditional expression while including elements in groups as per group by clause.

Syntax:

[select prop_Names] from POJO_Class_Name [[AS] var_Name][where condition][order by prop_Name asc/desc] [group by column_Name][having Condition]

Example09
JCode Cell
1 
2Query query = session.createQuery("select count(e.esal) FROM Employee AS e group bye.esal having e.esal<=8000");
3List<Long> list = query.list();
4for(Long val : list) {
5System.out.println(val);
6}
7

Aggregate Functions

The main intention of Aggregate functions is to provide small arithmetic calculations over the results.

Example10
JCode Cell
1 
2count: It able to count the no of results are generated from HQl query and the resultent count value is generated in the form of java.lang.Long type.
3

Aggregate Functions

Example11
JCode Cell
1 
2Query query = session.createQuery("select count(e.esal) FROM Employee AS e");
3List<Long> list = query.list();
4for(Long val : list) {
5System.out.println(val);
6}
7

Aggregate Functions

  • sum: It able to perform addition operation over the results which are generated from HQl query.
Example12
JCode Cell
1 
2Query query = session.createQuery("select sum(e.esal) FROM Employee AS e");
3List<Double> list = query.list();
4for(Double val : list) {
5System.out.println(val);
6}
7

Aggregate Functions

  • min: It able to get min value over all the results which are generated from HQl query.
Example13
JCode Cell
1 
2Query query = session.createQuery("select min(e.esal) FROM Employee AS e");
3List<Float> list = query.list();
4for(Float val : list) {
5System.out.println(val);
6}
7

Aggregate Functions

  • max: It able to get max value over all the results which are generated from HQl query.
Example14
JCode Cell
1 
2Query query = session.createQuery("select max(e.esal) FROM Employee AS e");
3List<Float> list = query.list();
4for(Float val : list) {
5System.out.println(val);
6}
7

Aggregate Functions

  • avg: It able to generate average value over all the generated results from HQL query.
Example15
JCode Cell
1 
2Query query = session.createQuery("select avg(e.esal) FROM Employee AS e");
3List<Double> list = query.list();
4for(Double val : list) {
5System.out.println(val);
6}
7

Generic Expressions

The main intention of Generic Expressions is to provide expressions in HQL queries inorder to perform simple Arithmetic calculations, comparisions,......

To provide generic expressions in HQL queries we have to use the following elements.

a)Arithmetic Operators: +, -, *, /, %,......

Example16
JCode Cell
1 
2Query query = session.createQuery("select (e.esal-500) FROM Employee AS e");
3List<Float> list = query.list();
4for(Float val : list) {
5System.out.println(val);
6}
7

Generic Expressions

b)Comparision Operators: ==, !=, <, >, <=, >= ,....

c)Logical Operators: && or AND, || or OR ,....

Example17
JCode Cell
1 
2Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.esal>=6000 AND e.esal<=8000");
3List<Object[]> list = query.list();
4System.out.println("ENO\tENAME\tESAL\tEADDR");
5System.out.println("---------------------------------");
6for(Object[] val : list) {
7for(Object o: val) {
8System.out.print(o+"\t");
9}
10System.out.println();
11}
12

d)Scalar Functions

lower(): To get results in lower case letters. upper(): To get results in Upper case letters.

Example18
JCode Cell
1 
2Query query = session.createQuery("select e.eno, lower(e.ename), e.esal, upper(e.eaddr) FROM Employee AS e");
3
📝 Key Takeaways
  • Key ideas of Hibernate - HQL (Hibernate Query Language) explained simply
  • Ready-to-use code examples
  • Exam-style questions at the end