aspose file tools*
The moose likes Object Relational Mapping and the fly likes why my oracle sequence id increases by 2 when I use hibernate to insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Reply locked New topic
Author

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

Raj Ohadi
Ranch Hand

Joined: Jun 30, 2006
Posts: 316
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
Rancher

Joined: Mar 06, 2001
Posts: 13459

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
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

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


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Raj Ohadi
Ranch Hand

Joined: Jun 30, 2006
Posts: 316
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
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

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
Ranch Hand

Joined: Jun 30, 2006
Posts: 316
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
Ranch Hand

Joined: Mar 04, 2006
Posts: 98
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
Ranch Hand

Joined: Jul 22, 2003
Posts: 55
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:

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!


--BJCK--
Chris Hendy
Ranch Hand

Joined: Mar 04, 2006
Posts: 98
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
Greenhorn

Joined: Nov 04, 2006
Posts: 5
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;



SCEA,SCWCD,OCDJWS,SCJP
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

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
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: why my oracle sequence id increases by 2 when I use hibernate to insert