SpringBoot druid sql 打印

duird自定义过滤器

以前一个项目日志里面看sql 习惯了，也能根据sql在日志中出现的频次，查看sql量是否大

但是官方文档 https://github.com/alibaba/druid/wiki/%E9%85%8D%E7%BD%AE_LogFilter 有点看不懂，试了下也没用

那就自定义一个 DruidFilter 实现sql在日志文件里面打印

1. 自定义filter

import com.alibaba.druid.filter.FilterAdapter;
import com.alibaba.druid.filter.FilterChain;
import com.alibaba.druid.filter.logging.Slf4jLogFilter;
import com.alibaba.druid.proxy.jdbc.JdbcParameter;
import com.alibaba.druid.proxy.jdbc.StatementProxy;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.SQLException;
import java.util.Collection;
import java.util.Map;

/**
 * 打印sql日志过滤器,也可以换成集成Slf4jLogFilter
 */
public class DruidLogFilter extends FilterAdapter {
    Logger logger = LoggerFactory.getLogger(DruidLogFilter.class);
    private static final String noParam = "NO_PARAM";
    @Override
    public void statement_close(FilterChain chain, StatementProxy statement) throws SQLException {
        super.statement_close(chain, statement);
        String sql = statement.getBatchSql();
        try {
            Map<Integer, JdbcParameter> parameters = statement.getParameters();
            if (StringUtils.isNotBlank(sql)) {
                Collection<JdbcParameter> values = parameters.values();
                for (JdbcParameter value : values) {
                    if (value != null && value.getValue() != null) {
                        Object value1 = value.getValue();
                        if(value1 instanceof String){
                            sql = sql.replaceFirst("\\?", "'"+value.getValue().toString()+"'");
                        }else {
                            sql = sql.replaceFirst("\\?", value.getValue().toString());
                        }

                    } else {
                        sql = sql.replaceFirst("\\?", noParam);
                    }
                }
                logger.info("Sql+Para : " + sql.replaceAll("\r|\n", ""));
            }
        } catch (Exception e){
            logger.error("打印带参数sql发生异常，" + sql);
        }
    }
}

2. druid识别filter

在 resource/META-INF文件夹中定义配置文件 druid-filter.properties

druid.filters.druidLogFilter=cn.gsh456.devops.config.DruidLogFilter

3.加载filter

配置文件中添加 (最后一行是最重点)

spring:
  datasource:
    url: jdbc:sqlite:./data/data.db
    driver-class-name: org.sqlite.JDBC
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      validation-query: select 1
      connectionProperties: druid.stat.logSlowSql=true;druid.stat.slowSqlMillis=1000
      filters: stat,druidLogFilter

4.其他

如果resource中 没有打进jar包(jar包根目录中META-INF文件夹中没有druid-filter.properties)，pom文件中配置下

    <build>
        <resources>
            <resource>
                <!-- 加载resources -->
                <directory>src/main/resources</directory>
            </resource>
        </resources>
   </build>