Spring-Data FETCH JOIN with Paging is not working

Question

I am trying to use HQL fetching my entity along with sub-entities using JOIN FETCH, this is working fine if I want all the results but it is not the case if I want a Page

My entity is

@Entity @Data public class VisitEntity {      @Id     @Audited     private long id;      .     .     .         @OneToMany(cascade = CascadeType.ALL,)     private List<VisitCommentEntity> comments; } 

and because I have millions of visits I need to use Pageable and I want to Fetch the comments in a single database query like :

@Query("SELECT v FROM VisitEntity v LEFT JOIN FETCH v.comments WHERE v.venue.id = :venueId and ..." ) public Page<VisitEntity> getVenueVisits(@Param("venueId") long venueId,...,         Pageable pageable); 

That HQL call throws the following exception:

Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=null,role=com.ro.lib.visit.entity.VisitEntity.comments,tableName=visitdb.visit_comment,tableAlias=comments1_,origin=visitdb.visit visitentit0_,columns={visitentit0_.visit_id ,className=com.ro.lib.visit.entity.VisitCommentEntity}}] [select count(v) FROM com.ro.lib.visit.entity.VisitEntity v LEFT JOIN FETCH v.comments WHERE v.venue.id = :venueId and (v.actualArrival > :date or v.arrival > :date)] at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1374) at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310) at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:309) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 

and once I remove the paging everything works fine

@Query("SELECT v FROM VisitEntity v LEFT JOIN FETCH v.comments WHERE v.venue.id = :venueId and  ..." ) public List<VisitEntity> getVenueVisits(@Param("venueId") long venueId,...); 

Obviously the problem is the count query from Spring-Data, but how can we fix it?

Answer 1

The easiest way is to use the countQuery attribute of the the @Query annotation to provide a custom query to be used.

@Query(value = "SELECT v FROM VisitEntity v LEFT JOIN FETCH v.comments …",        countQuery = "select count(v) from VisitEntity v where …") List<VisitEntity> getVenueVisits(@Param("venueId") long venueId, …); 

Answer 2

Alternatively in newest versions of Spring (supporting JPA 2.1 specification) you can use entity graph like this:

@EntityGraph(attributePaths = "roles") @Query("FROM User user") Page<User> findAllWithRoles(Pageable pageable); 

Of course named entity graphs work as well.