Quick GORM tip: trigger based primary key generation

GORM is a relational database to object mapper. To work properly it needs to know the primary key for each table.

The primary key uniquely (and quickly) identifies a single record from a table.

GORM (and Hibernate as the underlaying framework) supports many ways to identify a record in your favorite database.

 

Some legacy databases may use trigger-generated primary keys. This however is not supported by Hibernate / GORM by default.

The first solution would be to use generator type ‘assigned’ (no generator). However this does not work properly for inserts since no primary key will be given.

Also unit testing will not work, since the naive GORM testing database implementation requires a simple primary key at all times.

Since I’d like simple, readable unit tests, this needs a solution.

 

There are several identity key choices

Identity can be expressed in database tables in multiple ways, such as:

• technical, simplest to use, not very recognizable to humans
• composite, usually the complete record describes itself uniquely
• natural, a identifier from ’the real world’ like a postal code

 

The are many primary key generation strategies

To have an id, one needs to be generated somewhere. This can be done is several ways, in different places.

The following are standard options with GORM:

• auto increment, a database feature
• sequence, an simlilar alternative to auto increment
• assigned, this usually means the application needs to create the key (natural key usually)
• UUID: the preferred options nowadays, create a unique UUID typed id (usually from the application)

 

Some background on how GORM handles ids

• implicit id field, the (technical) id field is in every entity, but not explicitly
• reading is straight forward, key generation is irrelevant
• writing is only possible with a ‘working’ primary key

 

The thing with triggers

In our case Oracle triggers.

• triggers are pieces of code that ‘live’ in the database
• they apply either pre or post commit of a transaction

What if the trigger happens after the commit to generate the id?

Hibernate does not ‘see’ the id since it wasn’t in the transaction!

 

This problem should have a simple solution

• Fixed by implementing a custom key generation strategy
• Changed an example implementation to work with oracle 12

The proof is in the code.

 

GORM Domain class mapping

static mapping = {
    id column: "MY_ID", generator: 'nl.first8.TriggerAssignedIdentityGenerator'
}

 

The custom identity generator

package nl.first8

import java.sql.PreparedStatement
import java.sql.ResultSet
import java.sql.SQLException

import org.hibernate.HibernateException
import org.hibernate.dialect.Dialect
import org.hibernate.dialect.Oracle12cDialect
import org.hibernate.engine.spi.SessionImplementor
import org.hibernate.id.AbstractPostInsertGenerator
import org.hibernate.id.IdentifierGeneratorHelper
import org.hibernate.id.PostInsertIdentityPersister
import org.hibernate.id.SequenceIdentityGenerator.NoCommentsInsert
import org.hibernate.id.insert.AbstractReturningDelegate
import org.hibernate.id.insert.IdentifierGeneratingInsert
import org.hibernate.id.insert.InsertGeneratedIdentifierDelegate

/**
 * Provides a strategy to obtain the id generated by an Oracle (12c) trigger after insert.
 */
@SuppressWarnings("deprecation")
public class TriggerAssignedIdentityGenerator extends AbstractPostInsertGenerator {

   /**
    * {@inheritDoc}
    */
   @Override
   public InsertGeneratedIdentifierDelegate getInsertGeneratedIdentifierDelegate(
      PostInsertIdentityPersister persister, Dialect dialect, boolean isGetGeneratedKeysEnabled)
      throws HibernateException {
      return new Delegate(persister, dialect);
   }

   public static class Delegate extends AbstractReturningDelegate {

      private Dialect dialect;
      private String[] keyColumns;

      public Delegate(PostInsertIdentityPersister persister, Dialect dialect) {
         super(persister);
         this.dialect = dialect;
         this.keyColumns = getPersister().getRootTableKeyColumnNames();
         if (keyColumns.length > 1) {
            throw new HibernateException(
               "trigger assigned identity generator cannot be used with multi-column keys");
         }
      }

      /**
       * {@inheritDoc}
       */
      @Override
      public IdentifierGeneratingInsert prepareIdentifierGeneratingInsert() {
         return new NoCommentsInsert(dialect);
      }

      /**
       * {@inheritDoc}
       */
      @Override
      protected PreparedStatement prepare(String insertSQL, SessionImplementor session) throws SQLException {
         return session.connection().prepareStatement(insertSQL, keyColumns);
      }

      /**
       * {@inheritDoc}
       */
      @Override
      protected Serializable executeAndExtract(PreparedStatement insert, SessionImplementor session)
         throws SQLException {
         insert.executeUpdate();
         ResultSet generatedKeys = insert.getGeneratedKeys();
         try {
            return IdentifierGeneratorHelper.getGeneratedIdentity(generatedKeys, keyColumns[0], 
               getPersister().getIdentifierType(), new Oracle12cDialect());
         } finally {
            generatedKeys.close();
         }
      }
   }
}

This strategy reads the key field(s) from the record just after the insert finishes and generates the ‘identity’ object to return to the application code.

 

The extensibility of the Hibernate framework is super useful for these kinds of edge cases!