fetch = subselect

We have seen that the default fetch strategy for lazy Collections can result in an N+1 problem. We saw how to improve this using the batch size setting. Here also however the query count can end up being a little high.
Hibernate also allows all the Collections to be retrieved in a single query when a single collection is accessed. Thus as opposed to a reduced query count that we get via batching, in this case it is exactly 1 additional query.
The change in the Shelf.hbm file is as below:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.collection.smart">
    <class name="Shelf" table="SHELF">
        <id name="id" type="integer">
            <column name="ID" />
            <generator class="native" />
        </id>
        <property name="code" type="string">
            <column name="CODE" length="50" not-null="true" />
        </property>
        <set name="allBooks" cascade="all-delete-orphan" inverse="true" 

             fetch="subselect" >
            <key column="SHELF_ID" not-null="true" />
            <one-to-many class="Book" />
        </set>

    </class>
</hibernate-mapping>

I executed the same load query from our previous example

public static void testBatchedLoad() {
    Session session = sessionFactory.openSession();
    Transaction transaction = session.beginTransaction();
    System.out.println("Testing Query select");
    session = sessionFactory.openSession();
    transaction = session.beginTransaction();
    @SuppressWarnings("unchecked")
    List<Shelf> shelfs = session.createQuery("from Shelf shelf where" +
               " shelf.id <= 5 ").list();  
    for (Shelf shelf : shelfs) {
        System.out.println("shelf class is " + shelf.getClass());
        Set<Book> books = shelf.getAllBooks();            
        System.out.println("The Collection class is " + shelf.getAllBooks().getClass());
        System.out.println("Total books is " + books.size());
    }
        
    transaction.commit();
    session.close();
}

The logs are as below:

Testing Query select
Hibernate: 
    /* 
from
    Shelf shelf 
where
    shelf.id <= 5  */ 

    select
        shelf0_.ID as ID1_,
        shelf0_.CODE as CODE1_ 
    from
        SHELF shelf0_ 
    where
        shelf0_.ID<=5
shelf class is class com.collection.smart.Shelf
The Collection class is class org.hibernate.collection.PersistentSet
Hibernate: 
    /* load one-to-many com.collection.smart.Shelf.allBooks */ 

 select
        allbooks0_.SHELF_ID as SHELF3_1_,
        allbooks0_.ID as ID1_,
        allbooks0_.ID as ID0_0_,
        allbooks0_.Name as Name0_0_,
        allbooks0_.shelf_id as shelf3_0_0_ 
    from
        BOOK allbooks0_ 
    where
        allbooks0_.SHELF_ID in (
            select
                shelf0_.ID 
            from
                SHELF shelf0_ 
            where
                shelf0_.ID<=5
        )
Total books is 2
shelf class is class com.collection.smart.Shelf
The Collection class is class org.hibernate.collection.PersistentSet
Total books is 1
shelf class is class com.collection.smart.Shelf
The Collection class is class org.hibernate.collection.PersistentSet
Total books is 1
shelf class is class com.collection.smart.Shelf
The Collection class is class org.hibernate.collection.PersistentSet
Total books is 1

As can be seen above Hibernate generated only two queries. In the second query it fetched the collection for all shelves. The query was fired when we tried to get the size of the books collection for the first shelf. As of Hibernate 3.0, this fetch strategy is available for Collections only.
If you think that even two queries is one too many, fear not. Hibernate also offers a one query solution.