In this section, we will introduce one way to log the SQL queries that JdbcDriver sends to the database. We will use P6Spy (https://github.com/p6spy/p6spy).

The first change we need to make is to add the following dependencies into pom.xml:

<dependency>
  <groupId>p6spy</groupId>
  <artifactId>p6spy</artifactId>
  <version>${p6spy.version}</version>
</dependency>

At the time of writing, the P6Spy version we use is 3.7.0. To customize P6Spy, we can add the src/main/resources/spy.properties configuration file. The one that we will use looks as follows:

driverlist=com.mysql.jdbc.Driver
logfile=spy.log
dateformat=yyyy-MM-dd HH:mm:ss.SS
logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
customLogMessageFormat=- %(currentTime) | took %(executionTime)ms | connection %(connectionId) 
EXPLAIN %(sql);

filter=true
exclude=select 1 from dual

In order to let P6Spy capture the SQL queries, we will need to change the datasource configuration. Let's make changes to application-dev.properties, as follows:

spring.datasource.url=jdbc:p6spy:mysql://localhost:3306/task_agile?useSSL=false
spring.datasource.username=root
spring.datasource.password=1234
spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver

As you can see, we need to change the URL's jdbc:mysql to jdbc:p6spy:mysql and change the driver class name to com.p6spy.engine.spy.P6SpyDriver.

With everything ready, once the application starts, you can see SQL queries captured in spy.log, which looks as follows:

- 2018-08-30 20:55:53.333 | took 1ms | connection 0
EXPLAIN SELECT b.* FROM board b LEFT JOIN board_member bm ON b.id = bm.board_id WHERE bm.user_id = 34;

The EXPLAIN keyword is added in the customLogMessageFormat custom format of spy.properties. In this way, you can copy the SQL to do a MySQL explain of the query and figure out how to tune the performance of the query.

The following is the commit record: