- 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:
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
Basically, it’s fairly simple to execute HQL in Hibernate. Here are the steps:
Now, let’s go through various concrete examples.
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.
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'
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.
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).
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.");
}
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.");
}
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.
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.
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);
}
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());
}
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.
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
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.
Other Hibernate Tutorials:
About the Author:
Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on
Facebook and watch
his Java videos you YouTube.