HIbernate issue with Oracle Trigger for generating id from a sequence

Question

We have a before insert trigger that gets the next value from sequence. When the object is persisted with save() method, hibernate gets the value from the sequence and adds it to the object. and when the transaction is committed from Spring's service layer, the ID value is again increased on the database. how do I avoid getting nextval() if the object already has an id..

Here is what I ma trying to do..

UserDao

public User saveUser(User user){
      session.getCurrentSession.save(user);//line2
      return user;//line3  
 }

UserService

public void saveUserAndWriteToAudit(User user, UserAudit userAudit){
  userDao.saveUser(user);//line1
  userAudit.setUserId(user.getId);//line4
  userAudit.saveUserAudit(userAudit);//line5
}

And the User Class

 @Entity
  public class User{

     @Id
     @GeneratedValue(strategy=GenerationType.AUTO, generator="a1")
     @SequenceGenerator(name="a1", sequenceName="usersequence")
     private Long id;
     /////////////////
 }

When the cursor reaches line1 and line2 user object has null in id attribute. after line2, it has nextval from sequence - lets say 1. on line4, i have added user's id=1 to useraudit object.. when transaction is committed after line 5, 2 is inserted into User's id column and 1 into UserAudit's userId column. This serves no purpose to me :( How do I avoid this issue? Thanks!

Answer 1

Just update your trigger to only fire when not given an id.

create or replace
trigger sa.my_trigger
before insert on sa.my_table
for each row
when (new.id is null)
begin
   select sa.my_sequence.nextval
    into :new.id
    from dual;
end;

Answer 2

The above solution is great, it's saved me a lot of headaches on this problem.

My only gripe is it opens the door to user/codes to insert any ID value without actually inquiring the sequence.

I've found the following solution also works. It lets Hibernate find the max ID and have it incremented every time an insert statement is executed. But when it hits the database, the ID is ignored and replaced by the one generated by the trigger, so there's no uniqueness in a cluster problem:

    @Id
    @GeneratedValue(generator="increment")
    @GenericGenerator(name="increment", strategy = "increment")
    private Long id;

The biggest drawback is @GenericGenerator is a Hibernate annotation, so you lose JPA's portability. It's also not clear to the programmers that this ID is actually linked to a sequence while in fact, it's one of the tightest coupled solutions that use a sequence.

Answer 3

create or replace
trigger sa.my_trigger
before insert on sa.my_table
for each row
when (new.id is null)
begin
   select sa.my_sequence.nextval
    into :new.id
    from dual;
end;

this is something really wrong from database world.

Considering above solution, say sa.my_sequence.nextval is 51 and your hibernate framework will work without any issues. But if someone makes direct jdbc insert with your primary key value as 66 overriding the sequence (say, current value as 52), trigger will just insert.

The real issue is when sequence value incremented to 66, which will raise an exception in the trigger ending in resetting the sequence value. This really end in bad structure of schema design from database side.

Answer 4

Ideally you would remove the BEFORE INSERT TRIGGER. If you don't, Hibernate has no way of knowing the primary key, and it really needs that information. If you don't care about the object after the INSERT that might be okay (2nd level cache is still an issue), but if you need to use it right away it's a real problem. In the latter case, you could try this nasty approach:

1. Tell Hibernate that you manage the primary key yourself.
2. Create the new object and put an arbitrary value into the primary key.
3. Flush your Hibernate session and SELECT sequence.CURRVAL (assuming there is only one INSERT).
4. Load the object using the obtained current sequence value and don't use the above instance.