MyBatis Plus整合p6spy控制台打印美化格式的sql语句

简介：
p6spy配合mybatis plus可以在控制台打印出指定格式已执行的sql语句，便于开发人员分析和调试。下面介绍一下p6spy的简单配置和使用

使用：

一、引入p6spy依赖包：

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

二、在项目结构的配置资源包目录下创建名为 spy.properties 的文件，如下：

 spy.properties文件的配置内容如下：

#p6spy\u914D\u7F6E\u6587\u4EF6
module.log=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
# \u81EA\u5B9A\u4E49\u65E5\u5FD7\u6253\u5370
# 此为自定义指定的sql格式，配制类的全路径
logMessageFormat=com.hkl.configure.SpySqlFormatConfigure
# sql\u8F93\u51FA\u65B9\u5F0F  Slf4JLogger(\u8F93\u51FA\u5230\u65E5\u5FD7)  StdoutLogger(\u8F93\u51FA\u5230\u63A7\u5236\u53F0)
appender=com.p6spy.engine.spy.appender.StdoutLogger
## \u914D\u7F6E\u8BB0\u5F55Log\u4F8B\u5916
excludeCategories=info,debug,result,batc,resultset
# \u8BBE\u7F6E\u4F7F\u7528p6spy driver\u6765\u505A\u4EE3\u7406
deregisterDrivers=true
# \u65E5\u671F\u683C\u5F0F
dateFormat=yyyy-MM-dd HH:mm:ss
# \u5B9E\u9645\u9A71\u52A8 oracle mysql \u9A71\u52A8
driverList=com.mysql.cj.jdbc.Driver
# \u662F\u5426\u5F00\u542F\u6162SQL\u8BB0\u5F55
outageDetection=true
# \u6162SQL\u8BB0\u5F55\u6807\u51C6 /\u79D2
outageDetectionInterval=2

三、格式配置类 SpySqlFormatConfigure 代码：

配置方式一(推荐)：

package com.hkl.configure;

import com.p6spy.engine.spy.appender.MessageFormattingStrategy;
import lombok.extern.slf4j.Slf4j;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * <p>ClassName：SpySqlFormatConfigure</p >
 * <p>Description：打印sql日志信息配置类</p >
 * <p>Author：</p >
 * <p>Date：2021/12/3</p >
 */
@Slf4j
public class SpySqlFormatConfigure implements MessageFormattingStrategy {

    private final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    /**
     * <p>输出执行sql信息</p >
     * @author 
     * @date 2021/12/3
     * @param connectionId
     * @param now 执行时间
     * @param elapsed 耗时多少毫秒
     * @param category
     * @param prepared 准备执行的sql脚本
     * @param sql 执行的sql脚本
     * @param url 数据源连接地址
    */
    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String url) {
        String date = dateFormat.format(new Date(Long.parseLong(now)));
        if (log.isInfoEnabled()) {
            log.info("执行时间： {}", date);
            log.info("完整sql： {}", sql);
            log.info("耗时：{} 毫秒", elapsed);
        }
        return "";
    }
}

配置方式二：

package com.svw.newsvwuc.common.configure;


import com.p6spy.engine.spy.appender.MessageFormattingStrategy;
import com.svw.newsvwuc.common.enums.Constants;
import lombok.extern.slf4j.Slf4j;

import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * <p>ClassName: SpySqlFormatConfigure</p>
 * <p>Description:Spy记录sql日志</p>
 * <p>Date: 2021/8/5</p>
 */
@Slf4j
public class SpySqlFormatConfigure implements MessageFormattingStrategy {

    private static final Formatter formatter;

    static {
        formatter = new BasicFormatterImpl();
    }

