Updated April 5, 2023
What is Hibernate Query Language?
Hibernate Query Language is an object-oriented query language that resembles Structured Query Language (SQL) but unlike SQL, it works on persistent objects instead of tables. HQL also allows you to write the SQL queries with the help of native SQL. However, we should mostly try to write HQL queries as it has many advantages. Database independence makes database portability simpler and efficient. Other than this it is very easy to learn for Java developers and supports polymorphic queries and supports caching at various levels.
HQL internally converts the query into SQL which then, in turn, performs operations on the table contents. Hence, it is called the mediator or ORM i.e Object-Relational Mapping tool which acts as the bridge between objects and tables in the relational database. HQL is case insensitive when it comes to its keywords or clauses but is case sensitive when mentioning object names or persistent entities of tables.
Method of Session Interface
Query objects can be obtained by using the createQuery() method of the session interface. The Query interface of hibernating provides us with many methods like follows:
- public int executeUpdate(): We can perform manipulations like updation and deletion.
- public List list(): It is used while retrieving the result from a particular query.
- public Query setFirstResult(int rowno): It helps in specifying starting from which row number we want to retrieve the records.
- public Query setMaxResult(int recordCount): It helps in specifying the number of the rows we want to retrieve.
- public Query setParameter(int position, Object value): It helps in specifying the value of the JDBC styled parameter of the query.
- public Query setParameter(String name, Object value): It helps in specifying the value of the named parameter of the query.
FROM Clause
When you want to retrieve the whole object into the memory, we can use the FROM clause mentioning the name of the object which you want to retrieve. For example, there is one table named EDUCBA_DATA which stores NameOfTechnology, Duration, Sessions, ToolType. We have prepared equivalent persistent object i.e entity of the table names educbaData containing properties nameOfTechnology, duration, sessions, and toolType. We can retrieve all data of table EDUCBA_DATA using HQL query and educbaData object in the following way.
Code:
String hqlString = "From educbaData";
Query hqlQuery = session.createQuery(hqlString);
List resultSet = hqlQuery.list();AS Clause
It is used to specify the alternative name or alias to the entity name. For example, in the above example, we can give an alias to our entity educbaData as ed in the following way.
Code:
String hqlString = "FROM educbaData AS ed";
//OR can also be used without AS "FROM educbaData ed";
Query hqlQuery = session.createQuery(hqlString);
List resultSet = hqlQuery.list();SELECT Clause
When we want to retrieve only specific properties of entities or column values of the table then we use the SELECT clause to specify so. Suppose, we only want to get the NameOfTechnology and ToolType from table EDUCBA_DATA then we can write HQL query for the same in the following way.
Code:
String hqlString = "SELECT ed.nameOfTechnology,ed.toolType FROM educbaData ed";
Query hqlQuery = session.createQuery(hqlString);
List resultSet = hqlQuery.list();WHERE Clause
When we have to get data satisfying some specific conditions we can narrow up the result set using WHERE clause which helps us specifying the condition clauses that particular properties should satisfy. Suppose we want to get only those technology names who have language in the column ToolType column using the HQL query. Here is how we do it.
Code:
String hqlString = "SELECT ed.nameOfTechnology FROM educbaData ed WHERE ed.toolType='Language'";
Query hqlQuery = session.createQuery(hqlString);
List resultSet = hqlQuery.list();ORDER BY Clause
If we want to get the sorted data we can specify the column name and ascending or descending order. We can also sort based on more than one column in the HQL query by specifying the comma-separated property names. For example, arranging the records in ascending order based on the name of technology can be done in the following way.
Code:
String hqlString = "FROM educbaData ed ORDER BY ed.nameOfTechnology";
Query hqlQuery = session.createQuery(hqlString);
List resultSet = hqlQuery.list();By default, sorting is in ascending order. Now suppose if we want the data to be sorted based on the number of sessions in descending order and then on the name of technology in ascending order which can be done in the following way.
Code:
String hqlString = "FROM educbaData ed ORDER BY ed.sessions DESC,ed.nameOfTechnology";
Query hqlQuery = session.createQuery(hqlString);
List resultSet = hqlQuery.list();GROUP BY Clause
When we want to group the result based on some of the column basis then we can use the GROUP BY clause. It is most often used along with the aggregate functions to obtain summarized grouped values of certain columns/properties. Suppose you want to get the total number of sessions grouped based on the ToolType field in HQL. We can do so in the following manner.
Code:
String hqlString = "SELECT ed.toolType, SUM(ed.sessions) FROM educbaData ed GROUP BY ed.toolType";
Query hqlQuery = session.createQuery(hqlString);
List resultSet = hqlQuery.list();Using Named Parameters
Many times, we face a situation where we need to mention the values of the fields at runtime or externally. We can do so by using named parameters. We can get all the records with 10 sessions in the following way.
Code:
String hqlString = "FROM educbaData ed WHERE ed.session = :sessionCount";
Query hqlQuery = session.createQuery(hqlString);
hqlQuery.setParameter("sessionCount",10);
List resultSet = hqlQuery.list();Updating the records
We can update one or more records using the HQL query by using the UPDATE clause. We can update the session count of the “Javascript” name of technology’s session count to 35 using the following HQL query. executeUpdate method is used for updation and deletion which returns the number of rows that are affected while performing manipulation.
Code:
String hqlString = "UPDATE educbaData SET sessions=35 WHERE nameOfTechnology = 'Javascript'";
Query hqlQuery = session.createQuery(hqlString);
int affectedRowCount = hqlQuery.executeUpdate();Deleting the records
We can delete the records by using the DELETE clause in HQL query. If we want to delete the records whose session count is less than 5 then we can do so by using the executeUpdate method in the following way.
Code:
String hqlString = "DELETE FROM educbaData WHERE session < :minSessionCount";
Query hqlQuery = session.createQuery(hqlString);
hqlQuery.setParameter("minSessionCount",5);
int affectedRowCount = hqlQuery.executeUpdate();Inserting the records
We can insert the records in the following way in HQL. Alternatively, we can use the same method of the session interface and pass the object of the entity containing our desired values.
Code:
String hqlString = "INSERT INTO educbaData(nameOfTechnology, duration, sessions, toolType) SELECT nameOfTechnology, duration, sessions, toolType FROM edducationalPlatforms WHERE nameOfTechnology='Hibernate'";
Query hqlQuery = session.createQuery(hqlString);
int affectedRowCount = hqlQuery.executeUpdate();Conclusion
In this way, we can perform different manipulations in the database using the HQL query which is independent of the database and is faster for repetitive queries as it supports caching. Besides this, we can use aggregate functions like sum, max, min,avg and count methods in HQL. sum method helps retrieve the addition of a particular field which further can be used to retrieve summation values on the basis of the certain fields by using the GROUP BY clause along with it. Similarly, min and max aggregate functions are used to retrieve the minimum and maximum value of the particular field in the available records and avg function helps to get the average value of the field from the records.
The distinct method can be used to retrieve the unique records in a particular field. For pagination, we can use setFirstResult() and setMaxResults() methods which help in specifying starting from which record and how many records to retrieve.
Recommended Articles
This is a guide to Hibernate Query Language. Here we discuss an introduction to Hibernate Query Language with method of the session interface, different manipulations in the database. You can also go through our other related articles to learn more –
