使用带有Pageable的@Query获取带有JOIN FETCH的集合对象不起作用-Spring JPA

Question

尝试使用可分页的JOIN FETCH获取Post对象。但它抛出了一个例外。

实体

@Entity
public class Post {
@Id
private String postId;
private String postName;
@OneToMany(mappedBy = "Post", cascade = CascadeType.ALL)
private Set<PostTag> postTags = new HashSet<PostTag>();
}
@Entity
public class Tag {
@Id
private String tagId;
private String tagName;
@OneToMany(mappedBy = "tag", cascade = CascadeType.ALL)
@JsonIgnore
private Set<PostTag> postTags = new HashSet<PostTag>();
}
@Entity
public class PostTag {
@EmbeddedId
private PostTagId postTagId = new PostTagId();

@ManyToOne(fetch = FetchType.LAZY)
@MapsId("postId")
@JoinColumn(name = "post_Id")
@JsonIgnore
private Post post;

@ManyToOne(fetch = FetchType.LAZY)
@MapsId("tagId")
@JoinColumn(name = "tag_Id")
private Tag tag;

@OneToMany(mappedBy = "posttag", cascade = CascadeType.ALL)
@JsonIgnore
private Set<Items> items= new HashSet<Items>();
private String someDateField;
}
@Embeddable
public class PostTagId implements Serializable {
private String postId;
private String tagId;
//equals & hashcode ommited
}
public class Items{
@Id
private String itemId;
private String itemName;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumns({@JoinColumn(name = "post_id"), @JoinColumn(name = "tag_id")})
@JsonBackReference
@JsonIgnore
private PostTag postTag;
@OneToMany(mappedBy = "items", cascade = CascadeType.ALL)
@JsonIgnore
private Set<SubItems> subItems= new HashSet<SubItems>();
}
public class SubItems{
@Id
private String subItemId;
private String subItemName;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumns({@JoinColumn(name = "itemId")})
@JsonBackReference
@JsonIgnore
private Items items;
}

存储库查询如下，

尝试过了，

@Query(value = "select po from Post po INNER JOIN FETCH po.posttags pts INNER JOIN FETCH pts.tag t INNER JOIN FETCH pts.items i INNER JOIN FETCH i.subitems LEFT OUTER JOIN po.posttags pts2 where (t.tagName like :tName) and (pts.someDateField between :startDate and :endDate) order by pts2.someDateField desc", countQuery = "select count(po) from Post po INNER JOIN po.posttags pts INNER JOIN pts.tag t INNER JOIN pts.items i INNER JOIN i.subitems LEFT OUTER JOIN po.posttags pts2 where (t.tagName like :tName) and (pts.someDateField between :startDate and :endDate) order by pts2.someDateField desc")
Page<Post> findAllInOneQuery(String tName, String startDate, String endDate, Pageable pageable);

但这导致以下异常。

Caused by: org.hibernate.QueryException: illegal attempt to dereference collection [po0_.postId.postTags] with element property reference [someDateField]

但下面的列表返回类型工作得很好。为什么以及如何使用分页？

@Query(value = "select po from Post po INNER JOIN FETCH po.posttags pts INNER JOIN FETCH pts.tag t INNER JOIN FETCH pts.items i INNER JOIN FETCH i.subitems LEFT OUTER JOIN po.posttags pts2 where (t.tagName like :tName) and (pts.someDateField between :startDate and :endDate) order by pts2.someDateField desc", countQuery = "select count(po) from Post po INNER JOIN po.posttags pts INNER JOIN pts.tag t INNER JOIN pts.items i INNER JOIN i.subitems LEFT OUTER JOIN po.posttags pts2 where (t.tagName like :tName) and (pts.someDateField between :startDate and :endDate) order by pts2.someDateField desc")
List<Post> findAllInOneQuery(String tName, String startDate, String endDate);

为什么List按预期工作，而不是Page对象。是否缺少任何参数，或者我需要稍微更改查询以适应可分页性？

Answer 1

我读了你的问题，下面是我的建议：

要将Peagable与@Query注释一起使用，必须为查询指定countBy属性。

@Query(
value = "select po from Post po INNER JOIN FETCH po.posttags pts INNER JOIN FETCH pts.tag t INNER JOIN FETCH pts.items i INNER JOIN FETCH i.subitems LEFT OUTER JOIN po.posttags pts2 where (t.tagName like :tName) and (pts.someDateField between :startDate and :endDate) order by pts2.someDateField desc", countQuery = "select count(po) from Post po INNER JOIN po.posttags pts INNER JOIN pts.tag t INNER JOIN pts.items i INNER JOIN i.subitems LEFT OUTER JOIN po.posttags pts2 where (t.tagName like :tName) and (pts.someDateField between :startDate and :endDate) order by pts2.someDateField desc",
countBy = "select count(po) from Post po INNER JOIN FETCH po.posttags pts INNER JOIN FETCH pts.tag t INNER JOIN FETCH pts.items i INNER JOIN FETCH i.subitems LEFT OUTER JOIN po.posttags pts2 where (t.tagName like :tName) and (pts.someDateField between :startDate and :endDate) order by pts2.someDateField desc", countQuery = "select count(po) from Post po INNER JOIN po.posttags pts INNER JOIN pts.tag t INNER JOIN pts.items i INNER JOIN i.subitems LEFT OUTER JOIN po.posttags pts2 where (t.tagName like :tName) and (pts.someDateField between :startDate and :endDate)")
Page<Post> findAllInOneQuery(String tName, String startDate, String endDate, Pageable pageable);