MyBatis-Plus SQL日志

MyBatis-Plus 日志拦截器

EnhancedSqlLogInterceptor -> 拦截日志
SqlLogHandler -> 拓展功能：实现接口自定义处理日志

import com.baomidou.mybatisplus.core.toolkit.SystemClock;
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.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.lang.reflect.Array;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 增强版 SQL 日志打印拦截器（显示完整参数）
 */
@Slf4j
@Component
@Intercepts({
        @Signature(type = StatementHandler.class, method = "query", args = {Statement.class, org.apache.ibatis.session.ResultHandler.class}),
        @Signature(type = StatementHandler.class, method = "update", args = {Statement.class}),
        @Signature(type = StatementHandler.class, method = "batch", args = {Statement.class})
})
public class EnhancedSqlLogInterceptor implements Interceptor {

    // 慢SQL阈值（毫秒）
    private static final long SLOW_SQL_THRESHOLD = 1000;

    // 需要隐藏的参数值（如密码等敏感信息）
    private static final Set<String> HIDE_PARAMS = new HashSet<>(Arrays.asList(
            "password", "pwd", "secret", "token", "creditCard", "cvv"
    ));

    // 日期格式化
    private static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");

    // SQL 中的 ? 占位符正则
    private static final Pattern PLACEHOLDER_PATTERN = Pattern.compile("(?<!\\)\?(?!\?)");

    private static final int MAX_LINE_LENGTH = 120;

    @Autowired(required = false)
    private List<SqlLogHandler> sqlLogHandlers;

    // 使用正则表达式匹配 ? 占位符（确保是独立的问号）

    @Override
    public Object intercept(Invocation invocation) throws Throwable {

        // 记录开始时间
        long startTime = SystemClock.now();

        try {
            // 执行SQL
            return invocation.proceed();
        } finally {
            // 计算执行时间
            long endTime = SystemClock.now();
            long costTime = endTime - startTime;


            //如果开启了debug=true，则打印SQL
            if (log.isDebugEnabled() || costTime > SLOW_SQL_THRESHOLD || (sqlLogHandlers != null && !sqlLogHandlers.isEmpty())) {
                // 获取执行方法名
                String methodName = invocation.getMethod().getName();

                // 获取StatementHandler
                StatementHandler statementHandler = (StatementHandler) invocation.getTarget();

                // 获取BoundSql
                BoundSql boundSql = statementHandler.getBoundSql();

                // 获取原始SQL（带 ? 占位符）
                String sqlWithPlaceholders = boundSql.getSql().trim();

                // 获取完整SQL（带实际参数值）
                String completeSql = getCompleteSql(statementHandler, boundSql, sqlWithPlaceholders);
                // 构建日志信息
                String logMsg = buildLogMessage(methodName, completeSql, costTime);

                if(costTime > SLOW_SQL_THRESHOLD){
                    log.warn(logMsg);
                }else {
                    if(log.isDebugEnabled()){
                        log.debug(logMsg);
                    }
                }
                if(sqlLogHandlers != null && !sqlLogHandlers.isEmpty()){
                    for (SqlLogHandler sqlLogHandler : sqlLogHandlers) {
                        sqlLogHandler.handle(methodName, completeSql, costTime);
                    }
                }
            }
        }
    }

    /**
     * 获取完整SQL（替换 ? 为实际参数值）
     */
    private String getCompleteSql(StatementHandler statementHandler, BoundSql boundSql, String sqlWithPlaceholders) {
        try {
            // 获取参数映射
            List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
            if (parameterMappings == null || parameterMappings.isEmpty()) {
                return sqlWithPlaceholders;
            }

            // 获取配置信息
            MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
            MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
            Configuration configuration = mappedStatement.getConfiguration();
//            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();

            // 替换占位符
//            String completeSql = sqlWithPlaceholders;
            Object parameterObject = boundSql.getParameterObject();
            List<Object> paramValues = getParameterValues(boundSql, configuration, parameterObject);

            Matcher matcher = PLACEHOLDER_PATTERN.matcher(sqlWithPlaceholders);
            StringBuffer sb = new StringBuffer();
            int paramIndex = 0;
            while (matcher.find() && paramIndex < paramValues.size()) {
                String replacement = formatParameter(paramValues.get(paramIndex++));
                matcher.appendReplacement(sb, Matcher.quoteReplacement(replacement));
            }
            matcher.appendTail(sb);
            return sb.toString();

//            for (Object value : paramValues) {
//                int index = completeSql.indexOf('?');
//                if (index == -1) break;
//
//                String replacement = formatParameter(value);
//                completeSql = completeSql.substring(0, index) + replacement + completeSql.substring(index + 1);
//            }
//
//            return completeSql;
        } catch (Exception e) {
            log.error("获取完整SQL失败，将返回原始SQL", e);
            return sqlWithPlaceholders;
        }
    }

