Extra unnecessary join with filtering and EntityGraph in Spring Data JPA Repository

Question

I have an entity A which has a list of entity B.

I want to retrieve all A with the repository method :

findAllByBx(String x)

(which x is an attribute of B)

The context is set, now the problem :

I'll retrieve 1000 A and for each one, I'll have 2 or 3 B.

The first problem is that Spring generate 1001 requests because for each A, Spring request all B.

So, I found @EntityGraph annotation which is a great solution to my problem because it retrieves all A AND B at once !

BUT

I want to filter the result with x (see my repository method) and because of that, Hibernate defines its own join, then @EntityGraph would add an extra unnecessary join which is not even used anywhere in the query. On the other hand, without @EntityGraph, the entities will be fetched one by one... The least is when Spring creates A and B, it uses the extra join and not the filtered one. See below :

SELECT a1_0.id, b2_0.x
FROM A a1_0
LEFT JOIN B b1_0
    ON b1_0.a = a1_0.id
LEFT JOIN B b2_0
    ON b2_0.a = a1_0.id
WHERE b1_0.x = 'string'

Can anyone have an idea to solve it ?

Answer 1

You are using incorrect approach. You should never get a large amount of objects with one-to-many relation.

Better to use hybrid approach: do as much work as possible using standard ORM tools and use additional low level tools like: projections, dynamic fetching, native SQL.

Example

@Entity
class AuthorEntity {

    @Id
    Long id;

    @Column
    String name;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "author")
    List<BookEntity> books;

}

@Entity
class BookEntity {

    @Id
    Long id;

    @Column
    String label;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn
    AuthorEntity author;

}

Get all the books by label using JPQL. It will be without any join

@Query("select book " +
        "from BookEntity book " +
        "where book.label = 'some label'")
List<BookEntity> getBooks();

For each book you can get authorId, using book.getAuthor().getId(), without lazy fetching.

Get bookId and authorId by label using projections.

@Query("select book.id as bookId, book.author.id as authorId " +
        "from BookEntity book inner join book.author " +
        "where book.label = 'some label'")
List<BookEntityDto> getBooks();

interface BookEntityDto {

    Long getBookId();

    Long getAuthorId();

}

Get bookEntity and authorName by label using projections.

@Query("select b as book, b.author.name as authorName " +
        "from BookEntity b inner join b.author " +
        "where b.label = 'some label'")
List<BookEntityDto> getBooks();

interface BookEntityDto {

    BookEntity getBook();

    String getAuthorName();

}

Use paging for large lists and do multiple queries in place of one

@Query("select book " +
        "from BookEntity book " +
        "where book.label = 'some label' order by book.id asc")
List<BookEntity> getBooks(Pageable limit);