Mixed surrogate composite insert in JPA 2.0, PostgreSQL and Hibernate 3.5
First, we are using JPA 2.0 and Hibernate 3.5 as our continuity provider in PostgreSQL database.
We successfully use the database sequence via JPA 2.0 annotations as the auto-generated value for the single-pole surrogate keys and everything works fine.
We will now implement a two-time database schema that requires a mixed key as follows:
Table 1:
id (pk, integer, auto-generated-sequence)
validTimeBegin (pk, dateTime)
validTimeEnd (dateTime)
firstName (varChar)
Now we have a problem. You see, if we are a INSERT
new item, then the field id
will be automatically generated and fine. Only if we want a UPDATE
field inside this schema, then we have to change the column validTimeBegin
without changing the field id
and insert it on a new row like this:
BEFORE UPDATING A ROW:
|---|-------------------------|-------------------------|-------------------|
| id| validTimeBegin | validTimeEnd | firstName |
|---|-------------------------|-------------------------|-------------------|
| 1| 2010-05-01-10:00:00.000 | NULL | Gerald |
|---|-------------------------|-------------------------|-------------------|
AFTER ROW UPDATE happening 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 it doesn't work at all using the auto-generated sequence for the field id
, because when a new row is inserted, the ALWAYS id is auto -generated , even though it is indeed part of which should sometimes behave differently.
So my question is: Is there a way to tell hibernate via JPA to stop the automatic creation of the field id
in the event that I want to create a new flavor of the same person and continue as usual in every other case or I need to take over the full generating id with custom code?
Thanks in advance, Gerald
a source to share
So ... It's been a while without comment, and now I'm sure there is no way to do this. This is actually one of the reasons why developers start to create their own unique keys for databases through their own application-side ID generators. Thus, the database never knows about such a thing as a surrogate key; He just gets it.
By the way: We solved our problem by implementing our own methods for the task of updating such rows. These methods are now OR-Mapper dependent and are in no way compatible with JPA 2.0, but JPA simply does not support annotation for this behavior.
a source to share
AFAIK your ID field is not an identifier in the sense that Hibernate understands it. If I can suggest a different solution, given the fact that essentially you just want to implement database change logging:
- Step 0: use only ID as the primary key in hibernation. Use a composite primary key in Postgres.
- Step 1: add hibernate filters that automatically filter all rows with validEndTime set.
- Step 2: Add a custom update statement to hibernate which inserts instead.
- Step 3: Add a trigger to PostgreSQL that automatically sets the end time.
- Step 4. Create a bounded index for postgres for fast hibernation searches.
a source to share