How to find all revisions for an entity using spring data envers?

Question

I am using spring-data-envers in my spring boot application. I can successfully log the audits on my entities.

Now, I need to show audited data to the user in UI. Like there will be search form where user can select duration and entity for which he wants to see audit logs.

RevisionRepository provided by string-data-envers has only three methods as follows.

@NoRepositoryBean
public interface RevisionRepository<T, ID extends Serializable, N extends Number & Comparable<N>> {

    /**
     * Returns the revision of the entity it was last changed in.
     * 
     * @param id must not be {@literal null}.
     * @return
     */
    Revision<N, T> findLastChangeRevision(ID id);

    /**
     * Returns all {@link Revisions} of an entity with the given id.
     * 
     * @param id must not be {@literal null}.
     * @return
     */
    Revisions<N, T> findRevisions(ID id);

    /**
     * Returns a {@link Page} of revisions for the entity with the given id.
     * 
     * @param id must not be {@literal null}.
     * @param pageable
     * @return
     */
    Page<Revision<N, T>> findRevisions(ID id, Pageable pageable);
}

How do I write a custom query to get all the revisions for an entity between two dates by a particular user.

Note that, I have added additional columns to the user_rev_entity table where I store user id and modified date. If I join this table with the entity_aud table I can get the results.

Below are the scripts of my Audit tables.

CREATE TABLE `user_rev_entity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` bigint(20) NOT NULL,
  `created_by` bigint(20) NOT NULL,
  `created_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

CREATE TABLE `revchanges` (
  `rev` int(11) NOT NULL,
  `entityname` varchar(255) DEFAULT NULL,
  KEY `FK_et6b2lrkqkab5mhvxkv861n8h` (`rev`),
  CONSTRAINT `FK_et6b2lrkqkab5mhvxkv861n8h` FOREIGN KEY (`rev`) REFERENCES `user_rev_entity` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `roles_aud` (
  `role_id` bigint(20) NOT NULL,
  `rev` int(11) NOT NULL,
  `revtype` tinyint(4) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `description_mod` bit(1) DEFAULT NULL,
  `display_name` varchar(255) DEFAULT NULL,
  `display_name_mod` bit(1) DEFAULT NULL,
  `is_enabled` bit(1) DEFAULT NULL,
  `enabled_mod` bit(1) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `title_mod` bit(1) DEFAULT NULL,
  PRIMARY KEY (`role_id`,`rev`),
  KEY `FK_pkqm51vsc35w2axvnns4bpas9` (`rev`),
  CONSTRAINT `FK_pkqm51vsc35w2axvnns4bpas9` FOREIGN KEY (`rev`) REFERENCES `user_rev_entity` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

So, basically I am looking for all the changes made by a particular user during particular time for an entity say Role.

There will be lot more such entities.

Answer 1

Have you looked at AuditReaderFactory and AuditReader?

Check out the documentation for AuditReader's createQuery:

A query creator, associated with this AuditReader instance, with which queries can be created and later executed.

That returns an AuditQueryCreator which you can use to create a query like so:

AuditQuery query = getAuditReader().createQuery()
.forRevisionsOfEntity(MyEntity.class, false, true);

There are a couple of options for forRevisionsOfEntity, see AuditQueryCreator documentation.

The query should allow you to select specific revisions using AuditCriterion

Hibernate envers documentation relating to this: http://docs.jboss.org/hibernate/orm/5.0/userGuide/en-US/html_single/#revisions-of-entity

You can add constraints to this query in the same way as to the previous one. There are some additional possibilities:

1. using AuditEntity.revisionNumber() you can specify constraints, projections and order on the revision number, in which the audited entity was modified
2. similarly, using AuditEntity.revisionProperty(propertyName) you can specify constraints, projections and order on a property of the revision entity, corresponding to the revision in which the audited entity was modified
3. AuditEntity.revisionType() gives you access as above to the type of the revision (ADD, MOD, DEL).

EDIT I gave a shot at an actual solution. I have little envers and hibernate criteria experience, so this might not be right, but maybe it will help you get started.

AuditQuery query = getAuditReader().createQuery()
        .forRevisionsOfEntity(MyEntity.class, false, true);

query.add(AuditEntity.revisionProperty("createdDate").gt(minDate))
        .add(AuditEntity.revisionProperty("createdDate").lt(maxDate))
        .add(AuditEntity.revisionProperty("createdBy").eq(userId));

//the documentation shows getSingleResult returns a number
//so i'm guessing a resultList also contains numbers
List<Number> resultList = query.getResultList();