SQL Analysis and Printing

MyBatis-Plus provides SQL analysis and printing functionality by integrating 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 that intercepts and logs database access operations by proxying JDBC drivers. This means your application can 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 allows you to customize the output format of SQL logs, including timestamps, execution time, SQL statements, and more.
• Log Output Control: The appender configuration option lets you choose where logs are output—to the console, files, or logging systems.

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 phases. However, due to performance overhead, it’s recommended to use it cautiously in production environments.

Example Project

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

• mybatis-plus-sample-crud

Dependency Installation

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

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

implementation 'p6spy:p6spy:3.9.1'

Configuration

Next, you need to make the appropriate configurations 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 contains multiple configuration options. Here are examples of some key configurations:

# Module list, choose appropriate configuration based on 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 recording
outagedetection=true

# Slow SQL threshold (in seconds)
outagedetectioninterval=2

# Filter SQL printing 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

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

implementation 'com.github.gavlyukovskiy:p6spy-spring-boot-starter:1.12.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 configuration information about p6spy-spring-boot-starter, please refer to GitHub.

Important 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 shows as null, add commit to the excludecategories.
• If batch operations don’t print SQL, remove batch from excludecategories.
• For duplicate printing issues with batch operations, use MybatisPlusLogFactory (added in version 3.2.1).
• Please note that this plugin may introduce performance overhead and is not recommended for use in production environments.

By following these steps, you can conveniently analyze and print SQL statements during development. Remember to adjust configurations according to your specific needs for optimal usage.