Logging SQL as it’s executed is a fairly common desire when developing applications. Perhaps an ORM (such as Hibernate) is being used, and you want to see the actual SQL being executed. Or maybe you’re tracking down a performance problem and need to know if it’s in the application or the database, so step #1 is finding out what query is executing and for how long.
Solving this problem once and for all (at least for Spring Boot applications), I created Log4jdbc Spring Boot Starter. It’s a very simple yet powerful way to log SQL queries (and more, such as timing information). And unlike other solution, the queries logged are ready to run – the ‘?’ parameters are replaced with their values. This means you can copy and paste the query from the log and run them unmodified in the SQL query tool of your choice, saving a lot of time.
For background, my motivation for this work is a result of a Spring Boot / Hibernate application I have in progress. I started by using spring.jpa.properties.hibernate.show_sql=true but that only logs queries with ‘?’ place holders. To log the values, add spring.jpa.properties.hibernate.type=trace. At least now I had the query and the values for it, but to run it in my query tool (I need to EXPLAIN the query), I had to replace each ‘?’ with the value – and I had over 20 place holders. That got old fast.
There are other approaches to log queries, such as the one described in Display SQL to Console in Spring JdbcTemplate. I’m not a fan of this approach because it only works for queries made through JdbcTemplate (so Hibernate queries aren’t logged, for example) and it’s an awfully lot of code to include and therefore have to maintain in each project.
I discovered Log4jdbc but it’s a bit of a pain to setup in a Spring Boot application because it:
- doesn’t use the Spring Environment (application.properties)
- needs setup to wrap the DataSource’s in the Log4jdbc DataSourceSpy
Wanting to solve this problem precisely once and never again, I created Log4jdbc Spring Boot Starter.
To use it, just add to your project:
<dependency><groupid>com.integralblue</groupid>
<artifactid>log4jdbc-spring-boot-starter</artifactid>
<version>[INSERT VERSION HERE]</version>
</dependency>
Then turn on the logging levels as desired in application.properties, for example:
logging.level.jdbc.sqlonly=DEBUG
When no logging is configured (all loggers are set to fatal or off), log4jdbc returns the original Connection.
See the Log4jdbc Spring Boot Starter project page for more information.
