HQL – Hibernate Query Language Examples

In this HQL tutorial, learn HQL (hibernate query language), HQL syntax for various CRUD statements, named and native sql queries, associations and aggregations etc. with the help of examples.

HQL is an object-oriented query language, similar to SQL, but instead of operating on tables and columns, HQL works with persistent objects and their properties. This is main difference between hql vs sql.

HQL is a superset of the JPQL, the Java Persistence Query Language. A JPQL query is a valid HQL query, but not all HQL queries are valid JPQL queries.

HQL is a language with its own syntax and grammar. It is written as strings, like “from Product p“.

HQL queries are translated by Hibernate into conventional SQL queries. Note that Hibernate also provides the APIs that allow us to directly issue SQL queries as well.

Please note that Hibernator’s query facilities do not allow us to alter the database structure. We can only add/update/delete the data inside tables.
Table Of Contents

1. HQL Syntax
	1.1. Update Operation
	1.2. Delete Operation
	1.3. Insert Operation
	1.4. Select Operation
2. The from Clause and Aliases
3. The select Clause and Projection
4. Using Named Parameters
5. Paging Through the Result Set
6. Obtaining a Unique Result
7. Sorting Results with the order by Clause
8. Associations
9. Aggregate Methods
10. Named Queries
11. Using Native SQL
12. Enable Logging and Commenting

1. HQL Syntax

HQL syntax is defined as an ANTLR grammar. The grammar files are included in the grammar directory of the Hibernate core download. (ANTLR is a tool for building language parsers). Lets outline the syntax for the four fundamental CRUD operations here:

1.1. HQL Update Statement

UPDATE alters the details of existing objects in the database. In-memory entities, managed or not, will not be updated to reflect changes resulting from issuing UPDATE statements.

UPDATE [VERSIONED]
   [FROM] path [[AS] alias] [, ...]
   SET property = value [, ...]
   [WHERE logicalExpression]
  • path – fully qualified name of the entity or entities
  • alias – used to abbreviate references to specific entities or their properties, and must be used when property names in the query would otherwise be ambiguous.
  • VERSIONED – means that the update will update time stamps, if any, that are part of the entity being updated.
  • property – names of properties of entities listed in the FROM path.
  • logicalExpression – a where clause.

An example of the update statement. In this example, we are updating Employee data with hql update query multiple columns.

Query query=session.createQuery("update Employee set age=:age where name=:name");
query.setInteger("age", 32);
query.setString("name", "Lokesh Gupta");
int modifications=query.executeUpdate();

1.2. HQL DELETE Statement

DELETE removes the details of existing objects from the database. In-memory entities will not be updated to reflect changes resulting from DELETE statements.

This also means that Hibernate’s cascade rules will not be followed for deletions carried out using HQL. However, if you have specified cascading deletes at the database level (either directly or through Hibernate, using the @OnDelete annotation), the database will still remove the child rows.

DELETE
   [FROM] path [[AS] alias]
   [WHERE logicalExpression]

In practice, deletes might look like this:

Query query=session.createQuery("delete from Account where acctStatus=:status");
query.setString("acctStatus", "PURGED");
int rowsDeleted=query.executeUpdate();

1.3. HQL INSERT Statement

An HQL INSERT cannot be used to directly insert arbitrary entities. It can only be used to insert entities constructed from information obtained from SELECT queries (unlike ordinary SQL, in which an INSERT command can be used to insert arbitrary data into a table, as well as insert values selected from other tables).

INSERT
   INTO path ( property [, ...])
   select

The name of an entity is the path. The property names are the names of properties of entities listed in the FROM path of the incorporated SELECT query.

As HQL INSERT statement can only use data provided by an HQL SELECT statement, the application of INSRT statements can be limited.

Given below is an example of copying users to a purged table before actually purging them :

Query query=session.createQuery("insert into purged_accounts(id, code, status) "+
    "select id, code, status from account where accStatus=:status");
query.setString("accStatus", "PURGED");
int rowsCopied=query.executeUpdate();

1.4. HQL SELECT Statement

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

[SELECT [DISTINCT] property [, ...]]
   FROM path [[AS] alias] [, ...] [FETCH ALL PROPERTIES]
   WHERE logicalExpression
   GROUP BY property [, ...]
   HAVING logicalExpression
   ORDER BY property [ASC | DESC] [, ...]

