Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 39 ยท HQL, Criteria and Native Queries

HQL Joins, Aggregates and Subqueries

Join entities in HQL with inner, left and fetch joins, group and filter with aggregates, write subqueries and case expressions, and run the equivalent SQL on H2.

Real screens rarely need a single entity. You want each author with the number of books, orders above the average, customers who never bought anything. HQL handles these with joins, aggregate functions, group by, subqueries and case. The big difference from SQL is that you join along mapped associations (a.books), not along foreign key columns, so you almost never write an on clause by hand.

The examples assume two entities: Author (with @OneToMany List<Book> books) and Book (with @ManyToOne Author author). HQL appears as read-only code. The runnable programs run the equivalent SQL on H2 so you can see the result sets. If the mappings are new to you, see the associations chapter.

Inner and left joins

Java
// every author that has at least one book (inner join)
"select a.name, b.title from Author a join a.books b"

// every author, even without books (left join: b may be null)
"select a.name, b.title from Author a left join a.books b"

// with an extra condition on the join itself
"select a.name, b.title from Author a left join a.books b on b.price > 20"

Hibernate reads the join condition from the mapping. join a.books b becomes join books b on b.author_id = a.id. Two rules to remember:

  • join means inner join. Rows on the left with no match disappear.
  • Putting a filter in where after a left join turns it back into an inner join for those rows. Use on if the filter belongs to the join.

You may also navigate with a path: select b.title from Book b where b.author.name = :name. This is an implicit join. It works, but it always produces an inner join and hides the SQL, so write explicit joins in non-trivial queries.

Fetch joins

A join fetch loads the association into the entity in the same query instead of one extra query per row:

Java
List<Author> authors = session.createSelectionQuery(
        "select distinct a from Author a left join fetch a.books where a.country = :c",
        Author.class)
    .setParameter("c", "DE")
    .getResultList();

fetch is not a filter and has no alias for conditions: it fills author.getBooks() with all books. It is the main cure for the N+1 problem, covered in fetch strategies. In Hibernate 6 and later, distinct is no longer needed to remove duplicate parents, because Hibernate de-duplicates entity results itself. Older code still carries it, which is harmless.

Aggregates and grouping

HQL supports count, sum, avg, min and max. Remember the result types: count returns a Long, sum of an integer type returns a Long, and avg returns a Double.

Java
List<Object[]> rows = session.createSelectionQuery("""
        select a.name, count(b), avg(b.price)
        from Author a left join a.books b
        group by a.id, a.name
        having count(b) >= 2
        order by count(b) desc
        """, Object[].class).getResultList();

where filters rows before grouping, having filters groups after. Java text blocks make long queries readable.

Here is the same idea as SQL on H2:

Java Joins and group by (SQL equivalent)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Notice the second query: Grace has no books but is still listed (with a null title), and Ada keeps only her expensive book, because the price filter lives in the on clause. Move it to where and Grace disappears, since b.price > 20 is never true for a null price.

Subqueries

HQL supports subqueries in where, having, and (since Hibernate 6) in from and select:

Java
// books more expensive than the average of all books
"from Book b where b.price > (select avg(x.price) from Book x)"

// authors without any book
"from Author a where not exists (select 1 from Book b where b.author = a)"

// authors whose books are all cheap
"from Author a where 20 > all (select b.price from a.books b)"

// authors with the number of their books, as a scalar subquery
"select a.name, (select count(b) from Book b where b.author = a) from Author a"

exists, in, all, any and some work as in SQL. For collections you also have is empty, size(a.books) and member of, which Hibernate translates to subqueries or correlated counts for you.

Case, coalesce and functions

Java
"select b.title, case when b.price < 15 then 'cheap' when b.price < 35 then 'mid' else 'premium' end from Book b"

"select coalesce(b.subtitle, 'none') from Book b"
"select upper(a.name), length(a.name), substring(a.name, 1, 3) from Author a"
"select year(b.publishedOn), count(b) from Book b group by year(b.publishedOn)"

Hibernate 6 knows the portable functions extract, year, month, concat, trim, abs, round, cast(x as String) and more. Use function('name', args) to call a database-specific function. Register functions you use often with a FunctionContributor so they become first-class.

Java Subqueries and case expressions (SQL equivalent)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Bulk updates and deletes

HQL can change many rows in one statement without loading entities:

Java
int updated = session.createMutationQuery(
        "update Book b set b.price = b.price * :factor where b.price < :limit")
    .setParameter("factor", new BigDecimal("1.05"))
    .setParameter("limit", new BigDecimal("20"))
    .executeUpdate();

Be careful: a bulk statement goes straight to the database and bypasses the persistence context. Entities already loaded keep their old values, and version columns are not incremented unless you write update versioned Book .... Clear or refresh the session after bulk work, and see batch processing for large jobs.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does join a.books b in HQL produce?

    A plain join is an inner join and Hibernate takes the join condition from the mapping. Use left join or join fetch for the other behaviors.
  2. 2. Where should a filter go if it must not remove authors that have no matching books in a left join?

    A where condition on the right side is false for null rows and removes them. A condition in on keeps the left rows.
  3. 3. What is the difference between where and having?

    where is evaluated per row; having is evaluated on the aggregated groups.
  4. 4. Why can a bulk HQL update leave stale entities in the session?

    Bulk statements run in the database only. Loaded entities are not updated, so clear or refresh them.

Summary

  • Join along associations: join is inner, left join keeps the left side, join fetch loads the association eagerly in one query.
  • Use on for conditions that belong to a join and having for filters on aggregates.
  • Subqueries, case, coalesce and portable functions are built in.
  • Bulk update and delete skip the persistence context: clear or refresh afterwards.

Next: The Criteria API