Mybatis-Plus系列---【自定义sql拦截器打印完整sql及耗时】

最新推荐文章于 2025-12-11 22:00:34 发布

原创最新推荐文章于 2025-12-11 22:00:34 发布 · 358 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #sql #数据库

1.自定义sql拦截器(注意：Executor是org.apache.ibatis.executor.Executor包下的，不要导错了)

import cn.hutool.core.util.ObjectUtil;
import lombok.extern.slf4j.Slf4j;
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.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 java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.regex.Matcher;

@Intercepts({
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.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 = "update", args = {MappedStatement.class, Object.class})
})
@Slf4j
public class SqlPrintInterceptor implements Interceptor {

    // ANSI颜色常量
    private static final String ANSI_RESET = "\u001B[0m";
    private static final String ANSI_RED = "\u001B[31m";
    private static final String ANSI_GREEN = "\u001B[32m";
    private static final String ANSI_YELLOW = "\u001B[33m";
    private static final String ANSI_CYAN = "\u001B[36m";

    // 慢SQL阈值(毫秒)
    private static final long SLOW_SQL_THRESHOLD = 2000L;
    
    // 日期格式化器(线程安全)
    private static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        String sqlId = mappedStatement.getId();
        
        long startTime = System.currentTimeMillis();
        Object result = null;
        Throwable exception = null;
        
        try {
            result = invocation.proceed();
        } catch (Throwable t) {
            exception = t;
            throw t;
        } finally {
            long costTime = System.currentTimeMillis() - startTime;
            printSqlLog(invocation, sqlId, costTime, result, exception);
        }
        
        return result;
    }

    private void printSqlLog(Invocation invocation, String sqlId, long costTime, Object result, Throwable exception) {
        String fullSql;
        try {
            fullSql = buildCompleteSql(invocation);
        } catch (Exception e) {
            log.error("拼接完整SQL失败", e);
            fullSql = "SQL拼接失败: " + e.getMessage();
        }

        // 统计返回行数
        String resultInfo = getResultInfo(result);
        
        // 构建日志
        StringBuilder sb = new StringBuilder();
        sb.append("\n┌─────────────────────────────────────────────────────────────");
        sb.append("\n│ SQL ID  : ").append(sqlId);
        sb.append("\n│ 耗时    : ").append(costTime).append(" ms");
        sb.append("\n│ 结果    : ").append(resultInfo);
        if (exception != null) {
            sb.append("\n│ 异常    : ").append(exception.getClass().getSimpleName()).append(" - ").append(exception.getMessage());
        }
        sb.append("\n│ SQL     : ").append(fullSql);
        sb.append("\n└─────────────────────────────────────────────────────────────");

        // 根据执行时间和结果选择颜色
        String color;
        String prefix;
        if (exception != null) {
            color = ANSI_RED;
            prefix = "❌ SQL ERROR";
        } else if (costTime > SLOW_SQL_THRESHOLD) {
            color = ANSI_YELLOW;
            prefix = "⚠️ SLOW SQL";
        } else {
            color = ANSI_GREEN;
            prefix = "✅ SQL OK";
        }

        System.out.println(color + prefix + sb + ANSI_RESET);
    }

    private String getResultInfo(Object result) {
        if (result == null) {
            return "null";
        }
        if (result instanceof Collection) {
            return "返回 " + ((Collection<?>) result).size() + " 条记录";
        }
        if (result instanceof Integer) {
            return "影响 " + result + " 行";
        }
        return result.getClass().getSimpleName();
    }

    private String buildCompleteSql(Invocation invocation) {
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        Object parameterObj = invocation.getArgs().length > 1 ? invocation.getArgs()[1] : null;

        Configuration configuration = mappedStatement.getConfiguration();
        BoundSql boundSql = mappedStatement.getBoundSql(parameterObj);
        String sqlTemplate = boundSql.getSql().replaceAll("\\s+", " ").trim();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        Object parameterObject = boundSql.getParameterObject();

        if (ObjectUtil.isEmpty(parameterMappings) || parameterObject == null) {
            return sqlTemplate;
        }

        TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
        
        // 单个简单参数
        if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
            return sqlTemplate.replaceFirst("\\?", Matcher.quoteReplacement(getParamValue(parameterObject)));
        }

        // 多个参数
        MetaObject metaObject = configuration.newMetaObject(parameterObject);
        for (ParameterMapping mapping : parameterMappings) {
            String paramName = mapping.getProperty();
            Object paramValue = getParameterValue(boundSql, metaObject, paramName);
            String valueStr = getParamValue(paramValue);
            sqlTemplate = sqlTemplate.replaceFirst("\\?", Matcher.quoteReplacement(valueStr));
        }
        
        return sqlTemplate;
    }

    private Object getParameterValue(BoundSql boundSql, MetaObject metaObject, String paramName) {
        if (boundSql.hasAdditionalParameter(paramName)) {
            return boundSql.getAdditionalParameter(paramName);
        }
        if (metaObject.hasGetter(paramName)) {
            return metaObject.getValue(paramName);
        }
        return null;
    }

    private String getParamValue(Object paramValue) {
        if (paramValue == null) {
            return "NULL";
        }

        if (paramValue instanceof String) {
            // 防止SQL注入显示(仅用于日志，实际执行还是PreparedStatement)
            return "'" + paramValue.toString().replace("'", "''") + "'";
        }

        if (paramValue instanceof Date) {
            return "TO_DATE('" + new SimpleDateFormat(DATE_TIME_FORMATTER.toString()).format((Date) paramValue) + "','" + DatePattern.NORM_DATETIME_PATTERN + "')";
        }

        if (paramValue instanceof LocalDateTime) {
            return "TO_DATE('" + ((LocalDateTime) paramValue).format(DATE_TIME_FORMATTER) + "','" + DatePattern.NORM_DATETIME_PATTERN + "')";
        }

        if (paramValue instanceof LocalDate) {
            return "TO_DATE('" + ((LocalDate) paramValue).format(DATE_FORMATTER) + "','" + DatePattern.NORM_DATE_PATTERN + "')";
        }

        if (paramValue instanceof Boolean) {
            return (Boolean) paramValue ? "1" : "0";
        }

        return paramValue.toString();
    }

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

    @Override
    public void setProperties(Properties properties) {
        // 可以从配置文件读取参数，如慢SQL阈值等
    }
}

2.注册拦截器

import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * SQL 日志打印配置类
 * 只有当配置文件中 mybatis-plus.sql-log.enabled = true 时，这个类才会生效
 */
@Configuration
// 核心控制开关：matchIfMissing = false 表示如果不配置这个属性，默认是不开启的
@ConditionalOnProperty(prefix = "mybatis-plus.sql-log", name = "enabled", havingValue = "true", matchIfMissing = false)
public class MybatisSqlLogConfig {

    @Bean
    public SqlPrintInterceptor sqlPrintInterceptor() {
        return new SqlPrintInterceptor();
    }
}