MyBatis保存执行的SQL到数据库

 

当项目运行之后出现BUG的时候，想要从SQL中找问题，就会发现参数都是用“？”来代替的，想要在SQL工具中执行SQL就得把SQL语句和参数一个一个地粘贴出来，当参数较多的时候很容易出现错误，使用下面的配置可以将SQL保存到数据库中，一旦出现BUG可以直接从数据库中取SQL，便于快速定位问题

application-mybatis.xml（数据库配置信息）配置：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:mvc="http://www.springframework.org/schema/mvc"

       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="
       http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context.xsd
       http://www.springframework.org/schema/tx
       http://www.springframework.org/schema/tx/spring-tx.xsd
       http://www.springframework.org/schema/aop
       http://www.springframework.org/schema/aop/spring-aop.xsd
       http://www.springframework.org/schema/mvc
       http://www.springframework.org/schema/mvc/spring-mvc.xsd
">
    <context:property-placeholder location="jdbc.properties"/>
    <bean class="com.alibaba.druid.pool.DruidDataSource" id="dataSource">
        <property name="driverClassName" value="驱动"/>
        <property name="password" value="数据库密码"/>
        <property name="username" value="用户名"/>
        <property name="url" value="数据源"/>
        <property name="proxyFilters">
            <list>
                <ref bean="log4jFilter"/>
            </list>
        </property>
    </bean>
    <bean class="com.dlj.spring.config.Log4jFilter" id="log4jFilter">
        <property name="statementExecutableSqlLogEnable" value="true"/>
    </bean>
    <bean class="org.mybatis.spring.SqlSessionFactoryBean" id="sqlSessionFactory">
        <property name="dataSource" ref="dataSource"/>
        <property name="mapperLocations" value="mapper/*.xml"/>
        <property name="typeAliasesPackage" value="com.spring.*.entity"/>
    </bean>
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer" id="mapperScannerConfigurer">
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
        <property name="basePackage" value="com.spring.*.mapper"/>
    </bean>
</beans>

LogFilter（mybatis监听）配置：

package com.spring.config;

import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

import com.alibaba.druid.filter.FilterChain;
import com.alibaba.druid.filter.FilterEventAdapter;
import com.alibaba.druid.filter.logging.LogFilterMBean;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.alibaba.druid.proxy.jdbc.CallableStatementProxy;
import com.alibaba.druid.proxy.jdbc.ConnectionProxy;
import com.alibaba.druid.proxy.jdbc.DataSourceProxy;
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.util.JdbcUtils;
import com.spring.sys.entity.SysLogEntity;
import com.spring.sys.mapper.SysLogMapper;
import org.springframework.beans.factory.annotation.Autowired;

/**
 * @author wenshao [szujobs@hotmail.com]
 */
public abstract class LogFilter extends FilterEventAdapter implements LogFilterMBean {
    @Autowired
    private SysLogMapper sysLogMapper;
    protected String dataSourceLoggerName = "druid.sql.DataSource";
    protected String connectionLoggerName = "druid.sql.Connection";
    protected String statementLoggerName = "druid.sql.Statement";
    protected String resultSetLoggerName = "druid.sql.ResultSet";

    private boolean connectionConnectBeforeLogEnable = true;
    private boolean connectionConnectAfterLogEnable = true;
    private boolean connectionCommitAfterLogEnable = true;
    private boolean connectionRollbackAfterLogEnable = true;
    private boolean connectionCloseAfterLogEnable = true;

    private boolean statementCreateAfterLogEnable = true;
    private boolean statementPrepareAfterLogEnable = true;
    private boolean statementPrepareCallAfterLogEnable = true;

    private boolean statementExecuteAfterLogEnable = true;
    private boolean statementExecuteQueryAfterLogEnable = true;
    private boolean statementExecuteUpdateAfterLogEnable = true;
    private boolean statementExecuteBatchAfterLogEnable = true;
    private boolean statementExecutableSqlLogEnable = false;

    private boolean statementCloseAfterLogEnable = true;

    private boolean statementParameterClearLogEnable = true;
    private boolean statementParameterSetLogEnable = true;

    private boolean resultSetNextAfterLogEnable = true;
    private boolean resultSetOpenAfterLogEnable = true;
    private boolean resultSetCloseAfterLogEnable = true;

    private boolean dataSourceLogEnabled = true;
    private boolean connectionLogEnabled = true;
    private boolean connectionLogErrorEnabled = true;
    private boolean statementLogEnabled = true;
    private boolean statementLogErrorEnabled = true;
    private boolean resultSetLogEnabled = true;
    private boolean resultSetLogErrorEnabled = true;

    protected DataSourceProxy dataSource;

    public LogFilter() {
        configFromProperties(System.getProperties());
    }

