Hibernate – Query Language
polymorphicHibernate is a Java framework that makes it easier to create database-interactive Java applications. In HQL, instead of a table name, it uses a class name. As a result, it is a query language that is database-independent.
Hibernate converts HQL queries into SQL queries, which are used to perform database actions. Although Native SQL may be used directly with Hibernate, it is encouraged to utilize HQL wherever feasible to prevent database portability issues.
HQL has many benefits. Some benefits are:
- HQL is database-independent.
- polymorphic queries supported which are type-safe.
- It is portable and easy to learn for Java programmers.
The Query interface provides object-oriented methods and capabilities for representing and manipulating HQL queries.
Note: 1. Keywords like FROM, SELECT, WHERE are not case-sensitive in HQL.
2. Table and Column names are case-sensitive in HQL.
Hibernate Query Language (HQL) Clauses
There are many HQL clauses available to interact with relational databases, and several of them are listed below:
- FROM Clause
- SELECT Clause
- WHERE Clause
- ORDER BY Clause
- UPDATE Clause
- DELETE Clause
- INSERT Clause
For details and examples regarding each clause is mentioned below.
1. FROM Clause
To load a whole persistent object into memory, the FROM clause is used.
String hib = "FROM Student";
Query query = session.createQuery(hib);
List results = query.list();
2. SELECT Clause
The SELECT clause is used when only a few attributes of an object are required rather than the entire object.
String hib = "SELECT S.roll FROM Student S";
Query query = session.createQuery(hib);
List results = query.list();
3. WHERE Clause
Filtering records is done with the WHERE clause. It’s used to retrieve only the records that meet a set of criteria.
String hib = "FROM Student S WHERE S.id = 5";
Query query = session.createQuery(hib);
List results = query.list();
4. ORDER BY Clause
The ORDER BY clause is used to sort the results of an HQL query.
String hib = "FROM Student S WHERE S.id > 5 ORDER BY S.id DESC";
Query query = session.createQuery(hib);
List results = query.list();
NOTE:
1. Order By – DESC will sort in descending order
2. Order By – ASC will sort in ascending order
5. UPDATE Clause
The UPDATE clause is required to update the value of an attribute.
String hib = "UPDATE Student set name=:n WHERE roll=:i";
Query q=session.createQuery(hib);
q.setParameter("n","John");
q.setParameter("i",23);
int status=q.executeUpdate();
System.out.println(status);
6. DELETE Clause
It is required to delete a value of an attribute.
String hib = "DELETE FROM Student WHERE id=10";
Query query=session.createQuery(hib);
query.executeUpdate();
7. INSERT Clause
It is required to Insert values into the relation.
String hib = "INSERT INTO Student(first_name, last_name)" +
"SELECT first_name, last_name FROM backup_student";
Query query = session.createQuery(hib);
int result = query.executeUpdate();
Pagination using Query
For pagination using query we have two methods available for it, below table contains the methods and its description.
Method | Action Performed |
---|---|
Query setMaxResults(int max) | Instructs Hibernate to get a specific number of items. |
Query setFirstResult(int starting_no) | Takes an integer as an argument that represents the first row in your result set, beginning with row 0. |
Example for pagination using HQL
To fetch the few row data at a time we can use pagination using HQL. For this example we are fetching the rows 5 to 10.
String hib = "FROM Student"
Query query=session.createQuery(hib);
query.setFirstResult(5);
query.setMaxResult(10);
List list=query.list();
The above example returns the list of records from 5 to 10.
Aggregate Methods
Similar to SQL, HQL has several aggregation techniques, some of them are mentioned below-
- Average
- Max
- Min
- Count
- Sum
For details and examples regarding each aggregate method is mentioned below.
Average
To find the average of marks
String hib = "SELECT AVG(marks) FROM Student";
Query q=session.createQuery(hib);
Max
To find the maximum mark among all the available marks.
String hib = "SELECT MAX(marks) FROM Student";
Query q=session.createQuery(hib);
Min
To find the minimum mark among all the available marks.
String hib = "SELECT MIN(marks) FROM Student";
Query q=session.createQuery(hib);
Count
To find the count of id present.
String hib = "SELECT COUNT(id) FROM Student";
Query q=session.createQuery(hib);
NOTE: For counting only distinct values you can use DISTINCT in the following ways-
1. With the SELECT clause as “SELECT DISTINCT name FROM Student”
2. With the FROM clause (deprecated) as “SELECT * FROM DISTINCT Student”
Sum
To find the sum of all the marks of Student.
String hib = "SELECT SUM(marks) FROM Student";
Query q=session.createQuery(hib);
List<Integer> list=q.list();
System.out.println(list.get(0));
Conclusion
Overall, this article has provided a detailed explanation for Hibernate Query Language (HQL), a tool building a bridge of communication with any relational databases in Java applications. By utilizing HQL, developers can achieve various benefits:
- Database independence and portability
- write type-safe queries.
- Benefits from object-oriented features like the Query interface
As discussed in the above examples, HQL offers a range of clauses for various use cases:
- Manipulating data including
- FROM
- SELECT
- WHERE
- ORDER BY
- UPDATE
- DELETE
- INSERT
- Additionally, it also provides various aggregation methods like
- average
- maximum
- minimum
- count
- sum
- enabling efficient data analysis
Overall, mastering HQL can significantly enhance the efficiency and maintainability of Java applications that interact with relational databases.