2

I have an entity which is persisted in an Oracle schema. On DB insert, a trigger generates an entity ID by using a DB sequence. Using the trigger is mandatory since it also updates another table beside the ID generating sequence (kind of log table) which is important for legacy components.

How to configure the Hibernate id generator in my entity mapping?

Looking around in similar Stackoverflow questions, I found some solutions which do not fit in my case:

  1. Using the sequence directly: <generator class="sequence">...</generator> . This is not possible because the rest of trigger code would not be executed
  2. Using <generator class="select">...</generator> which means that after inserting, Hibernate selects the entity using another unique property (according to Hibernate-3.3 manual 5.1.4.6. ). This is also not possible here because there is no other unique column and using a set of properties is not supported.
  3. Using <generator class="assigned">...</generator> and setting a fake ID before calling save() . This id would be ignored by the DB trigger and the resulting DB row would have the correct ID. However, my Java instance wouldn't have and this solution appeals ugly since it makes assumptions on the implementation of the trigger.

Is there a good solution for this problem?

rainer198
  • 3,195
  • 2
  • 27
  • 42
  • whats with identity? it returns the inserted id – Firo Feb 08 '12 at 16:21
  • 1
    Something sounds iffy with that DB trigger. It should be written to only get a sequence value if one is not provided in the insert, e.g. `IF :new.pk_id IS NULL THEN SELECT my_seq.NEXTVAL INTO :new.pk_id FROM DUAL; END IF;` - then the rest of the trigger can use the ID, whether it was provided by the trigger or by Hibernate. – Jeffrey Kemp Feb 10 '12 at 06:32
  • OK, sorry for never having answered this: @Firo: `identity` is not supported for Oracle (http://www.roseindia.net/hibernate/hibernateidgeneratorelement.shtml) – rainer198 Jul 09 '12 at 15:30
  • For clarification: the trigger pulls an id from a sequence which is used for multiple tables (thus the IDs among these tables are unique). Further, the trigger registers the new entity in a table, let's say `Entity`, with PK `entityId`. Thus, the application cannot assign an ID and bypass the trigger, since this could disturb the uniqueness. So, @Jeffrey, your rewritten trigger is not a solution for my problem. – rainer198 Jul 09 '12 at 15:35
  • Finally, I ended up using `assigned` and let the trigger overwrite this manually assigned ID. This was OK for my case since I did not need the real ID or that entity for the further processing. – rainer198 Jul 09 '12 at 15:57

2 Answers2

0

Generating ID and updating other tables should be separate tasks. You can use the sequence directly and only leave "updates other tables" code in the trigger.

Any particular reason why this can't be done?

jva
  • 2,797
  • 1
  • 26
  • 41
  • At least, the trigger has to be executed since it updates another table (see my comment of today). On the other hand, I cannot change the trigger code since other applications are expecting that the trigger is calling the sequence. – rainer198 Jul 09 '12 at 15:41
  • You can change the trigger to only fill ID if it is NULL. Then it can be also used by other applications. – jva Jul 09 '12 at 16:09
0

<generator class="select"> is the more correct solution. The fact that you cannot select back the auto-generated value just shows a problem with ypu model and choice of generator IMHO. You can try using <generator class="sequence-identity">, but as I mention in the javadocs (org.hibernate.id.SequenceIdentityGenerator(), in my experience support for getGeneratedKeys in Oracle drivers is quite spotty, or at least was back a few years ago when i first developed and tested that generator.

Steve Ebersole
  • 9,339
  • 2
  • 48
  • 46
  • Well, as I described, I have no other unique column which could be used for this subsequenz select and a combination is not supported. – rainer198 Jul 09 '12 at 16:00
  • A combination *is* supported. Mark the properties you want to use as the unique key for subsequent look up as @NaturalId and the select generator will automatically use that for generated primary key retrieval. My point was simply that this, again IMHO, is a bad database set up. And my second point was that you are missing option number (4) in your list which would be to use `` – Steve Ebersole Jul 09 '12 at 18:37
  • @SteveEbersole Under Hibernate 3.6.10, attempting to use the `select` generator with multiple columns annotated as `@NaturalId` produces `org.hibernate.id.IdentityGenerationException: select generator does not currently support composite natural-id properties; need to specify [key] in generator parameters`. Can you clarify which version of Hibernate supports this generator strategy with a composite natural key? – Tim Feb 03 '15 at 16:08