Hibernate is such a cool product. I’ve been using it a few years now but I was a bit slow starting because I didn’t connect the name with the function. I mean, something to do with going to sleep? Never mind it is excellent. Just in case you’re still wondering what it is Hibernate lets you work with Java objects and looks after saving and fetching those objects to/from a relational database. But you probably knew that.

Naturally there are ways to map the database tables against the Java objects and, while this can be simple enough, sometimes it is complex and today was one of those times.

The problem I hit today concerned an existing (think legacy) database. I really wanted to avoid changes to the db structure, or even the data. The area of interest concerned two objects: ProdCatalog and ProdCatSection. ProdCatSection extends ProdCatalog.

These both map to the same table. Now, Hibernate handles this nicely enough. You define a discriminator column and have two discriminator values, one for each object, so Hibernate knows how to instantiate the objects from the row. All the examples I found for this assumed there was a common super class that two concrete classes extend, but if you have two concrete classes like I have it works just fine.

I put this in the top of the ProdCatalog class definition:

@Entity
@Table(name = "PROD_CATALOG", schema = "PRODUCT2")
@DiscriminatorValue(value="ProdCatalog")
@DiscriminatorColumn("recordType")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
public class ProdCatalog implements java.io.Serializable,ProductCatalogMember {

In the ProdCatSection I only need this:

@Entity
@Table(name = "PROD_CATALOG", schema = "PRODUCT2")
@DiscriminatorValue(value="ProdCatSection")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
public class ProdCatSection extends ProdCatalog

That would work… except that there is no recordType in the table. No, the guys who put this database together decided to keep the discriminator in another table, called PROD_CAT_STRUCTURE.

Even worse, this doesn’t have one simple field. There is a column in PROD_CAT_STRUCTURE that may be null, indicating we have a ProdCatalog and not null indicates a ProdCatSection. There can be multiple records in the join as well.

Now, Hibernate does not seem to have a way of saying @DiscriminatorValue(value=null) and, anyway, I need a null and and ‘anything else’. The answer is to use the DiscriminatorFormula

@DiscriminatorFormula("case when exists (select 1 from PROD_CAT_STRUCTURE pcs
    where pcs.CAT_ID = CAT_ID and pcs.CAT_ID_PARENT is null)
    then 'ProdCatalog' else 'ProdCatSection' end")

The formula is just SQL (Oracle’s dialect in this case) and this formula will return ProdCatalog or ProdCatSection depending on the result of the query. I could test this using my usual SQL tools to verify the query was valid (though I had to edit a literal into the second CAT_ID for that).

Now that the formula (rather than DiscriminatorColumn) is being used we have the right values coming back and the DiscriminatorValue annotation can work. The names I chose for these can, of course, be anything at all, but it made sense to me to use the object names I hoped to create. The actual requirement is that the results of the formula matches the DiscriminatorValue values. The formula only needs to go on the ProdCatalog class.

Part of the exercise of getting this working involved generating the classes using the Hibernate tools, specifically the Eclipse plugin. I have had trouble installing this in the past and went abut it with a bit more determination than before. I’m using Eclipse 3.5 (Galileo). Invoking the update manager (help->install new software) seemed to work but, once it had finished, there was no sign of any Hibernate in my Eclipse workspace.

So I did the download and unzip approach. The version I used was 3.2.4.v200910211631N-H194-GA and that worked just fine.