拦截mybatis并输出完整sql语句详细步骤

一名陈序员

已于 2022-11-25 13:45:38 修改

阅读量2.9k

点赞数 3

文章标签： mybatis sql java

于 2022-11-25 13:44:19 首次发布

本文链接：https://blog.csdn.net/m0_56864757/article/details/128036473

版权

由于公司项目需要，在每次查询完成之后都要把完整的sql语句打印出来，需要集成输出完整SQL插件到框架中

【单数据源配置】

第一步：编写拦截器，并实现打印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 + "=查询";
            }
            // 获取到节点的id,即sql语句的id
            String sqlId = ms.getId();
            // BoundSql就是封装myBatis最终产生的sql类
            BoundSql boundSql = ms.getBoundSql(parameter);
            // 获取节点的配置
            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语句节点id + sql语句
     *
     * @param configuration 配置
     * @param boundSql      boundSql
     * @param sqlId         sqlId
     * @param time          执行时间
     * @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] 执行 [" + 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("【执行信息】").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)) {
                        // 该分支是动态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) {}
}

第二步在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();
}
}

继续测试，发现还是没打印日志信息：

原来执行完sessionFactoryBean.getObject() 后，插件虽然由spring管理，但不会生效
查了官方文档后，发现需要预先配置好加入的插件，配置如下：

@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();
}

至此，成功打印！