Hibernate Updates and Oracle Stored Procedures

This is a continuation of a previous post and builds on the project setup contained therein.

Motivation: It is sometimes desirable to use Oracle stored procedures for standard CUD (create, update, delete) operations; for example, when re-factoring a database to remove trigger calls, one possible solution would be to allow modifications to tables only through procedures. The operations performed by triggers can then be moved into these procedures.

Add an Update Method

We will first use Hibernate's built-in support for updating our Author domain Object. We can add a new method to the AuthorDAO to examine its behavior.

....
public void update(final Author author) {
  getHibernateTemplate().update(author);
}
....

Add a Test Case

We generally should not test the frameworks that we use, but here we will be moving outside of the framework. We want to make sure that using our custom update stored procedure will not break the existing behavior that Hibernate provides to us, so we will add a new verification of update behavior to the AuthorDAOTest.

....
@Test
public void testUpdate() throws Exception {
  final Author author = getAuthorDAO().findByLastNameUsingHQL("Thoreau").get(0);
  author.setLastName("Miller");
  getAuthorDAO().update(author);
  assertEquals(1, getAuthorDAO().findByLastNameUsingHQL("Miller").size());
}
....

Write a Stored Procedure

Now we will write our custom update stored procedure. To figure out the procedure's argument signature, we can look at the Hibernate console output when we run the test case above. We should note the order of the arguments, which is alphabetic for the updated fields with the primary key in the last position.

• Hibernate: update MY_ORCL.AUTHOR set FIRST_NAME=?, LAST_NAME=? where ID=?

This order must be maintained in our stored procedure call. We can also use this statement as the basis for the update contained in the procedure body.
We can create this stored procedure through SQLPlus.

CREATE OR REPLACE PROCEDURE update_author
(  vFirstName IN author.first_name%type,
   vLastName IN author.last_name%type,
   vId IN author.id%type  ) AS
BEGIN
  UPDATE author SET first_name=vFirstName, last_name=vLastName where id=vId;
END update_author;

Call the Stored Procedure From @SQLUpdate

Now that we have a procedure in our schema, we need a way to call it from our DAO. Hibernate provides annotations specific to these CUD operations

• @org.hibernate.annotations.SQLUpdate
• @org.hibernate.annotations.SQLInsert
• @org.hibernate.annotations.SQLDelete
• @org.hibernate.annotations.SQLDeleteAll

Here, we can add the custom update to our Author domain Object, alongside the named queries from the last tutorial.

....
@Entity
@org.hibernate.annotations.NamedNativeQuery(name = "findByLastName", query = "call findByLastName(?, :vLastName)", callable = true, resultClass = Author.class)
@javax.persistence.NamedNativeQuery(name = "findByFirstName", query = "{ ? = call findByFirstName(:vFirstName) }", resultClass = Author.class, hints = { @javax.persistence.QueryHint(name = "org.hibernate.callable", value = "true") })
@org.hibernate.annotations.SQLUpdate(sql = Author.UPDATE_AUTHOR)
@Table(name = "AUTHOR", schema = "MY_ORCL")
public class Author implements java.io.Serializable {

  public static final String UPDATE_AUTHOR = "call update_author(:vFirstName, :vLastName, :vId)";
....

If we run our test case again, we will see that the stored procedure is now called to perform the update.

• Hibernate: call update_author(:vFirstName, :vLastName, :vId)

Write a Hibernate Interceptor

Suppose we would like to pass an extra parameter to the stored procedure, say a counter for the number of times this procedure has been called from a particular instance of an application. We can modify our argument list with an extra parameter, here adding the counter at the end.

CREATE OR REPLACE PROCEDURE update_author
(  vFirstName IN author.first_name%type,
   vLastName IN author.last_name%type,
   vId IN author.id%type,
   vCounter INTEGER  ) AS
BEGIN
  UPDATE author SET first_name=vFirstName, last_name=vLastName where id=vId;
END update_author;

We will also need to modify the call syntax where it is declared in the domain Object to accept this new parameter.

....
public class Author implements java.io.Serializable {

  public static final String UPDATE_AUTHOR = "call update_author(:vFirstName, :vLastName, :vId, :vCounter)";
....

Finally, we can write an Interceptor that will set the counter value in this parameter. The Interceptor will increment a static field each time the update_author procedure is called. This counter is passed to the stored procedure.

package spring.hibernate.oracle.stored.procedures.domain;

import java.util.concurrent.atomic.AtomicLong;
import org.hibernate.EmptyInterceptor;

public class UpdateAuthorInterceptor extends EmptyInterceptor {

  private static final long serialVersionUID = 2908952460484632623L;
  private static final AtomicLong counter = new AtomicLong();

  @Override
  public String onPrepareStatement(final String sql) {
    if (sql.equals(Author.UPDATE_AUTHOR)) {
      return sql.replaceFirst(":vCounter", String.valueOf(counter.getAndIncrement()));
    }
    return super.onPrepareStatement(sql);
  }
}

Now we will configure the Interceptor in the applicationContext.xml for use by the LocalSessionFactoryBean.

....
<bean id="sessionFactory"
  class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
....
  <property name="entityInterceptor" ref="updateAuthorInterceptor" />
</bean>
<bean id="updateAuthorInterceptor"
  class="spring.hibernate.oracle.stored.procedures.domain.UpdateAuthorInterceptor" />
....

If we run the test case again, we will see console output indicating that Hibernate is calling the update procedure with this counter value.

• Hibernate: call update_author(:vFirstName, :vLastName, :vId, 0)

Other CUD Operations

With these same steps, we can customize Create (@SQLInsert) and Delete (@SQLDelete and @SQLDeleteAll) operations to use stored procedures.