P6Spy【SpringBoot配置实现输出sql日志】

maven项目导入jar包

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

编写P6SpyConfig类

package com.example.demo.P6Spy;

import com.p6spy.engine.spy.P6DataSource;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.config.BeanPostProcessor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.Ordered;
import org.springframework.core.PriorityOrdered;

import javax.sql.DataSource;

@Configuration
public class P6SpyConfig {

    /**
     * P6数据源包装, 打印SQL语句
     */
    @Bean
    public P6DataSourceBeanPostProcessor p6DataSourceBeanPostProcessor() {
        return new P6DataSourceBeanPostProcessor();
    }

    class P6DataSourceBeanPostProcessor implements BeanPostProcessor, PriorityOrdered {
        @Override
        public Object postProcessBeforeInitialization(Object bean, String beanName) throws BeansException {
            return bean;
        }

        @Override
        public Object postProcessAfterInitialization(Object bean, String beanName) throws BeansException {
            if (bean instanceof DataSource) {
                return new P6DataSource((DataSource) bean);
            }
            return bean;
        }

        @Override
        public int getOrder() {
            return Ordered.LOWEST_PRECEDENCE;
        }
    }

}

配置spy.properties文件

# 是否自动刷新 默认 flase
#autoflush=false
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 监测属性配置文件是否进行重新加载
reloadproperties=true
# 属性配置文件重新加载的时间间隔，单位:秒 默认60s
reloadpropertiesinterval=60
# 使用日志系统记录sql
appender=com.p6spy.engine.spy.appender.Slf4JLogger
# 自定义日志打印
logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
customLogMessageFormat=%(executionTime)ms | %(sqlSingleLine)
# date类型字段记录日志时使用的日期格式 默认dd-MMM-yy
databaseDialectDateFormat=yyyy-MM-dd HH:mm:ss   
# boolean类型字段记录日志时使用的日期格式 默认boolean 可选值numeric
databaseDialectBooleanFormat=boolean
# 是否通过jmx暴露属性 默认true
jmx=true
# 是否开启日志过滤 默认false， 这项配置是否生效前提是配置了 include/exclude/sqlexpression
filter=true
# 过滤 Log 时所排除的表名列表，以逗号分隔 默认为空
exclude=Z020_LOG_RESOURCE,Z020_LOG_OPERATE
# 配置记录Log例外
excludecategories=info,debug,result,batc,resultset

运行结果

成功打印了sql语句在控制台。