R – queries in Hibernate polymorphism


I'm relatively new to Hibernate and am trying to migrate a relatively simple existing database to something that I can use with Hibernate, and am looking for advice on how to structure my application.

I have 3 types of entities:

  • Item
  • Category
  • PropertyType

Item and Category and PropertyType are all similar; they both have a name, description, creation date, etc., but they each have additional separate features:

  • Item has a concrete location (URL)
  • Category contains sets of Item and/or Category, in a many-to-many association between Category and Item, and an adjacency list from parent Category to child Category.
  • PropertyType is a metadata key for use in a Property table of key/value fields associated with Items, Categories, or other PropertyTypes

Right now I have Item and Category are defined in separate tables, but I'm wondering whether it would be wise to make an abstract Node entity class for which Item and Category are subclasses. PropertyType is different enough that it probably should live in its own table. (I only have 3 entries for Property where a PropertyType is applied to another PropertyType and might get rid of them if it makes things easier, so that a PropertyType always applies to a Node).

At present, my Property table contains fields for PropertyTypeId, ID (the ID of an Item, Category, or PropertyType the property applies to), id_context (discriminator for previous field: 0 = Item, 1 = Category, 2 = PropertyType), and value (arbitrary string).

Specific questions:

  1. If I do want to maintain my existing table structure (e.g. Item and Category have different tables), how do I tell Hibernate to use the id_context discriminator for determining property types? Do I have to make Item/Category/PropertyType be subclasses of a common ancestor so that when I get Property objects associated with a given PropertyType, it can return lists containing Item or Category?

  2. Should I be using Hibernate at all? I know how to make the SQL queries to do what I want, I just am having trouble thinking about how to wrap Hibernate around it.

  3. What do I have to do to nudge Hibernate into doing the queries I want? e.g. if I just want to return Property records, where PropertyType is 258, that apply to Category objects (id_context=2), I know how to do it in SQL (SELECT * FROM Property WHERE PropertyType=258 AND id_context=2) but am not sure how to do that in Hibernate.

Best Solution

What you want with the foreign-key reference and the discriminator column on the foreign key side is currently not supported by hibernate. A true Hibernate-aholic would probably argue that it is not a sound relational model either, because you cannot define a foreign-key constraint.

To model that with Hibernate, you would have to merge all three entity types or have two separate columns, one referencing the Item/Category (which would be unified) and one referencing the PropertyType: Essentially breaking up the PropertyType class in two disjunct subclasses.

You'll basically have to change your entire DB setup to fit with Hibernate.

There are reasons to move to Hibernate other then 'transparent' ORM. I think you should look elsewhere in your application to determine whether Hibernate is the right match for it: What problems are you facing? What do you want Hibernate to solve? There are numerous features that Hibernate provides that could be beneficial: - Lazy Loading - Late writes (only write to DB when TX committed) - 1st and 2nd level caching - ...