Introduction of HQL with explination by R4R Team

We know that the Hibernate is a query based language which is work on objects. So we can say that the hibernate is the object oriented language. It works on the table and column. We can say that it works with the persistent objects and their properties. In Hibernate we translate the SQL Query which works to perform the action on the database.

We can use SQL statements directly with hibernate using Native SQL but we always use the HQL Query with database. It gives us the advantes of Hibernate SQL generation and caching strategies.

We always remember that in SQL SELECT,FROM,WHERE keyword are not case sensitive but properties like table and column names are case sensitive in HQL.

We divided into some basic category to the SQL properties which we use. So here we will discuss that how we need to use?

SQL properties

Description

FROM

We use FROM when we want to load the persistend object into the memory. With the help of the following syntex we can understand what is the mean of tha above line:

String hql = "FROM Hibernate_HQL";

Query query = session.createQuery(hql);

List results = query.list();

When we use a class thorough a package then we need to fully qualify a class name in HQL, just specify the package and class name as follows:

String hql = "FROM r4r.hibernate.criteria.Hibernate_HQL";

Query query = session.createQuery(hql);

List results = query.list();

AS

In the SQL AS keyword perform the common action means it define the relation two table. But in HQL it can be used to assign to the classes for the HQL qeries.

For Understand the above discussion we need to go through with the example which is as given above:

String hql = "FROM Hibernate_HQL AS HQ";

Query query = session.createQuery(hql);

List results = query.list();

In the HQL AS keyword is optional. We can specify without using the AS keyword. With the help of the AS keyword we can specify the alias directly after the class name.

We can see in the below example:

String hql = "FROM Hibernate_HQL HQ";

Query query = session.createQuery(hql);

List results = query.list();

SELECT

The SELECT is used to fetch tables from the database which provides more control over the result set than the from. When we want to obtain few properties of objects instead of the complete object, for this we can use the SELECT clause.

We have given below simple syntax of using SELECT clause to get just first_name field of the Employee object:

String hql = "SELECT HQ.firstName FROM Hibernate_HQL HQ";

Query query = session.createQuery(hql);

List results = query.list();

We can understand the given above line. we can say that it is notable here that Hibernate_HQL.firstName is a property of Hibernate_HQL object rather than a field of the Hibernate_HQL table.

WHERE

WHERE is used in the HQL When we want to narrow the specific objects that are returned from storage.

With the help of the Following simple syntax of using WHERE clause:

String hql = "FROM Hibernate_HQL HQ WHERE HQ.id = 10";

Query query = session.createQuery(hql);

List results = query.list();

ORDER BY

When we want to sort HQL query's results, then we need to use the ORDER BY clause. With the help we can order the results by any property on the objects in the result set either ascending (ASC) or descending (DESC).

Given below simple syntax of using ORDER BY clause:

String hql = "FROM Hibernate_HQL HQ WHERE HQ.id > 10 ORDER BY HQ.salary DESC";

Query query = session.createQuery(hql);

List results = query.list();

When ever we want to sort by more than one property, then we need to add additional properties to the end of the order by clause, separated by commas as follows:

String hql = "FROM Hibernate_HQL HQ WHERE HQ.id > 10 " + "ORDER BY HQ.firstName DESC, HQ.salary DESC ";

Query query = session.createQuery(hql);

List results = query.list();

GROUP BY

GROUP BY tells us that the Hibernate pull information from the database and group it based on a value of an attribute and, With the help we can find the aggregate value.

Given simple syntax of using GROUP BY clause:

String hql = "SELECT SUM(HQ.salary), HQ.firtName FROM Hibernate_HQL HQ " + "GROUP BY E.firstName";

Query query = session.createQuery(hql);

List results = query.list();

Using Named

Hibernate supports named parameters in its HQL queries. This makes writing HQL queries that accept input from the user easy and you do not have to defend against SQL injection attacks.

Following is the simple syntax of using named parameters:

String hql = "FROM Hibernate_HQL HQ WHERE HQ.id = : Hibernate_HQL_id";

Query query = session.createQuery(hql);

query.setParameter("employee_id",10);

List results = query.list();

UPDATE

Bulk updates are new to HQL with Hibernate 3, and deletes work differently in Hibernate 3 than they did in Hibernate 2. The Query interface now contains a method called executeUpdate() for executing HQL UPDATE or DELETE statements.

The UPDATE clause can be used to update one or more properties of an one or more objects. Following is the simple syntax of using UPDATE clause:

String hql = "UPDATE Hibernate_HQL set salary = : salary " + "WHERE id = :employee_id";

Query query = session.createQuery(hql);

query.setParameter("salary", 1000);

query.setParameter("Hibernate_HQL_id", 10);

int result = query.executeUpdate();

System.out.println("Rows affected: " + result);

DELETE

The DELETE clause can be used to delete one or more objects. Following is the simple syntax of using DELETE clause:

String hql = "DELETE FROM Hibernate_HQL " + "WHERE id = : Hibernate_HQL_id";

Query query = session.createQuery(hql);

query.setParameter("Hibernate_HQL_id", 10);

int result = query.executeUpdate();

System.out.println("Rows affected: " + result);

INSERT INTO

HQL supports INSERT clause only where records can be inserted from one object to another object. Following is the simple syntax of using INSERT INTO clause:

String hql = "INSERT INTO Employee(firstName, lastName, salary)" + "SELECT firstName, lastName, salary FROM old_employee_Hibernate_HQL";

Query query = session.createQuery(hql);

int result = query.executeUpdate();

System.out.println("Rows affected: " + result);

11. Aggregate Methods

HQL supports a range of aggregate methods, similar to SQL. They work the same way in HQL as in SQL and following is the list of the available functions:

Functions

Description

avg(property name)

The average of a property's value

count(property name or *)

The number of times a property occurs in the results

max(property name)

The maximum value of the property values

min(property name)

The minimum value of the property values

sum(property name)

The sum total of the property values
The distinct keyword only counts the unique values in the row set. The following query will return only unique count:

String hql = "SELECT count(distinct E.firstName) FROM Hibernate_HQL E";
Query query = session.createQuery(hql);
List results = query.list();

Pagination using Query

There are two methods of the Query interface for pagination.

Method

Description

Query setFirstResult(int startPosition)

This method takes an integer that represents the first row in your result set, starting with row 0.

Query setMaxResults(int maxResult)

This method tells Hibernate to retrieve a fixed number maxResults of objects.

Using above two methods together, we can construct a paging component in our web or Swing application. 

By the given below example which can extend to fetch 10 rows at a time:

String hql = "FROM Hibernate_HQL";
Query query = session.createQuery(hql);
query.setFirstResult(1);
query.setMaxResults(10);
List results = query.list();
Leave a Comment:
Search
Categories
R4R Team
R4Rin Top Tutorials are Core Java,Hibernate ,Spring,Sturts.The content on R4R.in website is done by expert team not only with the help of books but along with the strong professional knowledge in all context like coding,designing, marketing,etc!