jdbc打印sql语句-p6spy配置

package com.hepengju.hekele.common.config;

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;
import java.util.Properties;

/**
 * P6Spy数据源装饰
 *
 * 
 *  说明: 打印真实SQL语句(替换好变量), SQL耗时及SQL语句过滤等作用
 *
 *  修正: 20190924
 *  说明: 发现在web-zuul中的jpa接口没有打印相关SQL日志, 检查发现是先进行了jpa自动配置, 跳过了此后置处理器.
 *       因此改为实现PriorityOrdered接口
 * 
 *
 * @see https://p6spy.readthedocs.io/en/latest/configandusage.html">P6Spy官方配置手册
 * @author hepengju 20190202
 */
@Configuration
public class P6SpyConfig {

    /**
     * P6Spy的初始化设置: 在实际项目中都是统一配置的,因此没有必要每个工程类路径下都放置一个spy.properties
     *
     * 
     *  1. 设置日期格式
     *  2. 定制化输出: 执行时间(最重要), 数据库连接ID(用来查看是否时同一个事务), 策略查看是否时回滚的, 单行SQL语句
     *  3. 排除定时验证语句: SELECT 1
     *  4. 排除无关紧要策略: 比如批量,提交等(但是回滚建议不要排除)
     * 
     */
    static {
        Properties prop = System.getProperties();
        String PRE = "p6spy.config.";
        prop.put(PRE + "dateformat"                     , "yyyy-MM-dd HH:mm:ss");
        prop.put(PRE + "appender"                       , "com.p6spy.engine.spy.appender.Slf4JLogger");
        prop.put(PRE + "logMessageFormat"               , "com.p6spy.engine.spy.appender.CustomLineFormat");
        prop.put(PRE + "customLogMessageFormat"         , "%(executionTime)ms | %(category) | %(sqlSingleLine)");
        prop.put(PRE + "databaseDialectDateFormat"      , "yyyy-MM-dd");
        prop.put(PRE + "databaseDialectTimestampFormat" , "yyyy-MM-dd HH:mm:ss");
        prop.put(PRE + "databaseDialectBooleanFormat"   , "boolean");
        prop.put(PRE + "filter"                         , "true");
        prop.put(PRE + "exclude"                        , "^SELECT 1");
        prop.put(PRE + "excludecategories"              , "info,debug,result,resultset,commit"); // batch也保留吧
    }

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

    /**
     * 如果是数据源则包装下, 否则直接返回; 并设置为最低优先级(即最后处理)
     */
    class P6DataSourceBeanPostProcessor implements BeanPostProcessor, PriorityOrdered {

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

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

}