在项目开发与维护过程中,常常需要对程序执行的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";}
}