How to use Hibernate Envers to audit data including username information

Recently, I have been working in a project which it was required to audit all the database transactions including the username. For this purpose I have been using Hibernate ORM Envers, which aims to enable easy auditing/versioning of persistent classes.

In order to show how to use Hibernate Envers in this post I have built a sample project, based on this Spring boot JPA project. Spring Boot makes it easy to create stand-alone, production-grade Spring based Applications that you can “just run”. In this case it is very handy because it includes: an embedded tomcat,’starter’ POMs to simplify your Maven configuration, and REST web services ready to use.

This post is structure in 5 simple steps, neverthelesss, the last 2 steps are only useful if you would like to add the username (or any other information) to your revisions.

1. Configure Hibernate envers

2. Audit tables

3. Read Revisions

4. Add username information to each revision

5. Read Revision including username

 

1.Configure Hibernate Envers

First of all, you will need to add the Hibernate Envers dependency into your project. As I am using Maven, for managing my dependecies, all I need to do is add the dependency into my pom file:

 

[xml]


org.hibernate
hibernate-envers
4.3.10.Final

[/xml]

2. Audit Tables

In order to be able to audit your entity tables, you only need to have an entity with a primary key and use the annotation @Audited. You can use the annotation @Audited either at the top of the class (that will audit all the fields of the class) or only in the fields that you would like to audit.

Once you add the @Audited annotation you will see that a new table with the suffix “_AUD” will be created for each entity. Also you will find that a new table called REVINFO, which contains all the revisions information, has been created. By default this table will only contain the id of the revision and the timestamp, although on section 4 and 5 I will show you how to add more data to this table, like the username.

Here is an example of an audited java class:

[java]
@Entity
@Audited
public class City implements Serializable {

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Column
private String name;

@Column
private String state;

@Column
private String country;

@Column
private String map;

//getters and setters

}
[/java]

In this case we will automatically have in our database 2 tables, one called City and another one called City_AUD which will include all the city fields plus a revision identifier and a revision type (creation, update, delete).

3. Read Revisions

If you have already configured Hibernate Envers into your project, now every time that you add or update an object into the database you will see that another row will be added to the audited table. Now we would like to get the different revisions for a specific object.

Hibernate Envers includes a class called Audit Reader which makes easier to read any revision. You only need to initialize it with an entity manager. Then you can find any revision using the find method:

AuditReader reader = AuditReaderFactory.get(entityManager);
reader.find(class, object id, revision id);

Here is an example of creating and updating a City object and then check the revisions:

[java]
final static String WRONG_STATE = “Wrong State”;
final static String OXFORDSHIRE_STATE = “Oxfordshire”;

@PersistenceContext
private EntityManager entityManager;

@Autowired
private Transactor transactor;

long cityId;

@Test
public void testSaveAndUpdateCity(){
transactor.perform(() -> {
addCity();
});
transactor.perform(() -> {
updateCity(cityId);
});

transactor.perform(() -> {
checkRevisions(cityId);
});

}

private void addCity(){
City city = new City(“Oxford”, “UK”);
city.setState(WRONG_STATE);
entityManager.persist(city);
entityManager.flush();
cityId = city.getId();
}

private void updateCity(long cityId) {
City updateCity = entityManager.find(City.class, cityId);
updateCity.setState(OXFORDSHIRE_STATE);
entityManager.persist(updateCity);
entityManager.flush();
}

private void checkRevisions(long cityId){
AuditReader reader = AuditReaderFactory.get(entityManager);

City city_rev1 = reader.find(City.class, cityId, 1);
assertThat(city_rev1.getState(), is(WRONG_STATE));

City city_rev2 = reader.find(City.class, cityId, 2);
assertThat(city_rev2.getState(), is(OXFORDSHIRE_STATE));
}

[/java]

Transactor is a custom class for running a piece of code in a single transaction. As you can see in the checkRevisions function, we are using the AuditReader for getting the different revisions for the same city.

The AuditReader has more methods which will make you easier to look for any revision, please have a look to the api for more information.

4. Add username information to each revision

If you have followed the first 3 steps, you should be able to audit and read any revision, and for some projects that’s all you need. Nevertheless, in other cases you will need to audit more information like the username of the person who did the transaction. For this purpose you will need to implement this 2 classes:

  • Revision Entity
  • Revision Listener

The Revision entity is the class which contains the data that you would like to audit in each transaction. It is mandatory that this class contains a revision number and a revision timestamp, either you can add these 2 fields manually or you can extend the DefaultRevisionEntity class which already contains them.

Here is an example of my revision entity which extends DefaultRevisionEntity and have an attribute for the username:

