關于Java中攔截mybatis并輸出完整sql語句的方法

更新時間：2023年08月14日 10:41:44 作者：一名陳序員

這篇文章主要介紹了關于Java中攔截mybatis并輸出完整sql語句的方法,假如項目中有很多很多的SQL我們不可能一一的去修改解決。這個時候我們就需要通過mybatis攔截SQL并且最終修改SQL,需要的朋友可以參考下

攔截mybatis

由于公司項目需要，在每次查詢完成之后都要把完整的sql語句打印出來，需要集成輸出完整SQL插件到框架中

單數據源配置

1. 第一步

編寫攔截器，并實現打印sql日志

package com.lz.pdcgpx.wdw.api.interceptor;
import com.sun.deploy.util.StringUtils;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
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.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 org.springframework.util.CollectionUtils;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;
/**
 * @description: SQLInterceptor
 * @author: chh
 * @date: 2022/10/25 9:26
 **/
@Intercepts({
        @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class}),
        @Signature(type = StatementHandler.class, method = "getBoundSql", args = {}),
        @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}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
})
@Component //SpringBoot使用該注解，注入容器
public class SqlInterceptor implements Interceptor {
    private static final Logger log = LoggerFactory.getLogger(SqlInterceptor.class);
    private static final Map<String, Object> MAP = new ConcurrentHashMap<>(2);
    private static final SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    /**
     * 重寫intercept，攔截sql，拼接完整sql語句
     *
     * @param invocation 調用
     * @return Object
     */
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object returnValue;
        long start = System.currentTimeMillis();
        returnValue = invocation.proceed();
        long end = System.currentTimeMillis();
        long time = end - start;
        try {
            final Object[] args = invocation.getArgs();
            //獲取原始的ms
            if (!(args[0] instanceof MappedStatement)) {
                return returnValue;
            }
            MappedStatement ms = (MappedStatement) args[0];
            Object parameter = null;
            //獲取參數，if語句成立，表示sql語句有參數，參數格式是map形式
            if (invocation.getArgs().length > 1) {
                parameter = invocation.getArgs()[1];
            }
            Method method = invocation.getMethod();
            String name = method.getName();
            String commandName = ms.getSqlCommandType().name();
            if (commandName.startsWith("INSERT")) {
                name = name + "=新增";
            } else if (commandName.startsWith("UPDATE")) {
                name = name + "=修改";
            } else if (commandName.startsWith("DELETE")) {
                name = name + "=刪除";
            } else if (commandName.startsWith("SELECT")) {
                name = name + "=查詢";
            }
            // 獲取到節(jié)點的id,即sql語句的id
            String sqlId = ms.getId();
            // BoundSql就是封裝myBatis最終產生的sql類
            BoundSql boundSql = ms.getBoundSql(parameter);
            // 獲取節(jié)點的配置
            Configuration configuration = ms.getConfiguration();
            // 獲取到最終的sql語句
            String sql = getSql(configuration, boundSql, sqlId, time, returnValue, name);
            log.info(sql);
        } catch (Exception e) {
            log.error("攔截sql處理出錯，出錯原因：" + e.getMessage());
            e.printStackTrace();
        }
        return returnValue;
    }
    /**
     * 封裝了一下sql語句，使得結果返回完整xml路徑下的sql語句節(jié)點id + sql語句
     *
     * @param configuration 配置
     * @param boundSql      boundSql
     * @param sqlId         sqlId
     * @param time          執(zhí)行時間
     * @param result        結果
     * @param name          sql操作類型
     * @return String
     */
    public static String getSql(Configuration configuration, BoundSql boundSql, String sqlId, long time, Object result, String name) {
        showSql(configuration, boundSql);
        String message = "[SqlInterceptor] 執(zhí)行 [" + name + "] 時間 [" + formatter.format(System.currentTimeMillis()) + "] sql耗時 [" + (double) time / 1000 + "] s";
        StringBuilder str = new StringBuilder();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        str.append("\n").append("----------------------------begin【SQL Execute Message】--------------------------------\n");
        str.append("【方法】").append(sqlId).append("\n");
        str.append("【sql】").append(MAP.get("sql"));
        str.append("\n");
        str.append("【參數映射】").append(parameterMappings);
        str.append("\n");
        str.append("【參數對象】").append(StringUtils.join((List<String>) MAP.get("parameters"), ", "));
        str.append("\n");
        str.append("【結果】 ");
        if (result != null) {
            if (result instanceof List) {
                str.append("共 ").append(((List) result).size()).append(" 條記錄\n");
            } else if (result instanceof Collection) {
                str.append("共 ").append(((Collection) result).size()).append(" 條記錄\n");
            } else {
                str.append("共 1 條記錄").append("\n");
            }
            //str.append("【結果詳情】").append(result).append("\n");
        } else {
            str.append("【結果】  NULL").append("\n");
        }
        str.append("【執(zhí)行信息】").append(message);
        str.append("\n");
        str.append("----------------------------end【SQL Execute Message】--------------------------------\n");
        return str.toString();
    }
    /**
     * 如果參數是String，則添加單引號，
     * 如果是日期，則轉換為時間格式器并加單引號； 對參數是null和不是null的情況作了處理
     *
     * @param obj 參數
     * @return String
     */
    private static String getParameterValue(Object obj) {
        String value;
        if (obj instanceof String) {
            value = "'" + obj + "'";
        } else if (obj instanceof Date) {
            value = "'" + formatter.format(obj) + "'";
        } else {
            if (obj != null) {
                value = obj.toString();
            } else {
                value = "";
            }
        }
        return value;
    }
    /**
     * 進行？的替換
     *
     * @param configuration 配置
     * @param boundSql      boundSql
     * @return void
     */
    public static void showSql(Configuration configuration, BoundSql boundSql) {
        // 獲取參數
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql
                .getParameterMappings();
        // sql語句中多個空格都用一個空格代替
        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
        List<String> list = new ArrayList<>();
        if (!CollectionUtils.isEmpty(parameterMappings) && parameterObject != null) {
            // 獲取類型處理器注冊器，類型處理器的功能是進行java類型和數據庫類型的轉換
            // 如果根據parameterObject.getClass(）可以找到對應的類型，則替換
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(parameterObject)));
                list.add(parameterObject + "(" + parameterObject.getClass().getSimpleName() + ")");
            } else {
                // MetaObject主要是封裝了originalObject對象，提供了get和set的方法用于獲取和設置originalObject的屬性值,主要支持對JavaBean、Collection、Map三種類型對象的操作
                MetaObject metaObject = configuration.newMetaObject(parameterObject);
                for (ParameterMapping parameterMapping : parameterMappings) {
                    String propertyName = parameterMapping.getProperty();
                    if (metaObject.hasGetter(propertyName)) {
                        Object obj = metaObject.getValue(propertyName);
                        if (Objects.nonNull(obj)) {
                            sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
                            list.add(parameterMapping.getProperty() + "=" + obj + "(" + obj.getClass().getSimpleName() + ")");
                        } else {
                            sql = sql.replaceFirst("\\?", "null");
                            list.add(parameterMapping.getProperty() + "=null");
                        }
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        // 該分支是動態(tài)sql
                        Object obj = boundSql.getAdditionalParameter(propertyName);
                        if (Objects.nonNull(obj)) {
                            sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
                            list.add(parameterMapping.getProperty() + "=" + obj + "(" + obj.getClass().getSimpleName() + ")");
                        } else {
                            sql = sql.replaceFirst("\\?", "null");
                            list.add(parameterMapping.getProperty() + "=null");
                        }
                    } else {
                        sql = sql.replaceFirst("\\?", "缺失");
                        list.add("缺失");
                    }//打印出缺失，提醒該參數缺失并防止錯位
                }
            }
        }
        MAP.put("sql", sql);
        MAP.put("parameters", list);
    }
    @Override
    public Object plugin(Object arg0) {
        return Plugin.wrap(arg0, this);
    }
    @Override
    public void setProperties(Properties properties) {}
}

