Using db generated values

There are scenarios when certain fields in the table can be managed by the database. In such cases we do not need Hibernate to update these fields from the model. One such scenario could be the audit fields. Most tables that we create include properties to manage the audit details. Typical columns found in the tables for auditing purposes are created_by, modified_by created_date and modified_date.

In most applications we update and manage these fields through the code. However Hibernate provides an alternative method that allows database to auto-generate and insert these column values (using triggers). I shall change the modified_date and created_date fields of the pet table. Every time a record is inserted in the pet table a trigger can be used to insert the created_date and modified_date values for the record. When a pet record is updated, a trigger can be used to change the value of modified_date to reflect the current save time. However care needs to be taken that Hibernate does not attempt to write into these columns when it saves the object. For this the property element includes a "generated" attribute.

Pet.hbm.xml

<property name="createdDate" type="timestamp" generated="insert"
        update ="false" insert="false">
     <column name="CREATED_DATE" />
</property>
<property name="modifedDate" type="timestamp" generated="always"
    update ="false" insert="false">
    <column name="MODIFIED_DATE" />
</property>

The triggers used to manage the two fields are as follows:

create trigger creation_audit_trail before insert on pet for each row
set new.created_date := now(),
new.modified_date := now();

create trigger modification_audit_trail before update on pet for each row
set new.modified_date := now();

If you read the triggers, then it can be seen that the sql code executes before the row is created or updated. So it is necessary to ensure that hibernate does not try to insert values into these columns. This is ensured by setting the values of "update" and "insert" to false. If both are set to false, then as far as Hibernate is concerned these fields are read only.

Java Code:

public static void main(String[] args) {
    Configuration configuration = new Configuration();
    configuration = configuration.configure();
    sessionFactory = configuration.buildSessionFactory();    
    Pet strayAnimal = new Pet();
    strayAnimal.setAge(10);
    strayAnimal.setCreatedBy(1);
    strayAnimal.setModifiedBy(1);
    strayAnimal.setName("unknown");
    strayAnimal.setTagId("-");
    
    Session session = sessionFactory.openSession();
    Transaction transaction = session.beginTransaction();
    session.save(strayAnimal);
    System.out.println("The created Date is " + strayAnimal.getCreatedDate()
        + " and the modified date is " + strayAnimal.getModifedDate());
    transaction.commit();
}

In the above code we have created and saved a Pet object. However we haven't set any values for created and modified date. As these values are inserted from the database there is no point in setting the values here. Following are the logs on executing the above highlighted line:

27000 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        PET
        (NAME, AGE, TAGID, OWNER_ID, MODIFIED_BY, CREATED_BY) 
    values
        (?, ?, ?, ?, ?, ?)

The modified_date and created_date fields were skipped in the code. Further logs are as below:

12359 [main] DEBUG org.hibernate.id.IdentifierGeneratorFactory  - Natively gener
ated identity: 1
12359 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - about to close Prepared
Statement (open PreparedStatements: 1, globally: 1)
12359 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - closing statement
12359 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - about to open PreparedS
tatement (open PreparedStatements: 0, globally: 0)
12359 [main] DEBUG org.hibernate.SQL  - 
    select
        pet_.CREATED_DATE as CREATED8_1_,
        pet_.MODIFIED_DATE as MODIFIED9_1_ 
    from
        PET pet_ 
    where
        pet_.ID=?
12359 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - preparing statement
12359 [main] DEBUG org.hibernate.type.LongType  - binding '1' to parameter: 1
12359 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - about to open ResultSet
 (open ResultSets: 0, globally: 0)
12453 [main] DEBUG org.hibernate.type.TimestampType  - returning '2011-08-01 00:
15:08' as column: CREATED8_1_
12453 [main] DEBUG org.hibernate.type.TimestampType  - returning '2011-08-01 00:
15:08' as column: MODIFIED9_1_

An additional select query was fired after the save which returned the db generated values.
Trying to modify the same object will result in the following:

Java Code:

Session session = sessionFactory.openSession();
Transaction transaction = session.beginTransaction();
Pet strayAnimal = (Pet) session.load(Pet.class, 1L);
strayAnimal.setAge(2);
transaction.commit();

The logs are :

26110 [main] DEBUG org.hibernate.SQL  - 
    update
        PET 
    set
        NAME=?,
        AGE=?,
        TAGID=?,
        OWNER_ID=?,
        MODIFIED_BY=?,
        CREATED_BY=? 
    where
        ID=?
...
26188 [main] DEBUG org.hibernate.SQL  - 
    select
        pet_.MODIFIED_DATE as MODIFIED9_1_ 
    from
        PET pet_ 
    where
        pet_.ID=?
26188 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - preparing statement
26203 [main] DEBUG org.hibernate.type.LongType  - binding '1' to parameter: 1
26219 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - about to open ResultSet
 (open ResultSets: 0, globally: 0)
26219 [main] DEBUG org.hibernate.type.TimestampType  - returning '2011-08-01 00:
23:15' as column: MODIFIED9_1_

This time the select query only fetched the modified_date field as this was the only property updated.
The behavior of the above select clause is controlled by the generated attribute. It tells Hibernate what events leads to a new value being generated for a particular column. As the createdDate had generated value as "insert", it was only fetched when an Insert SQL was executed. The modifiedDate attribute on the other hand had generated set to "always". This meant that it was fetched using SQL when both insert and update statements were fired.
I would never suggest using this functionality for the above purpose, but there may arise situations where certain data will be generated and set by the database. E.g. a complex trigger could be in operation. Or it is a legacy table that manages certain columns through trigger actions. It could be a table has 100 columns of which most have their values set to default through SQL. In such scenarios the "generated"attribute can be used, avoiding passing any value from Java to the database. The expense of this however is the additional SELECT query that is fired after the save/update SQL completes.