javahibernate-envers

Envers queries use wrong column names for _MOD columns


I am using Hibernate Envers with the @Audited( withModifiedFlag=true ) and I have a problem where the SQL generated for queries has different column names than the one generated by the HBM2DDL tool for the _MOD columns

The issue might have something to do with the fact that I am overriding the Hibernate naming strategy as described here. But trying to debug this, I have had no luck in verifying this theory - the naming strategy does not seem to hit any breakpoints.

The entity looks like this (with irrelevant properties omitted):

@Entity
@Table(name = "file")
@Audited(withModifiedFlag = true)
public class FileEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "file_name")
    private String fileName;
}

The generated _AUD table looks like this:

CREATE TABLE file_AUD (
    id int8 NOT NULL,
    REV int4 NOT NULL,
    REVTYPE int2,
    REVEND int4,
    file_name varchar(255),
    fileName_MOD boolean,
    PRIMARY KEY (id, REV)
);

I can see the difference between the file_name and fileName_MOD columns, but that seems to be expected, as described here

When making a change to the entity or when using the AuditReaderFactory for a query, the following SQL error is shown:

ERROR: column fileentity0_.file_name_mod does not exist
  Hint: Perhaps you meant to reference the column "fileentity0_.filename_mod".

I am using PostgreSQL and Hibernate 5.2.13.Final I understand there is a workaround with using the modifiedColumnName on each property, but the example above is just one of many entities I am trying to audit, so I am looking for other solutions.

EDIT:

Digging a bit deeper into the NamingStrategy, it seems the code was not actually using the custom one I had added. It seems the Hibernate naming_strategy property was split into implicit_naming_strategy and physical_naming_strategy and this seems to have went unnoticed as the version of Hibernate in the project was updated. Instead, these properties are provided by Spring JPA by default as follows:

hibernate.implicit_naming_strategy:org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy
hibernate.physical_naming_strategy:org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy

Since they do the job (outside of this Envers issue), I will not be using the custom one.

An example of a query I use:

AuditReader auditReader = AuditReaderFactory.get(entityManager);

AuditQuery auditQuery = auditReader.createQuery()
        .forRevisionsOfEntity(FileEntity.class, false, true)
        .addOrder(AuditEntity.revisionNumber().desc())
        .setFirstResult((int) pageable.getOffset())
        .setMaxResults(pageable.getPageSize());

Solution

  • The ideal solution is to extend the spring-boot implementations and check whether the identifeir ends with the _MOD suffix and if so, have it return the same identifier without changing it. Then configure spring-boot to use the custom naming strategy and things should work without having to rename columns.