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

最新推荐文章于 2024-08-25 20:48:51 发布

apply_bomb

最新推荐文章于 2024-08-25 20:48:51 发布

阅读量218

点赞数 3

文章标签：数据库 mybatis sql

本文链接：https://blog.csdn.net/apply_bomb/article/details/141530537

版权

前言

如果你使用的是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);


    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        //1. 执行sql
        long proceedStart = System.currentTimeMillis();
        Object returnValue = null;
        Exception proceedException = null;
        //执行sql时catch下异常，即使sql语法报错，也要打印完整sql
        try {
            returnValue = invocation.proceed();
        } catch (Exception e) {
            proceedException = e;
        }
        long proceedCost = System.currentTimeMillis() - proceedStart;


        //2.打印sql
        long printBegin = System.currentTimeMillis();
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        //部分mybatisplus拦截器内部可能会对Args中的sql进行修改，因此从Args中获取boundSql更接近与真实执行sql
        BoundSql 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#setParameters
    private 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 params
                        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);
                    }
                    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
                : "";
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public 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 values
        int 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 value
                Object 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;
        }

        @Override
        public 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打印结果