2.第二步

在SpringBoot啟動類通過bean注入即可

 
    @Bean
    public SqlInterceptor sqlInterceptor (){
        return new SqlInterceptor();
    }

最后的效果

這樣就完事啦

多數據源配置

在集成的過程中，出現了多數據源的情況下，攔截器失效的問題，死活不打印日志，解決方案如下

@Configuration
@MapperScan(basePackages = "com.**.dao", sqlSessionFactoryRef = "sqlSessionFactory1")
public class MybatisDataSourceAutoConfiguration {
    @Primary
    @Bean(name = "sqlSessionFactory1")
    public SqlSessionFactory basicSqlSessionFactory(DruidDataSource basicDataSource) throws Exception {
        MybatisSqlSessionFactoryBean factoryBean = new MybatisSqlSessionFactoryBean();
        factoryBean.setDataSource(basicDataSource);
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapping/*.xml"));
        return factoryBean.getObject();
    }
}

繼續(xù)測試，發(fā)現還是沒打印日志信息：

原來執(zhí)行完sessionFactoryBean.getObject() 后，插件雖然由spring管理，但不會生效查了官方文檔后，發(fā)現需要預先配置好加入的插件，配置如下：

@Primary
@Bean(name = "sqlSessionFactory1")
public SqlSessionFactory basicSqlSessionFactory(@Qualifier("dataSource1") DruidDataSource basicDataSource) throws Exception {
        MybatisSqlSessionFactoryBean factoryBean = new MybatisSqlSessionFactoryBean();
        factoryBean.setDataSource(basicDataSource);
        /* start */
        //配置攔截器插件
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.addInterceptor(new SqlInterceptor());
        factoryBean.setConfiguration(configuration);
        /* end */
        factoryBean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath:mapping/*.xml"));
        return factoryBean.getObject();
}

至此，成功打??！