如何使用JOIN编写JPQL,以便在一个调用中获取所有Post集合和相关标记&条目和子项,而无需从数据库中查询N+1。
实体
@Entity
public class Post {
@Id
private String postId;
private String postName;
@OneToMany(mappedBy = "Post", cascade = CascadeType.ALL)
private Collection<PostTag> postTags = new HashSet<PostTag>();
}
@Entity
public class Tag {
@Id
private String tagId;
private String tagName;
@OneToMany(mappedBy = "tag", cascade = CascadeType.ALL)
@JsonIgnore
private Collection<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;
}
JPA findAll()会触发这么多连接查询,即每个表的每个结果都有一个查询。例如,如果在所有被触发的表4中都存在1关联,那么总查询将被触发。
已经试过了
@Query("select po from Post po inner join fetch PostTag pt on po.postId=pt.post.postId inner join fetch Tag t on pt.tag.tagId = t.tagId")
Page<Post> findAllInOneQuery(Pageable pageable);
但这导致了例外。使用JOIN和@Query命令一次获得所有集合的正确方法是什么?
注释: Plain findAll()触发下面的查询,以获得与所有关联的1结果。
Hibernate: select post0_.postId as postid1_0_, post0_.postName as postname2_0_ from post post0_ inner join PostTag posttag1_ on post0_.postId=posttag1_.post_id inner join tagInfo taginfo2_ on posttag1_.tag_id=taginfo2_.tagId left outer join PostTag posttag3_ on post0_.postId=posttag3_.post_id where (taginfo2_.tagName like ?) and (posttag1_.someDateField between ? and ?) order by posttag3_.someDateField desc, posttag3_.pubDate desc offset 0 rows fetch next ? rows only
Hibernate: select posttag0_.post_id as post_id1_4_0_, posttag0_.tag_id as tag_i2_4_0_, posttag0_.post_id as post_id1_4_1_, posttag0_.tag_id as tag_i2_4_1_, posttag0_.content as content3_4_1_, posttag0_.someDateField as someDateField10_4_1_ from PostTag posttag0_ where posttag0_.post_id=?
Hibernate: select items0_.post_id as post_id3_1_0_, items0_.tag_id as tag_i4_1_0_, items0_.productId as producti1_1_0_, items0_.productId as producti1_1_1_, items0_.productName as productn2_1_1_, items0_.post_id as post_id3_1_1_, items0_.tag_id as tag_i4_1_1_ from Items items0_ where items0_.post_id=? and items0_.tag_id=?
Hibernate: select subitems0_.productId as producti3_3_0_, subitems0_.subitemId as subitemi1_3_0_, subitems0_.subitemId as subitemi1_3_1_, subitems0_.productId as producti3_3_1_, subitems0_.subitemName as subitemn2_3_1_ from SubItems subitems0_ where subitems0_.productId=?
Hibernate: select count(post0_.postId) as col_0_0_ from post post0_ inner join PostTag posttag1_ on post0_.postId=posttag1_.post_id inner join tagInfo taginfo2_ on posttag1_.tag_id=taginfo2_.tagId where (taginfo2_.tagName like ?) and (posttag1_.someDateField between ? and ?)
Hibernate: select taginfo0_.tagId as tagid1_2_0_, taginfo0_.tagName as tagna2_2_0_ from tagInfo taginfo0_ where taginfo0_.tagId=?
如何使用@Query将上述查询转换为单个连接获取!
解决方案已经尝试过了!!(不工作)
@Query("select po.postId as postid1_0_, po.postName as postname2_0_ from post po inner join fetch PostTag pt1 inner join fetch tagInfo t left outer join fetch PostTag pt2 where (t.tagName like :tName) and (pt1.someDateField between :sDate and :eDate) order by pt2.someDateField desc")
Page<Post> findAllInOneQuery(String tName, String sDate, String eDate, Pageable pageable);
异常:
illegal attempt to dereference collection [po.postId.posttag] with element property reference [someDateField]
发布于 2021-07-01 08:31:50
发布于 2021-07-07 07:40:57
用这个:
@Query("select po from Post po join fetch po.postTags pt join fetch pt.tag t where (t.tagName like :tName) and exists(select 1 from po.postTags pt2 where pt2.someDateField between :sDate and :eDate) order by pt.someDateField desc")
Page<Post> findAllInOneQuery(String tName, String sDate, String eDate, Pageable pageable);
https://stackoverflow.com/questions/68206264
复制相似问题