Using Pageable and EntityGraph, entire collection is loaded from database [DATAJPA-1677]

[spring-projects-issues]

Christoffer opened DATAJPA-1677 and commented

I noticed some weird behavior when I added an EntityGraph to my findAll repository method:

• When I use an EntityGraph, that specifies an attribute node on a field that uses a @OneToMany relationship, the entire collection will be loaded from the database (however, the Page object only contains my 25 limit objects).

• No limit is added to the SQL statement

• The number of rows loaded ~= playlists + items (which I believe should be ~= 25 + items)

• The same thing does not happen if I add a @OneToOne or @ManyToOne field to my attribute nodes.

  • The limit (25) is then added as expected to the SQL statement
  • The number of rows loaded = 25

 

Highly simplified code from my project:

 

@Entity
@NamedEntityGraph(
        name = "graph",
        attributeNodes = {
                @NamedAttributeNode(value = "items")
        }
)
public class Playlist {

    ....
    @OneToMany(mappedBy = "playlist")
    private Set<Item> items;
}

 

 

interface PlaylistAdminRepository extends JpaRepository<Playlist, Integer>, JpaSpecificationExecutor<Playlist> {
    @EntityGraph(value = "graph")
    Page<Playlist> findAll(Pageable pageable);
}

 

 

---

Affects: 2.2.4 (Moore SR4)

[spring-projects-issues]

Jens Schauder commented

Could you please provide a reproducer for this?

[spring-projects-issues]

Michał Stochmal commented

That's completely normal behavior - JPA provider will fetch whole set of data and then paginate it in memory when @OneToMany children records are fetched within single query. To overcome this problem use native query or fetch children records after fetching page of data.

 

Check out this blog post:

https://vladmihalcea.com/fix-hibernate-hhh000104-entity-fetch-pagination-warning-message/

[spring-projects-issues]

Suman Guduru commented

i'm also having same issue with entity graph 

[spring-projects-issues]

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

[schauder]

As described by Michael Stochmal this is normal JPA behavior.