完善SQL日志输出实现

liurunyu

2024-12-13 8c58db9a4b3ec49eebfdaf642b0298a482cd987a

完善SQL日志输出实现

 pipIrr-platform/pipIrr-common/src/main/java/com/dy/common/mybatis/PrintExceptionSqlInterceptor.java

@@ -1,27 +1,20 @@
package com.dy.common.mybatis;

import java.lang.reflect.InvocationTargetException;
import java.text.DateFormat;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Properties;

import lombok.extern.slf4j.Slf4j;
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.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
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;

/**
 * 拦截执行SQL发生异常的场景，并将执行错误，执行SQL和参数打印出来
@@ -53,11 +46,11 @@
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        // 获取执行方法的MappedStatement参数
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        Object parameter = null;
        if (invocation.getArgs().length > 1) {
            parameter = invocation.getArgs()[1];
        }
        Object[] args = invocation.getArgs();
        if(args != null && args.length > 1){
            MappedStatement mappedStatement = (MappedStatement) args[0];
            Object parameter = args[1];

        String sqlId = mappedStatement.getId();
        BoundSql boundSql = mappedStatement.getBoundSql(parameter);
        Configuration configuration = mappedStatement.getConfiguration();
@@ -68,8 +61,8 @@
            // 输出SQL异常信息
            log.error("SQL ErrorException:", e);
            log.info("SQL Parameters: {}", boundSql.getParameterObject());
            log.info("Execute SqlId: {}", sqlId);
            log.info("Completely Execute SQL: {}", getFullSql(configuration, boundSql));
                log.info("SQL Id: {}", sqlId);
                log.info("SQL: {}", PrintSqlHelp.getFullSql(configuration, boundSql));
            // 根据源异常类型进行返回
            if (e instanceof InvocationTargetException) {
                throw new InvocationTargetException(e);
@@ -80,6 +73,9 @@
            }
        }
        return response;
        }else{
            return invocation.proceed();
        }
    }

    /**
@@ -106,105 +102,5 @@
    public void setProperties(Properties properties) {
    }


    /**
     * 转义正则特殊字符 （$()*+.[]?\^{}
     * \\需要第一个替换，否则replace方法替换时会有逻辑bug
     */
    private static String makeQueryStringAllRegExp(String str) {
        if (str != null && !"".equals(str)) {
            return str.replace("\\", "\\\\")
                    .replace("*", "\\*")
                    .replace("+", "\\+")
                    .replace("|", "\\|")
                    .replace("{", "\\{")
                    .replace("}", "\\}")
                    .replace("(", "\\(")
                    .replace(")", "\\)")
                    .replace("^", "\\^")
                    .replace("$", "\\$")
                    .replace("[", "\\[")
                    .replace("]", "\\]")
                    .replace("?", "\\?")
                    .replace(",", "\\,")
                    .replace(".", "\\.")
                    .replace("&", "\\&");
        }
        return str;
    }

    /**
     * 获取参数对应的string值
     *
     * @param obj 参数对应的值
     * @return string
     */
    private static String getParameterValue(Object obj) {
        String value;
        if (obj instanceof String) {
            value = "'" + obj + "'";
        } else if (obj instanceof Date) {
            DateFormat formatter =
                    DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
            value = "'" + formatter.format(obj) + "'";
        } else {
            if (obj != null) {
                value = obj.toString();
            } else {
                value = "";
            }
        }
        // 对特殊字符进行转义，方便之后处理替换
        return value != null ? makeQueryStringAllRegExp(value) : "";
    }

    /**
     * 获取完整的执行SQL
     */
    public static String getFullSql(Configuration configuration, BoundSql boundSql) {
        try {
            return parseAndExtractFullSql(configuration, boundSql);
        } catch (Exception e) {
            // 如果解析失败返回原始SQL
            return boundSql.getSql();
        }
    }

    /**
     * 组装完整的sql语句并把对应的参数都代入到sql语句里面
     *
     * @param configuration Configuration
     * @param boundSql      BoundSql
     * @return sql完整语句
     */
    private static String parseAndExtractFullSql(Configuration configuration, BoundSql boundSql) {
        // 获取mapper里面方法上的参数
        Object sqlParameter = boundSql.getParameterObject();
        // sql语句里面需要的参数
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        // sql原始语句(?还没有替换成我们具体的参数)
        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
        if (!parameterMappings.isEmpty() && sqlParameter != null) {
            // sql语句里面的?替换成真实的参数
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            if (typeHandlerRegistry.hasTypeHandler(sqlParameter.getClass())) {
                sql = sql.replaceFirst("\\?", getParameterValue(sqlParameter));
            } else {
                MetaObject metaObject = configuration.newMetaObject(sqlParameter);
                for (ParameterMapping parameterMapping : parameterMappings) {
                    // 按顺序把?替换成对应的值
                    String propertyName = parameterMapping.getProperty();
                    if (metaObject.hasGetter(propertyName)) {
                        Object obj = metaObject.getValue(propertyName);
                        sql = sql.replaceFirst("\\?", getParameterValue(obj));
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        Object obj = boundSql.getAdditionalParameter(propertyName);
                        sql = sql.replaceFirst("\\?", getParameterValue(obj));
                    }
                }
            }
        }
        return sql;
    }

}

 pipIrr-platform/pipIrr-common/src/main/java/com/dy/common/mybatis/PrintSqlHelp.java