The fully qualified name of an entity is path. The alias names may be used to abbreviate references to specific entities or their properties and must be used when property names used in the query would otherwise be ambiguous.

The property names are the names of properties of entities listed in the FROM path.

If FETCH ALL PROPERTIES is used then lazy loading semantics will be ignored, and all the immediate properties of the retrieved object(s) will be actively loaded (this does not apply recursively).

WHERE is used to create hql select query with where clause.

When the properties listed consist only of the names of aliases in the FROM clause, the SELECT clause can be omitted in HQL. If we are using the JPA with JPQL, one of the differences between HQL and JPQL is that the SELECT clause is required in JPQL.

Given below are few examples:

// 1
from Account FETCH ALL PROPERTIES

// 2
Select a.id, a.name, a.status from Account a order by a.id

// 3
Select a.id, a.name, a.status from Account a where a.id > 100

2. HQL – from clause and aliases

The most important feature in HQL to note is the alias. Hibernate allows us to assign aliases to the classes in our query with the as a clause. Use the aliases to refer back to the class inside the query.

from Product as p

//or

from Product as product

The 'as' keyword is optional. We can also specify the alias directly after the class name as follows:

from Product product

If we need to fully qualify a class name in HQL, just specify the package and class name. Hibernate will take care of most of this behind the scenes, so we really need this only if we have classes with duplicate names in our application. If we have to do this in Hibernate, use syntax such as the following:

from com.howtodoinjava.geo.usa.Product

The from clause is very basic and useful for working directly with objects. However, if you want to work with the object’s properties without loading the full objects into memory, you must use the SELECT clause as explained in next section.

3. HQL SELECT clause and Projections

The SELECT clause provides more control over the result set than the from clause. If you want to obtain the properties of objects in the result set, use the SELECT clause.

For instance, we could run a projection query on the products in the database that only returned the names, instead of loading the full object into memory, as follows:

select product.name from Product product

The result set for this query will contain a List of java.lang.String objects. Additionally, we can retrieve the prices and the names for each product in the database, like so:

select product.name, product.price from Product product

If you’re only interested in a few properties, this approach can allow you to reduce network traffic to the database server and save memory on the application’s machine.

4. HQL Named Parameters

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

When using JDBC query parameters, any time you add, change or delete parts of the SQL statement, you need to update your Java code that sets its parameters because the parameters are indexed based on the order in which they appear in the statement.

Hibernate lets you provide names for the parameters in the HQL query, so you do not have to worry about accidentally moving parameters around in the query.

The simplest example of named parameters uses regular SQL types for the parameters:

String hql = "from Product where price > :price";
Query query = session.createQuery(hql);
query.setDouble("price",25.0);
List results = query.list();

5. HQL – Paging Through ResultSet

Pagination through the result set of a database query is a very common application pattern. Typically, you would use pagination for a web application that returned a large set of data for a query. The web application would page through the database query result set to build the appropriate page for the user.

The application would be very slow if the web application loaded all of the data into memory for each user. Instead, you can page through the result set and retrieve the results you are going to display one chunk at a time.

There are two methods on the Query interface for paging: setFirstResult() and setMaxResults(). The setFirstResult() method takes an integer that represents the first row in your result set, starting with row 0. You can tell Hibernate to only retrieve a fixed number of objects with the setMaxResults() method.

Your HQL will be unchanged while applying the caching — you need to only modify the Java code that executes the query.

Query query = session.createQuery("from Product");
query.setFirstResult(1);
query.setMaxResults(2);
List results = query.list();
displayProductsList(results);

If you turn on SQL logging, you can see which SQL commands Hibernate uses for pagination. For the open-source HSQLDB database, Hibernate uses TOP and LIMIT.

Microsoft SQL Server does not support the LIMIT command, so Hibernate uses only the TOP command. If your application is having performance problems with pagination, this can be very helpful for debugging.

If you only have one result in your HQL result set, Hibernate has a shortcut method for obtaining just that object as discussed next.

6. HQL – Getting a Unique Single Result

HQL’s Query interface provides a uniqueResult() method for obtaining just one object from an HQL query. Although your query may yield only one object, you may also use the uniqueResult() method with other result sets if you limit the results to just the first result. You could use the setMaxResults() method discussed in the previous section.

