Hibernate + oracle sequence + trigger

Question

I have a table with an index auto filled by a trigger that use a sequence (Oracle database)

CREATE TABLE A
(
  IDS                           NUMBER(10)      NOT NULL
)


CREATE OR REPLACE TRIGGER A_TRG
BEFORE INSERT
ON A REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
  :new.IDS := A_SEQ.nextval;
END A_TRG;
/

I have a matching Java class:

Class A {
   @Id
   @SequenceGenerator(name = "aSequence", sequenceName = "A_SEQ", allocationSize = 1)
   @GeneratedValue(generator = "aSequence", strategy = GenerationType.SEQUENCE)
   @Column(name = "IDS")
   Long id;

   ...
}

When I try to persist an instance of A like this:

EntityTransaction transaction = entityManager.getTransaction();
transaction.begin();
A a = new A();
Long id = getHibernateTemplate().save(a);
transaction.commit();

I get this problem:

• ID in code returned by the save call = "X"

• ID in database = "X+1"

Is there a way to setup Hibernate to let the database trigger create the ID ?

Thanks

Answer 1

Reponse found at HIbernate issue with Oracle Trigger for generating id from a sequence

I need to adapt my trigger to run only if no ID is given:

CREATE OR REPLACE TRIGGER A_TRG
BEFORE INSERT
ON A REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
WHEN (New.IDS is null) -- (1)
BEGIN
  :new.IDS := A_SEQ.nextval;
END A_TRG;
/

(1) this line allow Hibernate to manually call A_SEQ.nextVal to set the ID and then bypass the trigger else Hibernate will get the nextval for uselessly because the trigger will always reset the ID calling nextval again

Answer 2

In your class B you have @GeneratedValue(generator = "preferenceSequence") which not defined in the example that you have, it should be @GeneratedValue(generator = "bSequence")

By default hibernate allocation size is 50 the B: IDS=50 seems to suggest the mapping is picking the wrong sequence.