    public void configFromProperties(Properties properties) {
        {
            String prop = properties.getProperty("druid.log.conn");
            if ("false".equals(prop)) {
                connectionLogEnabled = false;
            } else if ("true".equals(prop)) {
                connectionLogEnabled = true;
            }
        }
        {
            String prop = properties.getProperty("druid.log.stmt");
            if ("false".equals(prop)) {
                statementLogEnabled = false;
            } else if ("true".equals(prop)) {
                statementLogEnabled = true;
            }
        }
        {
            String prop = properties.getProperty("druid.log.rs");
            if ("false".equals(prop)) {
                resultSetLogEnabled = false;
            } else if ("true".equals(prop)) {
                resultSetLogEnabled = true;
            }
        }
        {
            String prop = properties.getProperty("druid.log.stmt.executableSql");
            if ("true".equals(prop)) {
                statementExecutableSqlLogEnable = true;
            } else if ("false".equals(prop)) {
                statementExecutableSqlLogEnable = false;
            }
        }
    }

    @Override
    public void init(DataSourceProxy dataSource) {
        this.dataSource = dataSource;
    }

    public boolean isConnectionLogErrorEnabled() {
        return connectionLogErrorEnabled;
    }

    public boolean isResultSetCloseAfterLogEnabled() {
        return isResultSetLogEnabled() && resultSetCloseAfterLogEnable;
    }

    public void setResultSetCloseAfterLogEnabled(boolean resultSetCloseAfterLogEnable) {
        this.resultSetCloseAfterLogEnable = resultSetCloseAfterLogEnable;
    }

    public void setConnectionLogErrorEnabled(boolean connectionLogErrorEnabled) {
        this.connectionLogErrorEnabled = connectionLogErrorEnabled;
    }

    public boolean isResultSetLogErrorEnabled() {
        return resultSetLogErrorEnabled;
    }

    public void setResultSetLogErrorEnabled(boolean resultSetLogErrorEnabled) {
        this.resultSetLogErrorEnabled = resultSetLogErrorEnabled;
    }

    public boolean isConnectionConnectBeforeLogEnabled() {
        return isConnectionLogEnabled() && connectionConnectBeforeLogEnable;
    }

    public void setConnectionConnectBeforeLogEnabled(boolean beforeConnectionConnectLogEnable) {
        this.connectionConnectBeforeLogEnable = beforeConnectionConnectLogEnable;
    }

    public boolean isConnectionCloseAfterLogEnabled() {
        return isConnectionLogEnabled() && connectionCloseAfterLogEnable;
    }

    public boolean isConnectionRollbackAfterLogEnabled() {
        return isConnectionLogEnabled() && connectionRollbackAfterLogEnable;
    }

    public void setConnectionRollbackAfterLogEnabled(boolean connectionRollbackAfterLogEnable) {
        this.connectionRollbackAfterLogEnable = connectionRollbackAfterLogEnable;
    }

    public void setConnectionCloseAfterLogEnabled(boolean afterConnectionCloseLogEnable) {
        this.connectionCloseAfterLogEnable = afterConnectionCloseLogEnable;
    }

    public boolean isConnectionCommitAfterLogEnabled() {
        return isConnectionLogEnabled() && connectionCommitAfterLogEnable;
    }

    public void setConnectionCommitAfterLogEnabled(boolean afterConnectionCommitLogEnable) {
        this.connectionCommitAfterLogEnable = afterConnectionCommitLogEnable;
    }

    public boolean isConnectionConnectAfterLogEnabled() {
        return isConnectionLogEnabled() && connectionConnectAfterLogEnable;
    }

    public void setConnectionConnectAfterLogEnabled(boolean afterConnectionConnectLogEnable) {
        this.connectionConnectAfterLogEnable = afterConnectionConnectLogEnable;
    }

    public boolean isResultSetNextAfterLogEnabled() {
        return isResultSetLogEnabled() && resultSetNextAfterLogEnable;
    }

    public void setResultSetNextAfterLogEnabled(boolean afterResultSetNextLogEnable) {
        this.resultSetNextAfterLogEnable = afterResultSetNextLogEnable;
    }

    public boolean isResultSetOpenAfterLogEnabled() {
        return isResultSetLogEnabled() && resultSetOpenAfterLogEnable;
    }

    public void setResultSetOpenAfterLogEnabled(boolean afterResultSetOpenLogEnable) {
        this.resultSetOpenAfterLogEnable = afterResultSetOpenLogEnable;
    }

    public boolean isStatementCloseAfterLogEnabled() {
        return isStatementLogEnabled() && statementCloseAfterLogEnable;
    }

    public void setStatementCloseAfterLogEnabled(boolean afterStatementCloseLogEnable) {
        this.statementCloseAfterLogEnable = afterStatementCloseLogEnable;
    }

    public boolean isStatementCreateAfterLogEnabled() {
        return isStatementLogEnabled() && statementCreateAfterLogEnable;
    }

    public void setStatementCreateAfterLogEnabled(boolean afterStatementCreateLogEnable) {
        this.statementCreateAfterLogEnable = afterStatementCreateLogEnable;
    }

    public boolean isStatementExecuteBatchAfterLogEnabled() {
        return isStatementLogEnabled() && statementExecuteBatchAfterLogEnable;
    }

    public void setStatementExecuteBatchAfterLogEnabled(boolean afterStatementExecuteBatchLogEnable) {
        this.statementExecuteBatchAfterLogEnable = afterStatementExecuteBatchLogEnable;
    }

    public boolean isStatementExecuteAfterLogEnabled() {
        return isStatementLogEnabled() && statementExecuteAfterLogEnable;
    }

