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:
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:
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:
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:
The first configuration of our Author.java domain Object can use Hibernate's built-in support for a sequence-generated ID.
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.
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.
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.
Author.java must be updated to use the new ID generator, so that the primary key can be properly set.
From our AuthorTest.java, we can verify that the expected Sequence is indeed used to generate the primary key.
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.
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.
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:
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.