mybatis plus 的插件扩展中集成了 p6spy  这是一个 SQL分析的组件

配置

p6spy 的 maven 依赖

<dependency>  
    <groupId>p6spy</groupId>  
    <artifactId>p6spy</artifactId>  
</dependency>

spy.properties 配置

# p6spy 性能分析插件配置文件  
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory  
# 自定义日志打印  
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger  
#日志输出到控制台  
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger  
# 使用日志系统记录 sql#appender=com.p6spy.engine.spy.appender.Slf4JLogger  
# 设置 p6spy driver 代理  
#deregisterdrivers=true  
# 取消JDBC URL前缀  
useprefix=true  
# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.  
excludecategories=info,debug,result,commit,resultset  
# 日期格式  
dateformat=yyyy-MM-dd HH:mm:ss  
# SQL语句打印时间格式  
databaseDialectTimestampFormat=yyyy-MM-dd HH:mm:ss  
# 实际驱动可多个  
#driverlist=org.h2.Driver  
# 是否开启慢SQL记录  
outagedetection=true  
# 慢SQL记录标准 2 秒  
outagedetectioninterval=2  
# 是否过滤 Logfilter=true  
# 过滤 Log 时所排除的 sql 关键字，以逗号分隔  
exclude=SELECT 1

logMessageFormat 指定输出格式的实现类，mybatis-plus 提供了 P6SpyLogger 类格式化

public class P6SpyLogger implements MessageFormattingStrategy {
    public P6SpyLogger() {
    }

    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String url) {
        return StringUtils.isNotBlank(sql) ? " Consume Time：" + elapsed + " ms " + now + "\n Execute SQL：" + sql.replaceAll("[\\s]+", " ") + "\n" : "";
    }
}

输出的 SQL 日志

 Consume Time：2 ms 2024-04-10 21:46:08
 Execute SQL：SELECT id, tenant_id, contact_user_name, contact_phone, company_name, license_number, address, domain, intro, remark, package_id, expire_time, account_count, status, del_flag, create_dept, create_by, create_time, update_by, update_time FROM sys_tenant WHERE del_flag = '0' ORDER BY id ASC

p6spy 原理

PreparedStatementWrapper 实现了 PreparedStatement 接口，自定义实现执行处理器

mybatis 执行器执行 SQL 的时候，PreparedStatementHandler 中的操作

public <E> List<E> query(Statement statement, ResultHandler resultHandler) throws SQLException {  
    PreparedStatement ps = (PreparedStatement)statement;  
    ps.execute();  
    return this.resultSetHandler.handleResultSets(ps);  
}

execute 执行后，finally 中会处理该 SQL 的的日志输出

# PreparedStatementWrapper.java 

public boolean execute() throws SQLException {  
    SQLException e = null;  
    long start = System.nanoTime();  

    boolean var4;  
    try {  
        this.eventListener.onBeforeExecute(this.statementInformation);  
        var4 = this.delegate.execute();  
    } catch (SQLException var8) {  
        e = var8;  
        throw var8;  
    } finally {  
        this.eventListener.onAfterExecute(this.statementInformation, System.nanoTime() - start, e);  
    }  

    return var4;  
}

首次加载时，创建 logger 实例，getAppenderInstance 获取到 appender 配置

# 自定义日志打印  
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger  
#日志输出到控制台  
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger

getLogMessageFormatInstance() 会获取到配置文件中的sql格式化策略 P6SpyLogger

public static synchronized void initialize() {  
    P6ModuleManager moduleManager = P6ModuleManager.getInstance();  
    if (null != moduleManager) {  
        P6SpyOptions opts = (P6SpyOptions)moduleManager.getOptions(P6SpyOptions.class);  
        logger = opts.getAppenderInstance();  
        if (logger != null) {  
            if (logger instanceof FileLogger) {  
                String logfile = opts.getLogfile();  
                ((FileLogger)logger).setLogfile(logfile);  
            }  

            if (logger instanceof FormattedLogger) {  
                MessageFormattingStrategy strategy = opts.getLogMessageFormatInstance();  
                if (strategy != null) {  
                    ((FormattedLogger)logger).setStrategy(strategy);  
                }  
            }  
        }  

    }  
}

打印日志时，根据实现 MessageFormattingStrategy 的策略来进行输出处理，默认使用 SingleLineFormat 的实现

public abstract class FormattedLogger implements P6Logger {
    protected MessageFormattingStrategy strategy = new SingleLineFormat();

    protected FormattedLogger() {
    }

    public void logSQL(int connectionId, String now, long elapsed, Category category, String prepared, String sql, String url) {
        this.logText(this.strategy.formatMessage(connectionId, now, elapsed, category.toString(), prepared, sql, url));
    }

    public void setStrategy(MessageFormattingStrategy strategy) {
        this.strategy = strategy;
    }
}