DEV Community

Cover image for How To Perform JOIN Queries With JPA Criteria API
Antonello Zanini for Writech

Posted on • Updated on

How To Perform JOIN Queries With JPA Criteria API

JPA Criteria queries are based on the JPA Criteria API, which allows you to build type-safe queries in Spring Boot. This becomes easier if you use them with the JPA Metamodel classes, which can be automatically generated.

The JPA Criteria API offers several features but also comes with a few pitfalls. However, using JPA criteria queries with several JOINs is a bit tricky. Especially, if you have to perform multiple JOINs and want to select multiple entities.

So, let's learn everything you need to know about how to perform a JOIN query with JPA Criteria API.

Context

Let's consider the following scenario:

  • The Author entity has a many-to-many association with the Book entity. In detail, the books attribute of the Author entity maps this association. Similarly, the Book entity has an authors attribute.

  • The Book entity has a many-to-many association with the Genre entity. In detail, the genres attribute of the Book entity maps this association. Similarly, the Genre entity has a books attribute.

So, an author has many books, each of which has many genres. Or, in other words, a book has many authors and many genres.

Multiple JOIN Query With JPA Criteria API

Let's say you want to retrieve all books in a specific genre written by a particular author. You can get them with the following Criteria query:

// initializing the CriteriaBuilder and CriteriaQuery object
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Book> criteriaQuery = criteriaBuilder.createQuery(Book.class);

Root<Book> root = criteriaQuery.from(Book.class);
// defining the JOIN clauses
Join<Book, Author> author = root.join("authors");
Join<Book, Genre> genre = root.join("genres");

// specifying the WHERE conditions
criteriaQuery.where(
    criteriaBuilder.equal(author.get("id"), 3),
    criteriaBuilder.equal(genre.get("id"), 5)
);

// retrieving the list of books
List<Book> books = entityManager
                                .createQuery(criteriaQuery)
                                .getResultList();
Enter fullscreen mode Exit fullscreen mode

As you can see, the JPA Join class allows you to define JOIN queries with the Criteria API. Note that the join() method can also accept a JoinType parameter.

Use JoinType to specify the type of JOIN you want to perform. The possible values are:

  • INNER: for INNER JOINs.
// performing an INNER JOIN
root.join("authors", JoinType.INNER);
Enter fullscreen mode Exit fullscreen mode
  • LEFT: for LEFT OUTER JOINs.
// performing a LEFT JOIN
root.join("authors", JoinType.LEFT);
Enter fullscreen mode Exit fullscreen mode
  • RIGHT: for RIGHT OUTER JOINs.
// performing a RIGHT JOIN
root.join("authors", JoinType.RIGHT);
Enter fullscreen mode Exit fullscreen mode

By default, the join() method performs an INNER JOIN.

Selecting Multiple Entities in a JOIN Query With JPA Criteria API

Now, let's see how you can retrieve the book and genre info of all books in a specific genre written by a particular author. You can achieve this with the following Criteria query:

// initializing the CriteriaBuilder and CriteriaQuery object
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// if you want to perform a multiselect() you need define
// a CriteriaQuery with the Tuple type 
CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createQuery(Tuple.class);

Root<Book> root = criteriaQuery.from(Book.class);
// defining the JOIN clauses
Join<Book, Author> author = root.join("authors");
Join<Book, Genre> genre = root.join("genres");

// defining the multi SELECT clause and 
// specifying the WHERE conditions
criteriaQuery
             .multiselect(root, genre)
             .where(
                criteriaBuilder.equal(author.get("id"), 3),
                criteriaBuilder.equal(genre.get("id"), 5)
);

// retrieving the list of <Book, Genre> tuples
List<Tuple> bookGenreTuples = entityManager
                                          .createQuery(criteriaQuery)
                                          .getResultList();
Enter fullscreen mode Exit fullscreen mode

In this case, you have to initialize the CriteriaQuery as Tuple query. This special data structure allows you to retrieve the result of a multiselect() query.

In detail, you can extract the Book and Genre objects from an element of bookGenreTuples list as below:

// extracting the first tuple from the list
Tuple bookGenreTuple = bookGenreTuples.get(0);

// getting the first element of the tuple 
// and converting it into a Book object
Book book = bookGenreTuple.get(0, Book.class);

// getting the second element of the tuple
// and converting it into a Genre object
Genre genre = bookGenreTuple.get(1, Genre.class);
Enter fullscreen mode Exit fullscreen mode

Note that the resulting Tuple stores the entity objects in the same order as they specified them in the multiselect() method above.

Et voilà! You just learn how to perform JOIN queries with the JPA Criteria API in Spring Boot!

Conclusion

In this article, you learned how to write JPA Criteria queries that involve many JOIN clauses and select multiple entities. All this would not be possible without the JPA Criteria API.

Here, you learned how to define simple JOIN queries in Spring Boot with the JPA Criteria API, as well as more complex queries with multiselect logic. This can be a bit tricky, and thanks to this article you learned how to master Criteria JOIN queries in JPA.

Thanks for reading! I hope you found this article helpful.


The post "How To Perform JOIN Queries With JPA Criteria API" appeared first on Writech.

Top comments (0)