0

I need to log every database change to a file. And I need to show how data looked before and how data looked after.
For example, if I change person`s name it would be ok if following line was appended to my log file: "updated person table: field 'first_name' was changed from 'Alex' to 'George' (person_id = 4622)".
And if I insert new person: "inserted to person table: 'person_id' = 4623; 'last_name' = 'Smith'; 'first_name' = 'John'; 'status' = 0".

I make changes to db with services that use interfaces that extend JpaRepository and theoretically I could add code to append log file to every method of every service. But that's a lot of code, such approach does not follow DRY principles and I believe there should be better solution I haven't found yet. What should I use to achieve this?

I use Spring Boot version 2.0.3.RELEASE and my database is MariaDB.
Log file can have any format, but, I guess, the best would be yaml.

John
  • 467
  • 1
  • 6
  • 23

2 Answers2

2

You can use JDBC logging with log4jdbc - see my related post.

To get it working in Spring Boot app just add log4jdbc-spring-boot-starter to your project, when set jdbc logging props as following:

logging.level.resultsettable=info
logging.level.sqltiming=info
logging.level.sqlonly=fatal
logging.level.audit=fatal
logging.level.resultset=fatal
logging.level.connection=fatal

Then you get full SQL queries, their execution time and their results in the app log.

To make queries print in one line you can use these settings of log4jdbc:

log4jdbc.dump.sql.addsemicolon=true
log4jdbc.dump.sql.maxlinelength=0
log4jdbc.trim.sql.extrablanklines=false

Then you get in your log something like this:

2018-08-27 14:36:14.183  INFO 5452 --- [127.0.0.1] jdbc.sqltiming       : SELECT 1; {executed in 1 msec}
2018-08-27 14:36:14.184  INFO 5452 --- [127.0.0.1] jdbc.resultsettable  : 
|---------|
|?column? |
|---------|
|1        |
|---------|

Also you can output those SQL query and/or their result to a different log file. How to do this you can read here.

Cepr0
  • 28,144
  • 8
  • 75
  • 101
  • That's great, but can you tell me instead of showing it on console how can we add it in the file, also I don't want to specify the configuration in the application.properties, is there way to define these configuration in the logback-spring.xml. – Feezan Khattak Jan 11 '22 at 09:29
1

You could turn on show_sql and log hibernate output to a file but it won't out of the box give you the data used in the changes, just the form of the sql. To get the data as well you'd need to spy on the jdbc driver.

You could use spring data envers to log changes to audit tables. Then from the tables you can export to files if you wish.

Or you could look at ways to do the auditing at the db level

These are the options that come first to mind.

Ryan Dawson
  • 11,832
  • 5
  • 38
  • 61