mybatis拦截器 打印完整sql日志，并存入数据库

完整sql日志，并存入数据库"/>

mybatis拦截器 打印完整sql日志，并存入数据库

1、前言这个东西卡了我一天，终于弄好了，写个博客庆祝一下，顺便让帮助别人。我司有个需求，记录特定功能执行的sql，并存入数据库，这时我就想到了mybatis的拦截器。
2、参考文献：://www.jb51/article/236467.htm、获得完整SQL、	存入数据库问题在于存入数据库，Mybatis的插件先于spring容器的完全初始化，虽然加了@Component会被扫描加入容器管理，但是此时Mybatis的拦截器Dblnterceptor注入的对象EncryptManager是还未初始化到容器的。所以通过这种方式拿到的bean为空。简单来说就是@Autowired 不管用，得用别的方法，这个方法就是创建SpringBeanUtils工具类getBean，文献3有代码。

package com.ty.crm.util;import com.spire.pdf.packages.spreG;
import com.ty.crm.DO.Sys_Operation_LogDo;
import com.ty.crm.mapper.OperationLogMapper;
import com.ty.crm.shior.token.manager.TokenManager;
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.type.TypeHandlerRegistry;
import org.springframework.stereotype.Component;import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;@Intercepts({@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),}
)
@Component
public class SqlInterceptor implements Interceptor {Properties properties = null;@Overridepublic Object intercept(Invocation invocation) throws Throwable {Object target = invocation.getTarget(); //被代理对象Method method = invocation.getMethod(); //代理方法Object[] args = invocation.getArgs(); //方法参数// do something ...方法拦截前执行代码块this.addSqlLog(args);Object result = invocation.proceed();// do something ...方法拦截后执行代码块，不改变原有的sql执行过程return result;}@Overridepublic Object plugin(Object target) {// 只对要拦截制定类型的对象生成代理，这样能节省资源if (target instanceof Executor) {// 调用插件return Plugin.wrap(target, this);}return target;}// 设置插件属性（直接通过Spring的方式获取属性，所以这个方法一般也用不到），项目启动的时候数据就会被加载@Overridepublic void setProperties(Properties properties) {// 赋值成员变量，在其他方法使用this.properties = properties;}// 封装了一下sql语句，使得结果返回完整xml路径下的sql语句节点id + sql语句public void addSqlLog(Object[] args) {// 白名单：需要被记录日志的sql语句Mapper idList<String> whiteList = Arrays.asList("com.ty.crm.mapper.OrderMapper.insertpurchase2List","com.ty.crm.mapper.OrderMapper.updatePurchaserequisition2Detail","com.ty.crm.mapper.OrderMapper.updatePurchaserequisition1Detail");String sql = null;MappedStatement mappedStatement = null;try {mappedStatement = (MappedStatement) args[0];Object parameter = args[1];// 白名单过滤if (!whiteList.contains(mappedStatement.getId())) {return;}BoundSql boundSql = mappedStatement.getBoundSql(parameter); // BoundSql就是封装myBatis最终产生的sql类Configuration configuration = mappedStatement.getConfiguration(); // 获取节点的配置sql = getSql(configuration, boundSql); // 获取到最终的sql语句} catch (Exception e) {e.printStackTrace();throw new RuntimeException("日志保存异常");}if (sql == null || "".equals(sql)) {return;}//新增操作日志Sys_Operation_LogDo sys_operation_logDo = new Sys_Operation_LogDo();sys_operation_logDo.setAccount_no(TokenManager.getAccountNo());sys_operation_logDo.setLog_usr_id(TokenManager.getUserId().intValue());sys_operation_logDo.setLog_date(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));sys_operation_logDo.setLog_sql(sql);// 保存try {OperationLogMapper operationLogMapper = SpringBeanUtils.getBean(OperationLogMapper.class);operationLogMapper.insertOperationLog(sys_operation_logDo);} catch (Exception e) {e.printStackTrace();throw new RuntimeException("日志保存异常");}}// 获得sql，进行？的替换public static String getSql(Configuration configuration, BoundSql boundSql) {// 获取参数Object parameterObject = boundSql.getParameterObject();List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();// sql语句中多个空格都用一个空格代替String sql = boundSql.getSql().replaceAll("[\\s]+", " ");if (parameterMappings != null && parameterMappings.size() != 0 && parameterObject != null) {// 获取类型处理器注册器，类型处理器的功能是进行java类型和数据库类型的转换TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();// 如果根据parameterObject.getClass(）可以找到对应的类型，则替换if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(parameterObject)));} else {// MetaObject主要是封装了originalObject对象，提供了get和set的方法用于获取和设置originalObject的属性值,主要支持对JavaBean、Collection、Map三种类型对象的操作MetaObject metaObject = configuration.newMetaObject(parameterObject);for (ParameterMapping parameterMapping : parameterMappings) {String propertyName = parameterMapping.getProperty();if (metaObject.hasGetter(propertyName)) {Object obj = metaObject.getValue(propertyName);sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));} else if (boundSql.hasAdditionalParameter(propertyName)) {// 该分支是动态sqlObject obj = boundSql.getAdditionalParameter(propertyName);sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));} else {// 打印出缺失，提醒该参数缺失并防止错位sql = sql.replaceFirst("\\?", "缺失");}}}}return sql;}// 如果参数是String，则添加单引号， 如果是日期，则转换为时间格式器并加单引号； 对参数是null和不是null的情况作了处理private static String getParameterValue(Object obj) {String value = null;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(new Date()) + "'";} else {if (obj != null) {value = obj.toString();} else {value = "";}}return value;}}