    public void setStatementExecuteAfterLogEnabled(boolean afterStatementExecuteLogEnable) {
        this.statementExecuteAfterLogEnable = afterStatementExecuteLogEnable;
    }

    public boolean isStatementExecuteQueryAfterLogEnabled() {
        return isStatementLogEnabled() && statementExecuteQueryAfterLogEnable;
    }

    public void setStatementExecuteQueryAfterLogEnabled(boolean afterStatementExecuteQueryLogEnable) {
        this.statementExecuteQueryAfterLogEnable = afterStatementExecuteQueryLogEnable;
    }

    public boolean isStatementExecuteUpdateAfterLogEnabled() {
        return isStatementLogEnabled() && statementExecuteUpdateAfterLogEnable;
    }

    public void setStatementExecuteUpdateAfterLogEnabled(boolean afterStatementExecuteUpdateLogEnable) {
        this.statementExecuteUpdateAfterLogEnable = afterStatementExecuteUpdateLogEnable;
    }

    public boolean isStatementExecutableSqlLogEnable() {
        return statementExecutableSqlLogEnable;
    }

    public void setStatementExecutableSqlLogEnable(boolean statementExecutableSqlLogEnable) {
        this.statementExecutableSqlLogEnable = statementExecutableSqlLogEnable;
    }

    public boolean isStatementPrepareCallAfterLogEnabled() {
        return isStatementLogEnabled() && statementPrepareCallAfterLogEnable;
    }

    public void setStatementPrepareCallAfterLogEnabled(boolean afterStatementPrepareCallLogEnable) {
        this.statementPrepareCallAfterLogEnable = afterStatementPrepareCallLogEnable;
    }

    public boolean isStatementPrepareAfterLogEnabled() {
        return isStatementLogEnabled() && statementPrepareAfterLogEnable;
    }

    public void setStatementPrepareAfterLogEnabled(boolean afterStatementPrepareLogEnable) {
        this.statementPrepareAfterLogEnable = afterStatementPrepareLogEnable;
    }

    public boolean isDataSourceLogEnabled() {
        return dataSourceLogEnabled;
    }

    public void setDataSourceLogEnabled(boolean dataSourceLogEnabled) {
        this.dataSourceLogEnabled = dataSourceLogEnabled;
    }

    public boolean isConnectionLogEnabled() {
        return connectionLogEnabled;
    }

    public void setConnectionLogEnabled(boolean connectionLogEnabled) {
        this.connectionLogEnabled = connectionLogEnabled;
    }

    public boolean isStatementLogEnabled() {
        return statementLogEnabled;
    }

    public void setStatementLogEnabled(boolean statementLogEnabled) {
        this.statementLogEnabled = statementLogEnabled;
    }

    public boolean isStatementLogErrorEnabled() {
        return statementLogErrorEnabled;
    }

    public void setStatementLogErrorEnabled(boolean statementLogErrorEnabled) {
        this.statementLogErrorEnabled = statementLogErrorEnabled;
    }

    public boolean isResultSetLogEnabled() {
        return resultSetLogEnabled;
    }

    public void setResultSetLogEnabled(boolean resultSetLogEnabled) {
        this.resultSetLogEnabled = resultSetLogEnabled;
    }

    public boolean isStatementParameterSetLogEnabled() {
        return isStatementLogEnabled() && statementParameterSetLogEnable;
    }

    public void setStatementParameterSetLogEnabled(boolean statementParameterSetLogEnable) {
        this.statementParameterSetLogEnable = statementParameterSetLogEnable;
    }

    public boolean isStatementParameterClearLogEnable() {
        return isStatementLogEnabled() && statementParameterClearLogEnable;
    }

    public void setStatementParameterClearLogEnable(boolean statementParameterClearLogEnable) {
        this.statementParameterClearLogEnable = statementParameterClearLogEnable;
    }

    protected abstract void connectionLog(String message);

    protected abstract void statementLog(String message);

    protected abstract void statementLogError(String message, Throwable error);

    protected abstract void resultSetLog(String message);

    protected abstract void resultSetLogError(String message, Throwable error);

    public void connection_connectAfter(ConnectionProxy connection) {
        if (connection == null) {
            return;
        }

        if (connectionConnectAfterLogEnable && isConnectionLogEnabled()) {
            connectionLog("{conn-" + connection.getId() + "} connected");
        }
    }

    @Override
    public Savepoint connection_setSavepoint(FilterChain chain, ConnectionProxy connection) throws SQLException {

        Savepoint savepoint = chain.connection_setSavepoint(connection);

        if (isConnectionLogEnabled()) {
            connectionLog("{conn " + connection.getId() + "} setSavepoint-" + savepointToString(savepoint));
        }

        return savepoint;
    }

    @Override
    public Savepoint connection_setSavepoint(FilterChain chain, ConnectionProxy connection, String name)
            throws SQLException {
        Savepoint savepoint = chain.connection_setSavepoint(connection, name);

        if (isConnectionLogEnabled()) {
            connectionLog("{conn " + connection.getId() + "} setSavepoint-" + name);
        }

        return savepoint;
    }

