Logging SQL queries and performance tuning

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) \nEXPLAIN %(sql);\n
filter=true
exclude=select 1 from dual

In order to let P6Spy capture the SQL queries, we will need...