How To Perform JOIN Queries With JPA Criteria API
Tags: java, programming, softwaredevelopment, softwareengineering
Published: 2024-01-19 21:29:55

JPA Criteria queries are based on the JPA [Criteria API](https://docs.jboss.org/hibernate/orm/3.2/api/org/hibernate/Criteria.html), 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](https://writech.run/blog/criteria-queries-and-jpa-metamodel-with-spring-boot-and-kotlin-9c82be54d626).

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:

```java
// 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();
```

As you can see, the JPA [`Join`](https://docs.jboss.org/hibernate/jpa/2.1/api/javax/persistence/criteria/Join.html) class allows you to define JOIN queries with the Criteria API. Note that the `join()` method can also accept a [`JoinType`](https://docs.jboss.org/hibernate/jpa/2.1/api/javax/persistence/criteria/JoinType.html) parameter.

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

- [`INNER`](https://docs.jboss.org/hibernate/jpa/2.1/api/javax/persistence/criteria/JoinType.html#INNER): for INNER JOINs.

```java
// performing an INNER JOIN
root.join("authors", JoinType.INNER);
```

- [`LEFT`](https://docs.jboss.org/hibernate/jpa/2.1/api/javax/persistence/criteria/JoinType.html#LEFT): for LEFT OUTER JOINs.

```java
// performing a LEFT JOIN
root.join("authors", JoinType.LEFT);
```

- [`RIGHT`](https://docs.jboss.org/hibernate/jpa/2.1/api/javax/persistence/criteria/JoinType.html#RIGHT): for RIGHT OUTER JOINs.

```java
// performing a RIGHT JOIN
root.join("authors", JoinType.RIGHT);
```

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:

```java
// 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();
```

In this case, you have to initialize the [`CriteriaQuery`](https://docs.jboss.org/hibernate/jpa/2.1/api/javax/persistence/criteria/CriteriaQuery.html) as [`Tuple`](https://docs.jboss.org/hibernate/jpa/2.1/api/javax/persistence/Tuple.html) query. This special data structure allows you to retrieve the result of a [`multiselect()`](https://docs.jboss.org/hibernate/jpa/2.1/api/javax/persistence/criteria/CriteriaQuery.html#multiselect%28java.util.List%29) query.

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

```java
// 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);
```

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](https://writech.run/blog/how-to-perform-join-queries-with-jpa-criteria-api-9ccfe5c7a4ab/)" appeared first on [Writech](https://writech.run)._