    @Override
    public void connection_rollback(FilterChain chain, ConnectionProxy connection) throws SQLException {
        super.connection_rollback(chain, connection);

        if (connectionRollbackAfterLogEnable && isConnectionLogEnabled()) {
            connectionLog("{conn " + connection.getId() + "} rollback");
        }
    }

    @Override
    public void connection_rollback(FilterChain chain, ConnectionProxy connection, Savepoint savePoint)
            throws SQLException {
        super.connection_rollback(chain, connection, savePoint);

        if (connectionRollbackAfterLogEnable && isConnectionLogEnabled()) {
            connectionLog("{conn " + connection.getId() + "} rollback -> " + savepointToString(savePoint));
        }
    }

    @Override
    public void connection_commit(FilterChain chain, ConnectionProxy connection) throws SQLException {
        super.connection_commit(chain, connection);

        if (connectionCommitAfterLogEnable && isConnectionLogEnabled()) {
            connectionLog("{conn-" + connection.getId() + "} commited");
        }
    }

    @Override
    public void connection_setAutoCommit(FilterChain chain, ConnectionProxy connection, boolean autoCommit)
            throws SQLException {

        connectionLog("{conn-" + connection.getId() + "} setAutoCommit " + autoCommit);
        chain.connection_setAutoCommit(connection, autoCommit);
    }

    @Override
    public void connection_close(FilterChain chain, ConnectionProxy connection) throws SQLException {
        super.connection_close(chain, connection);

        if (connectionCloseAfterLogEnable && isConnectionLogEnabled()) {
            connectionLog("{conn-" + connection.getId() + "} closed");
        }
    }

    @Override
    public void statement_close(FilterChain chain, StatementProxy statement) throws SQLException {
        super.statement_close(chain, statement);

        if (statementCloseAfterLogEnable && isStatementLogEnabled()) {
            statementLog("{conn-" + statement.getConnectionProxy().getId() + ", " + stmtId(statement) + "} closed");
        }
    }

    @Override
    protected void statementExecuteBefore(StatementProxy statement, String sql) {
        statement.setLastExecuteStartNano();
        if (statement instanceof PreparedStatementProxy) {
            logParameter((PreparedStatementProxy) statement);
        }
    }

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

