Criteria预先获取连接集合以避免n + 1个选择 [英] Criteria eager fetch-joined collection to avoid n+1 selects

问题描述

假设项目和投标是实体：一个项目有很多投标。它们映射到典型的父/子关系中的 Hibernate ：

 < class name =Itemtable =ITEM> 
 ... 
< set name =bidsinverse =true> 
< key column =ITEM_ID/> 
<一对多等级=出价/> 
< / set> 
< / class> 
  

如何在试图访问每个商品的出价后避免n + 1选择执行？

  List< Item> items = session.createCriteria（Item.class）
 .createAlias（bids，b）。 
 .add（Restrictions.gt（b.amount，100））。 
 .list（）; 
  

注意我需要提取出价但 对集合进一步限制 （b.amount> 100）

我试过了以下失败：

  List< Item> items = session.createCriteria（Item.class）
 .setFetchMode（bids，FetchMode.JOIN）。 
 .createAlias（出价，b）。 
 .add（Restrictions.gt（b.amount，100））。 
 .list（）; 
 
列表<项目> items = session.createCriteria（Item.class）
 .createCriteria（bids）
 .add（Restrictions.gt（amount，100））。 
 .list（）; 
  

解决方案

这是为什么在fetch-joined collection会导致集合未初始化（ note ：没有限制的相同查询会产生对集合的渴望获取）：

如果你在表A和B之间有一个1：n的关系，并且你给B添加了一个限制并且想要急切地获取A和B，那么当你想要导航时会发生什么从A到B.
如果您只看到B中与限制匹配的数据，或者应该看到所有与A有关的B？ 请参阅更多...

但是，使用HQL代替标准，部分获取出价集合

  List< Item> items = session.createQuery（
from item i left join fetch i.bids b where b.amount> amount）
 .setParameter（amount，100）
 .list （）; 
  

在我看来，这是不一致的，但它是如何工作的 List< Item> items = session.createQuery（
from Item i left join fetch i.bids b+
where not exist（from Bid b where b.item = i and b.amount< = amount ））
.setParameter（amount，100）
.list（）;


这是一篇相关文章： Hibernate查询不返回完整对象。

Let's say Item and Bid are entities: an Item has many Bids. They are mapped in Hibernate in a typical parent/child relationship:

<class name="Item" table="ITEM">
  ...
  <set name="bids" inverse="true">
    <key column="ITEM_ID"/>
    <one-to-many class="Bid"/>
  </set>
</class>

How can I avoid n+1 selects when trying to access the bids of each Item after this query is executed?

List<Item> items = session.createCriteria(Item.class)
                        .createAlias("bids", "b").
                        .add(Restrictions.gt("b.amount", 100)).
                        .list();

Note I need an eager fetching for bids but with a further restriction on the collection (b.amount > 100)

I've tried the following unsuccessfully:

List<Item> items = session.createCriteria(Item.class)
                        .setFetchMode("bids", FetchMode.JOIN).
                        .createAlias("bids", "b").
                        .add(Restrictions.gt("b.amount", 100)).
                        .list();                        

List<Item> items = session.createCriteria(Item.class)
                        .createCriteria("bids")
                        .add(Restrictions.gt("amount", 100)).
                        .list();                        

解决方案

This is an explanation of why adding a restriction on the fetch-joined collection causes the collection was not initialized (note that the same query without the restriction produce an eager fetch for the collection):

"If you have a 1:n relation between tables A and B, and you add a restriction to B and want to fetch A and B it eagerly, the question would be what happens when you want to navigate from A to B. Should you only see the data in B that matches the restriction, or should you see all Bs that are related to A?" see more here ...

However, using HQL instead of criteria, fetch partially the bids collections

List<Item> items = session.createQuery(
          "from Item i left join fetch i.bids b where b.amount > :amount")
          .setParameter("amount", 100)
          .list();

It seems to me an inconsistency, but it's how this works

By the way, if what you need is the list of parents and all its children, but just the parents whose children all meet certain restriction, so you may use this

List<Item> items = session.createQuery(
          "from Item i left join fetch i.bids b " +
          "where not exists (from Bid b where b.item = i and b.amount <= :amount)")
          .setParameter("amount", 100)
          .list();

This is a related posts: Hibernate query not returning full object.

这篇关于Criteria预先获取连接集合以避免n + 1个选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！