How to log SQL queries and their results in Spring Data JPA projects with Log4jdbc-log4j2

To log SQL queries are sent to DB we are using this setting:

spring.jpa.showSql=true

It allows us to see a statement body but not its parameters:

insert into master (id, version, name) values (null, ?, ?)

And also we don’t see a result of the query.

Is there a way to do this?


See my answer below how to use Log4jdbc-log4j2 to achieve this…

Log4jdbc-log4j2 is a very useful tool that can log SQL and/or JDBC calls and their results when you works with DB in Spring Data JPA projects.

It can allow us to log SQL statements and their parameters:

insert into master (id, version, name) values (null, 0, 'master1')

select master0_.id as id1_1_, master0_.version as version2_1_, master0_.name as name3_1_ from master master0_ where master0_.name='master1'

And see their results in the table form:

|-----------------|
|scope_identity() |
|-----------------|
|1                |
|-----------------|

|---|---------|---------|
|id |version  |name     |
|---|---------|---------|
|1  |0        |master1  |
|---|---------|---------|

How to use it:

  1. Include Log4jdbc-log4j2 dependency to the project:
<dependency>
    <groupId>org.bgee.log4jdbc-log4j2</groupId>
    <artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
    <version>1.16</version>
</dependency>
  1. Replace you db connection (here example for H2):
spring.datasource.url=jdbc:log4jdbc:h2:mem:db;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.driverClassName=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
  1. Add to the resources dir a file log4jdbc.log4j2.properties that contains one line:
log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator
  1. Add to the app properties file a Log4jdbc-log4j2 configuration, for example (you can play with these options):
logging.level.jdbc.sqlonly=info
logging.level.jdbc.sqltiming=off
logging.level.jdbc.audit=off
logging.level.jdbc.resultset=off
logging.level.jdbc.resultsettable=info
logging.level.jdbc.connection=off