I have two entities linked by a @ManyToMany association. Let's say Text and Country. So in my Text entity, I have:
@ManyToMany(fetch = FetchType.LAZY)
private Set<Country> countries = new HashSet<>();
I want to perform a paginated query that returns texts, so in a JPA repository, I write:
Page<Text> findAll(Pageable pageable);
This works. The problem is that as countries are lazy loaded, hibernate executes an extra select query for each country to load.
So I can use @EntityGraph to fetch countries:
@EntityGraph("countries")
Page<Text> findAll(Pageable pageable);
It works, I have only one query, but it is wayyyyyyyyyy much longer (about 12x). I seems that it fetches the countries for all the texts (I have a lot of texts) before applying the pagination. This is confirmed by the Spring data team: https://github.com/spring-projects/spring-data-jpa/issues/1976
So is there a way to perform an optimized paginated query, without N+1 select and without loading the entire table ?
Thank you very much