Java hibernate hql join

Hibernate Query Language (HQL) Example

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 , ORDER BY 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.

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 Setproducts; 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 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:

1. How to execute HQL in Hibernate
2. List Query Example
3. Search Query Example
4. Using Named Parameters Example
5. Insert — Select Query Example
6. Update Query Example
7. Delete Query Example
8. Join Query Example
9. Sort Query Example
10. Group By Query Example
11. Pagination Query Example
12. Date Range Query Example
13. Using Expressions in Query
14. Using Aggregate Functions in Query

1. How to execute HQL in Hibernate

String hql = "Your Query Goes Here";

Query query = session.createQuery(hql);

List listResult = query.list();

int rowsAffected = query.executeUpdate();

• 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).

2. Hibernate List Query Example

String hql = «from Category»; Query query = session.createQuery(hql); List listCategories = query.list(); for (Category aCategory : listCategories)

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 listProducts = query.list(); for (Product aProduct : listProducts)

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 listProducts = query.list(); for (Product aProduct : listProducts)

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)

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 query = session.createQuery(hql); query.setParameter(«price», 488.0f); query.setParameter(«id», 43l); int rowsAffected = query.executeUpdate(); if (rowsAffected > 0)

7. Hibernate Delete Query Example

String hql = «delete from OldCategory where query = session.createQuery(hql); query.setParameter(«catId», new Long(1)); int rowsAffected = query.executeUpdate(); if (rowsAffected > 0)

8. Hibernate Join Query Example

String hql = «from Product p inner join p.category»; Query query = session.createQuery(hql); List listResult = query.list(); for (Object[] aRow : listResult)

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 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'

9. Hibernate Sort Query Example

Sorting in HQL is very similar to SQL using ORDER BY 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 listProducts = query.list(); for (Product aProduct : listProducts)

10. Hibernate Group By Query Example

Using GROUP BY 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

String hql = «select sum(p.price), p.category.name from Product p group by category»; Query query = session.createQuery(hql); List listResult = query.list(); for (Object[] aRow : listResult)

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.

String hql = «from Product»; Query query = session.createQuery(hql); query.setFirstResult(0); query.setMaxResults(10); List listProducts = query.list(); for (Product aProduct : listProducts)

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 listOrders = query.list(); for (Order anOrder : listOrders)

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 example, the following query returns only products with price is ranging from 500 to 1000 dollars:

from Product where price >= 500 and price 

14. Using Aggregate Functions in Hibernate Query
select count(name) from Product
 
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());
 
References:
 
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.
 
Источник