CODEWITHSUNDEEP
hibernategrails
thomj
thomj

Reputation: 383

Let Oracle trigger the sequence for ID instead of Hibernate?

I have an application that has an Oracle database where each table has a trigger that calls a sequence to get the next ID for that row. How do I configure my domains so that Hibernate doesn't specify the next ID on an insert and just lets Oracle and the trigger handle it?

Previously I had my domains setup to use the sequence generator and this seemed to work. I can't use this on our production systems because the DBA's will not let me grant select privileges to the sequence for Grails. This obviously breaks the generator for Hibernate.

Upvotes: 1

Views: 2496

Answers (2)

schmolly159
schmolly159

Reputation: 3881

There is this Hibernate forum topic, which discusses a custom generator using the generatedKeys from a JDBC insert to retrieve the id: https://forum.hibernate.org/viewtopic.php?t=973262

If that custom generator works for you you should be able use it via:

static mapping = {
    id generator:"jpl.hibernate.util.TriggerAssignedIdentityGenerator"
}

Upvotes: 5

Amir Pashazadeh
Amir Pashazadeh

Reputation: 7312

You can't, there are following scenarios when using a sequence support database and a autonumber support database:

  1. Sequence Support

    1. Hibernate fetches sequence's next value
    2. Hibernate sets the fetched value as ID of your entity
    3. Hibernate persists the entity, with it's ID being set in the query

  2. Auto-number support

    1. Hibernate persists the entity, without ID value
    2. Hibernate fetches assigned ID from JDBC driver

As a matter of fact Oracle's driver does not support step 2.2, so Hibernate can't retrieve the last assigned ID (so it can sets it in ID of your entity).

Upvotes: 0

Related Questions