用druid监控所有的可执行的sql

官方提供的 LogFilter 不能打印出batch-sql（算bug？），对事务的支持也没有 这里给出我的解决方案

ExecuteSqlFilter 继承 FilterEventAdapter ，这个不支持事务

package com.yiwugou.dbbackup;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.alibaba.druid.filter.FilterChain;
import com.alibaba.druid.filter.FilterEventAdapter;
import com.alibaba.druid.proxy.jdbc.JdbcParameter;
import com.alibaba.druid.proxy.jdbc.PreparedStatementProxy;
import com.alibaba.druid.proxy.jdbc.ResultSetProxy;
import com.alibaba.druid.proxy.jdbc.StatementProxy;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.SQLUtils.FormatOption;

/**
 * 
 * ExecuteSqlFilter
 * 
 * @author xy.z@qq.com
 *
 * @since 2020年02月18日 下午2:25:12
 */
public class ExecuteSqlFilter extends FilterEventAdapter {
    private FormatOption statementSqlFormatOption = new FormatOption(false, true);
    
    protected void statementLog(String message) {
        try {
            String sql = StringUtils.trim(message);
            System.out.println(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    protected void statementExecuteUpdateAfter(StatementProxy statement, String sql, int updateCount) {
        logExecutableSql(statement, sql);
    }

    @Override
    protected void statementExecuteAfter(StatementProxy statement, String sql, boolean firstResult) {
        logExecutableSql(statement, sql);
    }

    @Override
    protected void statementExecuteQueryAfter(StatementProxy statement, String sql, ResultSetProxy resultSet) {
        logExecutableSql(statement, sql);
    }

    @Override
    protected void statementExecuteBatchAfter(StatementProxy statement, int[] result) {
        if (statement instanceof PreparedStatementProxy) {
            String sql = ((PreparedStatementProxy) statement).getSql();
            logBatchExecutableSql(statement, sql);
        }
        List<String> sqls = statement.getBatchSqlList();
        if (sqls != null) {
            for (String sql : sqls) {
                logExecutableSql(statement, sql);
            }
        }
    }

    private static final String BATCH_PSTMT_ARGS = "batch-pstmt-args";

    @Override
    public void preparedStatement_addBatch(FilterChain chain, PreparedStatementProxy statement) throws SQLException {
        List<Object> parameters = formatParameters(statement);
        List<List<Object>> args = (List<List<Object>>) statement.getAttribute(BATCH_PSTMT_ARGS);
        if (args == null) {
            args = new ArrayList<>();
            statement.putAttribute(BATCH_PSTMT_ARGS, args);
        }
        args.add(parameters);
        super.preparedStatement_addBatch(chain, statement);
    }

    private void logBatchExecutableSql(StatementProxy statement, String sql) {
        List<List<Object>> args = (List<List<Object>>) statement.getAttribute(BATCH_PSTMT_ARGS);
        if (args != null && args.size() > 0) {
            for (List<Object> parameters : args) {
                String formattedSql = formattedSql(statement, sql, parameters);
                statementLog(formattedSql);
            }
            args.clear();
        }
    }

    private void logExecutableSql(StatementProxy statement, String sql) {
        int parametersSize = statement.getParametersSize();
        if (parametersSize == 0) {
            statementLog(sql);
            return;
        }
        List<Object> parameters = formatParameters(statement);
        String formattedSql = formattedSql(statement, sql, parameters);
        statementLog(formattedSql);
    }

    private String formattedSql(StatementProxy statement, String sql, List<Object> parameters) {
        String dbType = statement.getConnectionProxy().getDirectDataSource().getDbType();
        String formattedSql = SQLUtils.format(sql, dbType, parameters, this.statementSqlFormatOption);
        return formattedSql;
    }

    private List<Object> formatParameters(StatementProxy statement) {
        int parametersSize = statement.getParametersSize();
        List<Object> parameters = new ArrayList<Object>(parametersSize);
        for (int i = 0; i < parametersSize; ++i) {
            JdbcParameter jdbcParam = statement.getParameter(i);
            parameters.add(jdbcParam != null ? jdbcParam.getValue() : null);
        }
        return parameters;
    }
}

支持事务的filter

package com.yiwugou.dbbackup;

import java.sql.SQLException;
import java.sql.Savepoint;
import java.util.ArrayList;
import java.util.List;

import com.alibaba.druid.filter.FilterChain;
import com.alibaba.druid.proxy.jdbc.ConnectionProxy;

/**
 * 
 * TxExecuteSqlFilter
 * 
 * @author xy.z@qq.com
 *
 * @since 2018年11月27日 下午3:26:03
 */
public class TxExecuteSqlFilter extends ExecuteSqlFilter {
    private static final ThreadLocal<Boolean> AUTO_COMMIT = new ThreadLocal<>();
    private static final ThreadLocal<List<String>> TX_MESSAGES = new ThreadLocal<List<String>>() {
        @Override
        protected List<String> initialValue() {
            return new ArrayList<>();
        }
    };

    @Override
    public void connection_setAutoCommit(FilterChain chain, ConnectionProxy connection, boolean autoCommit)
            throws SQLException {
        super.connection_setAutoCommit(chain, connection, autoCommit);
        AUTO_COMMIT.set(autoCommit);
    }

    @Override
    public void connection_commit(FilterChain chain, ConnectionProxy connection) throws SQLException {
        try {
            super.connection_commit(chain, connection);
            Boolean b = AUTO_COMMIT.get();
            if (b != null && !b) {
                List<String> messages = TX_MESSAGES.get();
                if (messages != null && messages.size() > 0) {
                    for (String message : messages) {
                        super.statementLog(message);
                    }
                }
            }
        } finally {
            AUTO_COMMIT.remove();
            TX_MESSAGES.remove();
        }
    }

    @Override
    public void connection_rollback(FilterChain chain, ConnectionProxy connection) throws SQLException {
        try {
            super.connection_rollback(chain, connection);
        } finally {
            AUTO_COMMIT.remove();
            TX_MESSAGES.remove();
        }
    }

    @Override
    public void connection_rollback(FilterChain chain, ConnectionProxy connection, Savepoint savepoint)
            throws SQLException {
        try {
            super.connection_rollback(chain, connection, savepoint);
        } finally {
            AUTO_COMMIT.remove();
            TX_MESSAGES.remove();
        }
    }

    @Override
    protected void statementLog(String message) {
        Boolean b = AUTO_COMMIT.get();
        if (b == null || b) {
            super.statementLog(message);
        } else {
            TX_MESSAGES.get().add(message);
        }
    }
}