HIbernate issue with Oracle Trigger to generate identifier from sequence

Question

We have a before insert trigger that gets the next value from the sequence. When an object is saved using the save () method, hibernate gets the value from the sequence and adds it to the object. and when the transaction is completed from the Spring service level, the ID value again increases in the database. how can I avoid getting nextval () if the object already has an identifier.

Here is what I am trying to do.

Userdao

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

User service

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

And 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 the line1 and line2 objects, the user object is null in the id attribute. after row2, it has the next number from the sequence - say 1. on row4, I added the user ID = 1 to the useraudit object .. when the transaction is committed after row 5, 2 is inserted in the user ID column and 1 in the UserAudit userId column. For me, this is impractical :( How can I avoid this problem? Thanks!

Answer 1

Just update the trigger so that it only fires when there is no identifier.

 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 saved me a lot of headaches on this issue.

My only problem is that it opens the door to the user / codes to insert any identifier value without actually requesting a sequence.

I found the following solution. It allows Hibernate to find the maximum identifier and increment it each time the insert statement is executed. But when it enters the database, the identifier is ignored and replaced with the one generated by the trigger, so there is no uniqueness in the cluster problem :

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

The biggest flaw - @GenericGenerator is the Hibernate annotation, so you lose portability of the JPA. It is also not clear to programmers that this identifier is actually associated with a sequence, but in fact it is one of the most complex solutions that use a sequence.

Answer 3

Ideally, you should delete BEFORE THE TERMINAL. If you do not, Hibernate is unable to find out the primary key, and he really needs this information. If you don't care about the object after INSERT, that might be okay (the second level cache is still a problem), but if you need to use it right away, this is a real problem. In the latter case, you can try this unpleasant approach:

• Tell Hibernate that you are managing the primary key yourself.
• Create a new object and put an arbitrary value in the primary key.
• Reset the Hibernate session and SELECT sequence.CURRVAL (assuming there is only one INSERT).
• Load the object using the obtained value of the current sequence and do not use the above instance.

Answer 4

 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 the world of the database.

Given the above solution, let's say sa.my_sequence.nextval is 51 and your hibernation system will work without any problems. But if someone does a direct jdbc insertion with your primary key value as 66, redefining the sequence (for example, the current value as 52), the trigger will just be inserted.

The real problem is that the sequence value is increased to 66, which will cause an exception in the trigger, which ends with the reset of the sequence value. It really ends up with a poor database schema schema structure.