All @Version fields in AUD table are null when used hibernate-envers, but in entity - table filled okey?

Question

There is application spring+jpa+envers(hibernate) envers needs to save history of entities in special table.

After I saved a few times my entity, I expected to see filled version field in USER table and filled version field in USER_AUT. But actual result is correct value in USER table, but added REV_TYPE, REV columns( in field just couter's for all rows) and null in version colums.

I use 4.0.1.Final hibernate

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

But, when I look in table, all values in Version field are null

My entity is

import lombok.*;
import org.hibernate.annotations.GenericGenerator;
import org.hibernate.envers.Audited;

import javax.persistence.*;

@Entity
@Audited
@Table(name = "User", uniqueConstraints = {
        @UniqueConstraint(columnNames = { "prKey"})})
@NoArgsConstructor(access = AccessLevel.PUBLIC)
@AllArgsConstructor
@Getter
@Setter
public class User {
    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid2")
    @Column(name = "PR_KEY", unique = true)
    private String prKey;

    @Column(name = "name", length = 100, unique = false)
    private String name;

    @Version
    private int version;

    public User(String name){
        this.name = name;
    }
}

And when I get entities using audit:

 public List<User> getHistory(String id) {
        AuditReader auditReader = AuditReaderFactory.get(entityManagerFactory.createEntityManager());

        List<Number> auditVersions = auditReader.getRevisions(User.class, id);
        List<User> users = auditVersions.stream().map(item -> auditReader.find(User.class, id, item.intValue())).collect(Collectors.toList());

        return extractRiskMetrics(riskMetricRecords);
    }

So, my persistence - config is

@Configuration
@EnableTransactionManagement
@EnableJpaAuditing
@EnableJpaRepositories(basePackages = {"persistence"})
@ComponentScan(basePackages = {"persistence", "model"})
public class PersistenceConfig {
    private static final String PACKAGE_WITH_JPA_ENTITIES = "persistence";
    private final Logger log = Logger.getLogger(getClass());

    @Bean
    @Resource(type = DataSource.class, lookup = "jdbc/MyDatasource", name = "jdbc/MyDatasource")
    public DataSource dataSource() {
        final JndiDataSourceLookup dsLookup = new JndiDataSourceLookup();
        dsLookup.setResourceRef(true);
        DataSource dataSource = dsLookup.getDataSource("java:comp/env/jdbc/MyDatasource");
        return dataSource;
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
        LocalContainerEntityManagerFactoryBean entityManager = new LocalContainerEntityManagerFactoryBean();
        entityManager.setDataSource(dataSource());
        entityManager.setPackagesToScan(PACKAGE_WITH_JPA_ENTITIES);
        entityManager.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        entityManager.setJpaProperties(getHibernateProperties());
        log.info("Entity Manager configured.");
        return entityManager;
    }

    @Bean
    public JpaTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory);
        return transactionManager;
    }

    //Set properties hibernate
    private Properties getHibernateProperties() {
        Properties properties = new Properties();
        properties.put("hibernate.dialect", "org.hibernate.dialect.OracleDialect");
        properties.put("hibernate.show_sql", "true");
        properties.put("hibernate.hbm2ddl.auto", "none");
        properties.put("org.hibernate.envers.do_not_audit_optimistic_locking_field", false);

        properties.put("verifyServerCertificate", false);
        properties.put("useSSL", false);
        properties.put("requireSSL", false);
        properties.put("useLegacyDatetimeCode", false);
        properties.put("useUnicode", "yes");
        properties.put("characterEncoding", "UTF-8");
        properties.put("serverTimezone", "UTC");
        properties.put("useJDBCCompliantTimezoneShift", true);
        return properties;
    }
}

Updates:

org.hibernate.envers.do_not_audit_optimistic_locking_field set to false, but version fields still null.

May be it linked with conflict Spring Data Jpa and Hibernate - envers?

In fact, executed querie(changed and f.c.)

[1/22/19 14:04:51:996 MSK] 00000096 SystemOut     O Hibernate: update UserRecord set User=?, version=? where PR_KEY=? and version=?
[1/22/19 14:04:51:998 MSK] 00000096 SystemOut     O Hibernate: select hibernate_sequence.nextval from dual
[1/22/19 14:04:52:000 MSK] 00000096 SystemOut     O Hibernate: insert into REVINFO (REVTSTMP, REV) values (?, ?)
[1/22/19 14:04:52:002 MSK] 00000096 SystemOut     O Hibernate: insert into UserRecord_AUD (REVTYPE, busId, User, UserType, someInfo, PR_KEY, REV) values (?, ?, ?, ?, ?, ?, ?)

So, in AUD table there is no where version=?

Answer 1

Look at configuration setting org.hibernate.envers.do_not_audit_optimistic_locking_field.

This configuration setting controls whether or not Hibernate Envers will include the @Version annotated field in the audit schema or not. By default the setting is set to true which means that the optimistic locking field won't be audited. By setting this to false, you will audit the column's value.

I do want to caution you about setting this field to false.

If your application performs explicit optimistic-locking increment functionality, this will lead to additional rows being added to the audit history table even if none of the other database columns are changed as part of your business process. This is because once you enable @Version fields to be tracked, Hibernate Envers simply treats them as any other basic attribute on the entity. Therefore, forced optimistic-lock increment will trigger an audit change.

Answer 2

As you mentioned - REVINFO is a centralized table for all audited entities.

The basic idea presented below is to remap the revision numbers to an integer sequence - so RevNumber(2,5,31,125) will be remapped to customVersion(1,2,3,4)

Let's say you have EntityA and you want to fetch all revision s for it (and map each revision data to a custom class RevisionEntityDto).

Using AuditReader from Envers you can do something like:

AuditReader auditReader = AuditReaderFactory.get(entityManager);
//getRevisions() returns revisions sorted in ascending order (older revisions come first)
List<Number> entityARevisions = auditReader.getRevisions(EntityA.class, primaryKeyOfEntityA);

//entityARevisions is already sorted;
for (int customVersion = 0; customVersion < entityARevisions.size(); customVersion++) {
   createRevisionEntityDto(primaryKeyOfEntityA, auditReader, revision, customVersion);
}

private RevisionEntityDto createRevisionEntityDto(Long primaryKeyOfEntityA, AuditReader, Number revision) {
  EntityA revisionOfEntityA = auditReader.find(EntityA.class, primaryKey, revision);
  Date revDate = auditReader.getRevisionDate(revision);
  // at this point you have a single revision of EntityA
  return toRevisionEntityDto(revision, revisionOfEntityA, revDate);
}

private RevisionEntityDto toRevisionEntityDto(Number revision, EntityA revisionOfEntityA, Date revisionDate, int customVersion) {
  //here you do the mapping logic;
  RevisionEntityDto revEntityDto = new RevisionEntityDto();
  revEntityDto.setFieldA(revisionOfEntityA.getFieldA);
  revEntityDto.setDate(revisionDate); // you can use the date to sort if you want at a later stage;
  revEntityDto.setCustomVersion(customVersion);
  return revEntityDto;
}