Hibernate, assigning multiple columns as primary key

Question

Hi,

I am having below database schema.

Table:items
item_id int(11) autoincrement not null
item_name varchar(22) not null
version_start_date datetime not null
version_end_date datetime

The primary key for above table is (item_id,version_start_date). The semantics of version_start_date and version_end_date is to keep track of any changes to the table row. Like if the item name is modified, then a new row is added and old row version_end_date is updated.

I tried to model this in hibernate, but composite-key doesn't allow a generator to be present as per the schema. I need generator for itemid. How do I model this in hibernate.

I am blocked on this step :( Any help will be appreciated.

Thank you Bala

-- Updated

So, finally I am thinking of below approach. I don't want to go with natural id, because I have to generate the id. Instead I am thinking of having item_version_id field also in the table.

So, the new schema will be...

Table:items
item_version_id autoincrement not null
item_id int(11) autoincrement not null
item_name varchar(22) not null
version_start_date datetime not null
version_end_date datetime
primary key(item_version_id)
unique key(item_id,version_start_date)

I am thinking this approach will work. But, I am not sure how to do this in hibernate.

Only ID element can have generator. But in this case, I am having two generators. Is there a workaround for this.

Thank you
Bala

Answer 1

You need a custom hibernate type combined with a custom id generator. I don't think there is any way around this.

this should get you started. http://stackoverflow.com/questions/495536/hibernate-id-generator

edit-- I would highly recommend you use surrogate keys, as recommended in the hibernate documentation. If you don't you risk making things a lot more difficult than they need to be.

You can still model your versioning scheme as natural keys and query off that.

edit for your update--

Evidently this is possible now (wasn't back when when we had to solve this problem)

http://opensource.atlassian.com/projects/hibernate/browse/HHH-2060

you can now specify any generator for a part of a composite key. thats pretty sweet.

I still think your best bet is to use surrogate keys to make hibernate happy and your life easy, and a natural-key to satisfy your business requirements.