        if (statementExecuteAfterLogEnable && isStatementLogEnabled()) {
            statement.setLastExecuteTimeNano();
            double nanos = statement.getLastExecuteTimeNano();
            double millis = nanos / (1000 * 1000);

            statementLog("{conn-" + statement.getConnectionProxy().getId() + ", " + stmtId(statement) + "} executed. "
                    + millis + " millis. \n" + sql);
        }
    }

    @Override
    protected void statementExecuteBatchBefore(StatementProxy statement) {
        statement.setLastExecuteStartNano();
    }

    @Override
    protected void statementExecuteBatchAfter(StatementProxy statement, int[] result) {
        String sql;
        if (statement instanceof PreparedStatementProxy) {
            sql = ((PreparedStatementProxy) statement).getSql();
        } else {
            sql = statement.getBatchSql();
        }

        logExecutableSql(statement, sql);

        if (statementExecuteBatchAfterLogEnable && isStatementLogEnabled()) {
            statement.setLastExecuteTimeNano();
            double nanos = statement.getLastExecuteTimeNano();
            double millis = nanos / (1000 * 1000);

            statementLog("{conn-" + statement.getConnectionProxy().getId() + ", " + stmtId(statement)
                    + "} batch executed. " + millis + " millis. \n" + sql);
        }
    }

    @Override
    protected void statementExecuteQueryBefore(StatementProxy statement, String sql) {
        statement.setLastExecuteStartNano();
        if (statement instanceof PreparedStatementProxy) {
            logParameter((PreparedStatementProxy) statement);
        }
    }

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

        if (statementExecuteQueryAfterLogEnable && isStatementLogEnabled()) {
            statement.setLastExecuteTimeNano();
            double nanos = statement.getLastExecuteTimeNano();
            double millis = nanos / (1000 * 1000);

            statementLog("{conn-" + statement.getConnectionProxy().getId() + ", " + stmtId(statement) + ", rs-"
                    + resultSet.getId() + "} query executed. " + millis + " millis. \n" + sql);
        }
    }

    @Override
    protected void statementExecuteUpdateBefore(StatementProxy statement, String sql) {
        statement.setLastExecuteStartNano();
        if (statement instanceof PreparedStatementProxy) {
            logParameter((PreparedStatementProxy) statement);
        }
    }

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

        if (statementExecuteUpdateAfterLogEnable && isStatementLogEnabled()) {
            statement.setLastExecuteTimeNano();
            double nanos = statement.getLastExecuteTimeNano();
            double millis = nanos / (1000 * 1000);

            statementLog("{conn-" + statement.getConnectionProxy().getId() + ", " + stmtId(statement)
                    + "} update executed. effort " + updateCount + ". " + millis + " millis. \n" + sql);
        }
    }

    private void logExecutableSql(StatementProxy statement, String sql) {
        if (!isStatementExecutableSqlLogEnable()) {
            return;
        }

        int parametersSize = statement.getParametersSize();
        if (parametersSize == 0) {
            statementLog("{conn-" + statement.getConnectionProxy().getId() + ", " + stmtId(statement) + "} executed. \n"
                    + sql);
            return;
        }

        List<Object> parameters = new ArrayList<Object>(parametersSize);
        for (int i = 0; i < parametersSize; ++i) {
            JdbcParameter jdbcParam = statement.getParameter(i);
            parameters.add(jdbcParam.getValue());
        }

        String dbType = statement.getConnectionProxy().getDirectDataSource().getDbType();
        String formattedSql = SQLUtils.format(sql, dbType, parameters);
        if (formattedSql.indexOf("INSERT INTO TB_SYS_LOG") == -1){
            SysLogEntity sysLogEntity = new SysLogEntity();
            sysLogEntity.setContext(formattedSql);
            sysLogEntity.setCreateTime(new Date());
            sysLogMapper.saveSysLog(sysLogEntity);
        }
        statementLog("{conn-" + statement.getConnectionProxy().getId() + ", " + stmtId(statement) + "} executed. \n"
                + formattedSql);
    }

    @Override
    public void resultSet_close(FilterChain chain, ResultSetProxy resultSet) throws SQLException {
        chain.resultSet_close(resultSet);

        StringBuffer buf = new StringBuffer();
        buf.append("{conn-");
        buf.append(resultSet.getStatementProxy().getConnectionProxy().getId());
        buf.append(", ");
        buf.append(stmtId(resultSet));
        buf.append(", rs-");
        buf.append(resultSet.getId());
        buf.append("} closed");

        if (isResultSetCloseAfterLogEnabled()) {
            resultSetLog(buf.toString());
        }
    }

    @Override
    public boolean resultSet_next(FilterChain chain, ResultSetProxy resultSet) throws SQLException {
        boolean moreRows = super.resultSet_next(chain, resultSet);

        if (moreRows) {
            if (resultSetNextAfterLogEnable && isResultSetLogEnabled()) {
                try {
                    StringBuffer buf = new StringBuffer();
                    buf.append("{conn-");
                    buf.append(resultSet.getStatementProxy().getConnectionProxy().getId());
                    buf.append(", ");
                    buf.append(stmtId(resultSet));
                    buf.append(", rs-");
                    buf.append(resultSet.getId());
                    buf.append("}");
                    buf.append(" Result: [");

                    ResultSetMetaData meta = resultSet.getMetaData();
                    for (int i = 0, size = meta.getColumnCount(); i < size; ++i) {
                        if (i != 0) {
                            buf.append(", ");
                        }
                        int columnIndex = i + 1;
                        int type = meta.getColumnType(columnIndex);

                        Object value;
                        if (type == Types.TIMESTAMP) {
                            value = resultSet.getTimestamp(columnIndex);
                        } else if (type == Types.BLOB) {
                            value = "<BLOB>";
                        } else if (type == Types.CLOB) {
                            value = "<CLOB>";
                        } else if (type == Types.NCLOB) {
                            value = "<NCLOB>";
                        } else if (type == Types.BINARY) {
                            value = "<BINARY>";
                        } else {
                            value = resultSet.getObject(columnIndex);
                        }
                        buf.append(value);
                    }

                    buf.append("]");

                    resultSetLog(buf.toString());
                } catch (SQLException ex) {
                    resultSetLogError("logging error", ex);
                }
            }
        }

        return moreRows;
    }

    @Override
    public Object callableStatement_getObject(FilterChain chain, CallableStatementProxy statement, int parameterIndex)
            throws SQLException {
        Object obj = chain.callableStatement_getObject(statement, parameterIndex);

        if (obj instanceof ResultSetProxy) {
            resultSetOpenAfter((ResultSetProxy) obj);
        }

        return obj;
    }

    @Override
    public Object callableStatement_getObject(FilterChain chain, CallableStatementProxy statement, int parameterIndex,
                                              java.util.Map<String, Class<?>> map) throws SQLException {
        Object obj = chain.callableStatement_getObject(statement, parameterIndex, map);

        if (obj instanceof ResultSetProxy) {
            resultSetOpenAfter((ResultSetProxy) obj);
        }

        return obj;
    }

    @Override
    public Object callableStatement_getObject(FilterChain chain, CallableStatementProxy statement, String parameterName)
            throws SQLException {
        Object obj = chain.callableStatement_getObject(statement, parameterName);

        if (obj instanceof ResultSetProxy) {
            resultSetOpenAfter((ResultSetProxy) obj);
        }

        return obj;
    }

    @Override
    public Object callableStatement_getObject(FilterChain chain, CallableStatementProxy statement,
                                              String parameterName, java.util.Map<String, Class<?>> map)
            throws SQLException {
        Object obj = chain.callableStatement_getObject(statement, parameterName, map);

        if (obj instanceof ResultSetProxy) {
            resultSetOpenAfter((ResultSetProxy) obj);
        }

        return obj;
    }

    @Override
    protected void resultSetOpenAfter(ResultSetProxy resultSet) {
        if (resultSetOpenAfterLogEnable && isResultSetLogEnabled()) {
            try {
                StringBuffer buf = new StringBuffer();
                buf.append("{conn-");
                buf.append(resultSet.getStatementProxy().getConnectionProxy().getId());
                buf.append(", ");
                buf.append(stmtId(resultSet));
                buf.append(", rs-");
                buf.append(resultSet.getId());
                buf.append("}");

                String resultId = buf.toString();
                resultSetLog(resultId + " open");

                buf.append(" Header: [");

                ResultSetMetaData meta = resultSet.getMetaData();
                for (int i = 0, size = meta.getColumnCount(); i < size; ++i) {
                    if (i != 0) {
                        buf.append(", ");
                    }
                    buf.append(meta.getColumnName(i + 1));
                }
                buf.append("]");

                resultSetLog(buf.toString());
            } catch (SQLException ex) {
                resultSetLogError("logging error", ex);
            }
        }
    }

    protected void statementCreateAfter(StatementProxy statement) {
        if (statementCreateAfterLogEnable && isStatementLogEnabled()) {
            statementLog("{conn-" + statement.getConnectionProxy().getId() + ", stmt-" + statement.getId()
                    + "} created");
        }
    }

    protected void statementPrepareAfter(PreparedStatementProxy statement) {
        if (statementPrepareAfterLogEnable && isStatementLogEnabled()) {
            statementLog("{conn-" + statement.getConnectionProxy().getId() + ", pstmt-" + statement.getId()
                    + "} created. \n" + statement.getSql());
        }
    }

    protected void statementPrepareCallAfter(CallableStatementProxy statement) {
        if (statementPrepareCallAfterLogEnable && isStatementLogEnabled()) {
            statementLog("{conn-" + statement.getConnectionProxy().getId() + ", cstmt-" + statement.getId()
                    + "} created. \n" + statement.getSql());
        }
    }

    @Override
    protected void statement_executeErrorAfter(StatementProxy statement, String sql, Throwable error) {
        if (this.isStatementLogErrorEnabled()) {
            statementLogError("{conn-" + statement.getConnectionProxy().getId() + ", " + stmtId(statement)
                    + "} execute error. " + sql, error);
        }
    }

    private String stmtId(ResultSetProxy resultSet) {
        return stmtId(resultSet.getStatementProxy());
    }

    private String stmtId(StatementProxy statement) {
        StringBuffer buf = new StringBuffer();
        if (statement instanceof CallableStatementProxy) {
            buf.append("cstmt-");
        } else if (statement instanceof PreparedStatementProxy) {
            buf.append("pstmt-");
        } else {
            buf.append("stmt-");
        }
        buf.append(statement.getId());

        return buf.toString();
    }

    protected void logParameter(PreparedStatementProxy statement) {
        if (isStatementParameterSetLogEnabled()) {
            {
                StringBuffer buf = new StringBuffer();
                buf.append("{conn-");
                buf.append(statement.getConnectionProxy().getId());
                buf.append(", ");
                buf.append(stmtId(statement));
                buf.append("}");
                buf.append(" Parameters : [");

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

                    int sqlType = parameter.getSqlType();
                    Object value = parameter.getValue();
                    switch (sqlType) {
                        case Types.NULL:
                            buf.append("NULL");
                            break;
                        default:
                            buf.append(String.valueOf(value));
                            break;
                    }
                }
                buf.append("]");
                statementLog(buf.toString());
            }
            {
                StringBuffer buf = new StringBuffer();
                buf.append("{conn-");
                buf.append(statement.getConnectionProxy().getId());
                buf.append(", ");
                buf.append(stmtId(statement));
                buf.append("}");
                buf.append(" Types : [");
                for (int i = 0, parametersSize = statement.getParametersSize(); i < parametersSize; ++i) {
                    JdbcParameter parameter = statement.getParameter(i);
                    if (i != 0) {
                        buf.append(", ");
                    }
                    if (parameter == null) {
                        continue;
                    }
                    int sqlType = parameter.getSqlType();
                    buf.append(JdbcUtils.getTypeName(sqlType));
                }
                buf.append("]");
                statementLog(buf.toString());
            }
        }
    }

    @Override
    public void dataSource_releaseConnection(FilterChain chain, DruidPooledConnection conn) throws SQLException {
        long connectionId = -1;

        if (conn.getConnectionHolder() != null) {
            ConnectionProxy connection = (ConnectionProxy) conn.getConnectionHolder().getConnection();
            connectionId = connection.getId();
        }

        chain.dataSource_recycle(conn);

        if (connectionCloseAfterLogEnable && isConnectionLogEnabled()) {
            connectionLog("{conn-" + connectionId + "} pool-recycle");
        }
    }

    @Override
    public DruidPooledConnection dataSource_getConnection(FilterChain chain, DruidDataSource dataSource,
                                                          long maxWaitMillis) throws SQLException {
        DruidPooledConnection conn = chain.dataSource_connect(dataSource, maxWaitMillis);

        ConnectionProxy connection = (ConnectionProxy) conn.getConnectionHolder().getConnection();

        if (connectionConnectAfterLogEnable && isConnectionLogEnabled()) {
            connectionLog("{conn-" + connection.getId() + "} pool-connect");
        }

        return conn;
    }

    @Override
    public void preparedStatement_clearParameters(FilterChain chain, PreparedStatementProxy statement)
            throws SQLException {

        if (isStatementParameterClearLogEnable()) {
            statementLog("{conn-" + statement.getConnectionProxy().getId() + ", pstmt-" + statement.getId()
                    + "} clearParameters. ");
        }
        chain.preparedStatement_clearParameters(statement);
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) {
        return iface == this.getClass() || iface == com.alibaba.druid.filter.logging.LogFilter.class;
    }

    @SuppressWarnings("unchecked")
    @Override
    public <T> T unwrap(Class<T> iface) {
        if (iface == this.getClass() || iface == com.alibaba.druid.filter.logging.LogFilter.class) {
            return (T) this;
        }
        return null;
    }

    protected String savepointToString(Savepoint savePoint) {
        String savePointString = null;
        try {
            savePointString = savePoint.getSavepointName();
        } catch (SQLException e) {
            try {
                savePointString = String.valueOf(savePoint.getSavepointId());
            } catch (SQLException e1) {
                savePointString = savePoint.toString();
            }
        }
        return savePointString;
    }
}

 

