I have a table like below.
╔════╦══════════════╦══════╗
║ Id ║ User ║ Age ║
╠════╬══════════════╬══════╣
║ 1 ║ Foo Bar ║ 35 ║
║ 2 ║ Bar Foo ║ 40 ║
║ 3 ║ Bob Dixon ║ 50 ║
║ 4 ║ Alice Spolsky║ 59 ║
╚════╩══════════════╩══════╝
Now these are the initial values and I want to store the audits with Hibernate Envers including all, old and new values. Default configuration lets me only store the new values, thus I miss the first ever created objects' values. For example if I say to update the first age to 55, then the aud table will be like below (excluding detail columns):
╔════╦══════════════╦══════╗
║ Id ║ User ║ Age ║
╠════╬══════════════╬══════╣
║ 1 ║ Foo Bar ║ 55 ║
╚════╩══════════════╩══════╝
And after this point I don't know if the initial value was 35. What I need is like an audit table below:
╔════╦══════════════╦══════════╦═════════╗
║ Id ║ User ║ OldAge ║ New Age ║
╠════╬══════════════╬══════════╬═════════╣
║ 1 ║ Foo Bar ║ 35 ║ 55 ║
╚════╩══════════════╩══════════╩═════════╝
How can I achieve such auditing with Hibernate Envers?
It would appear that perhaps you're either
In order for Envers to respond to changes, it is critical that database changes be done via Hibernate so that its event framework fires all the Envers listeners that are responsible for generating the audit history. If any step bypasses this, no audit entry will be generated.
Envers also does not generate audit history for an entity mapping that already has data. The expectation is that your entity table is empty and that insert, update, and delete events will be captured by Hibernate, propagated to Envers, and the audit history will be mutated based on those. With existing data, there are no events and thus no audit history gets automatically generated. It's on the implementor to properly seed the audit history if you intend to enable auditing on tables with existing data.
So if we step back and assume that you did not manually seed or enable Envers on an entity with data that already existed in the ORM base table, then your audit table should have looked like this after your update:
+---+--------------------+-----+-----+---------+
|Id | User | Age | REV | REVTYPE |
+---+--------------------+-----+-----+---------+
| 1 | Foo Bar | 35 | 1 | 0 |
| 2 | Bar Foo | 40 | 1 | 0 |
| 3 | Bob Dixon | 50 | 1 | 0 |
| 4 | Alice Spolsky | 59 | 1 | 0 |
| 1 | Foo Bar | 55 | 2 | 1 |
+---+--------------------+-----+-----+---------+
You'll notice the column REVTYPE. This column represents the operation associated to the row mutation. These values are part of an enumeration type where:
The Envers Query API allows you to fetch a list of revision numbers for a given entity's primary key and from there you can fetch each revision and perform the difference between object instances using any diff library or write your own.
Envers also supports an annotation attribute called withModifiedFlags. This is disabled by default as it adds quite a number of additional support columns to the entity model, but when enabled your model would actually look like the following:
+---+--------------------+----------+-----+---------+-----+---------+
|Id | User | User_MOD | Age | Age_MOD | REV | REVTYPE |
+---+--------------------+----------+-----+---------+-----+---------+
| 1 | Foo Bar | 1 | 35 | 1 | 1 | 0 |
| 2 | Bar Foo | 1 | 40 | 1 | 1 | 0 |
| 3 | Bob Dixon | 1 | 50 | 1 | 1 | 0 |
| 4 | Alice Spolsky | 1 | 59 | 1 | 1 | 0 |
| 1 | Foo Bar | 0 | 55 | 1 | 2 | 1 |
+---+--------------------+----------+-----+---------+-----+---------+
These _MOD columns are useful as it allows you to build sophisticated queries to check if certain columns of interest changed in a revision. While it does not allow you to get the previous value from the current audit row, that information can be deduced by looking at the audit table's history for that entity primary key.
You might be asking, well if Envers can add these _MOD columns, why not these "prior-value" columns. In theory we could do that, but I think its important to step back and measure if that would really be valuable.
I think it would be far more valuable to enhance the Query API in such a way that a user could ask Envers to produce some representation of an entity's history, perhaps differences between two concrete revisions or perhaps over the lifetime between two revisions. This representation would then allow you to get the prior and current values from it.
In short, there would then be no need to change the schema as the API would do this entirely for you pramatically.