Hibernate SequenceGenerator returning incorrect nextval

Question

This is my second post about hibernate. Since I started using hibernate it has given more problems than the problems it has solved. It almost makes me feel like I should have stuck to plain old JDBC. Anyway,

Here is one of the problems I'm trying to battle.

My Sequence generator in the .hbm files looks as follows.

<id name="id" type="long" column="ID">
    <generator class="sequence">
        <param name="sequence">ADVENTURES_ID_SEQ</param>
        <param name="allocationSize">1</param>
        <param name="initialValue">17599</param>
    </generator>
</id>

Please note: initial value is 17599. This is because LAST_NUMBER in the oracle sequence is: 17599

CREATED         25-APR-12
LAST_DDL_TIME   25-APR-12
SEQUENCE_OWNER  ADVENTURE_ADMIN
SEQUENCE_NAME   ADVENTURES_ID_SEQ
MIN_VALUE       1
MAX_VALUE       9999999999999999999999999999
INCREMENT_BY    1
CYCLE_FLAG      N
ORDER_FLAG      N
CACHE_SIZE      20
LAST_NUMBER     17599

When I run the code I see the next sequence generated as 200, 201 in the Hibernate Debug statement.

DEBUG SQL - select ADVENTURES_ID_SEQ.nextval from dual
DEBUG SequenceGenerator - Sequence identifier generated: 201

I expected the nextval should have been 17600. Seems like the oracle sequence is not getting used at all.

What is wrong in my configuration and how to fix it. Any help is greatly appreciated.

Thanks

Answer 1

Self Answer (Workaround): I'm still seeing the issue but for now I have a workaround. Since it worked for me I'm selecting the question as complete.

I'm letting Oracle generate the nextId using Trigger:

create or replace
TRIGGER ADVENTURE_ADMIN.ADVENTURES_ID_TRIG 
BEFORE INSERT ON ADVENTURE_ADMIN.ADVENTURES FOR EACH ROW
WHEN (new.ID IS NULL)
BEGIN
    SELECT ADVENTURES_ID_SEQ.NEXTVAL INTO :new.ID FROM DUAL;  
END;

Then I'm letting hibernate use the Oracle Trigger generated ID. There are two ways of doing this.

First is by using Hibernate select. Disadvantage of this approach is you will need another column in the table with unique constraint which is used by the hibernate as a key to fetch the row. It doesn't really work for me as I have tables with primary key as the only unique key.

Second is by using TriggerAssignedIdentityGenerator created by Jean-Pol Landrain, Martin Zeltner. Source could be found here. This has enabled me to get around the problem of finding another unique key for the table.

Following is how I'm using it:

<id name="id" type="long" column="ID">
    <generator class="org.hibernate.id.TriggerAssignedIdentityGenerator" />
</id>

Please Note: Hibernate version you are using matters. I'm using 3.5.4. hibernate 3.6 didn't work for me. JDBC and Oracle Driver version also matters. You can refer to the documentation in the source file for the same.