Java Spring-Data FETCH JOIN with Paging 不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21549480/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Spring-Data FETCH JOIN with Paging is not working
提问by Ehab Al-Hakawati
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
我正在尝试使用 HQL 获取我的实体以及使用 JOIN FETCH 的子实体，如果我想要所有结果，这工作正常，但如果我想要一个页面，情况就不是这样了
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 :
并且因为我有数百万次访问，所以我需要使用 Pageable 并且我想在单个数据库查询中获取评论，例如：
@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:
该 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)
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?
显然问题是来自 Spring-Data 的计数查询，但我们如何解决它？
采纳答案by Oliver Drotbohm
The easiest way is to use the countQueryattribute of the the @Queryannotation to provide a custom query to be used.
最简单的方法是使用注释的countQuery属性@Query来提供要使用的自定义查询。
@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, …);
回答by Max
You have to specify countQueryparam for @Queryand now you can use Pageor Listas return value.
您必须指定countQueryparam for@Query现在您可以使用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);
回答by Piotr Tempes
Alternatively in newest versions of Spring (supporting JPA 2.1 specification) you can use entity graph like this:
或者，在最新版本的 Spring（支持 JPA 2.1 规范）中，您可以像这样使用实体图：
@EntityGraph(attributePaths = "roles")
@Query("FROM User user")
Page<User> findAllWithRoles(Pageable pageable);
Of course named entity graphs work as well.
当然，命名实体图也能工作。
回答by heobo
Try countProjection
尝试 countProjection
@Query(value="SELECT v FROM VisitEntity v LEFT JOIN FETCH v.comments WHERE v.venue.id = :venueId and ..." ,
countProjection = "v.id")
public Page<VisitEntity> getVenueVisits(@Param("venueId") long venueId,...,
Pageable pageable);
