mybatis拦截器打印完整带参数sql，可直接放入数据库工具执行

本文主要是介绍mybatis拦截器打印完整带参数sql，可直接放入数据库工具执行，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

前言

如果你使用的是mybatisplus，那至少有3种方法打印sql, 如下博客

mybatisplus开启sql打印的三种方式_mybatisplus打印sql语句配置-CSDN博客

但是其中2种方法参数会打印问号，不能直接放入数据库工具执行

而使用p6spy插件需要引入新依赖，有未知的性能消耗。

因此，这里分享一种通过mybatis拦截器，实现sql打印

代码

package com.gree;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.mapping.ParameterMode;
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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;/*** 基于Mybatis Plus的SQL输出拦截器。* 完美的输出打印 SQL 及执行时长、statement。* 注意：该插件有性能损耗，不建议生产环境使用。*/@Intercepts(value = {@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})
})
//@Component用于注入到spring中，后续会被mybatis-plus-boot-starter中的代码从spring中取出并注入到mybatis中
@Component
public class MybatisPlusPrintSqlInterceptor implements Interceptor {Logger logger = LoggerFactory.getLogger(MybatisPlusPrintSqlInterceptor.class);@Overridepublic Object intercept(Invocation invocation) throws Throwable {//1. 执行sqllong proceedStart = System.currentTimeMillis();Object returnValue = null;Exception proceedException = null;//执行sql时catch下异常，即使sql语法报错，也要打印完整sqltry {returnValue = invocation.proceed();} catch (Exception e) {proceedException = e;}long proceedCost = System.currentTimeMillis() - proceedStart;//2.打印sqllong printBegin = System.currentTimeMillis();MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];//部分mybatisplus拦截器内部可能会对Args中的sql进行修改，因此从Args中获取boundSql更接近与真实执行sqlBoundSql boundSql = null;for (int i = invocation.getArgs().length - 1; i >= 0; i--) {if (invocation.getArgs()[i] instanceof BoundSql) {boundSql = (BoundSql) invocation.getArgs()[i];}}if (boundSql == null) {Object parameter = null;if (invocation.getArgs().length > 1) {parameter = invocation.getArgs()[1];}boundSql = mappedStatement.getBoundSql(parameter);}String statement = mappedStatement.getId();Configuration configuration = mappedStatement.getConfiguration();showSql(configuration, boundSql, proceedCost, statement);long printEnd = System.currentTimeMillis();System.out.println("本次打印sql消耗时间：" + (printEnd - printBegin));//3. sql执行异常的报错扔出去if (proceedException != null) {throw proceedException;}return returnValue;}private void showSql(Configuration configuration, BoundSql boundSql, long elapsed, String statement) {String logText = formatMessage(elapsed, getSqlWithValues(boundSql.getSql(), buildParameterValues(configuration, boundSql)), statement);if (Boolean.TRUE == false) {// 打印红色 SQL 日志System.err.println(logText);} else {logger.info("\n{}", logText);}}// com.baomidou.mybatisplus.core.MybatisParameterHandler#setParametersprivate static Map<Integer, Object> buildParameterValues(Configuration configuration, BoundSql boundSql) {Object parameterObject = boundSql.getParameterObject();// ParameterMapping描述参数，包括属性、名称、表达式、javaType、jdbcType、typeHandler等信息List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();if (parameterMappings != null) {Map<Integer, Object> parameterValues = new HashMap<>();//类型处理器用于注册所有的 TypeHandler，并建立 Jdbc 类型、JDBC 类型与 TypeHandler 之间的对应关系TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();for (int i = 0; i < parameterMappings.size(); i++) {ParameterMapping parameterMapping = parameterMappings.get(i);if (parameterMapping.getMode() != ParameterMode.OUT) {Object value;String propertyName = parameterMapping.getProperty();if (boundSql.hasAdditionalParameter(propertyName)) { // issue #448 ask first for additional paramsvalue = 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);}parameterValues.put(i, new Value(value));}}return parameterValues;}return Collections.emptyMap();}public static String formatMessage( long elapsed, String sql, String statement) {return (sql != null && sql != "") ?
//                " Consume Time：" + elapsed + " ms "  + " (" + statement + ")" + "  Execute SQL：" + sql.replaceAll("[\\s]+", " ")//" Consume Time：" + elapsed + " ms ,  Execute SQL：" + sql.replaceAll("[\\s]+", " ")
//                " Consume Time：" + elapsed + " ms ,  Execute SQL：" + sql" 本次执行sql耗时：" + elapsed + " ms "  + " (" + statement + ")" + "  执行的sql打印：" + sql: "";}@Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this);}@Overridepublic void setProperties(Properties properties0) {}public static String getSqlWithValues(String statementQuery, Map<Integer, Object> parameterValues) {final StringBuilder sb = new StringBuilder();// iterate over the characters in the query replacing the parameter placeholders// with the actual valuesint currentParameter = 0;for (int pos = 0; pos < statementQuery.length(); pos++) {char character = statementQuery.charAt(pos);if (statementQuery.charAt(pos) == '?' && currentParameter <= parameterValues.size()) {// replace with parameter valueObject value = parameterValues.get(currentParameter);sb.append(value != null ? value.toString() : new Value().toString());currentParameter++;} else {sb.append(character);}}return sb.toString();}/*** 基于p6spy的简易数据类型转换类。** @author laiqi* @date 2023-4-4*/public static class Value {public static final String NORM_DATETIME_PATTERN = "yyyy-MM-dd HH:mm:ss";public static final String databaseDialectDateFormat = NORM_DATETIME_PATTERN;public static final String databaseDialectTimestampFormat = NORM_DATETIME_PATTERN;public static final String databaseDialectBooleanFormat = "numeric";private Object value;public Value(Object valueToSet) {this();this.value = valueToSet;}public Value() {}public Object getValue() {return value;}public void setValue(Object value) {this.value = value;}@Overridepublic String toString() {return convertToString(this.value);}public String convertToString(Object value) {String result;if (value == null) {result = "NULL";} else {if (value instanceof byte[]) {result = new String((byte[]) value);} else if (value instanceof Timestamp) {result = new SimpleDateFormat(databaseDialectTimestampFormat).format(value);} else if (value instanceof Date) {result = new SimpleDateFormat(databaseDialectDateFormat).format(value);} else if (value instanceof Boolean) {if ("numeric".equals(databaseDialectBooleanFormat)) {result = Boolean.FALSE.equals(value) ? "0" : "1";} else {result = value.toString();}} else {result = value.toString();}result = quoteIfNeeded(result, value);}return result;}private String quoteIfNeeded(String stringValue, Object obj) {if (stringValue == null) {return null;}if (Number.class.isAssignableFrom(obj.getClass()) || Boolean.class.isAssignableFrom(obj.getClass())) {return stringValue;} else {return "'" + escape(stringValue) + "'";}}private String escape(String stringValue) {return stringValue.replaceAll("'", "''");}}
}

注意

1. 如果你的项目里有mybatis-plus-boot-starter，那只需要把这个类复制到springboot能被扫描到的地方就行，如果项目中没有mybatis-plus-boot-starter，那你可能还需要手动将这个拦截器注入到mybatis里

2. 如果你的项目有有mybatis-plus-boot-starter，并且存在其他mybatis拦截器或者mybatisplus的拦截器，那你需要调整bean的创建顺序，使得该打印sql的拦截器最早注入spring中，这样mybatisplus从spring中获取拦截器的时候，该打印sql的拦截器排在最前面，注入mybatis后，该打印sql的拦截器就会最后执行，你能获得更加准确的sql打印结果