  /**
   * Formats a log message for the logging module
   *
   * @param connectionId the id of the connection
   * @param now          the current ime expressing in milliseconds
   * @param elapsed      the time in milliseconds that the operation took to complete
   * @param category     the category of the operation
   * @param prepared     the SQL statement with all bind variables replaced with actual values
   * @param sql          the sql statement executed
   * @return the formatted log message
   */
    @Override
    public String formatMessage(final int connectionId, final String now, final long elapsed, final String category, final String prepared, final String sql) {
      return "\n#" + now + " | took " + elapsed + "ms | " + category + " | connection " + connectionId + formatter.format(sql) +";";
    }
}

四、在模块yaml文件中配置数据源连接，使用p6spy代理数据库驱动(com.p6spy.engine.spy.P6SpyDriver)

  #数据源配置
  datasource:
    name: mysql
    type: com.alibaba.druid.pool.DruidDataSource
    #druid相关配置
    druid:
      #监控统计拦截的filters
      filters: stat
      #使用p6spy代理类
      driver-class-name: com.p6spy.engine.spy.P6SpyDriver
      #基本属性
      url: jdbc:p6spy:mysql://ipxxx:3306/xxx库名?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT&autoReconnect=true&failOverReadOnly=false
      username: xxx
      password: xxx
      #配置初始化大小/最小/最大
      initial-size: 1
      min-idle: 1
      max-active: 20
      #获取连接等待超时时间
      max-wait: 60000

五、以上步骤配置完成后，测试控制台打印sql语句自动拼接好参数，如下：

2021-10-20 14:46:08 INFO  [newsvwuc-quote-service,,,] http-nio-9004-exec-3 com.svw.newsvwuc.common.configure.SpySqlFormatConfigure 执行时间： 2021-10-20 14:46:08
2021-10-20 14:46:08 INFO  [newsvwuc-quote-service,,,] http-nio-9004-exec-3 com.svw.newsvwuc.common.configure.SpySqlFormatConfigure 完整sql： SELECT  PURCHASE_PLAN_ID,CUST_ID,CUST_NAME,CUST_PHONE,PAY_TYPE,PAY_AMOUNT,LOAN_BANK,POLICY_ID,POLICY_DURATION,PAY_RATIO,FINAL_RATIO,FIRST_PAY,RATE,MONTH_PAY,PURCHASE_TAX,OLD_CAR_NAME,OLD_CAR_BARND,OLD_CAR_BARND_NAME,OLD_CAR_SERIES,OLD_CAR_SERIES_NAME,OLD_CAR_MODEL,OLD_CAR_PLATE_NUMBER,OLD_CAR_VIN,OLD_CAR_REGISTER_DATE,OLD_CAR_MILEAGE,OLD_CAR_COLOR,OLD_CAR_COLOR_NAME,PURCH_PRICE,REPLACE_SUBSIDY,OLD_CAR_DESC,USED_CAR_PRICE,BRAND_CODE,BRAND_NAME,SERIES_CODE,SERIES_NAME,MODEL_CODE,MODEL_NAME,MODEL_YEAR,VERSION_NO,VERSION_NAME,INTERIOR_CODE,INTERIOR_NAME,COLOR_CODE,COLOR_NAME,OPTIONAL,GUIDANCE_PRICE,NEW_CAR_COUPON,COUPON_PRICE,PURCHASE_PLAN_PIC,SEATS_NO,DLR_ID,SALE_PERSON,CREATE_BY,CREATE_DATE,UPDATE_BY,UPDATE_DATE  FROM tt_purchase_plan 
 
 WHERE PURCHASE_PLAN_ID = 157 AND DLR_ID = 11
2021-10-20 14:46:08 INFO  [newsvwuc-quote-service,,,] http-nio-9004-exec-3 com.svw.newsvwuc.common.configure.SpySqlFormatConfigure 耗时：12 毫秒

说明： 
1、以上配置使用方式可以放在项目的公共模块中，例如放在 server-common 模块中，可供其他多个服务模块全局共同使用
2、mybatis-plus自身的打印sql脚本 mybatis-plus.configuration.log-impl 不能开启，否则p6spy打印不生效