JPQL Join Queries with Spring Data JPA

In this tutorial, we’ll explore few commonly used JPQL joins using Spring Data JPA, with a focus on understanding their power and flexibility.

1. What is JPQL? 

JPQL, or Java Persistence Query Language, is a platform-independent query language defined as part of the JPA specification. Unlike SQL, which operates on tables and columns in a database, JPQL operates on Java entities and their attributes. It abstracts the underlying database so that developers can write queries without being tied to a specific database's syntax or structure. 

Key JPQL highlights:

Entity-centric: Queries are written against entity objects, not database tables. 

Polymorphic: It supports polymorphic queries, allowing you to query on parent entities and retrieve child entities. 

Portable: Since JPQL doesn't rely on database-specific syntax, it promotes code portability across various databases.

1. Create JPA Entities

For this example, we'll use two entities: Author and Book.

Author

@Entity
public class Author {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;

    @OneToMany(mappedBy = "author")
    private Set<Book> books = new HashSet<>();

    // Constructors, getters, setters...
}

Book

@Entity
public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String title;

    @ManyToOne
    @JoinColumn(name = "author_id")
    private Author author;

    // Constructors, getters, setters...
}

Creating Join Queries with JPQL 

2.1. Basic Inner Join 

To fetch all books with their corresponding authors:

public interface BookRepository extends JpaRepository<Book, Long> {
    @Query("SELECT b FROM Book b JOIN b.author a")
    List<Book> findAllWithAuthors();
}

2.2. Left Join 

To fetch all authors and any books they've written (even if an author hasn't written any books):

public interface AuthorRepository extends JpaRepository<Author, Long> {
    @Query("SELECT a FROM Author a LEFT JOIN FETCH a.books")
    List<Author> findAllWithBooks();
}

2.3. Join with Conditions 

To fetch books written by authors named "John":

public interface BookRepository extends JpaRepository<Book, Long> {
    @Query("SELECT b FROM Book b JOIN b.author a WHERE a.name = 'John'")
    List<Book> findAllByAuthorNamedJohn();
}

3. Avoiding The N+1 Problem

A common pitfall is inadvertently causing an "N+1" select issue, where the application performs unnecessary additional queries. One way to mitigate this is using the FETCH JOIN:

public interface AuthorRepository extends JpaRepository<Author, Long> {
    @Query("SELECT a FROM Author a JOIN FETCH a.books")
    List<Author> findAllAuthorsAndBooks();
}

By using FETCH, we're ensuring that the associated books are loaded immediately, rather than being lazily fetched later.

4. Multiple Joins 

JPQL allows for multiple joins. Let's assume books have a Publisher entity:

public interface BookRepository extends JpaRepository<Book, Long> {
    @Query("SELECT b FROM Book b JOIN b.author a JOIN b.publisher p WHERE p.name = 'Penguin'")
    List<Book> findAllByPublisherNamedPenguin();
}

5. Conclusion 

In this tutorial, we have seen what is JPQL and how to perform JPQL joins using Spring Data JPA.