How do I annotate Id column if value is generated by a trigger?

Question

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

The actual question is: How do I get the current value of the Id in my entity after a EntityManager.persist?
I tried:

@Id
private long id;

-> id is 0;

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

-> id is 0;

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

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

IDs are generated in the database in the first two approaches, but I don't have the value in my object.

Answer 1

Contrary to @JB Nizet's comments, I can actually think of many reasons why we'd let a trigger assign IDs: execution of stored procs, manual execution of SQLs and running native queries in Hibernate, just to name a few.

I personally found the following solution quite satisfactory. It lets Hibernate find the max ID and have it incremented every time an insert statement is called. But when the statement hits the database, the ID is ignored and overridden 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.

Another alternative is to modify the trigger to only increment sequence when ID is null. See "Hibernate issue with Oracle Trigger for generating id from a sequence". In most cases, this is the best solution because it clearly shows the ID is linked to a sequence. My only gripe is it gives user/hibernate the option to insert any ID without actually inquiring the sequence in the first place.

Answer 2

Unless you are bound to the trigger, that seems like a level of obfuscation over the sequence, and it seems to go outside the normal Hibernate lifecycle. Why not directly 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 will get the value back, as Hibernate is directly involved in the generation, and there isn't something happening after the fact.

Answer 3

You can exchange the db generated key within the Oracle session. For this, the insert trigger must call

dbms_session.set_identifier(new_id)

From your code, you can retrieve the new key value with the query

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 for persisting the new entity and retrieving the generated key value.