[java]
@Entity
@RevisionEntity(UserRevisionListener.class)
public class UserRevEntity extends DefaultRevisionEntity {

private String username;

public String getUsername() { return username; }
public void setUsername(String username) { this.username = username; }
}
[/java]

Note that the class contains the @RevisionEntity(UserRevisionListener.class) annotation. The revision listener is the class where you need to specify how to populate the additional data into the revision. This class needs to implement the RevisionListener interface which has only one method called newRevision.

Let’s see an example:

[java]
public class UserRevisionListener implements RevisionListener {

public final static String USERNAME = “Suay”;

@Override
public void newRevision(Object revisionEntity) {
UserRevEntity exampleRevEntity = (UserRevEntity) revisionEntity;
exampleRevEntity.setUsername(USERNAME);
}
}
[/java]

In this example, I am auditing any transaction with the same username (“SUAY”), but in a real environment you should get the username from a User service or any other method that you use to log the users.

5. Read Revision including username

For getting the user information, I run a query using the method “forRevisionsOfEntity” which will return a triplet of objects: the entity, the entity revision information and at last the revision type. Only the second object is the one that I am interested because it containes the username information. Please have a look to the following example:

[java]
@Test
public void testUserRevisions() throws Exception{
transactor.perform(() -> {
addCity();
});
transactor.perform(() -> {
checkUsers();
});
}

private void checkUsers(){
AuditReader reader = AuditReaderFactory.get(entityManager);
AuditQuery query = reader.createQuery()
.forRevisionsOfEntity(City.class, false, false);

//This return a list of array triplets of changes concerning the specified revision.
// The array triplet contains the entity, entity revision information and at last the revision type.
Object[] obj = (Object[]) query.getSingleResult();

//In this case we want the entity revision information object, which is the second object of the array.
UserRevEntity userRevEntity = (UserRevEntity) obj[1];

String user = userRevEntity.getUsername();
assertThat(user, is(UserRevisionListener.USERNAME));

}
[/java]

Running the sample project

All the code used in this post is available in github.

If you would like to run the project, you just need to run the main methon in SampleDataJpaApplication.

The project includes a H2 console so you can see on any browser your database. Try:

1) go to http://localhost:8080/console

2) In the login form, please add the following information:

JDBC URL: jdbc:h2:mem:testdb
username: sa
password:
(empty password)

3) Now you should see the followin database structure:
h2console

You may also like...

