Hibernate Tip: Using LEFT JOIN and FETCH JOIN in a CriteriaQuery


Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.


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 post a comment below.

Question:

In one of my previous Hibernate Tips, I explained the difference between a JOIN, a LEFT JOIN, and a JOIN FETCH clause. I used JPQL in all examples, and Igor asked how he could do the same using JPA’s Criteria API.

Because the Criteria API presents a few pitfalls, I decided to answer it in a new Hibernate Tip. So, the question I want to answer today is:

How can I create a JOIN, LEFT JOIN and JOIN FETCH clause using JPA’s Criteria API?

Solution:

JPA’s different JOIN clauses are one of the essential parts of JPQL and the Criteria API. They tell Hibernate which database tables it shall join in the generated SQL query and how it shall do that.

Unfortunately, the Criteria API is not as easy to use as most of us would prefer. That’s especially the case if you want to use a JOIN FETCH clause or select multiple entities.

Defining a JOIN clauses 

Defining a JOIN clause is pretty simple. You first call the from method on your CriteriaQuery object to get a Root object. In the next step, you can call the join method to define your JOIN clause based on a managed association.

In the following example, the books attribute of the Author entity models a many-to-many association to the Book entity. So, I can use it in my CriteriaQuery to join the Author with the Book entity.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Author> cq = cb.createQuery(Author.class);
Root<Author> root = cq.from(Author.class);
Join<Object, Object> book = root.join(Author_.BOOKS);

ParameterExpression<String> pTitle = cb.parameter(String.class);
cq.where(cb.like(book.get(Book_.TITLE), pTitle));

TypedQuery<Author> q = em.createQuery(cq);
q.setParameter(pTitle, "%Hibernate%");
List<Author> authors = q.getResultList();

If you activate the logging of SQL statements, you can see the expected SQL statement in the log file. Hibernate uses inner joins to join the Author with the BookAuthor table and the BookAuthor table with the Book table.

18:23:33,672 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_ 
    inner join
        BookAuthor books1_ 
            on author0_.id=books1_.authorId 
    inner join
        Book book2_ 
            on books1_.bookId=book2_.id 
    where
        book2_.title like ?

Defining a LEFT JOIN or RIGHT JOIN clause

You can define a LEFT JOIN or a RIGHT JOIN clause in almost the same way as you create an inner join clause. The only difference is that you need to provide a JoinType as the second parameter to the join method.

JoinType is an enum. You can choose between:

  • INNER, which is the default,
  • LEFT, and
  • RIGHT

Let’s create a query that selects Author and Book entities and joins them using a LEFT JOIN.

The best way to do that is to create a query that returns Tuple instances. You can then call the multiselect method to select Author and Book entities. The returned Tuple interface provides a comfortable way to process the result of your query.

And as you can see in the code, the creation of the Root and the LEFT JOIN is almost identical to the previous example.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> cq = cb.createTupleQuery();
Root<Author> root = cq.from(Author.class);
Join<Object, Object> book = root.join(Author_.BOOKS, JoinType.LEFT);
cq.multiselect(root, book);

ParameterExpression<String> pLastName = cb.parameter(String.class);
cq.where(cb.equal(root.get(Author_.LAST_NAME), pLastName));

TypedQuery<Tuple> q = em.createQuery(cq);
q.setParameter(pLastName, "Janssen");
List<Tuple> authorBooks = q.getResultList();

When you run this code, you can see in the log file, that Hibernate generates an SQL query with the expected LEFT JOIN clause.

18:44:57,970 DEBUG [org.hibernate.SQL] - 
    select
        author0_.id as id1_0_0_,
        book2_.id as id1_1_1_,
        author0_.firstName as firstNam2_0_0_,
        author0_.lastName as lastName3_0_0_,
        author0_.version as version4_0_0_,
        book2_.publisherid as publishe5_1_1_,
        book2_.publishingDate as publishi2_1_1_,
        book2_.title as title3_1_1_,
        book2_.version as version4_1_1_ 
    from
        Author author0_ 
    left outer join
        BookAuthor books1_ 
            on author0_.id=books1_.authorId 
    left outer join
        Book book2_ 
            on books1_.bookId=book2_.id 
    where
        author0_.lastName=?

Defining a JOIN FETCH clause

Working with a JOIN FETCH clause in a CriteriaQuery is a little special.

First of all, you can’t create it using the join method. You need to call the fetch method instead. If you want to define a LEFT JOIN FETCH or a RIGHT JOIN FETCH clause, you need to provide a JoinType enum value as the second parameter.

The second big difference is the return type of the fetch method. The returned Fetch interface is very similar to the Join interface. But it doesn’t define a get method. So, you can’t use it to access any attributes of the JOIN FETCHed entity.

