p6spy使用

前言

p6spy是一个开源项目，通常使用它来跟踪数据库操作，查看程序运行过程中执行的SQL语句。

示例

添加依赖

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

自定义输出格式

package com.tfjybj.study.provider.config;

import com.p6spy.engine.spy.appender.MessageFormattingStrategy;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * p6spy打印日志输出格式修改
 * 1.只打印最终执行的sql.
 * 2.sql换到下一行
 * 3.结尾处增加分号,以标示sql结尾
 */
public class P6SpyLogger implements MessageFormattingStrategy {
    private final SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS");

    public P6SpyLogger() {
    }

    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql) {
        StringBuffer buffer = new StringBuffer();
        return !"".equals(sql.trim()) ? buffer.append("/* ")
                .append(this.format.format(new Date()))
                .append(" | took ").append(elapsed)
                .append("ms | ").append(category)
                .append(" | connection ")
                .append(connectionId)
                .append(" */ \n ")
                .append(sql)
                .append(";")
                .toString() : "";
    }
}

新建spy.properties

driverlist=com.mysql.cj.jdbc.Driver
reloadproperties=true
appender=com.p6spy.engine.spy.appender.Slf4JLogger
#P6SpyLogger类全路径名
logMessageFormat=com.tfjybj.study.provider.config.P6SpyLogger
databaseDialectDateFormat=yyyy-MM-dd HH:mm:ss
excludecategories=info,debug,result,resultset

修改application.yml配置文件中数据库的配置

spring:
  datasource:
    driver-class-name: com.p6spy.engine.spy.P6SpyDriver
    username: root
    password: root
    url: jdbc:p6spy:mysql://127.0.0.1:3306/practice?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8

控制台打印效果