Oracle 12c IDENTITY and popular ORMs.

Since Oracle 12c came to the market with new IDENTITY options I faced few times with the cases what developers using ORM like ExclipseLink and Hibernate experienced issues. I decided to summarize something from my work with this option here.

In normal when we use similar option in MySQL 5.:

CREATE TABLE accounts (
     id INT NOT NULL AUTO_INCREMENT,
     username VARCHAR(30) NOT NULL,
     password VARBINARY(255) NOT NULL,
     email  VARCHAR(255) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=INNODB;

nothing prevents us from using standard JPA GenerationType.IDENTITY strategy both in EclipseLink and Hibernate:

package com.volt.master;

import javax.persistence.Entity;
import javax.persistence.Table;
import javax.persistence.Id;
import javax.persistence.GenerationType;
import javax.persistence.GeneratedValue;
import javax.persistence.Column;

@Entity
@Table(name="accounts", schema="dmitry")
public class Account {

	@Id
	@GeneratedValue(strategy=GenerationType.IDENTITY)
	private Integer id;
	
	@Column
	private String username;
...

We can use the following code to persist our entity and get key value, generated in auto:

       EntityManagerFactory emf = Persistence.createEntityManagerFactory("MySQLUnit");
       EntityManager em = emf.createEntityManager(); 
       em.getTransaction().begin(); 
       Account acc = new Account();
...
	acc.setUsername(username);
	acc.setPassword(byteData);
	acc.setEmail(email);
	em.persist(acc);
	em.getTransaction().commit();
	System.out.println( acc.getId() );
       em.close();
       emf.close();

 Hibernate uses the following piece of code to provide AUTO_INCREMENT column extraction:

 @Override
 public String getIdentitySelectString() {
  return "select last_insert_id()";
 }

Eclipselink uses the very same:

    @Override
    public ValueReadQuery buildSelectQueryForIdentity() {
        ValueReadQuery selectQuery = new ValueReadQuery();
        StringWriter writer = new StringWriter();
        writer.write("SELECT LAST_INSERT_ID()");
        selectQuery.setSQLString(writer.toString());
        return selectQuery;
    }

The case becomes freak when we come to Oracle IDENTITY.

EclipseLink provides the functionality what supports Oracle-specific syntax statement returning generated values of columns:

    @Override
    public DatabaseCall buildCallWithReturning(SQLCall sqlCall, Vector returnFields) {
        SQLCall call = new SQLCall();
        call.setParameters(sqlCall.getParameters());
        call.setParameterTypes(sqlCall.getParameterTypes());

        Writer writer = new CharArrayWriter(200);
        try {
            writer.write("BEGIN ");
            writer.write(sqlCall.getSQLString());
            writer.write(" RETURNING ");

            for (int i = 0; i < returnFields.size(); i++) {
                DatabaseField field = (DatabaseField)returnFields.elementAt(i);
                writer.write(field.getNameDelimited(this));
                if ((i + 1) < returnFields.size()) {
                    writer.write(", ");
                }
            }

            writer.write(" INTO ");

            for (int i = 0; i < returnFields.size(); i++) {
                DatabaseField field = (DatabaseField)returnFields.elementAt(i);
                call.appendOut(writer, field);
                if ((i + 1) < returnFields.size()) {
                    writer.write(", ");
                }
            }

            writer.write("; END;");

            call.setQueryString(writer.toString());

        } catch (IOException exception) {
            throw ValidationException.fileError(exception);
        }

        return call;
    }

Hibernate doesn’t though. OK, the time of experiments has come. Let’s take Oracle 12c and create the correspondent table (I don’t use JPA schema autogeneration feature – this is important):

CREATE TABLE accounts (
     id INT GENERATED AS IDENTITY,
     username VARCHAR(30) NOT NULL,
     password RAW(255) NOT NULL,
     email  VARCHAR(255) NOT NULL,
     PRIMARY KEY (id)
) TABLESPACE users

Try now the code where GenerationType.IDENTITY strategy is used.

       EntityManagerFactory emf = Persistence.createEntityManagerFactory("OracleUnit");
       EntityManager em = emf.createEntityManager(); 
       em.getTransaction().begin(); 
       Account acc = new Account();
	acc.setUsername("Test");
	acc.setPassword("Test".getBytes());
	acc.setEmail("Test@Test.com");
	em.persist(acc);
	em.getTransaction().commit();
	System.out.println( "New ID is "+acc.getId() );
       em.close();
       emf.close();   

We get «ORA-02289: sequence not exists.». What’s up ?

When we use Oracle provider GenerationType.IDENTITY strategy inside uses predefined sequence object SEQ_GEN_IDENTITY:

public static final String DEFAULT_IDENTITY_GENERATOR = "SEQ_GEN_IDENTITY

Sure we get a problem, because we did not create this sequence ! But – we don’t want this sequence to use, we have autogenerated ID and want to get a value back post insertion. Do we have an option ?

Yes, we have , and this option was previously used when triggers were the standard way of autogenerated keys in Oracle. This is EclipseLink-specific @ReturnInsert annotation:

    @Id
    @Column(name = "ID")
    @ReturnInsert
    private BigDecimal id;

But when we try. we get another fail:

Error Code: 32795
Call: BEGIN INSERT INTO ACCOUNTS (ID, EMAIL, PASSWORD, USERNAME) VALUES (?, ?, ?, ?) RETURNING ID INTO ?; END;
...
Internal Exception: java.sql.SQLException: ORA-32795: cannot insert into a generated always identity column

Problems are very clear – IDENTITY in it’s pure incarnation prevents the client from the explicit insertion of ID value into auto-generated ID. Very logical BTW, isn’t it ? Else how can we guarantee uniqueness ?

Now one point to remember. Oracle allows to overrode this problem using DEFAULT ON NULL option (we CAN insert null into id and OLNY in this case IDENTIRY shall work – poor idea IMHO for the reasons I described above, but it works):

CREATE TABLE accounts (
id INT GENERATED BY DEFAULT ON NULL AS IDENTITY,
username VARCHAR(30) NOT NULL,
password RAW(255) NOT NULL,
email  VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
) TABLESPACE users

In this case we get the generated ID:

New ID is 1

Fine. But I don’t want to allow clients non-consistent values insert, let’s get back to original option:

CREATE TABLE accounts (
     id INT GENERATED AS IDENTITY,
     username VARCHAR(30) NOT NULL,
     password RAW(255) NOT NULL,
     email  VARCHAR(255) NOT NULL,
     PRIMARY KEY (id)
) TABLESPACE users

And my purpose now to exclude ID from the column and value list in INSERT statement. Can I do it and how ? Yes, I can, using “returnOnly” option of the annotation:

@ReturnInsert(returnOnly=true)

Now everything works like a charm and as expected:

New ID is 1

Now – come to Hibernate.

Hibernate’s Oracle10gDialect prevents IDENTITY strategy from use at all:

Dialect does not support identity key generation

But Hibernate allows us to customize the value generator and solve this problem. Of course this requires a lot of coding – much more than in EclipseLink. There is the thread in Hibernate forum describing this methos: https://forum.hibernate.org/viewtopic.php?t=973262 — thanks  much for Jean-Pol Landrain !
I shall provide here the example what is  based purely on OraclePreparedStatement approach to illustrate how  Oracle-specific JDBC  code  can handle the problem:

package org.hibernate.id;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import oracle.jdbc.OraclePreparedStatement;

import org.hibernate.HibernateException;
import org.hibernate.dialect.Dialect;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.id.SequenceIdentityGenerator.NoCommentsInsert;
import org.hibernate.id.insert.AbstractReturningDelegate;
import org.hibernate.id.insert.IdentifierGeneratingInsert;
import org.hibernate.id.insert.InsertGeneratedIdentifierDelegate;

public class Ora12IdentityGenerator extends AbstractPostInsertGenerator {

   @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;
      private int keyId;

      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");
         }
      }

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

      @Override
      protected PreparedStatement prepare(String insertSQL, SessionImplementor session) 
              throws SQLException {
          insertSQL = insertSQL + " returning "+keyColumns[0]+" into ?";
          OraclePreparedStatement os = (OraclePreparedStatement)session.connection().prepareStatement(insertSQL);
          keyId = insertSQL.split("\\?").length;
          os.registerReturnParameter(keyId, Types.DECIMAL);
          return os;
      }

      @Override
      protected Serializable executeAndExtract(PreparedStatement insert, SessionImplementor session)
         throws SQLException {
          
        OraclePreparedStatement os = (OraclePreparedStatement)insert;
        os.executeUpdate();
      
        ResultSet generatedKeys = os.getReturnResultSet();
        if (generatedKeys == null) {
            throw new HibernateException("Nullable Resultset");
        }
        try {
           return IdentifierGeneratorHelper.getGeneratedIdentity(
                   generatedKeys, 
                   keyColumns[0],
                   getPersister().getIdentifierType());
        } finally {
            generatedKeys.close();
        }
      }
   }
}

Correspondent  changes in entry code  with @GenericGenerator Annotation should  be:

    @Id
    @Column(name = "ID")
    @Basic(optional=false)
    @GeneratedValue(generator="Identity12c")
    @GenericGenerator(name="Identity12c", strategy="org.hibernate.id.Ora12IdentityGenerator")
    private BigDecimal id;

Now  we have

New ID: 3

Another example what is Oracle-independent (in terms of JDBC library, not in syntax !) you can see here.