-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
 Post subject: ID generated by trigger with multiple key properties
PostPosted: Fri Sep 30, 2005 5:39 am 
Newbie

Joined: Fri Sep 30, 2005 5:16 am
Posts: 2
Location: Belgium
Hibernate version: 3.0.5
Database Oracle 9:

I'm having the following problem:

I want to insert records in a database that is using triggers to generate an ID for any records that are inserted to it.

The trigger itself is using a sequence to generate the ID. So using a sequence with Hibernate does not work, since hibernate will have the wrong ID of the record after the record insertion (the sequence is incremented by hibernate first, and then by the trigger).

I don't have a unique value to put inside the select id generator:

<generator class="select">
<param name="key">Unique value</param>
</generator>

I do have a unique value if I combine several Table fields.

I've had a quick look at the org.hibernate.id.SelectGenerator and I did not see a possability to set more then one key field, so it doesn't let me
define this.

Is there any way to resolve this (except from beating up the DBA untill he wants to drop the trigger)?

A select generator that can take a composite key would be nice. Does somthing like that exist?

Luc


Top
 Profile  
 
 Post subject:
PostPosted: Fri Sep 30, 2005 7:29 am 
Beginner
Beginner

Joined: Tue Sep 20, 2005 4:32 am
Posts: 29
Location: Cluj-Napoca
What I did in this case was to change the trigger like this:

CREATE OR REPLACE TRIGGER TRG_BI_LGU
BEFORE INSERT ON LoginUser
FOR EACH ROW
BEGIN
IF :NEW.loginUserId IS NULL OR :NEW.loginUserId = 0 THEN
SELECT SEQ_LGU.NEXTVAL INTO :NEW.loginUserId FROM DUAL;
END IF;
END;
/

So, the trigger will generate the id just in the case the Hibernate will set a null or 0 value for id.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Sep 30, 2005 10:22 am 
Newbie

Joined: Fri Sep 30, 2005 5:16 am
Posts: 2
Location: Belgium
A very good suggestion.
But saidly engough, the DBA doesn't want to change the trigger, since he can not oversee the impact on other applications.

We ended up adding a timestamp field, which i set from within my code. Then I can use the select id generator on that timestamp field.

But then I ended up with JDBC driver problems with the timestamp (oracle 8 JDBC driver, but had to switch to 9.2). I finally got that solved as well and everybody's happy now.

I still think that it would be nice to have more then one key field on the select ID generator though.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Sep 18, 2007 12:11 am 
Newbie

Joined: Mon Sep 17, 2007 11:42 pm
Posts: 16
Location: Auckland, New Zealand
I have the exact same problem. I would like to be able to specify more than one column for the select key like this:

<generator class="select">
<param name="key0">col4</param>
<param name="key1">col6</param>
<param name="key2">col9</param>
</generator>

as i do not have a unique natural column by itself.
I cannot change the schema.

to work around this, it seems I will have to manually re-query the db for my object, using my composite natural keys, in order to find my generated id.

either that, or use sequence.CURRVAL+1 with the "sequence" generator type, but there's no option for that either.

Curb that - more trawling of the forums turned this up:
http://forum.hibernate.org/viewtopic.php?t=973262
works like magic!


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.