java - Extra unnecessary join with filtering and EntityGraph in Spring Data JPA Repository - Stack Overflow

admin管理员组

文章数量:1417417

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 ?

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 ?

• java
• spring
• spring-boot
• jpa
• entitygraph

Share Improve this question Follow asked Jan 31 at 11:23 Baptiste C.Baptiste C. 2311 silver badge44 bronze badges 2

• Why you want to eliminate that extra join in SQL? If you worry about performance you should look at execution plan. Most likely SQL optimizer in your DB is able to get rid of it. – talex Commented Jan 31 at 11:35
• Check out other providers batch fetching: eclipse.dev/eclipselink/documentation/2.5/jpa/extensions/… for ways it can be solved. I believe Hibernate has a @BatchSize annotation that you can use on the mapping, see thorben-janssen/… – Chris Commented Jan 31 at 15:22

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 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);

If you have defined your own query in @Query annotation used on your repository method findAllByBx, and explicitly use joins in it, it will produce the double joins with the EntityGraph, which automatically adds the needed joins.

If this is true, just remove the extra join in your @Query to solve your problem.

本文标签： javaExtra unnecessary join with filtering and EntityGraph in Spring Data JPA RepositoryStack Overflow