The uniqueResult() method on the Query object returns a single object, or null if there are zero results. If there is more than one result, then the uniqueResult() method throws a NonUniqueResultException.

String hql = "from Product where price>25.0";
Query query = session.createQuery(hql);
query.setMaxResults(1);
Product product = (Product) query.uniqueResult();

7. HQL – Sorting the Results

To sort your HQL query’s results, you will need to use the order by clause.

You can order the results by any property on the objects in the result set: either ascending (asc) or descending (desc). You can use order on more than one property in the query if you need to.

from Product p where p.price>25.0 order by p.price desc

If you wanted to sort by more than one property, you would just add the additional properties to the end of the order by clause, separated by commas.

For instance, you could sort by product price and the supplier’s name, as follows:

from Product p order by p.supplier.name asc, p.price asc
[/ql]

<a name="associations"></a>
<h2>8. HQL Associations</h2>

Associations allow you to use <strong>more than one class in an HQL query</strong>, just as SQL allows you to use joins between tables in a relational database. 

Hibernate supports five different types of joins: <strong>inner join, cross join, left outer join, right outer join, and full outer join</strong>. 

If you use cross join, just specify both classes in the from clause (from Product p, Supplier s). For the other joins, use a join clause after the from clause. Specify the type of join, the object property to join on, and an alias for the other class.

You can use an inner join to obtain the supplier for each product, and then retrieve the supplier name, product name, and product price, as so:


select s.name, p.name, p.price from Product p inner join p.supplier as s

You can retrieve the objects using the similar syntax:

from Product p inner join p.supplier as s

9.HQL Aggregate Methods

HQL supports a range of aggregate methods, similar to SQL. They work the same way in HQL as in SQL, so you do not have to learn any specific Hibernate terminology. The difference is that in HQL, aggregate methods apply to the properties of persistent objects.

You may use the count(*) syntax to count all the objects in the result set, or count(product.name) to count the number of objects in the result set with a name property. Here is an example using the count(*) method to count all products:

select count(*) from Product product

The aggregate functions available through HQL include the following:

  1. avg(property name): The average of a property’s value
  2. count(property name or *): The number of times a property occurs in the results
  3. max(property name): The maximum value of the property values
  4. min(property name): The minimum value of the property values
  5. sum(property name): The sum total of the property values

10. HQL Named Queries

Named queries are created via class-level annotations on entities; normally, the queries apply to the entity in whose source file they occur, but there’s no absolute requirement for this to be true.

Named queries are created with the @NamedQueries annotation, which contains an array of @NamedQuery sets; each has a query and a name.

An example of named queries may look like this:

@NamedQueries({
        @NamedQuery(name = "supplier.findAll", query = "from Supplier s"),
        @NamedQuery(name = "supplier.findByName",
                query = "from Supplier s where s.name=:name"),
})

Executing above named query is even simpler.

Query query = session.getNamedQuery("supplier.findAll");
List<Supplier> suppliers = query.list();

Read More – Hibernate named query tutorial

11. HQL – Native SQL Queries

Although you should probably use HQL whenever possible, Hibernate does provide a way to use native SQL statements directly through Hibernate. One reason to use native SQL is that your database supports some special features through its dialect of SQL that are not supported in HQL. Another reason is that you may want to call stored procedures from your Hibernate application.

You can modify your SQL statements to make them work with Hibernate’s ORM layer. You do need to modify your SQL to include Hibernate aliases that correspond to objects or object properties. You can specify all properties on an object with {objectname.*}, or you can specify the aliases directly with {objectname.property}.

Hibernate uses the mappings to translate your object property names into their underlying SQL columns. This may not be the exact way you expect Hibernate to work, so be aware that you do need to modify your SQL statements for full ORM support.

You will especially run into problems with native SQL on classes with subclasses—be sure you understand how you mapped the inheritance across either a single table or multiple tables so that you select the right properties off the table.

Underlying Hibernate’s native SQL support is the org.hibernate.SQLQuery interface, which extends the org.hibernate.Query interface. Your application will create a native SQL query from the session with the createSQLQuery() method on the Session interface.

public SQLQuery createSQLQuery(String queryString) throws HibernateException

After you pass a string containing the SQL query to the createSQLQuery() method, you should associate the SQL result with an existing Hibernate entity, a join, or a scalar result. The SQLQuery interface has addEntity(), addJoin(), and addScalar() methods.

