Hibernate Envers & Oracle: Automatic column name conversion to uppercase

Good morning,

I’m implementing Spring Data Envers v3.4.3 which embeds Hibernate Envers v6.6.8.Final in my backend project. It’s a database agnostic application so it can work currently with both databases: MS Sql and Oracle.

The tables names and columns names look like: mdm_Branch, TimeZoneKey, BranchKey, CurrencyKey, etc..

To be compatible with Oracle that is sensitive case, I created my JPA entities in the following way:

@Entity
@Table(name="\"mdm_Branch\"")
@Audited
@AuditTable(value="\"mdm_Branch_Aud\"")
public class BranchEntity extends AuditableEntity {    
/**     * Key     */    
@Id    
@Column(name="\"BranchKey\"", nullable = false)    
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="sequenceBranch")   
@SequenceGenerator(name="sequenceBranch", sequenceName="\"seq_mdm_Branch\"", allocationSize=1)    
@Getter    
private Long branchKey;    

/**     * Name     */    
@Column(name="\"BranchName\"", nullable=false, length = 20)    
@Getter   
@Setter    
private String branchName;    

/**     * Time Zone     */    
@ManyToOne(fetch = FetchType.LAZY,            optional = false,            cascade = {CascadeType.DETACH, CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH})    
@JoinColumn(name="\"TimeZoneKey\"", nullable=false)    
@Getter    
@Setter    
private TimeZoneEntity timeZone;    

/**     * Status     */    
@ManyToOne(fetch = FetchType.LAZY,            cascade = {CascadeType.DETACH, CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH},            optional = false)    
@JoinColumn(name="\"RowStatusKey\"", nullable = false)    
@Getter    
@Setter    
private RowStatusEntity rowStatus;    

/**     * UUID alternate key     */    
@Column(name="\"BranchExtKey\"", nullable = false)    
@Getter    
private UUID branchExtKey = UUID.randomUUID();    
}

And I added this parameter to my properties file:

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

Everything seems to work fine however I’m encountering some problems with Envers. I created the main table to store the revision number and the associated entity looks like:

@Entity
@RevisionEntity
@Table(name="\"cfg_RevisionInfo\"")
@EntityListeners(AuditingEntityListener.class)
public class RevisionInfoEntity {    
/**     * Key     */    
@Id    
@Column(name="\"RevisionKey\"", nullable = false)   
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="sequenceRevisionInfo") 
@SequenceGenerator(name="sequenceRevisionInfo", sequenceName="\"seq_cfg_RevisionInfo\"", allocationSize=1)    
@RevisionNumber    
@Getter    
private Long revisionKey;    

/**     * Revision date as timestamp     */    
@Column(name="\"RevisionTimestamp\"")    
@RevisionTimestamp    
@Getter    
@Setter    
private Long revisionTimestamp;   

 /**     * Revision date: When?     */    
@Column(name="\"RevisionDate\"",            nullable=false)    
@CreatedDate    
@Getter    
@Setter    
@TimeZoneStorage(TimeZoneStorageType.NATIVE)    
protected OffsetDateTime revisionDate;    

/**     * Revised by: Who?     */    
@Column(name="\"RevisedBy\"",            length = 50,            updatable=false,            nullable=false)    
@CreatedBy    
@Getter    
@Setter    
protected String revisedBy;    
}

The structure of mentioned audit table in the entity BranchEntity looks like:

CREATE TABLE "mdm_Branch_Aud"(
	"RevisionKey" NUMBER NOT NULL, 
	"RevisionType" NUMBER NOT NULL, 
	"BranchKey" NUMBER NOT NULL, 
	"BranchName" VARCHAR2(20 CHAR) NOT NULL, 
	"TimeZoneKey" NUMBER NOT NULL,
	"CreationDate" TIMESTAMP (6) WITH TIME ZONE NOT NULL, 
	"CreatedBy" VARCHAR2(50 CHAR) NOT NULL,
	"ModificationDate" TIMESTAMP (6) WITH TIME ZONE NOT NULL, 
	"ModifiedBy" VARCHAR2(50 CHAR) NOT NULL,
	"RowStatusKey" NUMBER NOT NULL,
	"RecordVersion" NUMBER NOT NULL,
	"BranchExtKey" RAW(16) NOT NULL,
	CONSTRAINT "PK_mdm_Branch_Aud" PRIMARY KEY ("RevisionKey", "BranchKey") ENABLE,
	CONSTRAINT "FK_mdm_Branch_Aud_cfg_RevInfo" FOREIGN KEY ("RevisionKey") REFERENCES "cfg_RevisionInfo" ("RevisionKey") ENABLE
);

The script created by Hibernate Envers to insert data into the audit table looks like

insert into "mdm_Branch_Aud" ("RevisionType","BranchExtKey","BranchName", "CreatedBy", "CreationDate", "ModificationDate", "ModifiedBy", "RecordVersion", RowStatusKey, TimeZoneKey, "RevisionKey", "BranchKey") 
values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

As you can notice, both columns RowStatusKey andTimeZoneKey are not automatically doubled quoted so the names are automatically upper cased and then have the following error:

ERROR [http-nio-8081-exec-7] o.h.e.jdbc.spi.SqlExceptionHelper : ORA-00904: “TIMEZONEKEY”: invalid identifier
ERROR [http-nio-8081-exec-7] c.m.w.a.e.ApiExceptionHandler : java.sql.SQLSyntaxErrorException: ORA-00904: “TIMEZONEKEY”: invalid identifier

Just in case, TimeZoneEntity and RowStatusEntity are also annotated with @Audited. Don’t understand what is happening with Oracle. Don’t have any problem if I switch over MS Sql.

Could you give me some help to solve this problem? Do I need to create a customized strategy name for envers? If yes, how?

JOS

Hello @Josh, to reiterate what seems to be the problem: the RowStatusKey and TimeZoneKey columns are not rendered as quoted for the insert statement for the Audit table. This only happens for the audit table, and not the entity table itself.

It might be that there is some problem with column-naming specific to envers. Also, I noticed these two are the only two @JoinColumn names, whereas the others are @Columns, that might be part of the problem. Other than this considerations, I don’t see anything wrong with your mappings.

There are a lot of components at play here. I would suggest trying to create an isolated reproducer using only Hibernate with our test case template and if you are able to reproduce the issue, create a new ticket in our issue tracker and attach that reproducer.

Ok. It’s done. I created a small project to show the problem/bug and then created a new ticket on your Jira account