New file
@@ -0,0 +1,123 @@
package com.dy.common.mybatis;

import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandlerRegistry;

import java.text.DateFormat;
import java.util.Date;
import java.util.List;
import java.util.Locale;

/**
 * @Author: liurunyu
 * @Date: 2024/12/13 10:39
 * @Description
 */
public class PrintSqlHelp {

    /**
     * 获取完整的执行SQL
     */
    public static String getFullSql(Configuration configuration, BoundSql boundSql) {
        try {
            return parseAndExtractFullSql(configuration, boundSql);
        } catch (Exception e) {
            // 如果解析失败返回原始SQL
            return boundSql.getSql();
        }
    }

    /**
     * 组装完整的sql语句并把对应的参数都代入到sql语句里面
     *
     * @param configuration Configuration
     * @param boundSql      BoundSql
     * @return sql完整语句
     */
    private static String parseAndExtractFullSql(Configuration configuration, BoundSql boundSql) {
        // 获取mapper里面方法上的参数
        Object sqlParameter = boundSql.getParameterObject();
        // sql语句里面需要的参数
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        // sql原始语句(?还没有替换成我们具体的参数)
        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
        if (!parameterMappings.isEmpty() && sqlParameter != null) {
            // sql语句里面的?替换成真实的参数
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            if (typeHandlerRegistry.hasTypeHandler(sqlParameter.getClass())) {
                sql = sql.replaceFirst("\\?", getParameterValue(sqlParameter));
            } else {
                MetaObject metaObject = configuration.newMetaObject(sqlParameter);
                for (ParameterMapping parameterMapping : parameterMappings) {
                    // 按顺序把?替换成对应的值
                    String propertyName = parameterMapping.getProperty();
                    if (metaObject.hasGetter(propertyName)) {
                        Object obj = metaObject.getValue(propertyName);
                        sql = sql.replaceFirst("\\?", getParameterValue(obj));
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        Object obj = boundSql.getAdditionalParameter(propertyName);
                        sql = sql.replaceFirst("\\?", getParameterValue(obj));
                    }
                }
            }
        }
        return sql;
    }


    /**
     * 获取参数对应的string值
     *
     * @param obj 参数对应的值
     * @return string
     */
    private static String getParameterValue(Object obj) {
        String value;
        if (obj instanceof String) {
            value = "'" + obj + "'";
        } else if (obj instanceof Date) {
            DateFormat formatter =
                    DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
            value = "'" + formatter.format(obj) + "'";
        } else {
            if (obj != null) {
                value = obj.toString();
            } else {
                value = "";
            }
        }
        // 对特殊字符进行转义，方便之后处理替换
        return value != null ? makeQueryStringAllRegExp(value) : "";
    }



    /**
     * 转义正则特殊字符 （$()*+.[]?\^{}
     * \\需要第一个替换，否则replace方法替换时会有逻辑bug
     */
    private static String makeQueryStringAllRegExp(String str) {
        if (str != null && !"".equals(str)) {
            return str.replace("\\", "\\\\")
                    .replace("*", "\\*")
                    .replace("+", "\\+")
                    .replace("|", "\\|")
                    .replace("{", "\\{")
                    .replace("}", "\\}")
                    .replace("(", "\\(")
                    .replace(")", "\\)")
                    .replace("^", "\\^")
                    .replace("$", "\\$")
                    .replace("[", "\\[")
                    .replace("]", "\\]")
                    .replace("?", "\\?")
                    .replace(",", "\\,")
                    .replace(".", "\\.")
                    .replace("&", "\\&");
        }
        return str;
    }
}

 pipIrr-platform/pipIrr-common/src/main/java/com/dy/common/mybatis/PrintSqlInterceptor.java

@@ -8,6 +8,7 @@
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

@@ -49,13 +50,20 @@
        if (args != null && args.length > 1) {
            MappedStatement mappedStatement = (MappedStatement) args[0];
            Object parameter = args[1];
            BoundSql boundSql = mappedStatement.getBoundSql(parameter);

            String sql = boundSql.getSql();
            log.info("\n\nSQL：\n" + sql + "\n");
            //System.out.println("SQL语句：" + sql);
            //BoundSql boundSql = mappedStatement.getBoundSql(parameter);
            //String sql = boundSql.getSql();
            //log.info("\n\nSQL：\n" + sql + "\n");

            String sqlId = mappedStatement.getId();
            BoundSql boundSql = mappedStatement.getBoundSql(parameter);
            Configuration configuration = mappedStatement.getConfiguration();
            log.info("SQL Parameters: {}", boundSql.getParameterObject());
            log.info("SQL Id: {}", sqlId);
            log.info("SQL: {}", PrintSqlHelp.getFullSql(configuration, boundSql));
        }

        return invocation.proceed();
    }

}