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