Hi Folks,
I'm trying to map a legacy table structure that uses a generic lookup table for lookup values. Unfortunately the way it was implemented is not optimal and is making the mapping difficult.

Here are the lookup table columns:
- lookupid (auto-generated PK)
- type (varchar type of the lookup item. ex: 'state', 'personstatus', etc)
- lookupvalue (the value of the item - should be unique within each type of list)
- description (a label for the item. ex: 'Florida', 'closed', etc)

Now, tables that are associated with lookup only store the lookupvalue, and not the type. the type is just inferred in the code.

I was going to create a composite-id for lookup that's made up of the type and the lookupvalue since those two should be unique. Then, in the mapping when another class is associated to lookup, it could pass the lookupvalue and hard-code the appropriate type. But in the <many-to-one> you can only put <column> tags which means I can't supply a hard-coded value in place of a column.

hmmm, pretty difficult to explain let alone implement. but if anyone has run into this situation, help would be much appreciated.

The perfect case for the StringValuedEnumType, http://www.hibernate.org/273.html.... assuming that the lookup tables are pretty much static. If you have to allow for new lookup values often enough that creating a new enum member and redeploying your app is prohibitive, then you need to use a composite key.

If you want to continue with hard-coding, you can do that, though it's not recommended. Replace the column attribute/element with the formula attribute/element.

thanks tenwit. I'm having trouble finding much information on the formula element (other than a brief mention in Hibernate in Action). How can I use it to supply a hardcoded value in place of a column attribute? Just like this?: <formula>status</formula>

I'm actually using a StringValuedEnumType to define the different lookuptypes in the code. In the mapping of the association, i'd like to reference one of those enum values using the formula element you mentioned. So I would end up with something like <formula>LookupType.Status</formula>. I know that on its own wouldn't work, but is there another way?

Pure SQL goes inside the formula attribute/element, hence its appropriateness when hardcoding stuff. Whatever you put in there is put directly into the end result where clause. There is no substitution of any kind.

So you could put "<many-to-one class="State" formula="name='Florida'"/>". OrYou have to use subselect for referencing other tables, even if they're already referenced elsewhere in the mapping, because you have no way of determining what alias hibernate has assigned to the other table. It's not efficient, but it is effective.