This post is the second one in my series about Hibernate Envers. In the previous post, I showed you how to add Envers to your project, let it document all changes of your audited entities and perform basic queries to retrieve data from your log. That was everything you need to get started with Hibernate Envers. But if you want to use it in a real project, you will quickly recognize that you need more advanced query capabilities. Envers provides you with a powerful query API that allows you to define these kinds of queries easily. I show you how to do that in this post.
The 2 dimensions of audit information
Hibernate Envers creates a new revision for each transaction that creates, updates or deletes an audited entity and stores it in the database. That adds a 2nd dimension to your data structure as you can see in the following graphic.
You can use 2 different perspectives when you look at your audit log. The vertical perspective looks at an entity instance and shows you in which revisions it was created, edited or deleted. The horizontal perspective looks at a revision and shows you the information stored in your database at that point in time.
With Hibernate Envers, you can use both perspectives to look at your audit information. You just need to tell Envers which perspective you want to use when you define your query. As soon as you’ve done that, you can use the API to define your query.
Create a vertical query
Let’s have a look at the definition of a vertical query first. It allows you to retrieve the revisions in which an entity instance was created, edited or deleted. You can create such a query by calling the forRevisionsOfEntity(Class c, boolean selectedEntitiesOnly, boolean selectDeletedEntities) method as I do in the following code snippet.
The selectedEntitiesOnly parameter defines if you want to retrieve a list of entities which changed at the selected revisions or if you want to retrieve a list of arrays with an instance of the affected entity, a revision entity, and a RevisionType enum. This parameter has no effect when you define a projection for your query. In the following code snippet, I set this parameter to true, and the query returns the entities which were active at the selected revisions.
The effect of the selectDeletedEntities is pretty obvious. When you set it to true, your query will also return deleted entities. All attributes of a deleted entity except its id are null.
AuditQuery q = auditReader.createQuery().forRevisionsOfEntity(Book.class, true, true); q.add(AuditEntity.id().eq(b.getId())); List<Book> audit = q.getResultList();
The first line in the code snippet creates an AuditQuery that selects Book entities for all revisions which created, updated or deleted a Book entity. In this example, I only want to retrieve all versions of one specific Book entity. I, therefore, add an expression in the second line of the code snippet. As you can see, you can use the AuditEntity class to reference the audited entity in an expression. It provides you dedicated methods to reference the id, revision number, and revision type attributes and the property(String propertyName) method to reference all other attributes in a generic way.
The next code snippet shows another example of a vertical query. This one returns the number of the first revision in which the Book entity with a given id had the title “Hibernate Tips – 64 Tips for your day to day work”. As you can see, I set the selectedEntitiesOnly parameter of the forRevisionsOfEntity method to false and I called the addProjection method to select the smallest revision number that fulfills the defined criteria.
AuditQuery q = auditReader.createQuery().forRevisionsOfEntity(Book.class, false, true); q.addProjection(AuditEntity.revisionNumber().min()); q.add(AuditEntity.id().eq(b.getId())); q.add(AuditEntity.property(“title”).eq(“Hibernate Tips – 64 Tips for your day to day work”)); Number revision = (Number) q.getSingleResult();
Create a horizontal query
You define horizontal queries in a similar way as the vertical queries I showed you before. You just need to call the forEntitiesAtRevision method instead of the forRevisionsOfEntity when you create the AuditQuery.
The AuditQuery in the following code snippet selects all Book entities in revision 2 which title contained the word “Hibernate” and returns them in the ascending order of their title. As you can see, you can use the same API to define a vertical and a horizontal query.
AuditQuery q = auditReader.createQuery().forEntitiesAtRevision(Book.class, 2); q.add(AuditEntity.property(“title”).ilike(“Hibernate”, MatchMode.ANYWHERE)); q.addOrder(AuditEntity.property(“title”).asc()); List<Book> audit = q.getResultList();
The AuditEntity class provides several other methods which allow to define more complex queries. You can, for example, join associated entities and connect multiple expressions as I do in the following code snippet. That AuditQuery returns all Book entities in revision 2 which title contained “JPA” or “Hibernate” and which were published by a Publisher which name contained “Manning”.
AuditQuery q = auditReader.createQuery().forEntitiesAtRevision(Book.class, 2); q.traverseRelation(“publisher”, JoinType.LEFT, “p”); q.add(AuditEntity.and( AuditEntity.or(AuditEntity.property(“title”).ilike(“JPA”, MatchMode.ANYWHERE), AuditEntity.property(“title”).ilike(“Hibernate”, MatchMode.ANYWHERE)), AuditEntity.property(“p”, “name”).ilike(“Manning”))); q.addOrder(AuditEntity.property(“title”).asc()); List<Book> audit = q.getResultList();
The audit log stores the data of your entities for each revision. This introduces a second dimension to your data and allows you to look at it from two perspectives:
- The vertical perspective shows you the revisions which created, updated or changed a specific set of entities.
- The horizontal perspective shows you your database at a given point in time.
Hibernate Envers provides an API that allows you to define complex queries for both perspectives. You just have to define which one you want to use when you create your AuditQuery. Afterward, you can use the same methods and concepts to select entities at a given point in time or to select revisions which affected a specific set of entities.