My (mostly technical) blog

Oracle + Hibernate sequence madness!

Posted on: April 22, 2007

I have been using Hibernate + Oracle 10g express in my projects for a while now. The thing that was irritating me the most was the fact that when I add a new entry in the database through Hibernate, its automatically generated ID is not the same as I would expect.

For example, adding 3 rows should generate the following IDs:

1,2,3

Instead, I was getting this:

2,4,6!

The problem was in the trigger that queries the sequence for the next value. Although Hibernate already queries the sequence for a value, the trigger ignores this and also queries for a value so the number jumps by 2!

The solution was to modify the trigger from:

CREATE OR REPLACE TRIGGER  “BI_ROOM”
  before insert on “ROOM”              
  for each row 
begin    
    select “ROOM_SEQ”.nextval into :NEW.ID from dual;
end;

to:

CREATE OR REPLACE TRIGGER  “BI_ROOM”
  before insert on “ROOM”              
  for each row 
begin  
if :new.id is null 
    then select “ROOM_SEQ”.nextval into :NEW.ID from dual;
end if; 
end;

which says that if the ID column is not specified in the SQL query, only then go and get it from the sequence.

Advertisements

16 Responses to "Oracle + Hibernate sequence madness!"

Dear sabbour,

Thanks for shading some light on this problem
of oracle + hibernate sequence. I am using postgresql + hibernate in my application and facing same problem with
sequences as u had encountered. I am not using any triggers but still coming across same irritating problem. my sequences are getting incremented by two and in some cases even by 3. I have checked it out in my log files and have observed that hibernate is incrementing these sequences by 1 as expected. but oblivious of this fact database is also doing the same thing and incrementing the sequence by two or even by 3.
so in the absence of any triggers can you help me to work around this problem.
thanking you in anticipation.

http://forum.hibernate.org/viewtopic.php?p=2379010

Hi can u please let me know how to use session.replicate(…) with this custom class. I want to use passed ID instead of trigger being called when ID passed is not null

Thanks Jean-Pol for your implementation! I will consider it in my next project. 🙂

Hi sabbour!

thanks for posting. now i understand why the id number keeps on jumping by 2. it works fine on my project. good thing i stumbled on your blog 🙂

“Although Hibernate already queries the sequence for a value, the trigger ignores this and also queries for a value so the number jumps by 2!”

Of course the trigger ignores it – it has no idea what Hibernate is doing. Question is, why are you using a trigger at all to get the next value in the sequence if Hibernate is already taking care of incrementing it???

Thanks a lot !!!
A great piece of advice.
Cheers

@Eric:
Well that’s an interesting insight! The problem is that the trigger was generated by the Oracle designer because I wanted the field to be auto-incremented regardless of which method I use to insert records in the database.

The thing is Hibernate should have been smart enough to know that, probably through a custom generator like that one: http://forum.hibernate.org/viewtopic.php?p=2379010

Thanks, great post 😉

Man thx got me out of trouble on the fly

you should try the “allocationSize=1” parameter for sequence generator

Thanks so much, works like magic for me.

The problem is that doing what Hibernate does is really inefficient. Selecting the sequence value from dual is considered poor practice in Oracle because you are basically doing two SQL operations (get sequence, insert row) instead of one. i.e. insert into mytable( myseq.nextval, other_data). It is not good to use a trigger either as this has two overheads, the trigger itself and the select from dual, but instead write the SQL yourself in a PLSQL function using the returning clause to return the primary key if you need it. I would be interested to know if hibernate can handle this? The other thing to be aware of is sequence caching. Primary keys are not meant to be sequential (only unique) and making them so in oracle (by setting your sequence to have no cache) impacts performance in multi-user environments.

When I passed primary ID in my obuject it is getting re-set when using generator from Jean-Pol Landrain. Can someone let me know fix for this, I want ID not to be generated when passed and not NULL.

Excelent, Thanks ! I waste a lot of time on this.

Training Jobs can also be a hazard if left on, so remember to switch them off and unplug them before going to bed or are leaving your campsite.
According to the 1947project, the citizens of Los Angeles demanded goats for
training jobs last fall after the serious wildfires in the area.
It will also block heat, smoke and thermal detectors.
Let us take a look at the conservatory with its huge
dome.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Ahmed Sabbour's Facebook profile
April 2007
S M T W T F S
« Dec   May »
1234567
891011121314
15161718192021
22232425262728
2930  

Tweets

Error: Twitter did not respond. Please wait a few minutes and refresh this page.

RSS StackOverflow

Recently bookmarked

%d bloggers like this: