SQL Analysis and Printing

MyBatis-Plus provides SQL analysis and printing functionality through the integration of the p6spy component, which conveniently outputs SQL statements and their execution time. This feature is available for MyBatis-Plus version 3.1.0 and above.

Introduction to p6spy

p6spy is a tool for intercepting and logging database access. It works by proxying the JDBC driver, allowing your application to use JDBC as usual while p6spy records all SQL statements and their execution times behind the scenes. This is particularly useful for SQL optimization during development and debugging.

p6spy offers more than just SQL logging. It also provides advanced features such as:

• Slow SQL Detection: By configuring outagedetection and outagedetectioninterval, p6spy can log SQL statements that exceed a specified execution time threshold.
• Custom Log Formatting: The logMessageFormat option allows you to customize the output format of SQL logs, including timestamps, execution time, SQL statements, and more.
• Log Output Control: The appender configuration lets you choose where logs are output—to the console, files, or a logging system.

p6spy is a powerful tool that provides MyBatis-Plus users with convenient SQL analysis and printing capabilities. With proper configuration, you can effectively monitor and optimize SQL statements during development and testing. However, due to performance overhead, it is recommended to use it cautiously in production environments.

Example Project

To better understand how to use this feature, refer to the official example project:

• mybatis-plus-sample-crud

Dependency Installation

First, you need to add the p6spy dependency to your project. Below are the installation methods for Maven and Gradle build tools:

Maven

<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
    <version>3.9.1</version>
</dependency>

Gradle

implementation 'p6spy:p6spy:3.9.1'

Configuration

Next, configure the settings in either application.yml or application.properties.

application.yml

spring:
  datasource:
    driver-class-name: com.p6spy.engine.spy.P6SpyDriver
    url: jdbc:p6spy:h2:mem:test
    # Other database configurations...

spy.properties

The spy.properties configuration file for p6spy includes multiple options. Below are some key configuration examples:

# Module list—choose the appropriate configuration based on the version
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory

# Custom log format
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger

# Log output to console
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger

# Disable JDBC driver registration
deregisterdrivers=true

# Use prefix
useprefix=true

# Excluded log categories
excludecategories=info,debug,result,commit,resultset

# Date format
dateformat=yyyy-MM-dd HH:mm:ss

# Actual driver list
# driverlist=org.h2.Driver

# Enable slow SQL logging
outagedetection=true

# Slow SQL threshold (in seconds)
outagedetectioninterval=2

# Filter SQL prints for tables starting with flw_
filter=true
exclude=flw_*

Spring Boot Integration

For Spring Boot projects, you can use p6spy-spring-boot-starter to simplify the integration process.

Dependency

Maven

<dependency>
    <groupId>com.github.gavlyukovskiy</groupId>
    <artifactId>p6spy-spring-boot-starter</artifactId>
    <version>1.11.0</version>
</dependency>

Gradle

implementation 'com.github.gavlyukovskiy:p6spy-spring-boot-starter:1.11.0'

Configuration

decorator:
  datasource:
    p6spy:
      # Log format
      log-format: "\ntime:%(executionTime) || sql:%(sql)\n"
      # Custom logger class
      logging: custom
      custom-appender-class: com.example.testinit.config.StdoutLogger

public class StdoutLogger extends com.p6spy.engine.spy.appender.StdoutLogger {
    public void logText(String text) {
        System.err.println(text);
    }
}

For more details about p6spy-spring-boot-starter configurations, refer to GitHub.

Notes

• The driver-class-name should be configured to the driver class provided by p6spy.
• The url prefix should be jdbc:p6spy, followed by the actual database connection address.
• If the printed SQL is null, add commit to excludecategories.
• If batch operations do not print SQL, remove batch from excludecategories.
• For duplicate SQL prints in batch operations, use MybatisPlusLogFactory (added in version 3.2.1).
• Note that this plugin may introduce performance overhead and is not recommended for production environments.

By following these steps, you can easily analyze and print SQL statements during development. Adjust the configurations as needed for optimal results.