As a workaround, you can cast the Fetch interface to a Join interface. Even so, the interfaces are independent of each other; this approach works with Hibernate and EclipseLink.

Here you can see an example of such a query.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Author> cq = cb.createQuery(Author.class);
Root<Author> root = cq.from(Author.class);
Join<Object, Object> book = (Join<Object, Object>) root.fetch(Author_.BOOKS);

ParameterExpression<String> pTitle = cb.parameter(String.class);
cq.where(cb.like(book.get(Book_.TITLE), pTitle));

TypedQuery<Author> q = em.createQuery(cq);
q.setParameter(pTitle, "%Hibernate%");
List<Author> authors = q.getResultList();

When you execute this code, you can see that Hibernate extended the SELECT clause and added all columns mapped by the Book entity. This is the important difference to a standard JOIN clause. The FETCH keyword tells Hibernate not only to join but also to fetch the associated entity.

19:19:34,650 DEBUG [org.hibernate.SQL] - 
    select
        author0_.id as id1_0_0_,
        book2_.id as id1_1_1_,
        author0_.firstName as firstNam2_0_0_,
        author0_.lastName as lastName3_0_0_,
        author0_.version as version4_0_0_,
        book2_.publisherid as publishe5_1_1_,
        book2_.publishingDate as publishi2_1_1_,
        book2_.title as title3_1_1_,
        book2_.version as version4_1_1_,
        books1_.authorId as authorId2_2_0__,
        books1_.bookId as bookId1_2_0__ 
    from
        Author author0_ 
    inner join
        BookAuthor books1_ 
            on author0_.id=books1_.authorId 
    inner join
        Book book2_ 
            on books1_.bookId=book2_.id 
    where
        book2_.title like ?

The better alternative to JOIN FETCH clauses

If you don’t like to cast the Fetch to a Join interface, you can use an EntityGraph instead. It’s another way to tell Hibernate to initialize the association.

In the following example, I use Hibernate’s proprietary GraphParser API to parse a String to an EntityGraph.

RootGraph<Author> graph = GraphParser.parse(Author.class, "books", em);

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Author> cq = cb.createQuery(Author.class);
Root<Author> root = cq.from(Author.class);
Join<Object, Object> book = root.join(Author_.BOOKS);

ParameterExpression<String> pTitle = cb.parameter(String.class);
cq.where(cb.like(book.get(Book_.TITLE), pTitle));

TypedQuery<Author> q = em.createQuery(cq);
q.setParameter(pTitle, "%Hibernate%");
List<Author> authors = q.getResultList();

The executed query is identical to the previous example. But this time, you don’t need to rely on any implementation details, when you cast the Fetch to a Join interface.

19:20:56,295 DEBUG [org.hibernate.SQL] - 
    select
        author0_.id as id1_0_0_,
        book2_.id as id1_1_1_,
        author0_.firstName as firstNam2_0_0_,
        author0_.lastName as lastName3_0_0_,
        author0_.version as version4_0_0_,
        book2_.publisherid as publishe5_1_1_,
        book2_.publishingDate as publishi2_1_1_,
        book2_.title as title3_1_1_,
        book2_.version as version4_1_1_,
        books1_.authorId as authorId2_2_0__,
        books1_.bookId as bookId1_2_0__ 
    from
        Author author0_ 
    inner join
        BookAuthor books1_ 
            on author0_.id=books1_.authorId 
    inner join
        Book book2_ 
            on books1_.bookId=book2_.id 
    where
        book2_.title like ?

Learn more:

If you want to learn more about the Criteria API, you should also read the following articles:

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!

6 Comments

  1. Hi.
    Is there any way to join “separate” table (no OneToOne or OneToMany between entities)?
    Instead of
    select p.* from parents p where p.id in (select c.parent_id from childs c where c.date_create between and )
    I want to get next
    select p.* from parents p inner join childs c on p.id = c.parent_id and c.date_create between and

  2. Hi,
    I get “javax.persistence.NoResultException: No entity found for query] with root cause” exception with Join Fetch query.

    How can we solve this problem?

    1. Avatar photo Thorben Janssen says:

      Hi Bilal,

      that’s most likely either because the JOIN FETCH added an inner to your query and there are no associated entities or your WHERE condition where not matched by any record in your DB.
      If it’s caused by the JOIN FETCH clause, please try a LEFT JOIN FETCH clause: root.fetch(“myAttribute”, JoinType.LEFT);

      Regards,
      Thorben

  3. What is “Author_”?

    1. Avatar photo Thorben Janssen says:

      The Author_ class is part of the JPA Metamodel. It describes the entity class and all its attributes. In this post, I use it to define typesafe queries.
      You can learn more about the JPA Metamodel here: https://thorben-janssen.com/static-metamodel/

  4. Avatar photo msbi training in hyderabad says:

    thanks for the information that was very helpful

Comments are closed.