    /**
     * 获取参数值列表
     */
    private List<Object> getParameterValues(BoundSql boundSql, Configuration configuration, Object parameterObject) {
        List<Object> paramValues = new ArrayList<>();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();

        if (parameterMappings != null) {
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            MetaObject metaObject = parameterObject == null ? null :
                    configuration.newMetaObject(parameterObject);

            for (ParameterMapping parameterMapping : parameterMappings) {
                String propertyName = parameterMapping.getProperty();

                // 处理敏感参数
                if (isSensitiveParam(propertyName)) {
                    paramValues.add("******");
                    continue;
                }

                if (metaObject != null && typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                    // 基本类型参数
                    paramValues.add(parameterObject);
                } else if (boundSql.hasAdditionalParameter(propertyName)) {
                    // 动态参数
                    Object value = boundSql.getAdditionalParameter(propertyName);
                    paramValues.add(value);
                } else if (parameterObject == null) {
                    paramValues.add(null);
                } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                    paramValues.add(parameterObject);
                } else if (metaObject != null) {
                    // 对象属性
                    Object value = metaObject.getValue(propertyName);
                    paramValues.add(value);
                } else {
                    paramValues.add(null);
                }
            }
        }
        return paramValues;
    }

    /**
     * 格式化参数值
     */
    private String formatParameter(Object param) {
        if (param == null) {
            return "NULL";
        }

        if (param instanceof String) {
            return "'" + param + "'";
        }

        if (param instanceof Date) {
            return "'" + DATE_FORMAT.format((Date) param) + "'";
        }

        if (param instanceof Number) {
            return param.toString();
        }

        if (param instanceof Boolean) {
            return Boolean.TRUE.equals(param) ? "1" : "0";
        }

        if (param instanceof byte[]) {
            return "[BINARY]";
        }

        if (param instanceof Collection) {
            return formatCollection((Collection<?>) param);
        }

        if (param.getClass().isArray()) {
            return formatArray(param);
        }

        return "'" + param.toString() + "'";
    }

    /**
     * 格式化集合参数
     */
    private String formatCollection(Collection<?> collection) {
        StringBuilder sb = new StringBuilder();
        for (Object item : collection) {
            if (sb.length() > 0) sb.append(",");
            sb.append(formatParameter(item));
        }
        return sb.toString();
    }

    /**
     * 格式化数组参数
     */
    private String formatArray(Object array) {
        StringBuilder sb = new StringBuilder();
        int length = Array.getLength(array);
        for (int i = 0; i < length; i++) {
            if (sb.length() > 0) sb.append(",");
            sb.append(formatParameter(Array.get(array, i)));
        }
        return sb.toString();
    }

    /**
     * 判断是否是敏感参数
     */
    private boolean isSensitiveParam(String paramName) {
        if (paramName == null) return false;

        String lowerParamName = paramName.toLowerCase();
        return HIDE_PARAMS.stream()
                .anyMatch(hideParam -> lowerParamName.contains(hideParam.toLowerCase()));
    }

    /**
     * 构建日志消息
     */
    private String buildLogMessage(String method, String sql, long costTime) {
        return String.format("\n" +
                        "┌───────────────────────────────────────────────────────────────────────────────\n" +
                        "│ SQL 方法: %s\n" +
                        "│ 执行时间: %d ms\n" +
                        "│ 完整 SQL: \n" +
                        "%s" +
                        "└───────────────────────────────────────────────────────────────────────────────",
                method, costTime, formatSqlForDisplay(sql)).replace("│        \n","");
    }

    /**
     * 优化版SQL格式化显示（解决换行问题）
     */
    private String formatSqlForDisplay(String sql) {
        // 1. 对长SQL进行智能换行
        List<String> formattedLines = new ArrayList<>();
        StringBuilder currentLine = new StringBuilder();

        // 按空格分割SQL单词
        String[] tokens = sql.split("\s+");

        for (String token : tokens) {
            // 检查添加该单词后是否超过最大行长度
            if (currentLine.length() + token.length() + 1 > MAX_LINE_LENGTH) {
                // 保存当前行并开始新行
                formattedLines.add(currentLine.toString());
                currentLine = new StringBuilder();
            }

            // 添加单词到当前行
            if (currentLine.length() > 0) {
                currentLine.append(" ");
            }
            currentLine.append(token);

            // 特殊处理：遇到特定关键字后强制换行
            if (isKeyword(token)) {
                formattedLines.add(currentLine.toString());
                currentLine = new StringBuilder();
            }
        }

        // 添加最后一行
        if (currentLine.length() > 0) {
            formattedLines.add(currentLine.toString());
        }

        // 2. 为每行添加前缀
        return formattedLines.stream()
                .map(line -> "│        " + line)
                .collect(Collectors.joining("\n")) + "\n";
    }

    /**
     * 判断是否是SQL关键字（需要换行的）
     */
    private boolean isKeyword(String token) {
        if (token == null || token.isEmpty()) {
            return false;
        }

        String upperToken = token.toUpperCase();
        return upperToken.equals("SELECT") ||
                upperToken.equals("FROM") ||
                upperToken.equals("WHERE") ||
                upperToken.equals("JOIN") ||
                upperToken.equals("INNER") ||
                upperToken.equals("LEFT") ||
                upperToken.equals("RIGHT") ||
                upperToken.equals("GROUP") ||
                upperToken.equals("ORDER") ||
                upperToken.equals("HAVING") ||
                upperToken.equals("LIMIT") ||
                upperToken.equals("OFFSET") ||
                upperToken.equals("UNION") ||
                upperToken.equals("VALUES");
    }

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

    @Override
    public void setProperties(Properties properties) {
        // 可配置属性
    }
}

SqlLogHandler 接口

public interface SqlLogHandler {

    void handle(String methodName, String completeSql, long costTime);
}

SqlLogHandler 实现类demo

import org.slf4j.Logger;
import org.springframework.stereotype.Component;


@Component
public class DBLogHandler implements SqlLogHandler {

    private static final Logger logger = org.slf4j.LoggerFactory.getLogger(DBLogHandler.class);

    @Override
    public void handle(String methodName, String completeSql, long costTime) {
        logger.info("当前操作用户 ： {}", SecurityUtils.getCurrentUserId());
        logger.info("DBLogHandler{}: {} costTime: {}", methodName, completeSql, costTime);
        AsyncExecutor.execute(() -> logger.info("异步执行器DBLogHandler{}: {} costTime: {}", methodName, completeSql, costTime));
    }
}