Spring Mybatis拦截器配合logback打印完整sql语句

在项目开发与维护过程中，常常需要对程序执行的sql语句，进行观察和分析。但是项目通常默认会使用org.apache.ibatis.logging.stdout.StdOutImpl日志配置，该配置是用System.out.println打印的日志，导致只能将执行语句打印到控制台，却没办法打印到日志文件中。如果放开logback日志等级，日志文件又会被其他大量的无用的debug信息填满。druid的日志打印又没办法实现精确打印。于是想到了拦截器的方法，将待执行的sql语句先拦截下来打印一遍再做执行操作。

创建自定义拦截器必须实现Interceptor接口，那么我们新建一个SqlLoggerInterceptor类并实现Interceptor接口，并且通过@Intercepts和@Signature注解来配置拦截的方法和对象

我们经常用到的就只有update和query，因此在@Intercepts内写上3个Signature：

@Slf4j
@Intercepts({//type指定代理的是那个对象，method指定代理Executor中的那个方法,args指定Executor中的query方法都有哪些参数对象//由于Executor中有两个query，因此需要两个@Signature@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),//需要代理的对象和方法@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class,ResultHandler.class, CacheKey.class, BoundSql.class}),@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class,ResultHandler.class})//需要代理的对象和方法
})

那么为什么是一个update和两个query呢？为什么没有insert，delete呢？

那是因为，update、query、query中的args中的对象是和Executor中的update、query、query方法中的参数是一一对应的。并且，Executor的update方法中已经包含了update，insert，delete方法。

源码：

    public int update(MappedStatement ms, Object parameter) throws SQLException {ErrorContext.instance().resource(ms.getResource()).activity("executing an update").object(ms.getId());if (this.closed) {throw new ExecutorException("Executor was closed.");} else {this.clearLocalCache();return this.doUpdate(ms, parameter);}}    public <E> List<E> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler) throws SQLException {BoundSql boundSql = ms.getBoundSql(parameter);CacheKey key = this.createCacheKey(ms, parameter, rowBounds, boundSql);return this.query(ms, parameter, rowBounds, resultHandler, key, boundSql);}public <E> List<E> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, CacheKey key, BoundSql boundSql) throws SQLException {ErrorContext.instance().resource(ms.getResource()).activity("executing a query").object(ms.getId());if (this.closed) {throw new ExecutorException("Executor was closed.");} else {if (this.queryStack == 0 && ms.isFlushCacheRequired()) {this.clearLocalCache();}List list;try {++this.queryStack;list = resultHandler == null ? (List)this.localCache.getObject(key) : null;if (list != null) {this.handleLocallyCachedOutputParameters(ms, key, parameter, boundSql);} else {list = this.queryFromDatabase(ms, parameter, rowBounds, resultHandler, key, boundSql);}} finally {--this.queryStack;}if (this.queryStack == 0) {Iterator var8 = this.deferredLoads.iterator();while(var8.hasNext()) {DeferredLoad deferredLoad = (DeferredLoad)var8.next();deferredLoad.load();}this.deferredLoads.clear();if (this.configuration.getLocalCacheScope() == LocalCacheScope.STATEMENT) {this.clearLocalCache();}}return list;}}

实现Interceptor接口中的方法，主要有三个方法分别是plugin、setProperties、intercept，而我们主要做的就是intercept方法，plugin方法一般固定即可，setProperties是在外部传近来的值

拦截器：

