Avoiding n+1 selects with Cached Hibernate Associations or Caching Collections as a whole

Question

I've got a one-to-many relationship: Parent record with n Child records. These records are frequently used and read-only and are good candidates for caching.

Here is an approximation of my Hibernate mapping:

`<class name="Parent" table="Parent>
   <cache usage="read-only"/>
   <id name="primary_key"/>
   <property name="natural_key"/>

   <set name="children" lazy="false" fetch="join">
      <cache usage="read-only"/>
      <key-column name="parent_id"/>
      <one-to-many class="Child"/>
   </set>
</class>

<class name="Child" table="Child">
   <cache usage="read-only"/>
   <id name="primary_key"/>
   <property name="parent_id"/>
</class>`

I frequently fetch the Parent by a natural key, rather than a primary key, so I need to enable Query Caching in order to take advantage of the 2nd Level Cache (I use ehcache).

Here's the problem: when I fetch a Parent and get a hit in the query cache, it becomes a "fetch by primary key" query. This is fine for the "one" end of my one-to-many. If the Parent is not found in the cache, it is fetched from the DB. If my n Child records are not found in the cache, Hibernate fetches them using n subsequent select queries. N+1 select problem.

What I want is a way to cache the collection of Child objects, keyed by the parent_id. I want Hibernate to look for my collection in the cache as a whole, rather than as a bunch of individual records. If the collection is not found, I want Hibernate to fetch the collection using 1 select statement - fetch all Child with parent_id=x.

Is this too much to ask from Hibernate + ehcache?

Answer 1

I found my own answer - it is possible to configure Hibernate + ehcache to do what I described above.

By declaring my Child as a value-type rather than an entity type (I believe these are the terms that the Hibernate community uses), I can essentially treat my Child as a component of Parent rather than a separate entity. Here's an example of my revised mapping:

<class name="Parent" table="Parent">
   <cache usage="read-only"/>
   <id name="primary_key"/>
   <property name="natural_key"/>

   <set name="children" lazy="false" fetch="join" table="Child">
      <cache usage="read-only"/>
      <key-column name="parent_id"/>
      <composite-element class="Child">
         <property name="property1" column="PROP1" type="string">
         <property name="property2" column="PROP2" type="string">
      </composite-element>
   </set>
</class>

The behaviour of my Child objects is a bit different under this configuration than it was previously - there is no separate primary key defined for Child now, no shared references, and no nullable fields/columns. See the Hibernate docs for more detail on this.

My Parent and Child are both read-only and I really only want to access instances of Child through a Parent - I don't use Child independent of Parent, so the value-type treatment is a good fit for my use case.

The big win for me was how the collection is cached under my new configuration. The collection cache now caches my collection as a whole, keyed by parent_id. It is no longer possible for some, but not all, of my collection to be in the cache. The collection is cached and evicted as a whole. More importantly, if Hibernate goes looking for my collection in the 2nd Level Cache and gets a miss, it fetches the whole collection from the DB with a single select query.

Here is my ehcache configuration:

 <ehcache>
    <cache name="query.Parent"
        maxElementsInMemory="10"
        eternal="false"
        overflowToDisk="false"
        timeToIdleSeconds="0"
        timeToLiveSeconds="43200" 
    </cache>
    <cache name="Parent"
        maxElementsInMemory="10"
        eternal="false"
        overflowToDisk="false"
        timeToIdleSeconds="0"
        timeToLiveSeconds="43200" 
    </cache>
    <cache name="Parent.children"
        maxElementsInMemory="10"
        eternal="false"
        overflowToDisk="false"
        timeToIdleSeconds="0"
        timeToLiveSeconds="43200" 
    </cache>
<ehcache>

Hopefully this example helps someone else.