本文介绍如何配置MyBatis以保存执行的SQL到数据库，方便在出现问题时，直接从数据库获取SQL进行排查，避免了手动拼接参数的困扰。涉及配置文件如application-mybatis.xml、SysLogMapper.xml和SysUserMapper.xml，以及相关的实体类和所需JAR包。
当项目运行之后出现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
扫一扫
9000
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
