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.

Join the Persistence Hub!

---

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: How to select a POJO with a Criteria Query
• Hibernate Tips: How to select multiple scalar values in a Criteria Query
• Hibernate Tip: How to Use a SubQuery in a CriteriaQuery
• Criteria Update/Delete – The easy way to implement bulk operations with JPA2.1