使用@Query和Pageable获取集合对象时连接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);

为什么列表按预期工作，但页面对象未按预期工作。是否缺少任何参数，或者是否需要稍微更改查询以适应可分页？

Answer 1

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

要将Peagable与查询一起使用，必须为查询指定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);