简介：
p6spy配合mybatis plus可以在控制台打印出指定格式已执行的sql语句，便于开发人员分析和调试。下面介绍一下p6spy的简单配置和使用
使用：
一、引入p6spy依赖包：
<dependency>
<groupId>p6spy</groupId>
<artifactId>p6spy</artifactId>
<version>3.8.1</version>
</dependency>
二、在项目结构的配置资源包目录下创建名为 spy.properties 的文件，如下：
image.png
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 代码：
image.png
配置方式一(推荐)：
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打印不生效
网友评论