11.1. Hibernate SQL Query Example

Using native SQL with scalar results is the simplest way to get started with native SQL. Sample Java code looks like this:

String sql = "select avg(product.price) as avgPrice from Product product";
SQLQuery query = session.createSQLQuery(sql);
query.addScalar("avgPrice",Hibernate.DOUBLE);
List results = query.list();

A bit more complicated than the previous example is the native SQL that returns a result set of objects. In this case, we will need to map an entity to the SQL query.

String sql = "select {supplier.*} from Supplier supplier";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity("supplier", Supplier.class);
List results = query.list();

//Hibernate modifies the SQL and executes the following command against the database:

select Supplier.id as id0_, Supplier.name as name2_0_ from Supplier supplier

12. HQL – Enabling Logs and Comments

Hibernate can output the underlying SQL behind your HQL queries into your application’s log file. This is especially useful if the HQL query does not give the results you expect, or if the query takes longer than you wanted. This is not a feature you will have to use frequently, but it is useful should you have to turn to your database administrators for help in tuning your Hibernate application.

12.1. HQL Logs

The easiest way to see the SQL for a Hibernate HQL query is to enable SQL output in the logs with the “show_sql” property. Set this property to true in your hibernate.cfg.xml configuration file and Hibernate will output the SQL into the logs. When you look in your application’s output for the Hibernate SQL statements, they will be prefixed with “Hibernate:”.

If you turn your log4j logging up to debug for the Hibernate classes, you will see SQL statements in your log files, along with lots of information about how Hibernate parsed your HQL query and translated it into SQL.

12.2. HQL Comments

Tracing your HQL statements through to the generated SQL can be difficult, so Hibernate provides a commenting facility on the Query object that lets you apply a comment to a specific query. The Query interface has a setComment() method that takes a String object as an argument, as follows:

public Query setComment(String comment)

Hibernate will not add comments to your SQL statements without some additional configuration, even if you use the setComment() method. You will also need to set a Hibernate property, hibernate.use_sql_comments, to true in your Hibernate configuration.

If you set this property but do not set a comment on the query programmatically, Hibernate will include the HQL used to generate the SQL call in the comment. I find this to be very useful for debugging HQL.

Use commenting to identify the SQL output in your application’s logs if SQL logging is enabled.

That’s all for now related to HQL tutorial. Keep visiting for more on hibernate.

Happy Learning !!

Was this post helpful?

Join 7000+ Awesome Developers

Get the latest updates from industry, awesome resources, blog updates and much more.

* We do not spam !!

14 thoughts on “HQL – Hibernate Query Language Examples”

  1. this is really a great content on java and related technologies just one thing wanted to put here is why these tutorial are not in order?

    Reply
  2. If we want a particular value as output in the query but that value is not present in the table,how it can be done in hibernate.For eg:
    select 2 from from student.but 2 is not a column in table ,i just want output as 2

    Reply
  3. HI Lokesh,
    i have Three table Student, Book, Book_history
    structure like below
    Student table contains Sid,Sname
    Book table contains Bid,BookName
    Book_history table contains Sid,Bid,book_taken_date,Book_return_date

    here my question is
    how to get list of book to studen using sid which is frequently used by the other Student not by given sid
    how to achieve above case using Criteria and Hql

    Thanks !!

    Reply
  4. Is there any possibility not to fire query instead of can’t we use annotation..if Yes..can you please give some example like this.. Thanks in advance.

    Reply
  5. Very good article!

    At some places you haven’t used session.beginTransaction() before executing the query, normally hibernate will throw an exception “createQuery is not valid without active transaction”. Is there any specific reason here?

    Thanks!

    Reply
  6. Hi Lokesh,
    In the Update syntax you have mentioned optional [FROM] . Could you please explain we should use from in update ?

    Reply
    • Use of FROM is completely optional. It’ more of readability perspective. Even if use like below:

      Query query=session.createQuery(&quot;update FROM Employee set age=:age where name=:name&quot;);
      

      Above update statement is completely valid and will run similarly as:

      Query query=session.createQuery(&quot;update Employee set age=:age where name=:name&quot;);
      
      Reply

Leave a Comment

HowToDoInJava

A blog about Java and related technologies, the best practices, algorithms, and interview questions.