How to deal with a legacy schema when using Oracle Before Insert Trigger for ID generation in Hibernate

We are using Oracle. For a given table we have a primary key which is set by a trigger before an insert is performed, for example:

CREATE OR REPLACE TRIGGER Table_name_TRG
BEFORE INSERT
ON Table_name REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
 :new.ID := Sequence_name.nextval;
END Table_name_TRG;

The trigger simply calls a sequence to get the nextval which is set as the primary key.

First approach

Here is a possible Hibernate mapping document:

 <class name="Entity_name" table="Table_name">
 <id name="ID">
 <generator class="sequence">
 <param name="sequence">Sequence_name</param>
 </generator>
 </id>
 ...
 <property name="name"/>
 ...
 </class>

Now we can dissable the trigger, and all things are going well.

BUT…. Imagine that our application must live together with a legacy system, that relies on the trigger to generate the table id, when writing to this table. Now the things have changed, we can’t disable the trigger.

Second approach

We can use the same Hibernate mapping document, and slightly modify the trigger, for not using the sequence if an id has been previously generated

CREATE OR REPLACE TRIGGER Table_name_TRG
BEFORE INSERT
ON Table_name REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
 if :new.ID is null then 
 :new.ID := Sequence_name.nextval;
 end if; 
END Table_name_TRG;

The trigger works well with the legacy system now, because the trigger is incrementing the sequence at insert time, due to the ID null value. And the trigger is going to perform properly with our application too, because hibernate is going to generate the ID by itself, and the trigger is doing nothing due to the ID not null value.

BUT…. how about if we are not allowed to modify the legacy schema? We can’t disable or modify the trigger. This is the hardest situation, and we have found (found, not invented ehhh) the solution.

Third approach

The solution was well documented by Jean-Pol Landrain ( @jplandrain ) on Dec 2004 Before Insert Trigger and ID generator

But it is not easy to find. We have tried it and it works perfectly.

The key is to code your own (Jean-Pol’s own) identity generator (TriggerAssignedIdentityGenerator)

package eu.albertomorales.hibernateIntro.persistency.dao.core.hibernate;

	import java.io.Serializable;
	import java.sql.PreparedStatement;
	import java.sql.SQLException;

	import org.hibernate.HibernateException;
	import org.hibernate.dialect.Dialect;
	import org.hibernate.engine.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;

	/**
	* A generator with immediate retrieval through JDBC3
	* {@link java.sql.Connection#prepareStatement(String)}. The value of the identity column must be
	* set from a "before insert trigger"
	* This generator only known to work with newer Oracle
	* drivers compiled for JDK 1.4 (JDBC3). The minimum version is 10.2.0.1
	* Note: Due to a bug in
	* Oracle drivers, sql comments on these insert statements are completely disabled.
	*
	* This class dos not use the method
	* {@link java.sql.Connection#prepareStatement(String, String[]) getGeneratedKeys} because with this
	* method the driver wants get the return type form the database meta. If the application user is
	* not the shemaowner, this is not possible. This is the reason why the ReturnParameter is hard set
	* to Long.
	*
	* @author Jean-Pol Landrain
	* @author Beat Sager
	*/
	public class TriggerAssignedIdentityGenerator extends AbstractPostInsertGenerator {

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

	    public static class Delegate extends AbstractReturningDelegate {
	        private final Dialect dialect;

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

	        public IdentifierGeneratingInsert prepareIdentifierGeneratingInsert() {
	            NoCommentsInsert insert = new NoCommentsInsert(dialect);
	            return insert;
	        }

	        protected PreparedStatement prepare(String insertSQL, SessionImplementor session) throws SQLException {
	            return session.getBatcher().prepareStatement(insertSQL, keyColumns);
	        }

	        protected Serializable executeAndExtract(PreparedStatement insert) throws SQLException {
	            insert.executeUpdate();
	            return IdentifierGeneratorHelper.getGeneratedIdentity(insert.getGeneratedKeys(), getPersister().getIdentifierType());
	        }
	    }
	}

and to use it on our Hibernate mapping file

 <class name="Entity_name" table="Table_name">
 <id name="ID"><generator class="eu.albertomorales.hibernateIntro.persistency.dao.core.hibernate.TriggerAssignedIdentityGenerator" /></id>
 ...
 <property name="name"/>
 ...
 </class>

 


Would you like to show us another FAQ? Have you got an interesting one, and would you like to share it with the comunity? If so, or you’re interested in receiving more information, please post them in the comments so we can write about it, and we can keep in touch!

And as always, if you’ve liked this post, you can it. Thank you.

Deja un comentario