Hibernate Tips: How to Use a SubQuery in a CriteriaQuery

Hibernate Tips is a series of posts in which I describe a quick and easy solution for common Hibernate questions. If you have a question for a future Hibernate Tip, please leave a comment below.

Question:

In one of our use cases, we use the Criteria API to build the query dynamically. Now I need to add a subquery. Does the Criteria API support subqueries? And how can I implement them?

Solution:

The Criteria API supports the same features as a JPQL query. So, you can use a subquery only in your WHERE but not in the SELECT or FROM clause.

Let’s take a look at an example.

I use a simple model consisting of an Author and a Book entity and a many-to-many association between them.

In the following code snippet, I use the Criteria API to define a query and a subquery that select all Authors who’ve written at least 3 Books.

// create the outer query
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(Author.class);
Root root = cq.from(Author.class);

// count books written by an author
Subquery sub = cq.subquery(Long.class);
Root subRoot = sub.from(Book.class);
SetJoin<Book, Author> subAuthors = subRoot.join(Book_.authors);
sub.select(cb.count(subRoot.get(Book_.id)));
sub.where(cb.equal(root.get(Author_.id), subAuthors.get(Author_.id)));

// check the result of the subquery
cq.where(cb.greaterThanOrEqualTo(sub, 3L));

TypedQuery query = em.createQuery(cq);
List authors = query.getResultList();

In the first step, I instantiate a CriteriaQuery which returns Author entities.

Then I call the subquery method on the CriteriaQuery to create a subquery that counts the Books written by the Author which is selected by the outer query.

As you can see, I define the subquery in the same way as I create a CriteriaQuery. I first set the Book entity as the root and join it with the Author entity. Then I use the count function to determine the number of Books in the SELECT clause. And after that, I compare the id of the Author entity which got selected in the outer query with the id of the Author selected in the subquery.

In the final step, I define the WHERE clause of the outer query. I want to select all Authors who have written at least 3 Books. So, I use the greaterThanOrEqualTo method to check if the result of the subquery is greater or equal 3.

When you execute this CriteriaQuery, Hibernate generates the following SQL statement.

16:55:38,728 DEBUG [org.hibernate.SQL] - 
    select
        author0_.id as id1_0_,
        author0_.firstName as firstNam2_0_,
        author0_.lastName as lastName3_0_,
        author0_.version as version4_0_ 
    from
        Author author0_ 
    where
        (
            select
                count(book1_.id) 
            from
                Book book1_ 
            inner join
                BookAuthor authors2_ 
                    on book1_.id=authors2_.bookId 
            inner join
                Author author3_ 
                    on authors2_.authorId=author3_.id 
            where
                author0_.id=author3_.id
        )>=3

Learn more:

Here are a few more Hibernate Tips using the Criteria API:

 

Hibernate Tips Book







Get more recipes like this one in my new book Hibernate Tips: More than 70 solutions to common Hibernate problems.

It gives you more than 70 ready-to-use recipes for topics like basic and advanced mappings, logging, Java 8 support, caching, and statically and dynamically defined queries.

Get it now!


Related Articles

Responses

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

    1. That should work without any issues. Please check my answer on StackOverflow.

  1. Where did “Book_.authors” come from?

    1. Hi Numan,

      the Book_ class is a generated class that’s part of JPA’s metamodel. You can use it to reference attributes in a type-safe way when implementing a CriteriaQuery or an EntityGraph.

      You can learn more about it here: https://thorben-janssen.com/static-metamodel/

      Regards,
      Thorben

  2. Hi,

    is it possible to use the subquery in the “from” or “select” clause?

    Thanks in advance.

    Greetings