Hibernate Query Language (HQL) Tutorial

In this post i will explain in details about HQL(Hibernate Query Language),HQL is an Object Oriented Query Language, It is moreover similar to the Structure Query Language, But instead performing operations on Database table and columns we perform operations on persistence object and their properties created using ORM tools like Hibernate. HQL has its own language,grammar and syntax. Its is written as more understandable strings like "From Student s".After submitting HQL queries, Hibernate convert these queries into underlying database queries according to the dialect we specified in Configuration file.

Benefits of the HQL:

- Independant of the Underlying Database
- Easy to learn in Object oriented ways
- Relational mapping to Objects done by Hibernate Engine
- HQL is good in writing static as well as dynamic complex queries
- Beneficial over HCQL(Hibernate Criteria Query Language), Because
   less Objects get created.

HQL "Query" Interface

It is an Obeject Oriented representation of Hibenate Query. A Query Instance
is obtained by using current session like,
Query query = Session.createQuery();

Query interface provides several method, some of those methods are :

int	executeUpdate()	Executes the update or delete statements
String[]	getNamedParameters()	Return the names of all the parameters of the Query
List<?>	list()	returns the list of all objects fetched by HQl query
Iterator	iterate()	returns the Query results an an iterator
void	setInteger(int position, int val)	set the value "val" to the parameter at "position"
void	setString(int position, String val)	set the value "val" to the parameter at "position"

JDBC style Parameters are also supported, Query is executed by calling one of the following method

List<?>	list()
ScrollableResult	scroll()
Iterate<?>	Iterate()

There are plenty more methods you can view @ "https://docs.jboss.org/hibernate/orm/3.2/api/org/hibernate/Query.html"

Lets check HQL crud operations one by one,

HQL Insert Operation

Lets check HQL crud operations one by one,

we have to use it with in association with SELECT query. Information obtained from SELECT statement then can be inserted
using insert query.

Syntax :

INSERT
    INTO (properties,[,....])
    SELECT (properties,[,....]) FROM 
    WHERE 
          

Where is fully qualified name of the Persistence entity of which we want to insert values
properties are the attributes of the path/Entity.

Make sure types of properties in INSERT & SELECT statements must match.

Example 1:

Query query = Session.createQuery("INSERT INTO Student" + "SELECT * From Candidate");
int Results = query.executeUpdate();

Example 2:

Query query = Session.createQuery("INSERT INTO Student(Roll_No,Name,Age)" + "SELECT id,cname,age From Candidate WHERE city=:city_name");
query.setString("city_name","AMRAVATI");
int Results = query.executeUpdate();

we have used here Named Parameters to set their values Explicitly and dynamically.(city =:city_name).

HQL Update Operation

An HQL Update Query is used to update the information of pre-existing object in the database table.

Syntax :

UPDATE
[FROM] [[AS] ALIAS] [,...]
SET Property = VALUE [,....]
WHERE 

PATH is fully qualified name of persistence Entity where we want to update values
properties are the attributes of the PATH to be updated.

Example :

Query query = Session.createQuery("UPDATE STUDENT SET age =: s_age WHERE name =: s_name");
query.setInteger("s_age",26);
query.setString("s_name","Sammujwal");
int Results = query.executeUpdate();

HQL Delete Operation

An HQL DELETE query will remove the pre-existing data specified in query from database.

Syntax :

DELETE
[FROM]  [[AS] ALIAS]
[WHERE ]

PATH is fully qualified name of persistance Entity of which we want to delete values

Example :

Query query = Session.createQuery("DELETE FROM STUDENT AS S WHERE S.CITY = 'AMRAVATI'");

HQL FROM Clause & Aliases

Hibernate allows us to create Aliases by using 'AS' keyword which is optional, by which we can refer
the Entity name with different alises.

Syntax :

"FROM Student as s"
       OR
"FROM Student s"

can also be used with fully qualified name of the Entity as,

FROM com.ron.Student as s

can also be used with SELECT clause for projection is,

SELECT S.name, S.age FROM Student as S

HQL SELECT Operation

An SELECT query is used to query the database for Entities and their properties.

Syntax :

[SELECT [DISTINCT]] [PROPERTIES,...]
FROM  [[AS] ALIAS ][,....] [FETCH ALL PROPERTIES]
WHERE  
GROUP BY [PROPERTIES,....]
HAVING 
ORDER BY PROPERTY [ASC | DESC]

Here 'FETCH ALL PROPERTIES' will work as its meaning implies,
the rest is similar to it's SQL counterpart.

Pagination Using HQL

HQL provides two important methods which could be used in the pagination,

- setFirstResult(Integer n)
It takes Integer as a parameter, which represents first 'n' results from database starting with 0

- setMaxResults(Integer n)
It takes Integer as parameter, which repersents maximum 'n' results to fetched from the database

Example :

Query query = Session.createQuery("From com.ron.model.Student");
query.setFirstResult(5);
query.setMaxResults(15);
List s_list = query.list();
displayProductList(results);

Obtaining Unique Result in HQL

HQL provides facility to get only single result from the database by uniqueResult() method of Session.

Example :

Query query = Session.createQuery("FROM Student WHERE NAME:='ROHAN'");
query.setMaxResults(1);
Student stud = (Student) query.uniqueResult();

In above example stud object is going get only one result from the query.

Sorting Multiple result using HQL

Order By clause helps us to sort the results either in asc(Ascending) or in desc(Descending) order, in general
all records will be in the order they are retrived from the database,By using order by clause we can sort them.

Example :

Query query = Session.createQuery("FROM Student AS s ORDER BY S.ROLL_NO ASC");

Joins in HQL

If you want to get Results that depends on more than one Entities then you should go for HQL Joins, Similar to
SQL, HQL also allows to get result by associating more than on Entities. Hibernate supports five Different types
of Joins.

- Inner Join, Cross Join, left Outer Join, Right Outer Join, Full Outer Join(BOTH)

there uses are similar to there use in SQL.

Example :

Query query = Session.createQuery("FROM Student as s LEFT JOIN s.Roll_No as sroll WITH Candidate as c");

Aggregate in HQL

HQL Supports several aggregate methods as SQL, also they work in the same way as in SQL,
Some of them are,
- avg(property) : Calculates average value of given property
- min(property) : Minimum Value from the given property
- max(property) : Maximum value from the given property
- count(property) : Count total number of property instances available
- sum(property) : returns sum of property of all records

Example :

SELECT COUNT(*) FROM STUDENT S;