首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >问答首页 >如何使用JOIN FETCH完成JPQL以在一个查询中收集多个连接

如何使用JOIN FETCH完成JPQL以在一个查询中收集多个连接
EN

Stack Overflow用户
提问于 2021-07-01 07:48:42
回答 2查看 2.4K关注 0票数 1

如何使用JOIN编写JPQL,以便在一个调用中获取所有Post集合和相关标记&条目和子项,而无需从数据库中查询N+1。

实体

代码语言:javascript
运行
复制
@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关联,那么总查询将被触发。

已经试过了

代码语言:javascript
运行
复制
@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结果。

代码语言:javascript
运行
复制
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将上述查询转换为单个连接获取!

解决方案已经尝试过了!!(不工作)

代码语言:javascript
运行
复制
@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);

异常:

代码语言:javascript
运行
复制
illegal attempt to dereference collection [po.postId.posttag] with element property reference [someDateField]
EN

回答 2

Stack Overflow用户

发布于 2021-07-01 08:31:50

您需要使用多个查询来获取所有关联实体,否则您将得到一个非常低效的SQL查询。

我建议如下:

没有在“一次”中这样做的原因是,您的连接将以所有相关行的笛卡儿积结束,这也是您(想必)得到一个MultipleBagFetchException的原因。

票数 0
EN

Stack Overflow用户

发布于 2021-07-07 07:40:57

用这个:

代码语言:javascript
运行
复制
@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);
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/68206264

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档