How to find all changes for an object using spring data?

Question

I am using spring-data-envers in my spring boot application. I can successfully register audits at my facilities.

Now I need to show the verified data to the user in the user interface. Similar to the search form, where the user can select the duration and entity for which he wants to see audit logs.

The RevisionRepository provided with string-data-envers has only three methods.

@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 to write your own query to get all changes for an entity between two dates by a specific user.

Please note that I added additional columns to the user_rev_entity table, where I store the user ID and the modified date. If I join this table using the entity_aud table, I can get the results.

The following are the scenarios 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'm looking for all the changes made by a particular user at a specific time when the entity says “Role”.

There will be more such facilities.

Answer 1

Spring Data Envers does not support custom queries, for example, in spring jpa data. All he does is the convenient RevisionRepository interface.

However, you can use the Revisions<N, T> findRevisions(ID id) method and apply the filter from your service level. Here is an example.

 @Service public UserService { @Resource private UserRepository userRepository; public List<Revision<Integer, User>> findRevisionsBetweenDates(int id, Date startDate, Date endDate){ return userRepository.findRevisions(id).getContent().stream().filter( revision -> revision.getEntity().getCreatedDate().getTime() > startDate.getTime() && revision.getEntity().getCreatedDate().getTime() < endDate.getTime() ).collect(Collectors.toList()); } 

This is not ideal, as you will get all versions from the database. However, this is the easiest way to save the abstraction from the database.

Otherwise, you need to skip spring Data Envers and work with the Envers API.

Good Luck Relationships

Answer 2

Have you watched AuditReaderFactory and AuditReader?

Check out the documentation for AuditReader createQuery :

The request creator associated with this AuditReader instance with which you can create and execute queries.

This returns an AuditQueryCreator, which you can use to create such a query:

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

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

The request should allow you to select specific versions using AuditCriterion

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

You can add restrictions to this request in the same way as the previous one. There are several additional features:

• using AuditEntity.revisionNumber (), you can specify restrictions, forecasts and order on the revision number in which the object was changed.
• similarly, using AuditEntity.revisionProperty (propertyName) you can specify restrictions, forecasts and order on the property of the revision of the entity corresponding to the revision in which the organization being audited was changed.
• AuditEntity.revisionType () gives you access, as indicated above, for the type of revision (ADD, MOD, DEL).

EDIT I gave a real solution. I have little chance of experiencing or sleeping, so this may not be right, but it may 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(); 

Answer 3

Another problem is that the findRevisions method internally uses the sql IN clause, which will fail if there are more than 999 records.