why my oracle sequence id increases by 2 when I use hibernate to insert

[Raj Ohadi]

I created table in oracle and a trigger that automatically insert the next sequence id into the table when we isnert a row. From SQL plus, the sequence id increases by 1 everytime when I isnert a row there. But when I used hibernate I found when I insert a object, the sequence id increments by 2. Why does it behave differently from sql plus insert ?

[David O'Meara]

The first question is: does it matter?

But have you set up Hibernate to manage IDs and sequences? If so, Hibernate will be getting an id, and then inserting which fires your trigger and then gets updated. Just a guess.

[Mark Spritzler]

Originally posted by David O'Meara:
The first question is: does it matter?

But have you set up Hibernate to manage IDs and sequences? If so, Hibernate will be getting an id, and then inserting which fires your trigger and then gets updated. Just a guess.

I was about to say the same thing.

Mark

[Raj Ohadi]

Here is what I set in my hibernate:

<id name="id" column="ID" type="long">
<generator class="sequence">
<param name="sequence">STUDENT_SEQ</param>
</generator>
</id>

And in my POJO "Student.java" class I have

public class Student {
private Long id;
private String name;
private String address;

// getter and setter
}

In my Oracle database I have a "student_seq" sequence. Does this cause the problem ? But how can I fix it ? I thought I have to sepcify this in hibernate, right ?

Please help.

Thanks.

[Mark Spritzler]

How about removing that insert trigger. That is where we are saying is the second increment of the ID.

Hibernate is incrementing it one in the beginning, then your trigger you mentioned is taking the next number, and 1+1 = 2.

Mark

[Raj Ohadi]

Originally posted by Mark Spritzler:
How about removing that insert trigger. That is where we are saying is the second increment of the ID.

Hibernate is incrementing it one in the beginning, then your trigger you mentioned is taking the next number, and 1+1 = 2.

Mark

I made that insert trigger (actually oracle automatically creates it when you uses its table creation tool) to let any SQL insert convenient without having to get NEXT_VAL. But that really causes the double increment. I still like to keep that insert trigger because we may have other non java code access database to use stright SQL to insert.

Now, do you think it is a problem when we sometimes have the sequence id increment by 2 (hibernate), and sometimes increment by 1 (using SQL), for the same table ?? I don't think it actually matters. Hope you could provide your opinion.

Thanks.

[Chris Hendy]

Either remove your setting of the id in hibernate and let the database trigger handle it under all circumstances.

Or change the trigger to get the next_val from the sequence and assign it to the column only if :new.ID IS NULL

[Bruno Frascino]

Originally posted by Chris Hendy:
Either remove your setting of the id in hibernate and let the database trigger handle it under all circumstances.

Or change the trigger to get the next_val from the sequence and assign it to the column only if :new.ID IS NULL

Hello there,
I have got a similar problem, this database can't be touched, Oracle, so they have sequences for each id. And I am doing reverse engineering, so I at first I did:

<table name="TABLE_ABC" >
<primary-key >
<generator class="ORACLE_SEQ_NAME"></generator>
<key-column name="ID" property="id" />
</primary-key>
</table>

But in the documentation says that I should use only these options:

AUTO - either identity column, sequence or table depending on the underlying DB
TABLE - table holding the id
IDENTITY - identity column
SEQUENCE - sequence

And I heard something about using "native" also. Now I am confused, what I need is let the database take care of my table ID, hibernate shouldn't care about it. Which option?
Thanks!

[Chris Hendy]

I can't claim to be a Hibernate expert - Oracle is more my bag - but a bit of googling shows many discussions saying that Hibernate and Oracle insert triggers setting a pk from a sequence don't work together.

Question : why can't Oracle be touched. Unless it is a third party product with a support agreement preventing code change, you (or the database developers or the DBA) should be able to amend the trigger as suggested above. If it is third party code you shouldn't be trying to execute DML against it via Hibernate.

If it is impossible to change the trigger, then your app should still work but the pk value will appear to increment by 2 for each insert.

[Vincent Zhen]

Hi, change your trigger as following(add a "if :NEW.STUDENT_ID is null " statement, then when hibernate increasing the sequence by 1, oracle trigger will not do it again, but other application can still use the trigger to get a increasing by 1 ID
):

CREATE OR REPLACE TRIGGER TRG_STUDENT_INSERT BEFORE INSERT ON STUDENT
FOR EACH ROW
BEGIN
if :NEW.STUDENT_ID is null
then SELECT STUDENT_SEQ.NEXTVAL INTO :NEW.STUDENT_ID FROM DUAL;
end if;
END;

[Mark Spritzler]

Originally posted by Vincent Zhen:
Hi, change your trigger as following(add a "if :NEW.STUDENT_ID is null " statement, then when hibernate increasing the sequence by 1, oracle trigger will not do it again, but other application can still use the trigger to get a increasing by 1 ID
):

CREATE OR REPLACE TRIGGER TRG_STUDENT_INSERT BEFORE INSERT ON STUDENT
FOR EACH ROW
BEGIN
if :NEW.STUDENT_ID is null
then SELECT STUDENT_SEQ.NEXTVAL INTO :NEW.STUDENT_ID FROM DUAL;
end if;
END;

Vincent. this is almost a year old thread.

Mark