package com.**.**.interceptor;import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandlerRegistry;
import java.lang.reflect.InvocationTargetException;
import java.text.DateFormat;
import java.util.*;@Slf4j
@Intercepts({//type指定代理的是那个对象，method指定代理Executor中的那个方法,args指定Executor中的query方法都有哪些参数对象//由于Executor中有两个query，因此需要两个@Signature@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),//需要代理的对象和方法@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class,ResultHandler.class, CacheKey.class, BoundSql.class}),@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class,ResultHandler.class})//需要代理的对象和方法
})
public class SqlLoggerInterceptor implements Interceptor {@Overridepublic Object intercept(Invocation invocation) throws Throwable {//mappedStatement是个很有用的数据MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];Object parameter = null;if (invocation.getArgs().length > 1) {//获得查询方法的参数，比如selectById(Integer id,String name)，那么就可以获取到四个参数分别是：//{id:1,name:"user1",param1:1,param2:"user1"}parameter = invocation.getArgs()[1];}String sqlId = mappedStatement.getId();//获得mybatis的*mapper.xml文件中映射的方法，如：com.best.dao.UserMapper.selectById//将参数和映射文件组合在一起得到BoundSql对象BoundSql boundSql = mappedStatement.getBoundSql(parameter);//获取配置信息Configuration configuration = mappedStatement.getConfiguration();Object returnValue = null;System.out.println("/*---------------java:" + sqlId + "[begin]---------------*/");//通过配置信息和BoundSql对象来生成带值得sql语句String sql = geneSql(configuration, boundSql);//打印sql语句System.out.println("==> mysql : " + sql);//先记录执行sql语句前的时间long start = System.currentTimeMillis();try {//开始执行sql语句returnValue = invocation.proceed();} catch (InvocationTargetException | IllegalAccessException e) {e.printStackTrace();}//记录执行sql语句后的时间long end = System.currentTimeMillis();//得到执行sql语句的用了多长时间long time = (end - start);//以毫秒为单位打印System.out.println("<== sql执行历时：" + time + "毫秒");//返回值，如果是多条记录，那么此对象是一个list，如果是一个bean对象，那么此处就是一个对象，也有可能是一个mapreturn returnValue;}/*** 如果是字符串对象则加上单引号返回，如果是日期则也需要转换成字符串形式，如果是其他则直接转换成字符串返回。** @param obj* @return*/private static String getParameterValue(Object obj) {String value;if (obj instanceof String) {value = "'" + obj.toString() + "'";} else if (obj instanceof Date) {DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);value = "'" + formatter.format(obj) + "'";} else {if (obj != null) {value = obj.toString();} else {value = "";}}return value;}/*** 生成对应的带有值得sql语句** @param configuration* @param boundSql* @return*/public static String geneSql(Configuration configuration, BoundSql boundSql) {Object parameterObject = boundSql.getParameterObject();//获得参数对象，如{id:1,name:"user1",param1:1,param2:"user1"}List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();//获得映射的对象参数String sql = boundSql.getSql().replaceAll("[\\s]+", " ");//获得带问号的sql语句if (parameterMappings.size() > 0 && parameterObject != null) {//如果参数个数大于0且参数对象不为空，说明该sql语句是带有条件的TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {//检查该参数是否是一个参数//getParameterValue用于返回是否带有单引号的字符串，如果是字符串则加上单引号sql = sql.replaceFirst("\\?", getParameterValue(parameterObject));//如果是一个参数则只替换一次，将问号直接替换成值} else {MetaObject metaObject = configuration.newMetaObject(parameterObject);//将映射文件的参数和对应的值返回，比如：id，name以及对应的值。for (ParameterMapping parameterMapping : parameterMappings) {//遍历参数，如:id,name等String propertyName = parameterMapping.getProperty();//获得属性名，如id,name等字符串if (metaObject.hasGetter(propertyName)) {//检查该属性是否在metaObject中Object obj = metaObject.getValue(propertyName);//如果在metaObject中，那么直接获取对应的值sql = sql.replaceFirst("\\?", getParameterValue(obj));//然后将问号?替换成对应的值。} else if (boundSql.hasAdditionalParameter(propertyName)) {Object obj = boundSql.getAdditionalParameter(propertyName);sql = sql.replaceFirst("\\?", getParameterValue(obj));}}}}return sql;//最后将sql语句返回}@Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this);}@Overridepublic void setProperties(Properties properties) {//properties可以通过System.out.println(properties);}
}

此拦截写好之后还需要springboot使用，如果是通过@Component扫描是无法正常注入到IOC容器中的，因此需要通过@Configuration和@Bean的方式进行，新建一个配置BatisPlusConfig类，代码如下

@Configuration
public class BatisPlusConfig {@Beanpublic String myInterceptor(SqlSessionFactory sqlSessionFactory) {//实例化插件SqlLoggerInterceptor sqlInterceptor = new SqlLoggerInterceptor();//创建属性值Properties properties = new Properties();properties.setProperty("prop1", "value1");//将属性值设置到插件中sqlInterceptor.setProperties(properties);//将插件添加到SqlSessionFactory工厂sqlSessionFactory.getConfiguration().addInterceptor(sqlInterceptor);return "interceptor";}
}