Basil Vandegriend: Professional Software Development

Audit columns are a common design pattern used to record data creation and modification information for database tables. A typical implementation of this pattern is to add four columns to every non-static database table: CREATE_USER, CREATE_TIMESTAMP, UPDATE_USER, and UPDATE_TIMESTAMP. The create columns are populated only when a record is initially populated, while the update columns are populated each time the record is updated.

While database triggers can be used to populate the timestamp columns, the user columns are trickier to populate. In a typical web application or three-tier architecture, individual clients do not communicate directly with the database but go through an intermediate layer – the web or application server. The data source used by the application server to connect to the database manages a pool of connections using a common application id to authenticate to the database. This avoids the overhead of a new database connection for each client request and allows a large number of user requests to be serviced by a smaller number of connections. Since it is the application, and not the user, who authenticates with the database, the database does not know the identity of the user behind the database operations, so triggers to populate user columns will not work as desired. The solution is to instead populate these columns within the application code.

Explicitly populating the audit columns in the code for every insert or update, however, is far from ideal - especially when using an object-relational mapping framework such as Hibernate. One of the major advantages of using Hibernate is its ability to encapsulate and hide (in a leaky fashion) the work involved in persisting to a relational database. This allows the business logic, expressed in terms of persisted domain objects, to be kept as readable and simple as possible. Cluttering this logic with calls to set the audit columns complicates the code and is error-prone – missing a single update or creation means there is a hole in the auditing. From the viewpoint of both maintainability and security, the ideal solution would be to configure Hibernate to automatically populate these audit columns whenever a record is created or updated.

The idea of adding orthogonal, or cross-cutting, functionality automatically to a code base is the realm of aspect-oriented programming. Hibernate supports this programming model through the use of interceptors, which allow client code to be executed as part of Hibernate's core processing. We can create an AuditInterceptor to set the audit columns of non-static domain objects, as identified by an Auditable interface. One significant issue is how to obtain the user id to set in the AuditInterceptor. Since the AuditInterceptor is registered with Hibernate and never called directly, there is no way to directly pass in the user id. The typical solution is to use a thread local singleton (i.e. an instance of ThreadLocal) to store the user id for the current thread. For a typical web application, at the start of processing an user's session, the user's id must therefore be registered with the AuditInterceptor. The code for Auditable and AuditInterceptor is shown below:

public interface Auditable
{

  public String getCreateUserId();

  public void setCreateUserId(String createUserId);

  public String getUpdateUserId();

  public void setUpdateUserId(String updateUserId);

}

public class AuditInterceptor extends EmptyInterceptor
{

  private static ThreadLocal userPerThread = new ThreadLocal();

  /**
   * Store the user for the current thread.
   * @param user Cannot be null or empty.
   */
  public static void setUserForCurrentThread(String user) {
    userPerThread.set(user);
  }

  /**
   * Get the user for the current thread.
   * (Used primarily for testing).
   * @return the current user.
   */
  public static String getUserForCurrentThread() {
    return userPerThread.get();
  }

  @Override public boolean onFlushDirty(Object entity,
    Serializable id, Object[] currentState,
    Object[] previousState, String[] propertyNames,
    Type[] types) {

    boolean changed = false;

    if (entity instanceof Auditable) {
      changed = updateAuditable(currentState, propertyNames);
    }
    return changed;
  }

  @Override public boolean onSave(Object entity,
    Serializable id, Object[] currentState,
    String[] propertyNames, Type[] types) {

    boolean changed = false;

    if (entity instanceof Auditable) {
      changed = updateAuditable(currentState, propertyNames);
    }
    return changed;

  }

  private boolean updateAuditable(Object[] currentState,
    String[] propertyNames) {
    boolean changed = false;
    for (int i = 0; i < propertyNames.length; i++) {
      if ("createUserId".equals(propertyNames[i])) {
        if (currentState[i] == null) {
          currentState[i] = userPerThread.get();
          changed = true;
        }
      }
      if ("updateUserId".equals(propertyNames[i])) {
        currentState[i] = userPerThread.get();
        changed = true;
      }
    }
    return changed;
  }

}

The AuditInterceptor must be registered with Hibernate. This can be done when the Hibernate session is created, as shown below. Depending on how sessions are created within your code base, you could also provide the current user id to the constructor of AuditInterceptor.

Session session = sessionFactory.openSession(new AuditInterceptor());

This article is one of a series on Hibernate Tips & Tricks.

If you find this article helpful, please make a donation.

Share this article: