How to Log MyBatis SQL and Execution Time with Config, Plugins, and p6spy

MyBatis encapsulates JDBC, allowing developers to focus on business SQL while reducing complexity. To output the SQL statements executed by MyBatis and measure their execution time, three common approaches are presented.

1. Configuration File Approach

By configuring logging in the application properties, SQL statements and parameters are printed to the console or a designated log location. The following YAML snippet shows the required settings:

<code>spring:
  application:
    name: longxia-biancheng
  # Database configuration
  datasource:
    username: longxia
    password: longxiabiancheng
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/longxia?rewriteBatchedStatements=true&amp;serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=true
  # MyBatis entity and mapper locations
  mybatis:
    type-aliases-package: com.longxia.mybatis.entity
    mapper-locations: classpath:mapper/*.xml
  # Enable SQL logging for MyBatis mappers
  logging:
    level:
      com.longxia.mybatis.mapper: debug
</code>

The resulting log shows the executed SQL and its parameters. This method is simple to set up and is suitable for testing or pre‑release environments, but it is not recommended for production due to potential performance impact.

2. MyBatis Plugin Approach

MyBatis provides four core plugin interfaces: Executor , StatementHandler , ParameterHandler , and ResultSetHandler . By implementing a plugin that intercepts StatementHandler , you can capture the SQL and its execution time. Below is a sample interceptor:

<code>@Component
@Intercepts({@Signature(
        type = StatementHandler.class,
        method = "query",
        args = {Statement.class, ResultHandler.class})})
public class LongxiaSqlLogInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        long startTime = System.currentTimeMillis();
        StatementHandler target = (StatementHandler) invocation.getTarget();
        String sql = target.getBoundSql().getSql();
        Object proceed = invocation.proceed();
        System.out.println("Current SQL: " + sql + ", elapsed time: " + (System.currentTimeMillis() - startTime));
        return proceed;
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
        // No properties needed
    }
}
</code>

When this plugin is registered, each query prints the SQL statement and the time it took to execute. This method is flexible and can be extended to trigger alerts when execution exceeds a threshold.

3. Third‑Party Framework – p6spy

p6spy allows you to log SQL and execution time without modifying business code. Integration steps:

Add the Maven dependency:

<code>&lt;dependency&gt;
    &lt;groupId&gt;p6spy&lt;/groupId&gt;
    &lt;artifactId&gt;p6spy&lt;/artifactId&gt;
    &lt;version&gt;3.8.7&lt;/version&gt;
&lt;/dependency&gt;
</code>

Configure the datasource to use the p6spy driver:

<code>spring:
  datasource:
    username: longxia
    password: longxiabiancheng
    driver-class-name: com.p6spy.engine.spy.P6SpyDriver
    url: jdbc:p6spy:mysql://localhost:3306/longxia?rewriteBatchedStatements=true&amp;serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=true
</code>

Create spy.properties under resources :

<code># Real driver
realdriver=com.mysql.jdbc.Driver
# Single‑line log format
logMessageFormat=com.p6spy.engine.spy.appender.SingleLineFormat
# Use SLF4J for logging
appender=com.p6spy.engine.spy.appender.Slf4JLogger
dateformat=yyyy-MM-dd HH:mm:ss
</code>

Running the application produces logs such as:

<code>2024-07-13 17:37:31.068 INFO 29615 --- [nio-8080-exec-1] p6spy : select * from student where id = ?|1
</code>

This approach is ideal for environments where you want detailed SQL tracing without code changes, but it should also be limited to testing or staging to avoid excessive logging in production.

In summary, printing MyBatis SQL and measuring execution time can be achieved via simple configuration, a custom interceptor plugin, or the p6spy library. Use the configuration method for quick debugging, the plugin for production‑grade monitoring with custom alerts, and p6spy when you need a non‑intrusive solution.