Hibernate: How To load one-to-many collections using a custom query

Recently, on one of the user groups, one of my colleague posted a question about loading one-to-many collections.  His requirement was quite unique compared to the stock standard one-to-many collections.  They were using Hibernate as the ORM tool.

The Requirement:

I will try and explain the requirement using an example.

• Let's say there are entities that need to store a set of attributes. 
  
• Attribute's are nothing but (key, value) pairs.  
• Attributes could be associated with any class that needs to have attributes.  
• For example, Image can have attributes like what is its dimension, what is its resolution etc.  
• While Video might need to save information like, what is its length and format.

We could argue here that, both Image and Video are Asset's and Asset can have Attribute's.  However, the point I am trying to make here is, there could be a totally unrelated class that needs to save attributes, for example we could have Attribute's associated with a Car class.  There is really nothing common between Image and a Car.

Hence, for the scope of this post we will assume that attributes could be associated with almost any entity and these entities are not related to each other in any way.

So far so good, the unique part was how they saved the parent entity reference.  Let's have a look at some sample data:

Showing how information will be saved using the ATTRIBUTE_IDENTIFIER column

Notice the ATTRIBUTE_IDENTIFIER column value?

Yes, that's the most interesting part.  To identify which attribute is associated with which entity the reference is stored in the following format:

<Full Class Name of Entity>:<Entity ID>

Weird? Yes, Weird but very interesting!

If we were to design the system from scratch then, obviously we would map the table a little differently but more often than not, we really have to live with what we have in hand.  So given the fact that we cannot alter the schema or store the information in any different way, challenge was to map the Attribute class with Image and Video entities so that we can achieve the desired result?

So much to clarify the requirement, phew!

How do they do it!

I had to look around a bit and try out a few things before I could find the solution for this requirement.

Short Story:

Use the custom SQL query to load the one-to-many collection entity.

Long Story:

Without wasting any more time, let's look at the code.  The Image and Video entity classes would look as follows

They are mostly stock standard classes but a few things to notice:

• They implement a convenience interface called AttributeProvider.  This interface is purely for convenience reasons its actually not really required (The code for AttributeProvider class is also shown above).  
• Both Image and Video class have a collection of Attribute entities (i.e. they both have a one-to-many relationship with Attribute entity)
• The method addAttribute adds the Attribute instance to the collection and sets the back reference to the parent entity in the Attribute class.  We will see how Attribute class handles this back reference in the next section.

 The Attribute class would look as follows:
There are a few things worth noticing about this class:

• It does not map the parent entity (i.e. Image or Video), it declares a reference to AttributeProvider interface but marks it as @Transient.  This instance is only needed when we generate the value of attributeIdentifier for the first time while saving the Attribute via cascading effect.
• It has a property called attributeIdentifier this will hold the value that will uniquely identify the entity associated with this Attribute.
• The getter for attributeIdentifier implements the logic needed to generate the identifier.
• It first check if the property attributeIdentifier is not null, if so then, return that value
• Else check if AttributeProvider is not null (i.e. the transient object), if so then, construct the attributeIdentifier in the <Full Class Name of Entity>:<Entity ID> format.
• In all other cases return null

The entities are done, lets look at the mapping hbm.xml file for these entities

The mapping file for Image would look something like this:
Note that:

• Everything else looks extremely common, only part that might be a little unique is the <loader /> tag
• We are specifying a query-ref called loadImageAttributes in the loader tag.  This informs Hibernate that, we want to load this one-to-many collection use the query identified by name "loadImageAttributes" 
• The key column specified in the mapping is called "ENTITY_ID".  Remember this column name, its going to play an important role in the next part.

The mapping file for Video:
Here the name of the loader query-ref is "loadVideoAttributes" and that is the only difference between the two mappings.

The mapping for Attribute:
Wow! this one has no mention of any of the parent entities, it only maps its basic properties without any relations.  Moreover, we didn't notice the mapping for the column "ENTITY_ID" (remember this column was mapped as the key column for the one-to-many association between Image-Attribute and Video-Attribute relationships).

How will the relationship between Image-Attribute and Video-Attribute work without this column?

The real magic happens in the loader queries that we are about to write.  The loader query for "loadImageAttributes":
Few interesting things about this query:

• Role attribute of <load-collection /> tag needs to point to the collection which will be loaded using this query.  In our example we want to load the Image.attributes collection.
• In addition to the other columns in the select clause we added another derived column called ENTITY_ID.
• This column is the same column that we used while mapping the one-to-many association between Image and Attribute.
• This column value is derived by removing the first 34 characters from the ATTRIBUTE_IDENTIFIER column
• Why did we remove 34 characters?  How did we reach to this number?
• Let's recollect how the Attribute is stored.  The ATTRIBUTE_IDENTIFIER column will have the value like com.gitshah.hibernate.test.Image:1.  
• To map it to an Image we need the Image ID.  The Image ID is stored after 34 characters (i.e. after "com.gitshah.hibernate.test.Image:" whose length is 34 characters) in the ATTRIBUTE_IDENTIFIER column
• Hence, to get the entity ID for Image entity we strip off first 34 characters from the value stored in ATTRIBUTE_IDENTIFIER column.
• The where condition constructs the ATTRIBUTE_IDENTIFIER value using the formula <Full Class Name of Entity>:<Entity ID>
• We only know the Full Class Name of the Entity (in this example com.gitshah.hibernate.test.Image) and ID of each Image instance would differ, because of this, we cannot construct the value of ATTRIBUTE_IDENTIFIER completely.  
• We let Hibernate fill in the ID value of the Image for us at the run time using a named parameter :imageId.  
• At run time when Hibernate needs to load Attribute's for Image with ID=9, it will automatically bind the named parameter :imageId to the value 9.

