从零开始SpringCloud Alibaba实战（43）——mybatis 拦截器打印完整sql实现

前言

项目开发中，sql执行会出现错误及性能缺陷，我们想把sql复制出现到客户端运行一下，先把sql打印到控制台

打印sql配置

方式一： 指定mybatis日志级别

# application.yml
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

# 等价于application.properties
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

方式二：配置mybatis-config.xml

<configuration>
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
</configuration>

方式三：指定整个mapper包下的日志级别

# application.yml
logging:
  level:
    com.example.demo.mapper: debug

# 等价于application.properties
logging.level.com.example.demo.mapper=debug

但这个打印的sql有个缺陷。
由于占位符，会出现好多？？？？？
，参数少还好，可以复制出来，但参数几十个呢，有没有一种办法，能把完整的sql打印出来呢？

mybatis 拦截器打印完整sql

代码

 
import java.lang.reflect.Field;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Properties;
 
import org.apache.commons.lang.StringUtils;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
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.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.Ordered;
import org.springframework.util.ReflectionUtils;
 
/**
 * 
 * @ClassName: MybatisSqlPrintIntercepter
 * @Description: sql 打印拦截器

 */
@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 }) })
public class MybatisSqlPrintIntercepter implements Interceptor, Ordered {
	private static final Logger logger = LoggerFactory.getLogger(MybatisSqlPrintIntercepter.class);
	private Configuration configuration = null;
 
	private static final ThreadLocal<SimpleDateFormat> DATE_FORMAT_THREAD_LOCAL = new ThreadLocal<SimpleDateFormat>() {
		@Override
		protected SimpleDateFormat initialValue() {
			return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
		}
	};
 
	@Override
	public Object intercept(Invocation invocation) throws Throwable {
		Object target = invocation.getTarget();
		long startTime = System.currentTimeMillis();
		try {
			return invocation.proceed();
		} finally {
			try {
				long endTime = System.currentTimeMillis();
				long sqlCost = endTime - startTime;
				StatementHandler statementHandler = (StatementHandler) target;
				BoundSql boundSql = statementHandler.getBoundSql();
				if (configuration == null) {
					final DefaultParameterHandler parameterHandler = (DefaultParameterHandler) statementHandler
							.getParameterHandler();
					Field configurationField = ReflectionUtils.findField(parameterHandler.getClass(), "configuration");
					ReflectionUtils.makeAccessible(configurationField);
					this.configuration = (Configuration) configurationField.get(parameterHandler);
				}
				// 替换参数格式化Sql语句，去除换行符
				String sql = formatSql(boundSql, configuration).concat(";");
				String 	warning = "";
				if(sqlCost > 100) {
					warning  = "[耗时过长]";
				}
				if(logger.isDebugEnabled()) {
					logger.debug("SQL==> {}    执行耗时:{} ms {}", sql, sqlCost,warning);
				}
			} catch (Exception e) {
				logger.error("==> 打印sql 日志异常 {}",e);
			}
		}
	}
 
	@Override
	public Object plugin(Object target) {
		return Plugin.wrap(target, this);
	}
 
	@Override
	public void setProperties(Properties properties) {
 
	}
 
	/**
	 * 获取完整的sql实体的信息
	 *
	 * @param boundSql
	 * @return
	 */
	private String formatSql(BoundSql boundSql, Configuration configuration) {
		String sql = boundSql.getSql();
		List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
		Object parameterObject = boundSql.getParameterObject();
		// 输入sql字符串空判断
		if (StringUtils.isBlank(sql)) {
			return "";
		}
		if (configuration == null) {
			return "";
		}
		TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
		sql = beautifySql(sql);
		// 参考mybatis 源码 DefaultParameterHandler
		if (parameterMappings != null) {
			for (ParameterMapping parameterMapping : parameterMappings) {
				if (parameterMapping.getMode() != ParameterMode.OUT) {
					Object value;
					String propertyName = parameterMapping.getProperty();
					if (boundSql.hasAdditionalParameter(propertyName)) {
						value = boundSql.getAdditionalParameter(propertyName);
					} else if (parameterObject == null) {
						value = null;
					} else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
						value = parameterObject;
					} else {
						MetaObject metaObject = configuration.newMetaObject(parameterObject);
						value = metaObject.getValue(propertyName);
					}
					String paramValueStr = "";
					if (value instanceof String) {
						paramValueStr = "'" + value + "'";
					} else if (value instanceof Date) {
						paramValueStr = "'" + DATE_FORMAT_THREAD_LOCAL.get().format(value) + "'";
					} else {
						paramValueStr = value + "";
					}
					sql = sql.replaceFirst("\\?", paramValueStr);
				}
			}
		}
		return sql;
	}
 
	private String beautifySql(String sql) {
		sql = sql.replaceAll("[\\s\n ]+", " ");
		return sql;
	}
 
	@Override
	public int getOrder() {
		return Ordered.HIGHEST_PRECEDENCE;
	}
 
}

2.添加拦截器

/**
	 * 完整sql 打印 拦截器配置
	 * @return
	 */
	@Bean
	@ConditionalOnExpression("${mybatis.sql.print:true}")
	public MybatisSqlPrintIntercepter mybatisSqlPrintIntercepter() {
		return new MybatisSqlPrintIntercepter();
	}

配置上此拦截器会打印完整sql