本篇内容介绍了“Mybatis拦截器打印sql问题怎么解决”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!
1.log4j2配置修改
关闭log4j2打印mybatis的sql配置,如果本来就没开启,那不需要。
2.配置日志开关
在springboot的yml文件配置变量logging.sql.enable=true,对拦截器做开关控制。
3.添加拦截器插件
// 采用自定义拦截器打印sql日志 sessionFactory.setPlugins(new Interceptor[]{this.getSqlLogInterceptor()});
4.拦截器逻辑描述
4.1 注入开关
@Value("${logging.sql.enable:true}") private Boolean sqlEnable;
4.2 获取sql
Object target = invocation.getTarget(); StatementHandler statementHandler = (StatementHandler) target; //获取绑定的SQL对象 BoundSql boundSql = statementHandler.getBoundSql(); //得到需要执行的sql语句,并进行格式 String sql = boundSql.getSql();
4.2 获取参数
//需要绑定的参数映射对象 List<ParameterMapping> parameterMappingList = boundSql.getParameterMappings();
4.3 sql替换参数
public String buidSql(String sql,Object[] parameters) { if (parameters == null || sql == null) { return ""; } List<Object> parametersArray = Arrays.asList(parameters); List<Object> list = new ArrayList<Object>(parametersArray); while (sql.indexOf("?") != -1 && list.size() > 0 && parameters.length > 0) { Object obj = list.get(0); if(null!=obj && obj instanceof String){ sql = sql.replaceFirst("\?", "'"+obj.toString()+"'"); }else if(null!=obj){ sql = sql.replaceFirst("\?", obj.toString()); } list.remove(0); } return sql.replaceAll("( ? (\s* ? )+)", " "); }
4.4 打印sql
log.debug(String.format( " ########################### Sql Start ###########################" + " StartTime : %s" + " ExecuteID : %s" + " ExecuteSQL : %s" + " ExecuteTime : %s ms" + " ########################### Sql End ########################### " ,startTimeStr,executeID,this.buidSql(sql,args.toString().split(",")),exeTime));
4.5打印效果
拦截器完整代码
package com.cloudpaas.plugin.mybatis.interceptor; import lombok.extern.slf4j.Slf4j; import org.apache.ibatis.executor.statement.StatementHandler; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.mapping.ParameterMapping; import org.apache.ibatis.mapping.ParameterMode; import org.apache.ibatis.plugin.*; import org.apache.ibatis.reflection.MetaObject; import org.apache.ibatis.reflection.SystemMetaObject; import org.apache.ibatis.scripting.defaults.DefaultParameterHandler; import org.apache.ibatis.session.Configuration; import org.apache.ibatis.session.ResultHandler; import org.apache.ibatis.type.TypeHandlerRegistry; import org.springframework.beans.factory.annotation.Value; import java.lang.reflect.Field; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.*; @Intercepts({ @Signature(type = StatementHandler.class, method = "query", args = {Statement.class, ResultHandler.class}), @Signature(type = StatementHandler.class, method = "update", args = {Statement.class}), @Signature(type = StatementHandler.class, method = "batch", args = { Statement.class }) }) @Slf4j public class SqlLogInterceptor implements Interceptor { private SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); @Value("${logging.sql.enable:true}") private Boolean sqlEnable; @Override public Object intercept(Invocation invocation) throws Throwable { if(!sqlEnable){ return invocation.proceed(); } Object target = invocation.getTarget(); MetaObject mObject = SystemMetaObject.forObject(invocation.getTarget()); MappedStatement mappedStatement = (MappedStatement)mObject.getValue("delegate.mappedStatement"); // 执行的mapper statement ID String executeID = mappedStatement.getId(); //获取当前的开始时间戳 long startTime = System.currentTimeMillis(); //记录当前时间 String startTimeStr=sdf.format(new Date()); StatementHandler statementHandler = (StatementHandler) target; try { return invocation.proceed(); } finally { long endTime = System.currentTimeMillis(); //sql的执行的时间 long exeTime = endTime - startTime; try{ //获取绑定的SQL对象 BoundSql boundSql = statementHandler.getBoundSql(); //得到需要执行的sql语句,并进行格式 String sql = boundSql.getSql(); sql=formatSql(sql); //得到默认的参数处理器 DefaultParameterHandler dph=(DefaultParameterHandler)statementHandler.getParameterHandler(); //利用反射机制,从DefaultParameterHandler获取Configuration和TypeHandlerRegistry Field configurationField=dph.getClass().getDeclaredField("configuration"); Field typeHandlerRegistryField=dph.getClass().getDeclaredField("typeHandlerRegistry"); //设置私有属性可访问 configurationField.setAccessible(true); //设置私有属性可访问 typeHandlerRegistryField.setAccessible(true); Configuration configuration=(Configuration) configurationField.get(dph); TypeHandlerRegistry typeHandlerRegistry=(TypeHandlerRegistry) typeHandlerRegistryField.get(dph); //sql的参数对象 Object parameterObject = boundSql.getParameterObject(); //需要绑定的参数映射对象 List<ParameterMapping> parameterMappingList = boundSql.getParameterMappings(); //处理sql的参数,该部分参考的是DefaultParameterHandler中setParameters方法中的实现 StringBuffer args=new StringBuffer(); if(parameterMappingList!=null && parameterMappingList.size()>0){ for(ParameterMapping parameterMapping:parameterMappingList){ //如果该参数不是输出参数,则进行处理 if (parameterMapping.getMode() != ParameterMode.OUT) { Object value; //参数的名字,属性 String propertyName = parameterMapping.getProperty(); //先从附加的,主要是list、array等的处理 if (boundSql.hasAdditionalParameter(propertyName)) { value = boundSql.getAdditionalParameter(propertyName); } else if (parameterObject == null) { value = null; } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) { //typeHandlerRegistry注册了某个类的处理 value = parameterObject; } else { //默认的MetaObject 的处理,根据参数获取值 MetaObject metaObject = configuration.newMetaObject(parameterObject); value = metaObject.getValue(propertyName); } if(value!=null){ if(value instanceof Date){ //如果是日期,则格式化一下 value=sdf.format(value); } } args.append(",").append(value); } } //删除第一个逗号 args.deleteCharAt(0); } log.debug(String.format( " ########################### Sql Start ###########################" + " StartTime : %s" + " ExecuteID : %s" + " ExecuteSQL : %s" + " ExecuteTime : %s ms" + " ########################### Sql End ########################### " ,startTimeStr,executeID,this.buidSql(sql,args.toString().split(",")),exeTime)); }catch(Exception e){ } } } @Override public Object plugin(Object target) { return Plugin.wrap(target, this); } @Override public void setProperties(Properties properties) { } private String formatSql(String sql) { // 输入sql字符串空判断 if (sql == null || sql.length() == 0) { return ""; } //格式sql 将回车换行制表符等替换成空,在将连续多个空格替换成1个空格,然后在去掉左右括号两边的空格,在去掉逗号左右两个的空格 return sql.replaceAll("[\t\n\x0B\f\r]", "").replaceAll(" +", " ") .replaceAll(" *\( *", "(").replaceAll(" *\) *", ")").replaceAll(" *, *", ","); } public String buidSql(String sql,Object[] parameters) { if (parameters == null || sql == null) { return ""; } List<Object> parametersArray = Arrays.asList(parameters); List<Object> list = new ArrayList<Object>(parametersArray); while (sql.indexOf("?") != -1 && list.size() > 0 && parameters.length > 0) { Object obj = list.get(0); if(null!=obj && obj instanceof String){ sql = sql.replaceFirst("\?", "'"+obj.toString()+"'"); }else if(null!=obj){ sql = sql.replaceFirst("\?", obj.toString()); } list.remove(0); } return sql.replaceAll("( ? (\s* ? )+)", " "); } }