Custom Inserts With Hibernate

Goal:

This article will present three techniques performing database insertions from Hibernate using sequence-generated primary keys. It is a continuation of a previous post, which describes techniques for calling Oracle stored procedures and functions for performing updates from Hibernate. Similar techniques may be used to perform custom inserts. These, however, present an interesting wrinkle in cases where the primary key for a row is generated directly by a database sequence. In order to keep the article current and to present an alternate build system, the project structure and libraries referenced are slightly different than those of the previous article in that Spring 3.0 and Maven2 are used. The use of Maven2 should simplify project configuration and library management.

Setup a New M2 Project For Hibernate

We can first create a simple M2 project in Eclipse, skipping archetype selection. We need not be concerned about the packaging and deployment of our code since we can verify our three approaches to Hibernate inserts with JUnit tests. We will also want to disable the project-specific Java Compiler settings to use Java 1.5 compliance for Hibernate annotations. The current default compliance for the M2 plug-in is 1.4.
We can configure our build for Spring, Hibernate, JUnit and Hibernate's dependencies. Because our examples rely on Oracle, and because licensing issues prevent the distribution of the Oracle JDBC jars from the central Maven2 repository, we can install the ojdbc14 driver in our local Maven2 repository and add it as a dependency in the project configuration.
To install the driver, we will run the following snippet from the command-line
mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc14 -Dversion=10.2.0.2.0 -Dpackaging=jar -Dfile=/path/to/ojdbc14.jar

Our pom.xml should look like this:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>timezra.blog.hibernate.custom_insert</groupId>
    <artifactId>timezra.blog.hibernate.custom_insert</artifactId>
    <name>timezra.blog.hibernate.custom_insert</name>
    <version>0.0.1-SNAPSHOT</version>
    <description>Hibernate Inserts Example</description>
    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>org.springframework.transaction</artifactId>
            <version>3.0.0.M2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>org.springframework.orm</artifactId>
            <version>3.0.0.M2</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-annotations</artifactId>
            <version>3.4.0.GA</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.5.2</version>
        </dependency>
        <dependency>
            <groupId>javassist</groupId>
            <artifactId>javassist</artifactId>
            <version>3.4.GA</version>
        </dependency>
        <dependency>
            <groupId>cglib</groupId>
            <artifactId>cglib</artifactId>
            <version>2.2</version>
        </dependency>
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.2.2</version>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc14</artifactId>
            <version>10.2.0.2.0</version>
        </dependency>
        <dependency>
            <scope>test</scope>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.5</version>
        </dependency>
        <dependency>
            <scope>test</scope>
            <groupId>org.springframework</groupId>
            <artifactId>org.springframework.test</artifactId>
            <version>3.0.0.M2</version>
        </dependency>
    </dependencies>
    <repositories>
        <repository>
            <id>SpringSource Enterprise Bundle Repository - External Bundle Milestones</id>
            <url>http://repository.springsource.com/maven/bundles/milestone</url>
        </repository>
        <repository>
            <id>SpringSource Enterprise Bundle Repository - SpringSource Bundle Releases</id>
            <url>http://repository.springsource.com/maven/bundles/release</url>
        </repository>
        <repository>
            <id>SpringSource Enterprise Bundle Repository - External Bundle Releases</id>
            <url>http://repository.springsource.com/maven/bundles/external</url>
        </repository>
    </repositories>
</project>

Now that we have a project layout, we can configure Spring to manage our datasources and Hibernate transactions. A simple configuration in a src/main/resources/applicationContext.xml file would look like this:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/tx
       http://www.springframework.org/schema/tx/spring-tx.xsd">
    
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
        <property name="username"><value>custom_insert</value></property>
        <property name="password"><value>custom_insert</value></property>
        <property name="url"><value>jdbc:oracle:thin:@localhost:1521:orcl</value></property>
    </bean>
    <bean id="sessionFactory"
        class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="configLocation" value="classpath:/hibernate.cfg.xml" />
        <property name="configurationClass" value="org.hibernate.cfg.AnnotationConfiguration" />
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.format_sql">true</prop>
                <prop key="hibernate.generate_statistics">true</prop>
                <prop key="hibernate.use_sql_comments">true</prop>
                <prop key="hibernate.hbm2ddl.auto">create</prop>
                <prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop>
                <prop key="hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory</prop>
            </props>
        </property>
    </bean>
    <bean id="txManager"
        class="org.springframework.orm.hibernate3.HibernateTransactionManager">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>
    <tx:annotation-driven transaction-manager="txManager"/>
</beans>

Create a Schema

