Is there way to record audit database in spring boot using Spring Data Envers with spring JDBCTemplate?

Question

I'm creating a spring web application that uses the MySQL database with spring JDBCTemplate. The problem is I want to record any changes in data that store in the MySQL database. I couldn't find any solution for Spring Data Envers with JDBCTemplates to record the changes.

What is the best way to record any changes of data of database? or by simply writing a text file on the spring app?

Answer 1

Envers on which Spring Data Envers builds is an add on of Hibernate and uses it's change detection mechanism in order to trigger writing revisions to the database.

JdbcTemplate doesn't have any of that, it just eases the execution of SQL statements by abstracting away repetitive tasks like exception handling or iterating over the ResultSet of queries. JdbcTemplate has no knowledge of what the statement it is executing is actually doing.

As so often you have a couple of options:

1. put triggers on your database that record changes.
2. use some database dependent feature like Oracles Change Data Capture
3. You could create a wrapper of JdbcTemplate which analyses the SQL statement and produces a log entry. This is only feasible when you need only very limited information, like what kind of statement was executed and which table was affected.
4. If you need more semantic information it is probably best to use an even higher level of your application stack like the controller or service to gather the relevant information and write it to the database. Probably using the JdbcTemplate as well.