使用druid自定义拦截器

         使用druid自定义的拦截器StatFilter，是可以通过日志进行慢sql打印的。但是如果想要把慢sql放入DB，或者通过钉钉告警的方式进行实时打印，则需要实现自定义的拦截器。

         第一步：重新自定义拦截器

      

package com.example.demo.filter;

@Component
public class SlowSqlTestFilter extends FilterEventAdapter implements StatFilterMBean {

    private static final String IGNORE_SQL = "SELECT 1";

    private static final String INSERT= "insert";
    private Logger logger = LoggerFactory.getLogger(this.getClass());

    @Resource
    private BbsDruidSqlDao bbsDruidSqlDao;
    @Resource
    private SlowSqlConfig slowSqlConfig;

    @Override
    protected void statementExecuteBefore(StatementProxy statement, String sql) {
        super.statementExecuteBefore(statement, sql);
        //sql开始执行的时间
        statement.setLastExecuteStartNano();
    }

    @Override
    protected void statementExecuteBatchBefore(StatementProxy statement) {
        super.statementExecuteBatchBefore(statement);
        //sql开始执行的时间
        statement.setLastExecuteTimeNano();
    }

    @Override
    protected void statementExecuteAfter(StatementProxy statement, String sql, boolean result) {
        if (IGNORE_SQL.equals(sql) || sql.contains(INSERT)) {
            return;
        }
        if (slowSqlConfig == null) {
            slowSqlConfig = SpringUtil.getBean(SlowSqlConfig.class);
        }
        final long nonNano = System.nanoTime();
        final long lastTime = nonNano - statement.getLastExecuteStartNano();
        long millis = lastTime / (1000 * 1000);
        if (millis >= slowSqlConfig.getSlowSqlMillis()) {
            slowSqlToMysql(statement, sql,millis);
        }

    }


    @Override
    protected void statementExecuteUpdateBefore(StatementProxy statement, String sql) {
        super.statementExecuteUpdateBefore(statement, sql);
        statement.setLastExecuteTimeNano();
    }

    @Override
    protected void statementExecuteUpdateAfter(StatementProxy statement, String sql, int updateCount) {
        final long nonNano = System.nanoTime();
        final long lastTime = nonNano - statement.getLastExecuteStartNano();
        long millis = lastTime / (1000 * 1000);
        if (millis >= slowSqlConfig.getSlowSqlMillis()) {
            slowSqlToMysql(statement, sql,millis);
        }
    }

    @Override
    protected void statementExecuteQueryBefore(StatementProxy statement, String sql) {
        super.statementExecuteQueryBefore(statement, sql);
        statement.setLastExecuteStartNano();
    }

    @Override
    protected void statementExecuteQueryAfter(StatementProxy statement, String sql, ResultSetProxy resultSet) {
        if (IGNORE_SQL.equals(sql)) {
            return;
        }
        final long nonNano = System.nanoTime();
        final long lastTime = nonNano - statement.getLastExecuteStartNano();
        long millis = lastTime / (1000 * 1000);
        if (millis >= slowSqlConfig.getSlowSqlMillis()) {
            slowSqlToMysql(statement, sql,millis);
        }
    }


    @Override
    protected void statement_executeErrorAfter(StatementProxy statement, String sql, Throwable error) {
        super.statement_executeErrorAfter(statement, sql, error);
    }

    private void slowSqlToMysql(StatementProxy statement, String sql,Long millis) {
        if (bbsDruidSqlDao == null) {
            bbsDruidSqlDao = SpringUtil.getBean(BbsDruidSqlDao.class);
        }
        String slowParamters = buildSlowParameters(statement);
        logger.info("慢sql语句{},入参:{}", sql, slowParamters);
        BbsDruidSql bbsDruidSql = BbsDruidSql.builder().slowSql(sql).slowSqlParam(slowParamters)
                .createTime(new Date()).updateTime(new Date()).slowSqlTraceid(millis.toString()).build();
        bbsDruidSqlDao.insertSelective(bbsDruidSql);
    }

    protected String buildSlowParameters(StatementProxy statement) {
        JSONWriter out = new JSONWriter();

        out.writeArrayStart();
        for (int i = 0, parametersSize = statement.getParametersSize(); i < parametersSize; ++i) {
            JdbcParameter parameter = statement.getParameter(i);
            if (i != 0) {
                out.writeComma();
            }
            if (parameter == null) {
                continue;
            }

            Object value = parameter.getValue();
            if (value == null) {
                out.writeNull();
            } else if (value instanceof String) {
                String text = (String) value;
                if (text.length() > 100) {
                    out.writeString(text.substring(0, 97) + "...");
                } else {
                    out.writeString(text);
                }
            } else if (value instanceof Number) {
                out.writeObject(value);
            } else if (value instanceof java.util.Date) {
                out.writeObject(value);
            } else if (value instanceof Boolean) {
                out.writeObject(value);
            } else if (value instanceof InputStream) {
                out.writeString("<InputStream>");
            } else if (value instanceof NClob) {
                out.writeString("<NClob>");
            } else if (value instanceof Clob) {
                out.writeString("<Clob>");
            } else if (value instanceof Blob) {
                out.writeString("<Blob>");
            } else {
                out.writeString('<' + value.getClass().getName() + '>');
            }
        }
        out.writeArrayEnd();

        return out.toString();
    }

    @Override
    public boolean isMergeSql() {
        return false;
    }

    @Override
    public void setMergeSql(boolean mergeSql) {

    }

    @Override
    public boolean isLogSlowSql() {
        return false;
    }

    @Override
    public void setLogSlowSql(boolean logSlowSql) {

    }

    @Override
    public String mergeSql(String sql, String dbType) {
        return null;
    }

    @Override
    public long getSlowSqlMillis() {
        return 0;
    }

    @Override
    public void setSlowSqlMillis(long slowSqlMillis) {

    }
}

第二步：把自定义的拦截器放入druid的datasource中

@ConfigurationProperties(prefix = "spring.datasource")
@Bean(initMethod = "init",destroyMethod = "close")
public DruidDataSource dataSource(){
    DruidDataSource dataSource = new DruidDataSource();
    //使用自定义拦截器 注意 不能new
    dataSource.setProxyFilters(Collections.singletonList(new SlowSqlTestFilter()));
    //dataSource.setProxyFilters(Collections.singletonList(statGwmFilter()));
    //设置druid的重置间隔
    dataSource.setTimeBetweenLogStatsMillis(60000);
    return dataSource;
}