Hibernate Query Language (HQL) Example

Details: Written by Nam Ha Minh; Last Updated on 12 December 2023 | Print Email

The Hibernate ORM framework provides its own query language called Hibernate Query Language or HQL for short. It is very powerful and flexible and has the following characteristics:

SQL similarity: HQL’s syntax is very similar to standard SQL. If you are familiar with SQL then writing HQL would be pretty easy: from SELECT, FROM, ORDERBY to arithmetic expressions and aggregate functions, etc.
Fully object-oriented: HQL doesn’t use real names of table and columns. It uses class and property names instead. HQL can understand inheritance, polymorphism and association.
Case-insensitive for keywords: Like SQL, keywords in HQL are case-insensitive. That means SELECT, select or Select are the same.
Case-sensitive for Java classes and properties: HQL considers case-sensitive names for Java classes and their properties, meaning Person and person are two different objects.

In this tutorial, we show you how to write HQL for executing fundamental queries (CRUD) as well as other popular ones. The following diagram illustrates relationship of the tables used in examples of this tutorial:

Database Structure

And here are the model classes annotated with JPA annotations:

Category class:

package net.codejava.hibernate;

import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;


@Entity
@Table(name = "CATEGORY")
public class Category {

	private long id;
	private String name;

	private Set<Product> products;

	public Category() {
	}

	public Category(String name) {
		this.name = name;
	}

	@Id
	@Column(name = "CATEGORY_ID")
	@GeneratedValue
	public long getId() {
		return id;
	}


	@OneToMany(mappedBy = "category", cascade = CascadeType.ALL)
	public Set<Product> getProducts() {
		return products;
	}

	// other getters and setters
}

Product class:

package net.codejava.hibernate;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table(name = "PRODUCT")
public class Product {
	private long id;
	private String name;
	private String description;
	private float price;

	private Category category;

	public Product() {
	}

	public Product(String name, String description, float price,
			Category category) {
		this.name = name;
		this.description = description;
		this.price = price;
		this.category = category;
	}

	@Id
	@Column(name = "PRODUCT_ID")
	@GeneratedValue
	public long getId() {
		return id;
	}

	@ManyToOne
	@JoinColumn(name = "CATEGORY_ID")
	public Category getCategory() {
		return category;
	}

	// other getters and setters
}

Order class:

package net.codejava.hibernate;

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity
@Table(name = "ORDERS")
public class Order {
	private int id;
	private String customerName;
	private Date purchaseDate;
	private float amount;
	private Product product;

	@Id
	@Column(name = "ORDER_ID")
	@GeneratedValue
	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	@Column(name = "CUSTOMER_NAME")
	public String getCustomerName() {
		return customerName;
	}

	@Column(name = "PURCHASE_DATE")
	@Temporal(TemporalType.DATE)
	public Date getPurchaseDate() {
		return purchaseDate;
	}


	@ManyToOne
	@JoinColumn(name = "PRODUCT_ID")
	public Product getProduct() {
		return product;
	}

	// other getters and setters
}

The upcoming examples are provided based on assumption that a Hibernate’s SessionFactory is opened and a transaction has been started. You can find more about how to obtain SessionFactory and start a transaction in the tutorial: Building Hibernate SessionFactory from Service Registry.

The following table of content is provided for your convenience:

How to execute HQL in Hibernate
List Query Example
Search Query Example
Using Named Parameters Example
Insert - Select Query Example
Update Query Example
Delete Query Example
Join Query Example
Sort Query Example
Group By Query Example
Pagination Query Example
Date Range Query Example
Using Expressions in Query
Using Aggregate Functions in Query

1. How to execute HQL in Hibernate

Basically, it’s fairly simple to execute HQL in Hibernate. Here are the steps:

Write your HQL:
```
String hql = "Your Query Goes Here";
```
Create a Query from the Session:
```
Query query = session.createQuery(hql);
```
Execute the query: depending on the type of the query (listing or update), an appropriate method is used:
- For a listing query (SELECT):
```
List listResult = query.list();
```
- For an update query (INSERT, UPDATE, DELETE):
```
int rowsAffected = query.executeUpdate();
```
Extract result returned from the query: depending of the type of the query, Hibernate returns different type of result set. For example:
- Select query on a mapped object returns a list of those objects.
- Join query returns a list of arrays of Objects which are aggregate of columns of the joined tables. This also applies for queries using aggregate functions (count, sum, avg, etc).

