History triggers and Hibernate

Recently, I helped one of our customers migrating a legacy database to Hibernate; one of the more interesting topics was versioning and audit logging. Actually, in the last couple of months, the subject of historical data came up several times. No matter if it was a legacy SQL schema or a migration from a broken object-oriented database, everyone had their own way to log data changes.

In this entry, I'll introduce a clean and nice solution for this issue. My proposal naturally integrates with Hibernate. Let's use database triggers and views instead of code in the application layer.

While it is in fact quite easy to write a Hibernate Interceptor for audit logging (an example can be found in Hibernate in Action or on the Hibernate Wiki ), we always like to use the features of the database system. Implementing audit logging in the database is the best choice if many applications share the same schema and data, and usually much less hassle to maintain in the long run.

First, let's create an entity we want to implement a change history for, a simple Item. In Java, this entity is implemented as the Item class. As usual for a Hibernate application that uses Detached Objects and automatic optimistic concurrency control, we give it an id and a version property:

public class Item {

    private Long id = null
    private int version;
    private String description;
    private BigDecimal price;

    Item() {}
    
    ... // Accessor and business methods    
}

This class is then mapped to a table using Hibernate metadata:

<hibernate-mapping>
<class name="Item" table="ITEM_VERSIONED>
    <id name="id" column="ITEM_ID">
        <generator class="native"/>
    </id>
    <version name="version" column="VERSION"/>
    <property name="description" column="DESC"/>
    <property name="price" column="PRICE"/>
</class>
</hibernate-mapping>

The name of the mapped table is ITEM_VERSIONED. This is actually not a normal base table, but a database view that joins the data from two base tables. Let's have a look at the two tables in Oracle:

create table ITEM (
    ITEM_ID    NUMBER(19) NOT NULL,
    DESC       VARCHAR(255) NOT NULL,
    PRICE      NUMBER(19,2) NOT NULL,
    PRIMARY KEY(ITEM_ID)
)

create table ITEM_HISTORY (
    ITEM_ID    NUMBER(19) NOT NULL,
    DESC       VARCHAR(255) NOT NULL,
    PRICE      NUMBER(19,2) NOT NULL,
    VERSION    NUMBER(10) NOT NULL,
    PRIMARY KEY(ITEM_ID, VERSION)
)

The ITEM table is our real entity relation. The ITEM_HISTORY table has a different primary key, using the ITEM_ID and VERSION column. Our goal is to have one row per entity instance in ITEM (the newest version of our data) and one row for each item version in ITEM_HISTORY:

ITEM_ID   DESC            PRICE
1         A nice Item.    123,99
2         Another one.     34,44

ITEM_ID   DESC            PRICE      VERSION
1         The original.   123,99     0
1         An update.      123,99     1
1         A nice Item.    123,99     2
2         Another one.     34,44     0

So, instead of mapping our Java entity to any of the two tables, we map it to a new virtual table, ITEM_VERSIONED. This view merges the data from both base tables:

create or replace view ITEM_VERSIONED (ITEM_ID, VERSION, DESC, PRICE) as
    select I.ITEM_ID as ITEM_ID,
        (select max(IH.VERSION)
            from ITEM_HISTORY HI
            where HI.ITEM_ID = I.ITEM_ID) as VERSION,
        I.DESC as DESC,
        I.PRICE as PRICE
    from   ITEM I

The ITEM_VERSIONED view uses a correlated subquery and a theta-style join to get the highest version number for a particular item from the history table, while selecting the current values from the row in ITEM. Of course we could also directly read all data from ITEM_HISTORY, but this query is more flexible, for example if you don't want to include all original columns in the history.

Hibernate can now read entities and it has a version number for automatic optimistic locking. However, we can not save entities, since the view is read-only. (In Oracle and most other databases, views created using a join can not be updated.) You will get an exception if you try to update an entity.

We solve this problem by writing a database trigger. The trigger will intercept all updates and insertions for the view and redirect the data to the base tables. This kind of trigger is called an /INSTEAD OF/ trigger. Let's first handle insertion:

create or replace trigger ITEM_INSERT
    instead of insert on ITEM_VERSIONED begin
    
    insert into ITEM(ITEM_ID, DESC, PRICE)
           values (:n.ITEM_ID, :n.DESC, :n.PRICE);
           
    insert into ITEM_HISTORY(ITEM_ID, DESC, PRICE, VERSION)
           values (:n.ITEM_ID, :n.DESC, :n.PRICE, :n.VERSION);
end;

This trigger will execute two inserts and split the data between the entity and entity history table. Next, update operations:

create or replace trigger ITEM_UPDATE
    instead of update on ITEM_VERSIONED begin
    
    update ITEM set
            DESC = :n.DESC,
            PRICE = :n.PRICE,
           where
            ITEM_ID = :n.ITEM_ID;
           
    insert into ITEM_HISTORY(ITEM_ID, DESC, PRICE, VERSION)
           values (:n.ITEM_ID, :n.DESC, :n.PRICE, :n.VERSION);
end;

The entity table is updated first, with the new data. Then, a new row is written to the ITEM_HISTORY table.

This is actually all you need to implement a basic history functionality, just check /INSTEAD OF/ trigger support in your database management system. You can even enhance this pattern and make it much more flexible: write a new Auditinfo value type class with user and timestamp information and add an auditinfo property to your entity class in Java. Map this to some new columns in your view using a Hibernate custom UserType and track the information by setting the property in a Hibernate Interceptor when updates and inserts occur. Use AOP to externalize this aspect from your POJOs...

HTH