We are almost done.  Let's query look at the "loadVideoAttributes" query.
It looks almost exactly like the previous query, on change is all references of Image have been replaced by Video.

That's it!  We are all set to roll.  Lets test this out.
If we run the above code we would see the following queries.
As expected the information is saved correctly.

Next test will try to fetch the Image and Video and print their attributes
This test simply loads all the AttributeProvider's and prints the attributes associated with them.  If we run the above test we should see an output similar to this:
That's all folks!  We have achieved the desired result.

PS: I tried doing this with @Loader Annotation but looks like there is a bug in Hibernate because of which it throws an NullPointerException.  But the fact remains, that something as unique as this requirement was possible using Hibernate without too much trouble is totally AWESOME!

+1 for Hibernate!

NHibernate throws InvalidCastException in DEBUG mode for entities with composite keys

Recently we faced a very weird issue with NHibernate.  The issue was, on one developer's machine, when we ran an Integration test to fetch an entity (using its primary key), NHibernate threw an InvalidCastException exception.
The issue was *not* reproducible on any other developer's machine.  This got me thinking, there must be some issue with the mappings which is causing NHibernate to throw the exception.  The developer must have made some local changes to the mapping files, that is causing the exception.

Investigation:

I tried various things to figure out what was causing the problem.  Here is the list of things that I tried.

Check mapping files for modifications:

I checked and rechecked the mapping files for any local modifications, but, No, there was no local modification.  I even checked-out all the mapping files just to be sure that there was no modification.  But still no luck, the issue was reproducible on her machine and not on any other machine.

Tried to reproduce the issue on my machine by connecting to other developers Database:

After the first failed attempt, next thought that crossed my mind was, may be, there was something wrong with entity data which is causing the exception.  I decided to run the same test on my machine by connecting to other developers database.

Surprisingly, I was still not able to reproduce the issue.  This was totally weird.  I was 99% positive that, issue would get reproduced once I connect to the order developers database.

I was running out of options.  How do proceed further?  What should be the next step?

Stick to basics - Debug with Elimination:

I was back to the drawing board.  Decided to use Elimination to narrow down the issue. The question that was bothering me was, Whats so special about the entity we are trying to load?

The entity we were trying to load was called EmpDept, it represents the Employee Department information (actual names of the entities have been changed).  So what was so special about this table?

This table was a legacy table, it did not have a primitive primary key, it had a composite key made of UserID and DeptID.

So what?  A lot of legacy databases have composite keys, NHibernate works well when it comes to mapping the composite keys, in fact, NHibernate has no problems loading the same entity on my machine!

I decided to, removed all other mapped properties and just kept the mappings for the composite key and try to fetch the entity.  This time, it did fetch the entity without any problems on the other developers machine!

We were starting to get somewhere now!

I started adding back the mappings to find out which one was causing the issue.  Finally, we did find the minimal possible mapping that would reproduce the issue.

The Cause:

We deduced that, the issue occurs when the following conditions are satisfied

• Entity is mapped with composite primary key
• Entity has a one-to-many relationship with other entity 
• The one-to-many relationship is not based on the composite primary key, instead its with property-ref 
• And the data type of property-ref is different from the id property 

Wow!  I know, those are too many conditions to be true at one time!

Let's look at the minimal mapping file that reproduced the issue
What do we have here?  We have a very simple mapping between two entities.

• Entity EmpDept is mapped to a table called EmpDepts
• EmpDept uses a composite primary key to class EmpDeptIdentifier
• The composite primary key consists of UserID and DeptID
• EmpDept entity as another property called Email
• There is a one-to-many relationship between EmpDept and Task entity i.e. One EmpDept can have Many Tasks
• Note that the relationship is not based on the composite primary key
• The relationship is based on a non identifier property called Email
• The data type of the Email property is string while the data type of the composite primary key is EmpDeptIdentifier 

By now, it must be clear that all of the required conditions for NHibernate to throw an exception have been satisfied, because of this NHibernate throws the InvalidCastException.

So far we had figured out, what conditions were required for NHibernate to throw the exception.  However the real mystery was, Why was the exception occurring only on one developers machine?  Why not on other developers machines?

The Enlightenment:

Our curiosity levels were at all time high!  We decided to debug the NHibernate code to figure out why was exact same code working perfectly fine on one machine and failing on other.

After some intense NHibernate code debugging we found the NHibernate code that was throwing the exception.
Notice the line that throws the InvalidCastException in the above code?  This is where the exception occurs. 

The above method is called to build a string that will be used for logging.  We traced back the call to this method, it was getting called from the MessageHelper.InfoString method, which in-turn was getting called from the LoadContexts.LocateLoadingCollection method.

We now knew the place from where the exception was thrown, but we still didn't know why it was thrown only on one machine. 

Looking through the NHibernate code we realized that, the method MessageHelper.InfoString is only called if NHibernate is running with DEBUG log level.

That ladies and gentlemen, was the "Aha Moment!" for us.  This was the missing piece of the puzzle.   The developer on whose machine the test was failing was running NHibernate at log level DEBUG, while I was running the test at log level INFO.  Because of this the method MessageHelper.InfoString was never invoked on my machine and thus no exception!

We Googled a little and found that there is an official bug reported for this scenario.  

Eventually it turned out that, what we initially though as a big issue was really a non issue, since NHibernate will not be configured in DEBUG mode 99% of the time (because it logs hell lot of information).  The issue we were facing is very unlikely in real systems.  Although it was not such a big issue after all, this experience taught us quite a few things about NHibernate!