How to annotate Id columns if a value is generated by a trigger?

Question

I have setup with Oracle XE 10g, Hibernate 3.5, JPA 2.0. There is a simple table with a primary key that is generated by the database trigger upon insertion. A trigger gets a value from a sequence. The trigger / sequence design was made by Oracle XE.

The real question is: how do I get the current Id value in my object after EntityManager.persist?
I tried:

@Id private long id; 

-> id 0 ;

 @Id @Generated(GenerationTime.ALWAYS) @Column(insertable = false, updatable = false) private long id; 

-> id 0 ;

 @Id @GeneratedValue(strategy=GenerationType.AUTO) private long id; 

-> Fails because Hibernate is trying to directly request a sequence (which does not exist).

Identifiers are generated in the database in the first two approaches, but I have no value in my object.

Answer 1

Contrary to the comments of @JB Nizet, I really can think of many reasons why we allowed trigger triggering identifiers: executing stored procedures, manually executing SQL queries and executing custom queries in Hibernate, to name a few.

I personally found the following solution quite satisfactory. It allows Hibernate to find the maximum identifier and increment it each time the insert statement is called. But when the operator enters the database, the identifier is ignored and redefined by the generated 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.

Another alternative is to change the trigger only to increase the sequence when the identifier is NULL. See " Hibernation issue with Oracle Trigger to generate an identifier from a sequence ." In most cases, this is the best solution, because it clearly shows that the identifier is associated with a sequence. My only problem is that it gives the user / sleeper the ability to insert any identifier, without actually requesting the sequence in the first place.

Answer 2

If you are not connected to a trigger, this is similar to the level of obfuscation above the sequence and seems to go beyond the normal Hibernate life cycle. Why not immediately call the sequence:

 @SequenceGenerator(name="alias_for_my_sequence", sequenceName="seq_name_in_oracle") @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="alias_for_my_sequence") @Id private Long id; 

Then you get the value back, since Hibernate is directly involved in the generation, and nothing happens after that.

Answer 3

You can exchange the generated db key in an Oracle session. To do this, the insert trigger must call

 dbms_session.set_identifier(new_id) 

From your code you can get a new key value with a request

 String sql = "SELECT sys_context('USERENV', 'CLIENT_IDENTIFIER') FROM dual"; Query query = em.createNativeQuery(sql); String new_id = (String) query.getSingleResult(); 

It is important to use the same EntityManager instance to save the new object and retrieve the generated key value.