具有分页的Spring-Data FETCH JOIN不起作用 [英] Spring-Data FETCH JOIN with Paging is not working
问题描述
我正在尝试使用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?
推荐答案
最简单的方法是使用<$的 countQuery
属性c $ c> @Query 注释,提供要使用的自定义查询。
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, …);
这篇关于具有分页的Spring-Data FETCH JOIN不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!