Rocket Surgery

I ran into a “gotcha” with Hibernate this past week.

I was working with an Oracle database where there are tables with synthetic (aka surrogate) keys that get their values from sequences — a fairly common thing, and a situation that Hibernate normally handles well with identity generator class of “sequence” or “native”.

But in this case, the Oracle database was using a before-insert trigger to get nextval from the sequence and stuff it into the primary key field before inserting the row into the table. The rationale for this was to ensure consistent management of the sequence for other code than the Hibernate/Java system that might hit the database.

Astute readers will recognize, of course, that if a database trigger establishes the value of the primary key outside application control, the application must read back the record immediately after insert in order to know the value of the primary key of the record it just inserted. Hibernate provides a generator class called “select” just for this purpose, that performs a select to read back the primary key field of the just-inserted row.

And here arises the gotcha - how do you isolate the record you just wrote? The Hibernate documentation states that the select identifier generator class assumes that the synthetic key is acting as a surrogate for a natural key - fair enough, since it is in the documentation, but it’s a pain. This means that any table where triggers are pulling values from a sequence to get primary keys must also maintain some other alternate candidate key that can be used in a select statement to uniquely isolate a row.

But the real problem goes beyond this: Hibernate’s select generator class does not support composite natural keys. So consider a table where the fields are ID, Parent, and Child. The Parent and Child together make up the natural key as a composite key, and the ID field is a synthetic surrogate key whose values are automatically populated on insert by a trigger. Because Hibernate cannot select the ID field based on a combination of two fields, and neither Parent nor Child on their own can uniquely identify a record, we are in a lousy situation.

So what are our choices? We could add another surrogate key field for purposes of reading the record back to get the real one - ick. We could write our own read-back routine with a query after inserts, but seemed like it defeated the purpose of Hibernate We could write our own implementation of the select generator that supports multiple fields - which might actually be the best long term solution, but one I didn’t have time for. Even then, extending/replacing the select generator still might not work in some situations, since the other columns in the table even in combination might not uniquely identify a row.

In my particular case however, we decided to abandon the trigger and let the application manage the sequence. After evaluating our situation it did not seem likely that there will other systems doing much work so we were able to make this call.

This entry was posted on Sunday, November 12th, 2006 at 2:50 pm and is filed under Software development.You can follow any responses to this entry through the RSS 2.0 feed.You can leave a response, or trackback from your own site.