Mybatis 列印完整的SQL

一只桔子2233發表於2024-04-02
MyBatisSQL

1、

c++ \033輸出設定

2、

package org.jeecg.config.mybatis;

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.parameter.ParameterHandler;
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.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.type.TypeHandlerRegistry;
import org.jeecg.common.constant.JeecgConstant;
import org.jeecg.common.util.CN;
import org.springframework.core.Ordered;

import java.lang.reflect.Field;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.Date;
import java.util.List;
import java.util.Objects;
import java.util.Properties;

/**
 * <p>
 * 自定義SQL外掛,功能如下
 * 1:列印SQL執行時間
 * 2:列印SQL,引數自動設定到SQL中
 * 3:區別慢SQL,SQL執行時間大於5秒的SQL為紅色字型，否則為黃色字型,(執行時間可以自定義)
 * </p>
 */
@Intercepts({
        @Signature(type = StatementHandler.class, method = "update", args = {Statement.class}),
        // @Signature(type = StatementHandler.class, method = "query", args = {MappedStatement.class, Statement.class, Object.class, RowBounds.class, ResultHandler.class}),
        // @Signature(type = StatementHandler.class, method = "batch", args = {Statement.class}),
        // @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class}),
        @Signature(type = StatementHandler.class, method = "query", args = {Statement.class, ResultHandler.class}),

})
@Slf4j
public class MybatisSqlPrintInterceptor implements Interceptor, Ordered {
    private static final Long DEFAULT_TIME_OUT = 5000L;
    private static final ThreadLocal<SimpleDateFormat> SIMPLE_DATE_FORMAT = ThreadLocal.withInitial(() -> new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"));

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        long startTime = System.currentTimeMillis();
        Object proceed = invocation.proceed();
        printSql(startTime, invocation);
        return proceed;
    }

    private void printSql(long startTime, Invocation invocation) {
        try {
            if (JeecgConstant.LogClass.getIsPrintSql()) {
                String sql = formatSql(invocation);
                String method = getMethod(invocation);
                long endTime = System.currentTimeMillis();
                long elapsedTime = endTime - startTime;
                String str = String.format("執行時間：%s ms,呼叫xml方法: %s [執行SQL]:\n%s ", elapsedTime, method, getColorString(sql));
                log.info(JeecgConstant.LogClass.logPre + str);
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }

    private String getColorString(String str) {
        //[c++ \033輸出設定]https://blog.csdn.net/weixin_45469233/article/details/130100885
        return String.format("\033[33;1m%s\033[0m", str);
    }

    @Override
    public void setProperties(Properties properties) {
        System.out.println("外掛配置的資訊：" + CN.toJSONString(properties));
    }

    /**
     * 格式化SQL及其引數
     */
    private String formatSql(Invocation invocation) throws Exception {
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        ParameterHandler parameterHandler = statementHandler.getParameterHandler();
        BoundSql boundSql = statementHandler.getBoundSql();
        String sql = boundSql.getSql();
        if (CN.isEmpty(sql)) {
            return "";
        }

        Class<? extends ParameterHandler> parameterHandlerClass = parameterHandler.getClass();
        Field mappedStatementField = parameterHandlerClass.getDeclaredField("mappedStatement");
        mappedStatementField.setAccessible(true);
        MappedStatement mappedStatement = (MappedStatement) mappedStatementField.get(parameterHandler);

        // 美化sql
        // sql = beautifySql(sql).toLowerCase();

        // 不傳引數的場景，直接把Sql美化一下返回出去
        // Object parameterObject = parameterHandler.getParameterObject();
        // List<ParameterMapping> parameterMappingList = boundSql.getParameterMappings();
        // if (Objects.isNull(parameterObject) || parameterMappingList.isEmpty()) {
        //     return sql;
        // }

        // 定義一個沒有替換過佔位符的sql，用於出異常時返回
        String sqlWithoutReplacePlaceholder = sql;

        try {
            sql = handleCommonParameter(boundSql, mappedStatement);
        } catch (Exception e) {
            System.err.println(JeecgConstant.LogClass.logPre + "[handleCommonParameter]" + e.getMessage());
            // 佔位符替換過程中出現異常，則返回沒有替換過佔位符但是格式美化過的sql
            return sqlWithoutReplacePlaceholder;
        }
        String sql1 = beautifySql(sql);
        return sql1;
    }

    /**
     * 替換SQL中的?,設定sql引數
     */
    private String handleCommonParameter(BoundSql boundSql, MappedStatement mappedStatement) {
        String sql = boundSql.getSql();
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        Configuration configuration = mappedStatement.getConfiguration();
        TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();

        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);
                }
                sql = replacePlaceholder(sql, value);
            }
        }
        return sql;
    }

    /**
     * 根據不同的propertyValue型別,匹配SQL?的型別並替換值
     */
    private String replacePlaceholder(String sql, Object propertyValue) {
        String value;
        if (Objects.nonNull(propertyValue)) {
            if (propertyValue instanceof String) {
                value = "'" + propertyValue + "'";
            } else if (propertyValue instanceof Date) {
                value = "'" + SIMPLE_DATE_FORMAT.get().format(propertyValue) + "'";
            } else if (propertyValue instanceof LocalDate) {
                value = "'" + SIMPLE_DATE_FORMAT.get().format((LocalDate) propertyValue) + "'";
            } else {
                value = propertyValue.toString();
            }
        } else {
            value = "null";
        }
        return sql.replaceFirst("\\?", value);
    }

    /**
     * 根據不同的超時時間列印不同顏色的字型，若超時時間大於預設的超時時間，列印紅色字型，否則列印黃色字型
     */
    private void printColorString(String str, Long timeOut) {
        if (timeOut < DEFAULT_TIME_OUT) {
            log.info(JeecgConstant.LogClass.logPre + "\033[33;4m" + str + "\033[0m");
        } else {
            log.info(JeecgConstant.LogClass.logPre + "\033[33;4m" + str + "\033[0m");
        }
    }

    private String beautifySql(String sql) {
        sql = sql.replaceAll("[\\s\n ]+", " ");
        return sql;
    }

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

    private String getMethod(Invocation invocation) throws Exception {
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        ParameterHandler parameterHandler = statementHandler.getParameterHandler();
        // BoundSql boundSql = statementHandler.getBoundSql();

        Class<? extends ParameterHandler> parameterHandlerClass = parameterHandler.getClass();
        Field mappedStatementField = parameterHandlerClass.getDeclaredField("mappedStatement");
        mappedStatementField.setAccessible(true);
        MappedStatement mappedStatement = (MappedStatement) mappedStatementField.get(parameterHandler);
        return mappedStatement.getId();
    }

    @Override
    public int getOrder() {
        return Ordered.HIGHEST_PRECEDENCE;
    }
}

相關文章