Spring-Data FETCH JOIN with Paging不工作

Question

我正在尝试使用HQL来获取我的实体以及使用JOIN FETCH的子实体，如果我想要所有的结果，这是很好的，但如果我想要一个页面，情况就不是这样了

我的实体是

@Entity
@Data
public class VisitEntity {

    @Id
    @Audited
    private long id;

    .
    .
    .   

    @OneToMany(cascade = CascadeType.ALL,)
    private List<VisitCommentEntity> comments;
}

因为我有数百万的访问量，所以我需要使用可分页，并且我想在单个数据库查询中获取评论，如下所示：

@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);

该HQL调用抛出以下异常：

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)

一旦移除了分页，一切都会正常工作

@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,...);

显然，问题是来自Spring-Data的count查询，但是我们如何解决它呢？

Answer 1

最简单的方法是使用@Query注释的countQuery属性来提供要使用的自定义查询。

@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

或者，在最新版本的Spring (支持JPA 2.1规范)中，您可以像这样使用实体图：

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

当然，命名实体图也适用。

Answer 3

您必须为@Query指定countQuery参数，现在您可以使用Page或List作为返回值。

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