Audit with JPA: creation and update date

When writing a business application with persistent data, some auditing capabilities are often required. Today, state of the art for persisting data involves using an ORM tool through the JPA interface. Being able to add two columns containing the creation date and the update date is a common auditing requirement. My colleague Borémi and I have had to answer this question. We have grouped and studied several implementations already used by other Octos. In order to help you choose the best tool for such need, I will present in this paper different solutions that we have compared.

For the purpose of this article, I will take the example of a Product table with a single description column.

Delegating to the database

For a long time, creation and update date columns have been managed by triggers on the database side. Even if it is not the most state of the art technology, this implementation meets well the need. The dates generated by the triggers can be retrieved on the Java side by mapping the two target columns. The major risks of such an implementation are to put in such triggers too much logic or logic that can conflict with business logic coded in the Java code. If these two columns only have a technical purpose and if triggers are the standard way for filling them in, then using trigger is the most appropriate choice.

Using @Version and a default value on the column

Another natural way to implement this functionality is by combining two functionalities provided by JPA and every database. First, the creation date is implemented by defining a default value on a CREATION_DT column. This way, each time a line is created in the PRODUCT table, the current timestamp will be inserted, providing a creation date for that line. As DEFAULT CURRENT_TIMESTAMP is an SQL92 instruction, I'm confident that all major databases implement it correctly. This default value can be easily defined through the columnDefinition property of the column in JPA. Moreover, by specifying the insertable=false and updatable=false properties, we can guarantee that this column will never be modified by the Java code. So, adding this annotation on a dateCrea field provides a creation timestamp: @Column(name="CREATION_TS", columnDefinition="TIMESTAMP DEFAULT CURRENT_TIMESTAMP", insertable=false, updatable=false). Then, the update date can be implemented by hacking the @Version column definition. The @Version annotation is used in the JPA specification to define a column used for the optimistic concurrency management. Each time an entity is modified, the value of that column is modified. That way, the JPA implementation can check before each merge operation if the version held by the entity manager is out of date or not. Fortunately, @Version can be defined on a TIMESTAMP column according to the JPA JavaDoc. The timestamp stored in that column is a very good estimator of the UPDATE_TS date (the latest modification date). To summarize, adding two attributes in each entity with the following annotations provides an easy way to get a create and an update date for that entity.

@Version
@Column(name="UPDATE_TS")
private Calendar dateMaj;
	
@Column(name="CREATION_TS", columnDefinition="TIMESTAMP DEFAULT CURRENT_TIMESTAMP", insertable=false, updatable=false)
private Calendar dateCrea;

However, this method has a drawback: you will get an update date earlier than the creation date for a newly created entity. This behavior has been observed with Hibernate but is probably applicable to other JPA implementations too. Indeed, @Version is handled by the JPA implementation on the Java side: the timestamp is generated just before the generation of the INSERT SQL order. This order is then sent to the database where it is executed. The new line is created in the PRODUCT table and the timestamp for the default value of the CREATION_TS column is generated only at this time. Thus, the insertion timestamp is always generated several milliseconds after the update timestamp. From a human point of view these two dates are probably valid but this small difference should be taken into account by a program. For example, such a request would return 0.

SELECT COUNT(*)
FROM product p
WHERE p.creation_ts=p.update_ts

Looking for all the newly created records requires a slightly more complicated request such as this one

SELECT COUNT(*)
FROM product p
where ABS(TIMESTAMPDIFF(SECOND, creation_ts, udate_ts))<=1

I have tried several workarounds to solve this problem but without satisfactory result. For example, it is not possible to avoid inserting the @Version attribute for newly created lines by adding an insertable=false instruction. This value is required by JPA in order to correctly manage the optimistic concurrency. Doing so leads to a JPA error. In brief, if you don't go along with these limitations in your environment you need to choose another implementation.

Using a base class for each entity

The second implementation on a simplicity scale is to define a base class which handles the creation and update date generation. For that purpose, we have used the @PrePersist and @PreUpdate attributes of the JPA specification.- A method with the PrePersist annotation is called each time the

persist

method is applied on the entity. We can use it to set the `dateCrea` attribute:

```
@PrePersist
void onCreate() {
	this.setDateCreaTech(new Timestamp((new Date()).getTime()));
}
```

A method with the [PreUpdate](http://download.oracle.com/javaee/5/api/javax/persistence/PreUpdate.html) annotation is called each time before any update operation is performed on the entity data: a flush of the entity, a call on the setters, and the end of a transaction. We can use it to set the

dateMaj

attribute.

```
@PreUpdate
void onPersist() {
	this.setDateMajTech(new Timestamp((new Date()).getTime()));
}
```

This schema on an Oracle documentation illustrates very well the transitions of the lifecycle where these methods are called. To summarize, we have defined a base class; all entities that need these two columns have to extend it. Please note that only the

@Entity

in a JPA meaning can extend such base class because only the events for @Entity (and not for embeddable class) are monitored.

@MappedSuperclass
public abstract class BaseEntity {
	/**
	 * Update date
	 */
	private Timestamp dateMajTech;
	/**
	 * Creation date
	 */
	private Timestamp dateCreaTech;

	/**
	 * @return the dateMajTech
	 */
	@Column(name = "UPDATE_TS", insertable = false, updatable = true)
	Timestamp getDateMajTech() {
		return dateMajTech;
	}

	/**
	 * @param dateMajTech
	 *            the dateMajTech to set
	 */
	void setDateMajTech(Timestamp dateMajTech) {
		this.dateMajTech = dateMajTech;
	}

	/**
	 * @return the dateCreaTech
	 */
	@Column(name = "CREATION_TS", insertable = true, updatable = false)
	Timestamp getDateCreaTech() {
		return dateCreaTech;
	}

	/**
	 * @param dateCreaTech
	 *            the dateCreaTech to set
	 */
	void setDateCreaTech(Timestamp dateCreaTech) {
		this.dateCreaTech = dateCreaTech;
	}

	@PrePersist
	void onCreate() {
		this.setDateCreaTech(new Timestamp((new Date()).getTime()));
	}

	@PreUpdate
	void onPersist() {
		this.setDateMajTech(new Timestamp((new Date()).getTime()));
	}
}

This second implementation requires a bit more preparation but is very simple to apply: you just have to extend a base class. From a design point of view it applies a slight constraint to the business model by requiring a technical inheritance. Such design with a technical base class was highly criticized for the EJB 2.0 and I confess I have been a bit hesitant about using it. However, such a base class does not require any more dependencies than a traditional JPA entity would. It can be unit tested; it can run outside a container. For the simple need of these two columns such an implementation is finally very productive.

Using an entity listener

If you don't want to introduce technical responsibilities into your business model or due to other constraints, you can add listeners on JPA events in an other way. The @PrePersist and @PreUdate annotations we have described in the previous implementation can be applied to a class dedicated to handle the timestamps. We have called it a TimestampEntityListener. Because the timestamps must be persisted in the table, they have to be declared on each entity. In order to share this code, both persistent fields have been placed in an embeddable class named TechnicalColmuns. Moreover, in order to be able to set these values in a generic way, an interface - hiding the business entities - is given as an argument to the TimestampEntityListener. The following listings show the interface, the embeddable class with the persistent fields and an implementation of an entity using this functionality.

public interface EntityWithTechnicalColumns {
	public TechnicalColumns getTechnicalColumns();
	
	public void setTechnicalColumns(TechnicalColumns technicalColumns);
}

@Embeddable
public class TechnicalColumns {
	@Column(name="UPDATE_TS", insertable=false, updatable=true)
	private Timestamp dateMaj;	
	@Column(name="CREATION_TS", insertable=true, updatable=false)
	private Timestamp dateCrea;
	/**
	 * @return the dateMaj
	 */
	public Timestamp getDateMaj() {
		return dateMaj;
	}
	/**
	 * @param dateMaj the dateMaj to set
	 */
	public void setDateMaj(Timestamp dateMaj) {
		this.dateMaj = dateMaj;
	}
	/**
	 * @return the dateCrea
	 */
	public Timestamp getDateCrea() {
		return dateCrea;
	}
	/**
	 * @param dateCrea the dateCrea to set
	 */
	public void setDateCrea(Timestamp dateCrea) {
		this.dateCrea = dateCrea;
	}
}

@Entity
public class Product implements EntityWithTechnicalColumns {
@Embedded
	private TechnicalColumns technicalColumns;
}

The TimestampEntityListener can then be defined in order to set the dateCrea and dateMaj fields of the TechnicalColumns instance.

public class TimestampEntityListener {
	@PrePersist
	void onCreate(Object entity) {
		if(entity instanceof EntityWithTechnicalColumns) {
			EntityWithTechnicalColumns eact = (EntityWithTechnicalColumns)entity;
			if(eact.getTechnicalColumns() == null) {
				eact.setTechnicalColumns(new TechnicalColumns());
			}
			eact.getTechnicalColumns().setDateCrea(new Timestamp((new Date()).getTime()));
		}
	}
	
	@PreUpdate
	void onPersist(Object entity) {
		if(entity instanceof EntityWithTechnicalColumns) {
			EntityWithTechnicalColumns eact = (EntityWithTechnicalColumns)entity;
			if(eact.getTechnicalColumns() == null) {
				eact.setTechnicalColumns(new TechnicalColumns());
			}
			eact.getTechnicalColumns().setDateMaj(new Timestamp((new Date()).getTime()));
		}
	}
}

Finally, this TimestampEntityListener should be registered in the META-INF/persistence.xml file.

<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm" version="1.0" >
   <persistence-unit-metadata>
       <persistence-unit-defaults>
           <entity-listeners>
               <entity-listener class="com.octo.rnd.TimestampEntityListener">
                   <pre-persist method-name="onCreate"/>
                   <pre-update method-name="onPersist"/>
               </entity-listener>
           </entity-listeners>
       </persistence-unit-defaults>
   </persistence-unit-metadata>
</entity-mappings>

This implementation requires still a bit more technical code but allows total isolation of the technical code from the business one. The business classes that require this functionality just need to implement the EntityWithTechnicalColumns interface. Similarly to the preceding implementation, please note that only the events for a JPA @Entity (and not the embeddable classes) are monitored. Another advantage of this implementation is that it is easily ported to an Hibernate implementation: The TimestampEntityListener is replaced by an EmptyInterceptor which is registered for example in a Spring configuration file like the class in the two following listings:

public class TimestampIntercerptor extends EmptyInterceptor {

    private static final long serialVersionUID = -7561360055103433456L;

    @Override
    public boolean onFlushDirty(Object entity, Serializable id, Object[] currentState, Object[] previousState,
            String[] propertyNames, Type[] types) {
		if(entity instanceof EntityWithTechnicalColumns) {
			EntityWithTechnicalColumns eact = (EntityWithTechnicalColumns)entity;
			if(eact.getTechnicalColumns() == null) {
				eact.setTechnicalColumns(new TechnicalColumns());
			}
			eact.getTechnicalColumns().setDateMaj(new Timestamp((new Date()).getTime()));
		}
        
    }

    @Override
    public boolean onSave(Object entity, Serializable id, Object[] state, String[] propertyNames, Type[] types) {
        if(entity instanceof EntityWithTechnicalColumns) {
			EntityWithTechnicalColumns eact = (EntityWithTechnicalColumns)entity;
			if(eact.getTechnicalColumns() == null) {
				eact.setTechnicalColumns(new TechnicalColumns());
			}
			eact.getTechnicalColumns().setDateCrea(new Timestamp((new Date()).getTime()));
		}
    }
}

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
		<property name="dataSource" ref="dataSource"></property>
		<property name="packagesToScan" value="com.octo.rnd" ></property>
		<property name="entityInterceptor">
		    <bean class="com.octo.rnd.TimestampIntercerptor"/>
  		</property>
</bean>

Delegating to a dedicated tool : Hades

Such functionality has been implemented in a framework that integrates itself very well with Spring. Hades Framework will incidentally be merged into the new Spring Data framework. The number of functionalities provided by this framework is much larger that our particular requirement, but the Auditing functionality matches our need. In order to use it, our Product class has to implement the Auditable<U, PK> interface, or more easily to extend the AbstractAuditable<U, Integer> base class. U is the class describing the User who has modified the Product, and PK is the primary key type of the product.

import javax.persistence.Entity;
import com.octo.rnd.hades.auditing.AbstractAuditable;

@Entity
public class Product extends AbstractAuditable<Integer> {

	private static final long serialVersionUID = 1462823665190583909L;
	private String description;
	//Getter and Setter
}

In that case too, this base class includes only fields and JPA annotations. Hades behavior is implemented by an EntityListener configured in the META-INF/orm.xml file.

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_1_0.xsd" version="1.0">
    <persistence-unit-metadata>
        <persistence-unit-defaults>
            <entity-listeners>
                <entity-listener class="org.synyx.hades.domain.auditing.support.AuditingEntityListener" />
            </entity-listeners>
        </persistence-unit-defaults>
    </persistence-unit-metadata>
</entity-mappings>

Finally, you have to declare the Hades namespace in the spring application-context.xml file and add the following line: <hades:auditing />. That's all. With a small JUnit test, we can see the following Hibernate logs:

Hibernate: insert into User (id, login) values (null, ?)
21:24:22,952        TRACE BasicBinder:81 - binding parameter [1] as [VARCHAR] - UserTest
Hibernate: call identity()
Hibernate: insert into Product (id, createdBy_id, createdDate, lastModifiedBy_id, lastModifiedDate, description) values (null, ?, ?, ?, ?, ?)
21:24:23,102        TRACE BasicBinder:70 - binding parameter [1] as [INTEGER] - <null>
21:24:23,103        TRACE BasicBinder:81 - binding parameter [2] as [TIMESTAMP] - Wed Mar 02 21:24:22 CET 2011
21:24:23,103        TRACE BasicBinder:70 - binding parameter [3] as [INTEGER] - <null>
21:24:23,104        TRACE BasicBinder:81 - binding parameter [4] as [TIMESTAMP] - Wed Mar 02 21:24:22 CET 2011
21:24:23,104        TRACE BasicBinder:81 - binding parameter [5] as [VARCHAR] - Product1
Hibernate: call identity()
Hibernate: select product0_.id as id0_2_, product0_.createdBy_id as createdBy5_0_2_, product0_.createdDate as createdD2_0_2_, product0_.lastModifiedBy_id as lastModi6_0_2_, product0_.lastModifiedDate as lastModi3_0_2_, product0_.description as descript4_0_2_, user1_.id as id7_0_, user1_.login as login7_0_, user2_.id as id7_1_, user2_.login as login7_1_ from Product product0_ left outer join User user1_ on product0_.createdBy_id=user1_.id left outer join User user2_ on product0_.lastModifiedBy_id=user2_.id where product0_.id=?
21:24:23,120        TRACE BasicBinder:81 - binding parameter [1] as [INTEGER] - 1
21:24:23,124        TRACE BasicExtractor:66 - found [null] as column [id7_0_]
21:24:23,124        TRACE BasicExtractor:66 - found [null] as column [id7_1_]
21:24:23,128        TRACE BasicExtractor:66 - found [null] as column [createdBy5_0_2_]
21:24:23,129        TRACE BasicExtractor:70 - found [2011-03-02 21:24:22.996] as column [createdD2_0_2_]
21:24:23,129        TRACE BasicExtractor:66 - found [null] as column [lastModi6_0_2_]
21:24:23,129        TRACE BasicExtractor:70 - found [2011-03-02 21:24:22.996] as column [lastModi3_0_2_]
21:24:23,130        TRACE BasicExtractor:70 - found [Product1] as column [descript4_0_2_]
Hibernate: update Product set createdBy_id=?, createdDate=?, lastModifiedBy_id=?, lastModifiedDate=?, description=? where id=?
21:24:24,224        TRACE BasicBinder:70 - binding parameter [1] as [INTEGER] - <null>
21:24:24,225        TRACE BasicBinder:81 - binding parameter [2] as [TIMESTAMP] - 2011-03-02 21:24:22.996
21:24:24,227        TRACE BasicBinder:70 - binding parameter [3] as [INTEGER] - <null>
21:24:24,227        TRACE BasicBinder:81 - binding parameter [4] as [TIMESTAMP] - Wed Mar 02 21:24:24 CET 2011
21:24:24,228        TRACE BasicBinder:81 - binding parameter [5] as [VARCHAR] - Product 1 modified
21:24:24,229        TRACE BasicBinder:81 - binding parameter [6] as [INTEGER] - 1

Notice that two extra columns createdBy_id and lastModifiedBy_id have been created. You can't deactivate this functionality. Because I didn't activate it in the applicationContext.xml file, the columns remain blank but they still exist in the database.

Auditing the user that does the modification is however a very common requirement. So let's have a quick overview of the corresponding configuration with Hades: I have defined an AbstractAuditable class that implements

package com.octo.rnd.hades.auditing;
import org.joda.time.DateTime;
import org.synyx.hades.domain.AbstractPersistable;
import org.synyx.hades.domain.auditing.Auditable;
//Other imports

@MappedSuperclass
public abstract class AbstractAuditable<PK extends Serializable> extends
        AbstractPersistable<PK> implements Auditable<String, PK> {

    private static final long serialVersionUID = 141481953116476081L;

    private String createdBy;

    @Temporal(TemporalType.TIMESTAMP)
    private Date createdDate;

    private String lastModifiedBy;

    @Temporal(TemporalType.TIMESTAMP)
    private Date lastModifiedDate;


    public DateTime getCreatedDate() {

        return null == createdDate ? null : new DateTime(createdDate);
    }

    public void setCreatedDate(final DateTime createdDate) {

        this.createdDate = null == createdDate ? null : createdDate.toDate();
    }

    public DateTime getLastModifiedDate() {

        return null == lastModifiedDate ? null : new DateTime(lastModifiedDate);
    }

    public void setLastModifiedDate(final DateTime lastModifiedDate) {

        this.lastModifiedDate =
                null == lastModifiedDate ? null : lastModifiedDate.toDate();
    }
	
	//Other getter and setter are classical ones
}

Then, I have defined an AuditorStringAwareImpl

package com.octo.rnd.hades.auditing;

import org.synyx.hades.domain.auditing.AuditorAware;

public class AuditorStringAwareImpl implements AuditorAware<String> {

    public String getCurrentAuditor() {

        return "AuditorString";
    }

}

I have configured these two beans in the Spring configuration applicationContext.xml.

<hades:auditing auditor-aware-ref="auditorStringAware"></hades:auditing>
	
	<bean id="auditingAware" class="com.octo.rnd.hades.auditing.AuditingAwareImpl">
		<constructor-arg>
			<ref bean="UserEm" />
		</constructor-arg>
	</bean> 
	<bean id="auditableProductDao" class="org.synyx.hades.dao.orm.GenericJpaDao" init-method="validate">
		<property name="domainClass" value="fr.bnpp.pf.personne.concept.model.Product" />
	</bean>
	
	<bean id="auditorStringAware" class="com.octo.rnd.hades.auditing.AuditorStringAwareImpl"></bean>
	<bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor"></bean>

I can then get the following logs:

Hibernate: insert into Product (id, createdBy, createdDate, lastModifiedBy, lastModifiedDate, description) values (null, ?, ?, ?, ?, ?)
21:50:18,759        TRACE BasicBinder:81 - binding parameter [1] as [VARCHAR] - AuditorString
21:50:18,769        TRACE BasicBinder:81 - binding parameter [2] as [TIMESTAMP] - Sat Apr 02 21:50:18 CEST 2011
21:50:18,770        TRACE BasicBinder:81 - binding parameter [3] as [VARCHAR] - AuditorString
21:50:18,770        TRACE BasicBinder:81 - binding parameter [4] as [TIMESTAMP] - Sat Apr 02 21:50:18 CEST 2011
21:50:18,771        TRACE BasicBinder:81 - binding parameter [5] as [VARCHAR] - Product1
Hibernate: call identity()
Hibernate: select product0_.id as id17_0_, product0_.createdBy as createdBy17_0_, product0_.createdDate as createdD3_17_0_, product0_.lastModifiedBy as lastModi4_17_0_, product0_.lastModifiedDate as lastModi5_17_0_, product0_.description as descript6_17_0_ from Product product0_ where product0_.id=?
21:50:18,804        TRACE BasicBinder:81 - binding parameter [1] as [INTEGER] - 1
21:50:18,811        TRACE BasicExtractor:70 - found [AuditorString] as column [createdBy17_0_]
21:50:18,850        TRACE BasicExtractor:70 - found [2011-04-02 21:50:18.614] as column [createdD3_17_0_]
21:50:18,851        TRACE BasicExtractor:70 - found [AuditorString] as column [lastModi4_17_0_]
21:50:18,851        TRACE BasicExtractor:70 - found [2011-04-02 21:50:18.614] as column [lastModi5_17_0_]
21:50:18,852        TRACE BasicExtractor:70 - found [Product1] as column [descript6_17_0_]
Hibernate: update Product set createdBy=?, createdDate=?, lastModifiedBy=?, lastModifiedDate=?, description=? where id=?
21:50:20,331        TRACE BasicBinder:81 - binding parameter [1] as [VARCHAR] - AuditorString
21:50:20,332        TRACE BasicBinder:81 - binding parameter [2] as [TIMESTAMP] - 2011-04-02 21:50:18.614
21:50:20,333        TRACE BasicBinder:81 - binding parameter [3] as [VARCHAR] - AuditorString
21:50:20,333        TRACE BasicBinder:81 - binding parameter [4] as [TIMESTAMP] - Sat Apr 02 21:50:20 CEST 2011
21:50:20,334        TRACE BasicBinder:81 - binding parameter [5] as [VARCHAR] - Product 1 modified
21:50:20,334        TRACE BasicBinder:81 - binding parameter [6] as [INTEGER] - 1

Instead of implementing manually the org.synyx.hades.domain.auditing.Auditable interface, we could have used directly the class AbstractAuditable provided by Hades. However, this class requires that the class U describing the user to be an entity. Moreover, retrieving a user entity in the AuditorStringAwareImpl leads to some problems. Loading order and dependency injection between the component managed by Spring and the listeners managed by Hibernate do not work out of the box. Having a list of users in the database was largely overkill for our need. So I won't get into such details in this article.

Delegating to a second dedicated tool : Hibernate Envers

Finally, Hibernate, the JPA major implementation, includes since its 3.5 version an auditing functionality. It was previously called Envers module. By just adding some Hibernate specific annotations on the classes and some properties in the configuration files, all changes are audited. For example on a product class you would add:

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.GeneratedValue;

@Entity
@Audited
public class Product {
    @Id
    @GeneratedValue
    private int id;
	
    private String description;
}

In order to use this functionality, you have to add in the META-INF/persistence.xml file

<persistence-unit>

<properties>
   <!-- other hibernate properties -->
   <property name="hibernate.ejb.event.post-insert" value="org.hibernate.ejb.event.EJB3PostInsertEventListener,org.hibernate.envers.event.AuditEventListener"></property>
   <property name="hibernate.ejb.event.post-update"  value="org.hibernate.ejb.event.EJB3PostUpdateEventListener,org.hibernate.envers.event.AuditEventListener"></property>
   <property name="hibernate.ejb.event.post-delete"  value="org.hibernate.ejb.event.EJB3PostDeleteEventListener,org.hibernate.envers.event.AuditEventListener"></property>
   <property name="hibernate.ejb.event.pre-collection-update" value="org.hibernate.envers.event.AuditEventListener"></property>
   <property name="hibernate.ejb.event.pre-collection-remove" value="org.hibernate.envers.event.AuditEventListener"></property>
   <property name="hibernate.ejb.event.post-collection-recreate" value="org.hibernate.envers.event.AuditEventListener"></property>
</properties>
</persistence-unit>

Hibernate creates for you an audit table, with an _AUD suffix, for each audited class. Such implementation was overkill for our particular need: adding extra tables was not desirable. However, for deep auditing requirements, envers module is one of the best choices.

Conclusion

To conclude, we finally used a base class in our particular case. This is not the right tool for every situation but I trust this table will help you make your choice: table { border-width:2px; border-style:solid; } td { border-width:1px; border-style:solid; }