Log4jFilter（mybatis监听）配置：

package com.spring.config;

import com.alibaba.druid.filter.logging.Log4jFilterMBean;
import org.apache.log4j.Level;
import org.apache.log4j.Logger;

/**
 * @author wenshao [szujobs@hotmail.com]
 */
public class Log4jFilter extends LogFilter implements Log4jFilterMBean {

    private Logger dataSourceLogger = Logger.getLogger(dataSourceLoggerName);
    private Logger connectionLogger = Logger.getLogger(connectionLoggerName);
    private Logger statementLogger  = Logger.getLogger(statementLoggerName);
    private Logger resultSetLogger  = Logger.getLogger(resultSetLoggerName);

    @Override
    public String getDataSourceLoggerName() {
        return dataSourceLoggerName;
    }

    @Override
    public void setDataSourceLoggerName(String dataSourceLoggerName) {
        this.dataSourceLoggerName = dataSourceLoggerName;
        dataSourceLogger = Logger.getLogger(dataSourceLoggerName);
    }

    public void setDataSourceLogger(Logger dataSourceLogger) {
        this.dataSourceLogger = dataSourceLogger;
        this.dataSourceLoggerName = dataSourceLogger.getName();
    }

    @Override
    public String getConnectionLoggerName() {
        return connectionLoggerName;
    }

    @Override
    public void setConnectionLoggerName(String connectionLoggerName) {
        this.connectionLoggerName = connectionLoggerName;
        connectionLogger = Logger.getLogger(connectionLoggerName);
    }

    public void setConnectionLogger(Logger connectionLogger) {
        this.connectionLogger = connectionLogger;
        this.connectionLoggerName = connectionLogger.getName();
    }

    @Override
    public String getStatementLoggerName() {
        return statementLoggerName;
    }

    @Override
    public void setStatementLoggerName(String statementLoggerName) {
        this.statementLoggerName = statementLoggerName;
        statementLogger = Logger.getLogger(statementLoggerName);
    }

    public void setStatementLogger(Logger statementLogger) {
        this.statementLogger = statementLogger;
        this.statementLoggerName = statementLogger.getName();
    }

    @Override
    public String getResultSetLoggerName() {
        return resultSetLoggerName;
    }

    @Override
    public void setResultSetLoggerName(String resultSetLoggerName) {
        this.resultSetLoggerName = resultSetLoggerName;
        resultSetLogger = Logger.getLogger(resultSetLoggerName);
    }

    public void setResultSetLogger(Logger resultSetLogger) {
        this.resultSetLogger = resultSetLogger;
        this.resultSetLoggerName = resultSetLogger.getName();
    }

    @Override
    public boolean isConnectionLogErrorEnabled() {
        return connectionLogger.isEnabledFor(Level.ERROR) && super.isConnectionLogErrorEnabled();
    }

    @Override
    public boolean isDataSourceLogEnabled() {
        return dataSourceLogger.isDebugEnabled() && super.isDataSourceLogEnabled();
    }

    @Override
    public boolean isConnectionLogEnabled() {
        return connectionLogger.isDebugEnabled() && super.isConnectionLogEnabled();
    }

    @Override
    public boolean isStatementLogEnabled() {
        return statementLogger.isDebugEnabled() && super.isStatementLogEnabled();
    }

    @Override
    public boolean isResultSetLogEnabled() {
        return resultSetLogger.isDebugEnabled() && super.isResultSetLogEnabled();
    }

    @Override
    public boolean isResultSetLogErrorEnabled() {
        return resultSetLogger.isEnabledFor(Level.ERROR) && super.isResultSetLogErrorEnabled();
    }

    @Override
    public boolean isStatementLogErrorEnabled() {
        return statementLogger.isEnabledFor(Level.ERROR) && super.isStatementLogErrorEnabled();
    }

    @Override
    protected void connectionLog(String message) {
        connectionLogger.debug(message);
    }

    @Override
    protected void statementLog(String message) {
        statementLogger.debug(message);
    }

    @Override
    protected void resultSetLog(String message) {
        resultSetLogger.debug(message);
    }

    @Override
    protected void resultSetLogError(String message, Throwable error) {
        resultSetLogger.error(message, error);
    }

