How to maintain history of tables in Hibernate

Abhishek SomaniSeptember 4th, 2013Last Updated: September 3rd, 2013

5 1,185 4 minutes read

To Maintain history of database or keep track of modification of database table row , we create a version table that contains fields identical to original table.Whenever original table gets changed , we create another entry in version table. So for every update query , we have to write an insert query in version table . There is a module available in hibernate to manage easy auditing of objects , and we don’t have to write separate insert query by ourselves.

Hibernate Envers provides in built mechanism to maintain history of objects in database . Envers is library for Hibernate that will help us to easily achieve audit functionality. This has been created by Adam Warski. From Hibernate 3.5, Envers is included as a Hibernate core module. Let’s take an example as to how Envers can be used to maintain history of objects.

Here is the pom dependency for Envers (Version will be same as your hibernate core and entity manager):

<dependency>  <groupId>org.hibernate</groupId> 
  <artifactId>hibernate-envers</artifactId> 
  <version>4.0.1.Final</version> 
</dependency>

You have to configure listeners in your hibernate.cfg.xml.

<mapping class="com.javaroots.model.User" />

  <listener class="org.hibernate.envers.event.AuditEventListener" type="post-insert"/>
  <listener class="org.hibernate.envers.event.AuditEventListener" type="post-update"/>
  <listener class="org.hibernate.envers.event.AuditEventListener" type="post-delete"/>
  <listener class="org.hibernate.envers.event.AuditEventListener" type="pre-collection-update"/>
  <listener class="org.hibernate.envers.event.AuditEventListener" type="pre-collection-remove"/>
  <listener class="org.hibernate.envers.event.AuditEventListener" type="post-collection-recreate"/>

Let’s take an example of User class . We want to keep track of user fields updating . To Enable history for User Object , we need to use @Audited annotation . If it used on class level , all the fields in the class will be treated as audit-able and change in any of the fields will have a new entry in audit table . If we want some fields not to be included in history , we can use @NotAudited annotation . If a NotAudited field is changed , there will be no entry in audit table.Here is the user class :

package com.javaroots.model;

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

import org.hibernate.envers.Audited;
import org.hibernate.envers.NotAudited;

/**
 * 
 * 
 * @author Abhishek Somani
 * 
 */
@Entity
@Audited
public class User {

 @Id
 @GeneratedValue(strategy = GenerationType.AUTO)
 private Long id;
 @Column(length = 20)
 private String firstName;
 @Column(length = 20)
 private String lastName;
 @Column(length = 20)
 @NotAudited
 private String password;

 public Long getId() {
  return id;
 }

 public void setId(Long id) {
  this.id = id;
 }

 public String getFirstName() {
  return firstName;
 }

 public void setFirstName(String firstName) {
  this.firstName = firstName;
 }

 public String getLastName() {
  return lastName;
 }

 public void setLastName(String lastName) {
  this.lastName = lastName;
 }

 public String getPassword() {
  return password;
 }

 public void setPassword(String password) {
  this.password = password;
 }

}

Here is the test class , where we are creating an entry in user table and then updating it’s field .

package com.javaroots.main;

import org.hibernate.Session;

import com.javaroots.model.User;
import com.javaroots.util.HibernateUtil;

public class HibernateTest {

public static void main(String[] args) {

        Session session = HibernateUtil.getSessionFactory().openSession();

        //one entry will be created in user table 
        //and audit entry created in user_aud table
        session.beginTransaction();

        User u = new User();
        u.setFirstName("Amitabh");
        u.setLastName("bachhan");
        u.setPassword("God");

        session.save(u);

        session.getTransaction().commit();

        session.beginTransaction();
        User amitabh = (User)session.get(User.class,1l);

        amitabh.setFirstName("Abhishek");

        session.getTransaction().commit();

        //no entry in audit table if we change password field
        //because this field is marked as @notAudited
        session.beginTransaction();
        amitabh = (User)session.get(User.class,1l);

        amitabh.setPassword("NotGod");

        session.getTransaction().commit();

   //get specific revision
        AuditReader reader = AuditReaderFactory.get(HibernateUtil.getSessionFactory().openSession());
        User abhishek = (User) reader.find(User.class, new Long(1), 2);
        System.out.println(abhishek.getFirstName() + " " + abhishek.getLastName());

        //get all revision

        List versions = reader.getRevisions(User.class, new Long(1));
        for (Number number : versions) {
          System.out.print(number + " ");
        }

    }

}

First ,a user row is created in user table .one row is created in user_aud with revision id , and user table fields . One row is created in revinfo table with revision id and timestamp . These two entries are done by envers automatically . Here are the sql queries and table structures :

Hibernate: 
    insert 
    into
        User
        (firstName, lastName, password) 
    values
        (?, ?, ?)
Hibernate: 
    insert 
    into
        REVINFO
        (REVTSTMP) 
    values
        (?)
Hibernate: 
    insert 
    into
        User_AUD
        (REVTYPE, firstName, lastName, id, REV) 
    values
        (?, ?, ?, ?, ?)
Hibernate: 
    update
        User 
    set
        firstName=?,
        lastName=?,
        password=? 
    where
        id=?
Hibernate: 
    insert 
    into
        REVINFO
        (REVTSTMP) 
    values
        (?)
Hibernate: 
    insert 
    into
        User_AUD
        (REVTYPE, firstName, lastName, id, REV) 
    values
        (?, ?, ?, ?, ?)
Hibernate: 
    update
        User 
    set
        firstName=?,
        lastName=?,
        password=? 
    where
        id=?
Hibernate: 
    select
        user_aud0_.id as id4_,
        user_aud0_.REV as REV4_,
        user_aud0_.REVTYPE as REVTYPE4_,
        user_aud0_.firstName as firstName4_,
        user_aud0_.lastName as lastName4_ 
    from
        User_AUD user_aud0_ 
    where
        user_aud0_.REV=(
            select
                max(user_aud1_.REV) 
            from
                User_AUD user_aud1_ 
            where
                user_aud1_.REV<=? 
                and user_aud0_.id=user_aud1_.id
        ) 
        and user_aud0_.REVTYPE<>? 
        and user_aud0_.id=?
Abhishek bachhan
Hibernate: 
    select
        user_aud0_.REV as col_0_0_ 
    from
        User_AUD user_aud0_ cross 
    join
        REVINFO defaultrev1_ 
    where
        user_aud0_.id=? 
        and user_aud0_.REV=defaultrev1_.REV 
    order by
        user_aud0_.REV asc

mysql> select * from user;
+----+-----------+----------+---------------+
| id | firstName | lastName | password      |
+----+-----------+----------+---------------+
|  1 | Amitabh   | bachchan    | God|
+----+-----------+----------+---------------+
1 row in set (0.03 sec)

mysql> select * from user_aud;
+----+-----+---------+-----------+----------+
| id | REV | REVTYPE | firstName | lastName |
+----+-----+---------+-----------+----------+
|  1 |   1 |       0 | Amitabh   | bachchan    |
+----+-----+---------+-----------+----------+
1 row in set (0.00 sec)

mysql> select * from revinfo;
+-----+---------------+
| REV | REVTSTMP      |
+-----+---------------+
|   1 | 1375956506278|
+-----+---------------+
1 row in set (0.00 sec)

mysql> select * from user;
+----+-----------+----------+----------------+
| id | firstName | lastName | password       |
+----+-----------+----------+----------------+
|  1 | Amitabh   | bachchan| NotGod |
+----+-----------+----------+----------------+
1 row in set (0.00 sec)

mysql> select * from user_aud;
+----+-----+---------+-----------+----------+
| id | REV | REVTYPE | firstName | lastName |
+----+-----+---------+-----------+----------+
|  1 |   1 |       0 | Amitabh   | bachchan |
|  1 |   2 |       1 | Abhishek  | bachchan|
+----+-----+---------+-----------+----------+
2 rows in set (0.00 sec)

mysql> select * from revinfo;
+-----+---------------+
| REV | REVTSTMP      |
+-----+---------------+
|   1 | 1375956506278|
|   2 | 1375956506328|
+-----+---------------+
2 rows in set (0.00 sec)