p6spy使用

 

转载:https://blog.csdn.net/gnd15732625435/article/details/81626095

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

示例
添加依赖

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

自定义输出格式

package cn.e3mall.common.utils;

import com.p6spy.engine.spy.appender.MessageFormattingStrategy;

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

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

    public String formatMessage(int connectionId, String now,
                         long elapsed, String category, String prepared, String sql) {
        if (!sql.trim().equals("")) {
            return format.format(new Date()) + " | took " + elapsed + "ms | " 
                + category + " | connection " + connectionId + "\n " + sql + ";";
        }
        return "";
    }
}

新建spy.properties

reloadproperties=true
appender=com.p6spy.engine.spy.appender.Slf4JLogger
#P6SpyLogger 类全路径名
logMessageFormat=cn.e3mall.common.utils.P6SpyLogger
databaseDialectDateFormat=yyyy-MM-dd hh:mm:ss
excludecategories=info,debug,result,resultset

修改数据库配置文件

<bean id="dataSource" class="com.p6spy.engine.spy.P6DataSource">
    <constructor-arg>
        <ref bean="dataSourceDefault"></ref>
    </constructor-arg>
</bean>
<bean id="dataSourceDefault" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
    ........
</bean>

在log4j.properties中添加配置

##p6spy
log4j.logger.p6spy=info,spyConsole
log4j.appender.spyConsole = org.apache.log4j.ConsoleAppender
log4j.appender.spyConsole.Target=System.out
log4j.appender.spyConsole.layout = org.apache.log4j.PatternLayout
log4j.appender.spyConsole.layout.ConversionPattern = #####-----%m%n

控制台打印效果

 

可以再参考下这个链接 更完善一些

https://blog.csdn.net/kisscatforever/article/details/78784254

https://panyongzheng.iteye.com/blog/2205748

5、appender配置，一般分为三种 
      #appender=com.p6spy.engine.logging.appender.Log4jLogger 
      # appender=com.p6spy.engine.logging.appender.StdoutLogger 
      #appender=com.p6spy.engine.logging.appender.FileLogger 
      其中，appender=com.p6spy.engine.logging.appender.StdoutLogger表示将输出的SQL或者日志信息输出到Console窗口。 

 

参考 https://www.jianshu.com/p/5a32434d43eb

 

module.log=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印
logMessageFormat=org.pkaq.core.log.P6SpyLogger
# 使用日志系统记录sql
appender=com.p6spy.engine.spy.appender.Slf4JLogger