Mixed surrogate composite key insert in JPA 2.0, PostgreSQL and Hibernate 3.5
- by Gerald
First off, we are using JPA 2.0 and Hibernate 3.5 as persistence provider on a PostgreSQL database.
We successfully use the sequence of the database via the JPA 2.0 annotations as an auto-generated value for single-field-surrogate-keys and all works fine.
Now we are implementing a bi-temporal database-scheme that requires a mixed key in the following manner:
Table 1:
id (pk, integer, auto-generated-sequence)
validTimeBegin (pk, dateTime)
validTimeEnd (dateTime)
firstName (varChar)
Now we have a problem. You see, if we INSERT a new element, the field id is auto-generated and that's fine. Only, if we want to UPDATE the field within this scheme, then we have to change the validTimeBegin column WITHOUT changing the id-field and insert it as a new row like so:
BEFORE THE UPDATE OF THE ROW:
|---|-------------------------|-------------------------|-------------------|
| id| validTimeBegin | validTimeEnd | firstName |
|---|-------------------------|-------------------------|-------------------|
| 1| 2010-05-01-10:00:00.000 | NULL | Gerald |
|---|-------------------------|-------------------------|-------------------|
AFTER THE UPDATE OF THE ROW happening at exactly 2010-05-01-10:35:01.788 server-time:
(we update the person with the id:1 to reflect his new first name...)
|---|-------------------------|-------------------------|-------------------|
| id| validTimeBegin | validTimeEnd | firstName |
|---|-------------------------|-------------------------|-------------------|
| 1| 2010-05-01-10:00:00.000 | 2010-05-01-10:35:01.788 | Gerald |
|---|-------------------------|-------------------------|-------------------|
| 1| 2010-05-01-10:35:01.788 | NULL | Jerry |
|---|-------------------------|-------------------------|-------------------|
So our problem is, that this doesn't work at all using an auto-generated-sequence for the field id because when inserting a new row then the id ALWAYS is auto-generated although it really is part of a composite key which should sometimes behave differently.
So my question is:
Is there a way to tell hibernate via JPA to stop auto-generating the id-field in the case I want to generate a new variety of the same person and go on as usual in every other case or do I have to take over the whole id-generation with custom code?
Thanks in advance,
Gerald
