Criteria eager fetch-joined collection to avoid n+1 selects

Question

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();                        

Answer 1

This criteria query seems right:

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

FetchMode.JOIN is meant to solve n+1 problem. Have you defined some default_batch_fetch_size | batch-size anywhere in the mapping or configuration, which is reverse impacting?

If not, can you please try below HQL and see this solves your problem?

 Query query = 
      session.createQuery("from Item it left join it.bids b where b.amount=:bids");
 query.setParamter(bids, 100);
 List<Item> items = query.list();