For this example, we can create a pristine user in our database. If you choose an existing schema, be aware that the hibernate.hbm2ddl.auto property in the applicationContext.xml will re-create the database any time the Hibernate container starts.
We will run these commands from sqlplus to create our user:

  sqlplus connect as SYSDBA
    create user custom_insert identified by custom_insert default tablespace users temporary tablespace temp;
    grant connect, resource to custom_insert;
    grant create table to custom_insert;

Create a Domain Object

With Spring and Maven2 configured and a schema available in Oracle, we can create our domain, which should look familiar from the previous article. The meta-data in the Author.java file maps the Object to a database table.
Our src/main/resources/hibernate.cfg.xml file can be as simple as this for a single domain Object:

<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <mapping class="timezra.blog.hibernate.custom_insert.domain.Author" />
    </session-factory>
</hibernate-configuration>

The first configuration of our Author.java domain Object can use Hibernate's built-in support for a sequence-generated ID.

package timezra.blog.hibernate.custom_insert.domain;

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

@Entity
@Table(name = "AUTHOR", schema = "CUSTOM_INSERT")
public class Author implements java.io.Serializable {

    private static final long serialVersionUID = -6270202393794713117L;
    private int id;
    private String firstName;
    private String lastName;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "author_id_seq")
    @SequenceGenerator(name = "author_id_seq", sequenceName = "author_id_seq")
    @Column(name = "ID", nullable = false)
    public int getId() {
        return id;
    }

    public void setId(final int id) {
        this.id = id;
    }

    @Column(name = "FIRST_NAME", nullable = false, length = 50)
    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(final String firstName) {
        this.firstName = firstName;
    }

    @Column(name = "LAST_NAME", nullable = false, length = 50)
    public String getLastName() {
        return lastName;
    }

    public void setLastName(final String lastName) {
        this.lastName = lastName;
    }
}

We can start with a simple suite, AuthorTest.java, to determine whether, after we perform an insertion into the database, the same Object will be retrieved using an HQL a query. The test will also verify that our configuration is correct, and it will build the structures in our database, i.e., the domain table and the sequence.

package timezra.blog.hibernate.custom_insert.domain;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertSame;

import java.util.Collection;

import org.hibernate.SessionFactory;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.transaction.TransactionConfiguration;
import org.springframework.transaction.annotation.Transactional;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "/applicationContext.xml" })
@TransactionConfiguration(transactionManager = "txManager", defaultRollback = true)
@Transactional
public class AuthorTest {

    @Autowired
    private SessionFactory sessionFactory;

    @Test
    public void insertAnAuthor() throws Exception {
        final Author author = new Author();
        author.setFirstName("Cameron");
        author.setLastName("McKenzie");
        author.setId(0);
        sessionFactory.getCurrentSession().save(author);

        final Collection<Author> authors = findByLastName("McKenzie");
        assertEquals(1, authors.size());
        assertSame(author, authors.iterator().next());
    }

    @SuppressWarnings("unchecked")
    private Collection<Author> findByLastName(final String lastName) {
        return sessionFactory.getCurrentSession() //
                .createQuery("from Author author where author.lastName = :vLastName") // 
                .setParameter("vLastName", lastName) //
                .list();
    }
}

Generate the Primary Key With a Trigger

Now that our database is setup, we can create a custom trigger to populate the Author ID directly in the database, instead of relying on Hibernate to populate this value.
In order to prevent our trigger from disappearing every time the Hibernate container starts, we must set the hibernate.hbm2ddl.auto property in the applicationContext.xml to update.

The before_author trigger will use the same author_id_seq, now that Hibernate has created it for us, but it will be managed directly by Oracle.

CREATE OR REPLACE TRIGGER before_author BEFORE INSERT ON author
FOR EACH ROW
  BEGIN
    SELECT author_id_seq.nextval INTO :NEW.id FROM DUAL;
  END;

In order to retrieve this primary key, we can use a TriggerAssignedIdentityGenerator.java, which will return the value of the primary key after the prepared statement is executed through JDBC. This method relies on implementation details of Oracle's JDBC driver, so only time will tell how fragile it may be. Also note that only one database hit is required for this insertion, but in order to retrieve other trigger-generated non-primary key values, a session#flush and #refresh may be required which will create extra network and database traffic.

package timezra.blog.hibernate.custom_insert.domain;

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.IdentifierGeneratorFactory;
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;

public class TriggerAssignedIdentityGenerator extends AbstractPostInsertGenerator {

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

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