29 Responses

  1. Nenad says:

    Thanks a lot! This is exactly the information I needed. You really saved me a lot of work, since I was going to implement the auditing myself.

  2. koen says:

    Nice Article,

    I tried to implement it with Spring (Security)
    I created a UserAuditService where i want to call the AuditReader,

    when i include like this:

    @PersistenceContext
    private EntityManager entityManager;

    I always get the next error:
    org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type [javax.persistence.EntityManagerFactory] is defined

    So i cannot do in that service:

    AuditReader auditReader = AuditReaderFactory.get(entityManager);

    I tried to add:
    @PersistenceUnit
    protected EntityManagerFactory emf;

    @Bean
    @Qualifier(value = “entityManager”)
    public EntityManager entityManagere(EntityManagerFactory entityManagerFactory) {
    return entityManagerFactory.createEntityManager();
    }

    But same error

    How to let the reader work in a spring contect?

  3. koen says:

    I found the solution of my own problem.

    The only thing I needed to do is Autowire the SessionFactory

    @Autowired
    SessionFactory sessionFactory;

    And later do:

    Session currentSession = sessionFactory.getCurrentSession();
    AuditReader auditReader = AuditReaderFactory.get(currentSession);

  4. Belen says:

    Where do you save the username? I add the two classes you mentioned and I don’t find where is it saved. Should’t be in revinfo?

    • Ignacio Suay says:

      Dear Belem,

      The username should be saved in the UserRevEntity table. Depending of your hibernate configuration this column will be added automatically or you will need to add it manually. In my case, I have set in the hibernate config file the property ddl-auto to “update”, that means that the column is added automatically, if it is not there.

      Please let me know if you have any comments or queries.

    • Petar Banicevic says:

      I had similar question – username wasn’t filled in. My answer is yes it should be REVINFO that will have username column. I quickly solved problem, easily, by adding in persistence.xml REVINFO entity. Once JPA found it there, it started to work. Somewhere along the way, JPA decided to change column names to another defaults.

      persistence.xml looks like:

      My entity looks like:
      @Entity
      @Table(name=”REVINFO”)
      @RevisionEntity(RevInfoListener.class)
      public class RevInfo extends DefaultRevisionEntity {

      In RevInfoListener I used small trick to get currently loged-on user from Spring:
      public class RevInfoListener implements RevisionListener {

      @Override
      public void newRevision(Object revInfo) {
      RevInfo exampleRevEntity = (RevInfo) revInfo;
      String username = getHttpServletRequest().getRemoteUser();
      exampleRevEntity.setUsername(username);
      }

      public HttpSession getSession() {
      ServletRequestAttributes attr = (ServletRequestAttributes) RequestContextHolder.currentRequestAttributes();
      return attr.getRequest().getSession(true); // true == allow create
      }

      public HttpServletRequest getHttpServletRequest() {
      ServletRequestAttributes attr = (ServletRequestAttributes) RequestContextHolder.currentRequestAttributes();
      return attr.getRequest(); // true == allow create
      }
      }

      • afattahi says:

        The RequestContextHolder throws
        No thread-bound request found: Are you referring to request attributes outside of an actual web request, or processing a request outside of the originally receiving thread? If you are actually operating within a web request and still receive this message, your code is probably running outside of DispatcherServlet/DispatcherPortlet: In this case, use RequestContextListener or RequestContextFilter to expose the current request.

        Any comments ? Did you add some configs

  5. Chris says:

    Hi, when i try to do something similar in my project i get an entityManager is closed error when i try to use AuditReader.
    Its not clear to me why you don;t get that problem and i do !
    do you have any guidance on how i should debug this ?
    Ive searched the web and tried various things but i always end up with this problem,

    • Ignacio Suay says:

      Hi Chris, coukd you give me more information about your project. Are you using Spring? J2EE? I guess your problem is how you are retrieving the entity manager and injecting it into the AuditReader.

      • Chris says:

        Hurrah, i managed to solve the problem… i have a spring boot project but when i run the code in the project it works. Only when i was running it as a Junit test was it failing. BY making the test method @Transactional seemed to solve the problem. Im not 100% sure why but clearly it stops the session from closing.

  6. Vamshi says:

    Hi,

    Thanks for the example. But I have a different scenario.
    I have 3 tables.

    student – id, name, addressid, educationId
    address – id, city, state, zip
    education – id, course, specification

    I want to audit such that the student_AUD table should have columns like this..
    id, name, addressid, city, state, zip, educationid, course, specification

    and whenever any information related to address or education is updated or deleted the data should be persisted in the student_AUD table itself and new AUD tables for address and education should not created.

    I am using MySQL, Spring boot and hibernate envers.

    Please help me.

    Thanks in advance

  7. Aman says:

    Hi,
    I am using Hibernate JPA
    with the dependency given above , It gave me runtime exceptions
    Error creating bean with name ‘entityManagerFactory’ defined in ServletContext resource [/WEB-INF/spring/applicationContext.xml]

    So I changed the version of hibernate-envers to 5.2 and it ran fine.

    org.hibernate
    hibernate-envers
    5.2.0.Final

    org.hibernate.javax.persistence
    hibernate-jpa-2.1-api
    1.0.0.Final

    But Audit tables are not getting created..

    Thanks,

    • Ignacio Suay says:

      Hi Aman,

      Thanks for letting me know the problem with the dependencies.

      I have just downloaded the project again and it runs fine in my machine but it could be because I have that dependencies stored in the m2 repository in my machine. Also I run the application and the tables were created.

      These are the steps that I followed:

      1) get the code from github

      2) run: mvn clean install ( you will see that the test are running and all the test should pass)

      3) run in SampleDataJpaApplication the main method

      4) In my browser go to: http://localhost:8080/console/ and access with username: sa and no password

      Please let me know if that helps or you are still having issues.

  8. Shoaib Khan says:

    Entity has no primary key attribute – How did you manged this issue with @Entity annotation on UserRevEntity?
    I”m using envers version 5.2.1.Final

    Seems the property id on DefaultRevisionEntity is private.

    • Ignacio Suay says:

      Did you add the @Id annotation in the UserRevEntity? It should look like something like:

      @Entity
      @Table(name = “REVINFOUSER”, catalog = “chassisCore”)
      @RevisionEntity(UserRevisionListener.class)
      public class UserRevEntity {

      @Id
      @GeneratedValue(strategy= GenerationType.IDENTITY)
      @RevisionNumber
      private int id;

      ….

  9. ShaimaMahmoud says:

    Thanks for the Post,
    But I have an Issue if you don’t mind to help.
    After I applied 1st and 2nd steps and as mentioned that tables should be created automatically ,
    I am trying to build and Run the project , I get a DB Validation exception

    Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing table [REVINFO]

    • Ignacio Suay says:

      Hi Shaima,

      If you would like to Hibernate to automatically create the REVINFO table, try to add the ddl-auto property to your hibernate configuration file:

      <property name="hbm2ddl.auto"> update </property>

      This property could have 4 possible values:
      validate: validate the schema, makes no changes to the database.
      update: update the schema.
      create: creates the schema, destroying previous data.
      create-drop: drop the schema at the end of the session.

      Hope it helps

      • Saul says:

        Hi,
        when I hibernate.hbm2ddl.auto to update, the application throws an error saying

        2017-02-09 14:47:09,810 [localhost-startStop-1] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:146) – Database ‘dbo’ does not exist. Make sure that the name is entered correctly.
        2017-02-09 14:47:09,810 [localhost-startStop-1] ERROR org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:272) – HHH000299: Could not complete schema update
        java.lang.NullPointerException
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
        at org.hibernate.tool.hbm2ddl.DatabaseMetadata.getTableMetadata(DatabaseMetadata.java:158)
        at org.hibernate.cfg.Configuration.generateSchemaUpdateScriptList(Configuration.java:1204)

        I’m really getting crazy with this error. If I set the prop to “create”, some of the tables lose all their data, especially the ones containing many to many relationships.

        Any idea? Thanks

        • Ignacio Suay says:

          Hi Saul,

          It looks like you are having some problems with hibernate recognising your schema. I could imagine 2 possible solutions:
          1) Try to add the naming convention in the same place where you have your hibernate properties. For instance in a different project I have ” naming-strategy: org.hibernate.cfg.EJB3NamingStrategy”
          2) You could add the @table annotation to your entities and then add the name of your schema as an attribute. The table annotation has 2 attributes “schema” and “catalog” that actually mean the same, but depending on the database that you are using you will need one or the other. For instance, you could add to your entity @Table(name=”myTable”, catalog=”dbo”)

          Finally, you need to be very careful when you use “create” as you pointed out, it will remove all the data from your database and create a new one each time you run your application.

          Please let me know if it solves the problem

  10. David says:

    Using your example, when I run “Object[] obj = (Object[]) query.getSingleResult();”, I get the following exception:
    javax.persistence.NonUniqueResultException
    at org.hibernate.envers.query.internal.impl.AbstractAuditQuery.getSingleResult(AbstractAuditQuery.java:104)
    at za.co.itdynamics.planner.audit.EnversAuditTest.checkTaskTypeRevisionUser(EnversAuditTest.java:138)
    at za.co.itdynamics.planner.audit.EnversAuditTest.lambda$testTaskTypeAuditing$3(EnversAuditTest.java:103)
    at za.co.itdynamics.planner.util.Transactor.perform(Transactor.java:22)
    at za.co.itdynamics.planner.audit.EnversAuditTest.testTaskTypeAuditing(EnversAuditTest.java:102)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
    at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassExecuter.runTestClass(JUnitTestClassExecuter.java:114)
    at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassExecuter.execute(JUnitTestClassExecuter.java:57)
    at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassProcessor.processTestClass(JUnitTestClassProcessor.java:66)
    at org.gradle.api.internal.tasks.testing.SuiteTestClassProcessor.processTestClass(SuiteTestClassProcessor.java:51)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35)
    at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
    at org.gradle.internal.dispatch.ContextClassLoaderDispatch.dispatch(ContextClassLoaderDispatch.java:32)
    at org.gradle.internal.dispatch.ProxyDispatchAdapter$DispatchingInvocationHandler.invoke(ProxyDispatchAdapter.java:93)
    at com.sun.proxy.$Proxy2.processTestClass(Unknown Source)
    at org.gradle.api.internal.tasks.testing.worker.TestWorker.processTestClass(TestWorker.java:109)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35)
    at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
    at org.gradle.internal.remote.internal.hub.MessageHub$Handler.run(MessageHub.java:377)
    at org.gradle.internal.concurrent.ExecutorPolicy$CatchAndRecordFailures.onExecute(ExecutorPolicy.java:54)
    at org.gradle.internal.concurrent.StoppableExecutorImpl$1.run(StoppableExecutorImpl.java:40)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)

  11. Panjie says:

    Thank you a lot! It’s help for me.

  12. Laksh says:

    Hi. I cloned your project and ran it. I am getting empty tables for _AUD tables. Can you tell me why and how to fix it ?

  13. Suril says:

    Hello Ignacio,
    Firstly, thanks for the article. It’s pretty useful.
    I’ve set it up on my spring-boot project.
    I’m using spring data jpa’s crud repository for my db transactions.

    For those transactions, wherever, I use the repository methods like save, update, delete I’m able to get entries in _aud tables ,but, for some custom repository methods where i’ve written JPQL queries with @Query annotation, I’m not able to get entries in aud and rev tables.

    Could you please share your views on this?
    Whether it is feasible or not?

    Thanks !!!

  14. Dheeraj Suthar says:

    Very useful article. Saved my day 🙂

  15. Anton says:

    Thanks a lot! Useful information!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.