Database: MS SQL Server 2005

Hibernate version: 3.2.0

I'm having trouble getting my one-to-one associations to load correctly from Hibernate's second-level cache. I have one particular HQL query, the results of which I'm caching using the query cache, and all of my other entities are added/loaded from the cache correctly; it's just my one-to-one associates that get added to the cache, but are being loaded from the database every time I run my HQL query (and afterward added once again to the cache, only to be ignored by subsequent executions of the same query).

I have the query cache turned on, second-level cache turned on, and I'm using EH Cache as my cache provider. Nearly everything is configured with a read-only cache strategy. I'm using Spring, and the relevant pieces of my configuration look like this:



The specific entities - an Account with a set of Registrant objects. Each Registrant has a one-to-one relationship with a RegistrantPIN object. In the database, the RegistrantPin table has RegistrantID column that has a a foreign key relationship with RegistrantID in the Registrant table.



In my application, I'm repeatedly running an HQL query that loads an Account object and forces initialization of the lazy registrants collection (via left join fetch). The Account object and the list of Registrant objects are put/loaded from the cache correctly, but the RegistrantPIN objects are coming from the database every time. Here is an excerpt from the cache log:



It's caching the RegistrantPIN object with identifier #46 again after having previously cached it 18 seconds prior. A database profiler confirms that the RegistrantPIN objects are being loaded from the database every time my HQL query is executed.

My situation seems extremely similar to another post I found in the NHibernate forums:

http://forum.hibernate.org/viewtopic.php?p=2378461

I tried setting fetch="select" on the many-to-one side as Karl suggests in that thread, but still had no luck. Just like the guy in that post, I'd like for my RegistrantPIN associations to be loaded from the second-level cache rather than the database every time a Registrant is loaded from the cache. I'm guessing it's a problem with my mapping that's causing this, but I'm pretty stuck at this point. Any ideas?

John

The 2nd level caché is one thing, the query caché is a different thing.

The 2nd level caché caches objects that you would later retrieve via load() or get() only.

The query caché is a separate mechanism, that you declare separately in your ehcache.xml, and you enable in your code setting cacheable to true.

Neither knows about the other.

OK, fair enough. But that doesn't really help me, as far as I'm aware. I used the wrong terminology, but the problem is still there. It's still a cache not acting as I would like it to.

I am setting cacheable to true, and the query cache is set up in my ehcache.xml. Like I said, every other entity in my query result is caching correctly. It's only my one-to-one assocations (there are two - I posted only one) that aren't working correctly.

What is that isn't working?
Your query caché or your 2nd level caché?

Please provide some client code example, and ehcache.xml

This behavior is actually expected for one-to-one relationships. Consider this:

1. Objects in the second-level cache are cached by primary key
2. In order to find the data for a RegistrantPIN object in the second-level cache you need to know it's primary key.
3. Loading the Account and Registrant objects doesn't give you the primary key for the RegistrantPIN object.
4. So, to find primary key, Hibernate needs to query the RegistrantPin table and locate the row with given registrantID given by the current Registrant object.
5. Since the database always has to be queried I guess Hibernate chooses the simple approach and just loads all data from the database.

One way to change this behavior is to swap the <one-to-one> and <many-to-one> sides of the relationship. Eg. add a registrantPinID column to the Registrant table. This means that in step 3 above Hibernate would know the primary key of the RegistrantPIN object and would be able to load the information from the second-level cache instead.

Thanks nordborg. That makes sense, but still seems a little strange to me. I can understand that we don't have the primary key for the RegistrantPIN object, but we do have a cached Registrant object which already has the RegistrantPIN I want stored in it. Just seems... odd to hit the database in that situation.

Unfortunately, changing the database structure isn't going to be an option at this point. Is there any other accepted way to get around this other than rolling my own caching solution?