    @Override
    protected void statementLogError(String message, Throwable error) {
        statementLogger.error(message, error);
    }
}

SysLogMapper.xml（保存日志的mapper文件）

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.spring.sys.mapper.SysLogMapper">
    <insert id="saveSysLog" parameterType="com.spring.sys.entity.SysLogEntity">
        <selectKey resultType="java.math.BigDecimal" order="BEFORE" keyProperty="id">
            SELECT SQ_SYS_LOG.NEXTVAL as id FROM DUAL
        </selectKey>
        INSERT INTO TB_SYS_LOG(ID,CONTEXT,CREATE_TIME) VALUES (#{id},#{context},#{createTime})
    </insert>
</mapper>

 SysUserMapper.xml（业务逻辑操作的mapper文件）

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.spring.sys.mapper.SysUserMapper">
    <select id="getAll" resultType="map" parameterType="string">
        SELECT * FROM TB_SYS_USER WHERE PHONE_NO=#{phone}
    </select>
</mapper>

SysLogMapper

package com.spring.sys.mapper;

import com.spring.sys.entity.SysLogEntity;

public interface SysLogMapper {
    /**
     * 保存日志信息
     * @param sysLogEntity
     * @return
     */
    Integer saveSysLog(SysLogEntity sysLogEntity);
}

SysUserMapper

package com.spring.sys.mapper;

import java.util.List;
import java.util.Map;

public interface SysUserMapper {
    List<Map<String,Object>> getAll(String phone);
}

SysLogMapper

package com.spring.sys.mapper;

import com.dlj.spring.sys.entity.SysLogEntity;

public interface SysLogMapper {
    /**
     * 保存日志信息
     * @param sysLogEntity
     * @return
     */
    Integer saveSysLog(SysLogEntity sysLogEntity);
}

SysLogEntity

package com.spring.sys.entity;

import java.math.BigDecimal;
import java.util.Date;

/**
 * 系统日志工具类
 */
public class SysLogEntity {
    private BigDecimal id;
    private String context;
    private Date createTime;

    public BigDecimal getId() {
        return id;
    }

    public void setId(BigDecimal id) {
        this.id = id;
    }

    public String getContext() {
        return context;
    }

    public void setContext(String context) {
        this.context = context;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
}

TestSpring

package com.spring.test;

import com.spring.sys.mapper.SysUserMapper;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.List;
import java.util.Map;

public class TestSpring {
    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext("application-mybatis.xml");
        SysUserMapper sysUserMapper = context.getBean(SysUserMapper.class);
        for (int i=0;i<10;i++){
            List<Map<String, Object>> all = sysUserMapper.getAll("18120123118");
            System.out.println(all);
        }
    }
}

需要执行的SQL

CREATE SEQUENCE SQ_SYS_LOG;
CREATE TABLE TB_SYS_USER(
  ID VARCHAR2(50),
  USER_NAME VARCHAR2(20),
  PASSWORD VARCHAR2(200)
);
CREATE TABLE TB_SYS_LOG(
  ID NUMBER,
  CONTEXT CLOB,
  CREATE_DATE DATE
);

 

 

 

 

需要引入的JAR包（此处的JAR包有点多，需要根据项目的需要进行筛选）

 

aip-java-sdk-4.11.1.jar
aspectjweaver-1.8.13.jar
common-codec-1.3-sources.jar
commons-lang3-3.5.jar
commons-pool2-2.4.2.jar
druid-1.0.19.jar
fastjson-1.2.49.jar
freemarker-2.3.9.jar
guava-21.0.jar
hamcrest-core-1.3.jar
httpclient-4.5.5.jar
httpcore-4.4.9.jar
jackson-annotations-2.9.0.jar
jackson-core-2.9.0.jar
jackson-databind-2.9.0.jar
jconsole.jar
jedis-2.7.0.jar
jsqlparser-1.1.jar
jsqlparser-2.0.jar
junit-4.12.jar
log4j-over-slf4j-1.7.25.jar
logback-classic-1.2.3.jar
logback-core-1.2.3.jar
mail-1.4.7.jar
mybatis-3.5.1.jar
mybatis-plus-2.1.9.jar
mybatis-spring-2.0.0.jar
ojdbc-6.jar
pagehelper-5.1.5.jar
slf4j-api-1.7.25.jar
spring-aop-5.0.5.RELEASE.jar
spring-aspects-5.0.5.RELEASE.jar
spring-beans-5.0.5.RELEASE.jar
spring-context-5.0.5.RELEASE.jar
spring-context-support-5.0.5.RELEASE.jar
spring-core-5.0.5.RELEASE.jar
spring-data-redis-1.5.0.RELEASE.jar
spring-expression-5.0.5.RELEASE.jar
spring-jcl-5.0.5.RELEASE.jar
spring-jdbc-5.0.5.RELEASE.jar
spring-messaging-5.0.5.RELEASE.jar
spring-test-5.0.5.RELEASE.jar
spring-tx-5.0.5.RELEASE.jar
spring-web-5.0.5.RELEASE.jar
spring-webmvc-5.0.5.RELEASE.jar
tools.jar
velocity-engine-core-2.0.jar