        private final String[] keyColumns;

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

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

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

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

Author.java must be updated to use the new ID generator, so that the primary key can be properly set.

....
    @Id
    @GeneratedValue(generator = "triggerAssigned")
    @GenericGenerator(name = "triggerAssigned", strategy = "timezra.blog.hibernate.custom_insert.domain.TriggerAssignedIdentityGenerator")
    @Column(name = "ID", nullable = false)
    public int getId() {
        return id;
    }
....

From our AuthorTest.java, we can verify that the expected Sequence is indeed used to generate the primary key.

....
    @Test
    public void theAuthorsIdIsGeneratedFromASequence() throws Exception {
        final Session session = sessionFactory.getCurrentSession();

        final Author cameronMcKenzie = createCameronMcKenzie();
        session.save(cameronMcKenzie);
        assertEquals(getTheCurrentAuthorSequenceValue().intValue(), cameronMcKenzie.getId());

        final Author christianBauer = createChristianBauer();
        session.save(christianBauer);
        assertEquals(getTheCurrentAuthorSequenceValue().intValue(), christianBauer.getId());
    }

    private BigDecimal getTheCurrentAuthorSequenceValue() {
        return (BigDecimal) sessionFactory.getCurrentSession() //
                .createSQLQuery("select author_id_seq.currval from dual") //
                .uniqueResult();
    }

    private Author createCameronMcKenzie() {
        final Author cameronMcKenzie = new Author();
        cameronMcKenzie.setFirstName("Cameron");
        cameronMcKenzie.setLastName("McKenzie");
        return cameronMcKenzie;
    }

    private Author createChristianBauer() {
        final Author christianBauer = new Author();
        christianBauer.setFirstName("Christian");
        christianBauer.setLastName("Bauer");
        return christianBauer;
    }
....

Insert With a Stored Procedure

Suppose we have a restriction against using triggers, or that we are restricted from calling INSERT statements directly.
We can drop the after_author trigger and create a new insert_author stored procedure that essentially performs the same task. This stored procedure strategy can be expanded to include the removal of after or statement triggers, as well.

DROP TRIGGER before_author;
CREATE OR REPLACE PROCEDURE insert_author
( vFirstName IN author.first_name%type,
  vLastName IN author.last_name%type,
  vId OUT author.id%type ) IS
BEGIN
  INSERT INTO author
    ( first_name, last_name, id )
    VALUES
    ( vFirstName, vLastName, author_id_seq.nextval )
    RETURNING id INTO vId;
END insert_author;

Note the way the procedure reports the generated primary key: we must include an OUT argument in the parameter signature and the insertion statement must return the key using this argument.

The Author.java domain Object can now include a declaration that the stored procedure will be used for insertions, much the same as we used the @SQLUpdate declaration in the previous article.

....
@Entity
@org.hibernate.annotations.SQLInsert(sql = "{call insert_author(:vFirstName, :vLastName, :vId)}", callable = true)
@Table(name = "AUTHOR", schema = "CUSTOM_INSERT")
....

Note the final parameter in the CallableStatement: Hibernate will populate all the statement parameters by index; if we need to add more parameters to our procedure, they must come after all the non-generated fields in our domain Object.

Our TriggerAssignedIdentityGenerator will not be able to retrieve the primary key, nor will it know enough to register that the key is contained in the OUT parameter. A more appropriate class could be called ProcedureAssignedIdentityGenerator.java and would look like this:

package timezra.blog.hibernate.custom_insert.domain;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import org.hibernate.HibernateException;
import org.hibernate.dialect.Dialect;
import org.hibernate.engine.SessionImplementor;
import org.hibernate.id.AbstractPostInsertGenerator;
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;

public class ProcedureAssignedIdentityGenerator extends AbstractPostInsertGenerator {

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

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

        public Delegate(final PostInsertIdentityPersister persister, final Dialect dialect) {
            super(persister);
            this.dialect = dialect;
        }

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

        @Override
        protected PreparedStatement prepare(final String insertSQL, final SessionImplementor session) throws SQLException {
            return session.getBatcher().prepareCallableStatement(insertSQL);
        }

        @Override
        protected Serializable executeAndExtract(final PreparedStatement insert) throws SQLException {
            final CallableStatement cs = (CallableStatement) insert;
            final int indexOfTheId = cs.getParameterMetaData().getParameterCount();
            cs.registerOutParameter(indexOfTheId, Types.NUMERIC);
            insert.executeUpdate();
            return cs.getInt(indexOfTheId);
        }
    }
}

Note that the primary key in this case must be an int or Integer and it must appear as the final parameter in the CallableStatement.

When we run our tests again, there should be no change in the results. The database Sequence still generates the Author ID. If we examine the generated SQL (from the console output), we will see that the stored procedure is invoked.

Using this project structure, we can now continue to add domain Objects and we can benefit from a mix of both Hibernate and Oracle-specific features.