Now, let’s go through various concrete examples.

2. Hibernate List Query Example

The following code snippet executes a query that returns all Category objects:

String hql = "from Category";
Query query = session.createQuery(hql);
List<Category> listCategories = query.list();

for (Category aCategory : listCategories) {
	System.out.println(aCategory.getName());
}

Note that in HQL, we can omit the SELECT keyword and just use the FROM instead.

3. Hibernate Search Query Example

The following statements execute a query that searches for all products in a category whose name is ‘Computer’:

String hql = "from Product where category.name = 'Computer'";
Query query = session.createQuery(hql);
List<Product> listProducts = query.list();

for (Product aProduct : listProducts) {
	System.out.println(aProduct.getName());
}

The cool thing here is Hibernate automatically generates JOIN query between the Product and Category tables behind the scene. Thus we don’t have to use explicit JOIN keyword:

from Product where category.name = 'Computer'

4. Using Named Parameters in Query Example

You can parameterize your query using a colon before parameter name, for example :id indicates a placeholder for a parameter named id. The following example demonstrates how to write and execute a query using named parameters:

String hql = "from Product where description like :keyword";

String keyword = "New";
Query query = session.createQuery(hql);
query.setParameter("keyword", "%" + keyword + "%");

List<Product> listProducts = query.list();

for (Product aProduct : listProducts) {
	System.out.println(aProduct.getName());
}

The above HQL searches for all products whose description contains the specified keyword:

from Product where description like :keyword

Then use the setParameter(name, value) method to set actual value for the named parameter:

query.setParameter("keyword", "%" + keyword + "%");

Note that we want to perform a LIKE search so the percent signs must be used outside the query string, unlike traditional SQL.

5. Hibernate Insert - Select Query Example

HQL doesn’t support regular INSERT statement (you know why - because the session.save(Object) method does it perfectly). So we can only write INSERT … SELECT query in HQL. The following code snippet executes a query that inserts all rows from Category table to OldCategory table:

String hql = "insert into Category (id, name)"
		+ " select id, name from OldCategory";

Query query = session.createQuery(hql);

int rowsAffected = query.executeUpdate();
if (rowsAffected > 0) {
	System.out.println(rowsAffected + "(s) were inserted");
}

Note that HQL is object-oriented, so Category and OldCategory must be mapped class names (not real table names).

6. Hibernate Update Query Example

The UPDATE query is similar to SQL. The following example runs a query that updates price for a specific product:

String hql = "update Product set price = :price where id = :id";

Query query = session.createQuery(hql);
query.setParameter("price", 488.0f);
query.setParameter("id", 43l);

int rowsAffected = query.executeUpdate();
if (rowsAffected > 0) {
	System.out.println("Updated " + rowsAffected + " rows.");
}

7. Hibernate Delete Query Example

Using DELETE query in HQL is also straightforward. For example:

String hql = "delete from OldCategory where id = :catId";

Query query = session.createQuery(hql);
query.setParameter("catId", new Long(1));

int rowsAffected = query.executeUpdate();
if (rowsAffected > 0) {
	System.out.println("Deleted " + rowsAffected + " rows.");
}

8. Hibernate Join Query Example

HQL supports the following join types (similar to SQL):

innerjoin (can be abbreviated as join).
leftouterjoin (can be abbreviated as leftjoin).
rightouterjoin (can be abbreviated as rightjoin).
fulljoin

For example, the following code snippet executes a query that retrieves results which is a join between two tables Product and Category:

String hql = "from Product p inner join p.category";

Query query = session.createQuery(hql);
List<Object[]> listResult = query.list();

for (Object[] aRow : listResult) {
	Product product = (Product) aRow[0];
	Category category = (Category) aRow[1];
	System.out.println(product.getName() + " - " + category.getName());
}

Using the join keyword in HQL is called explicit join. Note that a JOIN query returns a list of Object arrays, so we need to deal with the result set differently:

List<Object[]> listResult = query.list();

HQL provides with keyword which can be used in case you want to supply extra join conditions. For example:

from Product p inner join p.category with p.price > 500

That joins the Product and Category together with a condition specifies that product’s price must be higher than 500.

As stated earlier, we can write implicit join query which uses dot-notation. For example:

from Product where category.name = 'Computer'

That result in innerjoin in the resulting SQL statement.

9. Hibernate Sort Query Example

Sorting in HQL is very similar to SQL using ORDERBY clause follows by a sort direction ASC (ascending) or DESC (descending). For example:

String hql = "from Product order by price ASC";

Query query = session.createQuery(hql);
List<Product> listProducts = query.list();

for (Product aProduct : listProducts) {
	System.out.println(aProduct.getName() + "\t - " + aProduct.getPrice());
}

That lists all products by the ascending order of price.

10. Hibernate Group By Query Example

Using GROUPBY clause in HQL is similar to SQL. The following query summarizes price of all products grouped by each category:

select sum(p.price), p.category.name from Product p group by category

And here is the code snippet:

String hql = "select sum(p.price), p.category.name from Product p group by category";

Query query = session.createQuery(hql);
List<Object[]> listResult = query.list();

for (Object[] aRow : listResult) {
	Double sum = (Double) aRow[0];
	String category = (String) aRow[1];
	System.out.println(category + " - " + sum);
}

11. Hibernate Pagination Query Example

To return a subset of a result set, the Query interface has two methods for limiting the result set:

setFirstResult(intfirstResult): sets the first row to retrieve.
setMaxResults(intmaxResults): sets the maximum number of rows to retrieve.

For example, the following code snippet lists first 10 products:

String hql = "from Product";

Query query = session.createQuery(hql);
query.setFirstResult(0);
query.setMaxResults(10);

List<Product> listProducts = query.list();

for (Product aProduct : listProducts) {
	System.out.println(aProduct.getName() + "\t - " + aProduct.getPrice());
}

12. Hibernate Date Range Query Example

A nice feature of Hibernate is that it is able to defer parameter type to generate the resulting SQL statement accordingly. So using date time parameters in HQL is quick and easy, for example:

String hql = "from Order where purchaseDate >= :beginDate and purchaseDate <= :endDate";

Query query = session.createQuery(hql);

SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd");
Date beginDate = dateFormatter.parse("2014-11-01");

query.setParameter("beginDate", beginDate);

Date endDate = dateFormatter.parse("2014-11-22");
query.setParameter("endDate", endDate);

List<Order> listOrders = query.list();

for (Order anOrder : listOrders) {
	System.out.println(anOrder.getProduct().getName() + " - "
			+  anOrder.getAmount() + " - "
			+ anOrder.getPurchaseDate());
}

The above query lists only orders whose purchase date is in a specified range.

13. Using Expressions in Hibernate Query

For expressions used in the WHERE clause, HQL supports all basic arithmetic expressions similar to SQL include the following:

mathematical operators: +, -, *, /
binary comparison operators: =, >=, <=, <>, !=, like
logical operators: and, or, not
etc

For a complete list of expressions supported by Hibernate, click here.

For example, the following query returns only products with price is ranging from 500 to 1000 dollars:

from Product where price >= 500 and price <= 1000

14. Using Aggregate Functions in Hibernate Query

HQL supports the following aggregate functions:

avg(…), sum(…), min(…), max(…)
count(*)
count(…), count(distinct…), count(all…)

For example, the following query counts all products:

select count(name) from Product

And here’s the code snippet that shows how to extract the result:

String hql = "select count(name) from Product";

Query query = session.createQuery(hql);
List listResult = query.list();
Number number = (Number) listResult.get(0);
System.out.println(number.intValue());

You can get the sample project code on GitHub or download in the Attachments section below.

References:

So far you have learn various code examples to use queries in Hibernate. I recommend you to take this course to learn how to use Hibernate for data access layer of a complete Java web application.

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.

Attachments:
HibernateQueryTest.zip	[Eclipse-Maven Project]	21 kB

Hibernate Query Language (HQL) Example

References:

Other Hibernate Tutorials:

About the Author: