带有分页的 Spring-Data FETCH JOIN 不起作用

2022-01-18 00:00:00 spring java jpa spring-data spring-data-jpa

我正在尝试使用 HQL 来获取我的实体以及使用 JOIN FETCH 的子实体，如果我想要所有结果，这可以正常工作，但如果我想要页面则不是这样

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

我的实体是

@Entity
@Data
public class VisitEntity {

    @Id
    @Audited
    private long id;

    .
    .
    .   

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

因为我有数百万次访问，所以我需要使用 Pageable，并且我想在单个数据库查询中获取评论，例如:

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

该 HQL 调用引发以下异常:

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

显然问题是来自 Spring-Data 的计数查询，但我们该如何解决呢?

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

推荐答案

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

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, …);

相关文章