9

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.

Zeemee
  • 10,486
  • 14
  • 51
  • 81

3 Answers3

7

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.

Community
  • 1
  • 1
Christopher Yang
  • 3,769
  • 4
  • 30
  • 27
  • Please have a look at this example: http://developer-should-know.com/post/82479486933/how-to-use-oracle-before-insert-trigger-for-id It shows how to implement a custome hibernate generator that retrieves the id post insert. – Paul A. Trzyna May 22 '17 at 15:42
  • For me this returned id 94 after persist when the actual id in database created by trigger was 90. – Panu Haaramo Jan 04 '19 at 11:48
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.

atrain
  • 9,139
  • 1
  • 36
  • 40
  • Thank you, but: The trigger thing is the Oracle way to automatically generate incrementing PK values. If I get rid of that, I will loose the possibility to insert rows easily with direct queries on the database. And second, if I annotate like in your suggestion, I cannot switch easily to Derby for unit tests :(... What I need is an annotation that says: "Just take what the DB has generated". – Zeemee Aug 18 '11 at 13:51
  • Googled this point, and based on a cursory examination, it appears that other users have had success ref'ing Oracle sequences from Hibernate. Here's an example: https://forum.hibernate.org/viewtopic.php?p=2442821. On the Derby issue, unit testing against a DB platform that is different from your deployment platform is not a best practice and could lead to inconsistencies between unit testing and runtime behavior. – atrain Aug 18 '11 at 14:04
  • 2
    @Mulmoth: Unless there is another field uniquely identifying the row, how would Hibernate do to get the generated ID back from the database, since the only way of identifying the row that has just been inserted is its ID, that it doesn't know. In short, the ID is needed to get the ID. Your trigger is a really bad idea. – JB Nizet Aug 18 '11 at 14:10
1

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.

Frank
  • 11
  • 1