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

This is a reply on the Hibernate forum, read the full thread for the context.

As some noticed, I'm a bit impulsive about that kind of thing right now. We get one question a day, sometimes friendly, sometimes not so friendly, if we could just set up the book (Hibernate in Action) for free download or simply send it over.

Don't take it personal if I'm getting annoyed about that. It's sometimes just embarrassing when someone gives you the feeling that you owe them something, just because they use Hibernate. I'd like to explain why its not about the money for the book.

First, and that is really my own personal opinion, a professional Java developer should have enough money for whatever book in any case. Books are important, read at least one each month, better two. I don't buy any of that offshore jobs talk, but knowledge is the only thing that keeps you in business. I don't want to discuss $5 Amazon shipping costs about that.

Now I'd like to start a little rant here, so you better stop reading if you are not in the mood :)

Our software is free, our documentation is of course free, and we will always provide free support on a personal level in this forum, and whenever we think it is needed.

I also think that our software and the whole project shows that we are serious about what we are doing. We worked many nights and weekends last year. Gavin and me quit our jobs to make this possible. Max, David, Steve, Emmanuel spend hours a day after work, writing interesting things and answering questions (now up to 120/day) on the forum.

We really believe in professional open source as a business model in the software sector. Actually I'm now also employed by JBoss Group. We have two people working fulltime on Hibernate.

How do we earn our salary?

With open source, there are no license fees. This is great for developers, as they can sell the software they'd like to use to their project managers and accountants easily. Well, of course only if all the other qualities are right:

• good software

• good documentation and free support

• commercial support (risk management)

• nice extras

A successful vendor of closed software can provide the second for free, but you have to pay for the other three most of the time. Sometimes its hidden, sometimes not. The situation is quite different with an open source software project. First, there are no hidden costs, because you actually can't hide anything. Second, we as software developers don't like hiding any costs somewhere, we are not good with that kind of thing. It's much easier to tell people what they need and what it costs upfront. I also like the look on peoples faces if you admit that Hibernate solves only 95% of their problems, not 100! Shocking! The truth!

It's hard to break with the traditions and I remember the early Linux days when it was first used used commercially. You actually had to tell people that free software is good because they don't pay money for licenses. They wouldn't believe you, just don't ask for a (real) reason. Usually they expected some hidden costs in the other three items and simply denied that it was any good at all.

Sometimes, I think it was too much propaganda back then, now everyone expects everything for free!

To support a business, some revenue must be made from at least one of the four elements I listed.

I'm not talking about the book, because that really doesn't pay. I think most people know that books don't pay in the end, at least if your name is not Fowler or Bloch. Why do we write it then? It helps us to get more people (and therefore ideas and opinions) into Hibernate, and we can finally write down some thoughts we can't express in any other medium.

And yes, we also hope that it will help us to grow the Hibernate business. Thats our job. There is no professional quality without that background, or at least not for a long period of time.

Hibernate is naturally a very open project driven by the users. This will not change, no matter what our business model is. We think that listening to users and balancing requests is the heart of the project, it is the reason why people like it and why it works(tm).

About two years ago, a co-worker asked me innocently: What are scalar types anyway?

I realized that I had used that term one too often and he was really asking if I can define it, not just use it.

On my way to the JavaPolis conference I had some time to write up what I understand about /scalar types/. The truth is, I had a lot of time, because my train got the wrong route (twice!), and we even had to go backwards several times.

Just to make this entry even more exciting, I'll also explain what I understand as a /variable/ and a /value/.

A type names a /set of values/. So, a type has a name, we know that. But what is this set of values? According to Chris Date, a value is an /individual constant/. Now, stop thinking in software terms, and think about information and data. A value has, by definition, no location in time and space. For example, we don't know where or when the value Hibernate is located, this question doesn't even make sense. It's a value nevertheless, just as the number 4 and the number 2.

We know that in a strongly typed language such as Java, every value has to have a type. This means that every value in our software carries its type with it. Instead of working with value literals, as I did in the previous paragraph, we assign them to variables. A variable gives a value a location in time and space. In Java, the type of a value is associated with the variable.

String projectName;
projectName = "Hibernate"

In the first line, we declare that String is the type of the variable projectName. The values that can be assigned to projectName are therefore limited to the set of values allowed for the String type, i.e., all character strings. In the next line, we assign the value Hibernate to the variable projectName. The assignment operator (=) checks both operands for compatibility: Is the given value in the set of values allowed for the variables type? Strong typing means that this check is performed for all operations, including all method (operator) calls, not only assignment.

Let's talk about types in Java. We have built-in types such as String, but also custom user-defined types, such as Project. But a Java /class/ is only the mechanism used for type declaration and implementation. Sometimes we are sloppy and say a class is a type, but strictly speaking, it is not. A type is all externally visible elements of a class, that is all operators (methods) and components (attributes) declared public. We just use the mechanism of a class to define this, and of course, to implement the actual (internal) representation. So the following class defines and implements the Project type:

public Project {

   public String projectName;

   public void makeOpenSource() {
      ...
   }

   public Team getProjectTeam() {
      ...
  }

}

The Project type has one component and two operators. But where is this set of values defined?

Consider the type Object in Java: This type implies a set with all possible values. So, in fact, the set is implicit if you create a type. In other words, if you declare a variable of type Object, you can assign whatever value you want. By creating a type (writing a class or interface), you narrow that set. For example, a type Project might imply a set of all possible projects. You can of course further restrict the allowed values by adding constraints to that type (only projects with a name shorter than 10 characters), but thats not the issue here.

Back to the initial question: A scalar type is a type that is /encapsulated/, that is, it doesn't have any user-visible components. The Project type in the example above is non-scalar, it has the user-visible component (attribute) projectName.

On the other hand, the Team returned by the select operator getProjectTeam does not reveal a component, but only gives us a part of the possible representation of a Project. We simply don't now (and shouldn't care) if the Team returned is internally a component (attribute) of the Project or something completely different. The implementation is hidden.

If we follow the Best Practices for POJOs (or JavaBeans), we automatically create scalar types. Adding a select operator such as getProjectName and removing the public visible component projectName would make the example Project type scalar, that is encapsulated or atomic.

Keep in mind that /scalar/ is not related to /complexity/ in any way. Just because we call Java primitives scalar, doesn't make all scalar types primitive.