之前做伙伴匹配系统的时候,在队伍查询哪里遇到了bug,需要显示完整的sql,但是idea上的插件需要付费,在网上搜索之后找到了几种可以免费使用的方法

第一种 使用Mybatis拦截器

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
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.DefaultReflectorFactory;
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.stereotype.Component;

import java.sql.Connection;
import java.text.DateFormat;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Properties;
import java.util.regex.Matcher;

/**
 * 拦截StatementHandler类中参数类型为Statement的 prepare 方法
 * 即拦截 Statement prepare(Connection var1, Integer var2) 方法
 *
 * @author lkx
 */

@Component
@Slf4j
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class MybatisSqlLoggerInterceptor implements Interceptor {


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

        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        //通过MetaObject优雅访问对象的属性，这里是访问statementHandler的属性;：MetaObject是Mybatis提供的一个用于方便、
        //优雅访问对象属性的对象，通过它可以简化代码、不需要try/catch各种reflect异常，同时它支持对JavaBean、Collection、Map三种类型对象的操作。
        MetaObject metaObject = MetaObject
                .forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY,
                        new DefaultReflectorFactory());
        //先拦截到RoutingStatementHandler，里面有个StatementHandler类型的delegate变量，其实现类是BaseStatementHandler，然后就到BaseStatementHandler的成员变量mappedStatement
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
        //id为执行的mapper方法的全路径名，如com.uv.dao.UserMapper.insertUser
        String id = mappedStatement.getId();
        log.info("id ==> " + id);
        //sql语句类型 select、delete、insert、update
        String sqlCommandType = mappedStatement.getSqlCommandType().toString();
        log.info("类型 ==> " + sqlCommandType);

        BoundSql boundSql = statementHandler.getBoundSql();

        // 获取节点的配置
        Configuration configuration = mappedStatement.getConfiguration();
        // 获取到最终的sql语句
        String newsql = getSql(configuration, boundSql, id);
        log.info("拦截的sql ==>: " + newsql);
        long start = System.currentTimeMillis();
        Object returnValue = invocation.proceed();
        long end = System.currentTimeMillis();
        long time = (end - start);
        log.info("sql耗时 ==>: " + time);
        return returnValue;
        //return null;
    }

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

    @Override
    public void setProperties(Properties properties) {
    }


    /**
     * 封装了一下sql语句，
     * 使得结果返回完整xml路径下的sql语句节点id + sql语句
     *
     * @param configuration 配置
     * @param boundSql boundSql
     * @param sqlId sqlId
     * @return 返回处理的sql
     */
    private String getSql(Configuration configuration, BoundSql boundSql, String sqlId) {
        String sql = showSql(configuration, boundSql);
        StringBuilder str = new StringBuilder(100);
        str.append(sqlId);
        str.append(":");
        str.append(sql);
        return str.toString();
    }

    /**
     * 如果参数是String，则添加单引号， 如果是日期，则转换为时间格式器并加单引号；
     * 对参数是null和不是null的情况作了处理<br>
     *
     * @param obj 对象
     * @return 返回转换后的
     */
    private String getParameterValue(Object obj) {
        String value = null;
        if (obj instanceof String) {
            value = "'" + obj.toString() + "'";
        } else if (obj instanceof Date) {
            DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
            value = "'" + formatter.format(new Date()) + "'";
        } else {
            if (obj != null) {
                value = obj.toString();
            } else {
                value = "";
            }

        }
        return value;
    }

    /**
     * 进行？的替换
     *
     * @param configuration 配置
     * @param boundSql 原SQL
     * @return 返回替换后的sql
     */
    public String showSql(Configuration configuration, BoundSql boundSql) {
        // 获取参数
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql
                .getParameterMappings();
        // sql语句中多个空格都用一个空格代替
        String sql = boundSql.getSql().replaceAll("[\s]+", " ");
        if (CollectionUtils.isNotEmpty(parameterMappings) && parameterObject != null) {
            // 获取类型处理器注册器，类型处理器的功能是进行java类型和数据库类型的转换　　　　　　　
            // 如果根据parameterObject.getClass(）可以找到对应的类型，则替换
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                sql = sql.replaceFirst("\?", Matcher.quoteReplacement(getParameterValue(parameterObject)));

            } 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);
                        sql = sql.replaceFirst("\?", Matcher.quoteReplacement(getParameterValue(obj)));
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        // 该分支是动态sql
                        Object obj = boundSql.getAdditionalParameter(propertyName);
                        sql = sql.replaceFirst("\?", Matcher.quoteReplacement(getParameterValue(obj)));

                    } else {
                        //打印出缺失，提醒该参数缺失并防止错位
                        sql = sql.replaceFirst("\?", "缺失");
                    }
                }
            }
        }
        return sql;
    }

}

拦截到的sql

2023-02-20 10:04:59.003 INFO 15092 --- [nio-8080-exec-8] c.l.u.c.MybatisSqlLoggerInterceptor : id ==> com.luo.usercenter.mapper.TeamMapper.updateById

2023-02-20 10:04:59.004 INFO 15092 --- [nio-8080-exec-8] c.l.u.c.MybatisSqlLoggerInterceptor : 类型 ==> UPDATE

2023-02-20 10:04:59.004 INFO 15092 --- [nio-8080-exec-8] c.l.u.c.MybatisSqlLoggerInterceptor : 拦截的sql ==>: com.luo.usercenter.mapper.TeamMapper.updateById:UPDATE team SET name='testName', description='测试', status=0, password='' WHERE id=34 AND isDelete=0

==> Preparing: UPDATE team SET name=?, description=?, status=?, password=? WHERE id=? AND isDelete=0

2023-02-20 10:04:59.004 INFO 15092 --- [nio-8080-exec-8] c.l.u.c.MybatisSqlLoggerInterceptor : sql耗时 ==>: 0

第二种 P6spy

<!-- https://mvnrepository.com/artifact/p6spy/p6spy -->

<dependency>

    <groupId>p6spy</groupId>

    <artifactId>p6spy</artifactId>

    <version>3.9.0</version>

</dependency>

修改配置

数据源
spring:
    datasource:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.p6spy.engine.spy.P6SpyDriver
        username: root
        password: root
        url: jdbc:p6spy:mysql://pig-mysql:3307/pig?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowMultiQueries=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai

添加配置spy.properties

# SQl语句分析插件配置
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
#日志输出到控制台
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# 使用日志系统记录 sql
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
# 设置 p6spy driver 代理
deregisterdrivers=true
# 取消JDBC URL前缀
useprefix=true
# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategories=info,debug,result,commit,resultset
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 实际驱动可多个
#driverlist=org.h2.Driver
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准 2 秒
outagedetectioninterval=2

拦截到的sql

Consume Time：76 ms 2021-07-06 16:50:29
Execute SQL：INSERT INTO sys_dict ( type, description, `system`, remarks ) VALUES ( 'tt', 'tt', '0', 'ttt' )
Consume Time：46 ms 2021-07-06 16:50:30
Execute SQL：INSERT INTO sys_log ( type, title, create_by, remote_addr, user_agent, request_uri, method, params, time, service_id ) VALUES ( '0', '添加字典', 'admin', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.138 Safari/537.36', '/dict', 'POST', '', 3023, 'pig' )
Consume Time：8 ms 2021-07-06 16:50:30
Execute SQL：SELECT COUNT(*) FROM sys_dict WHERE del_flag = '0'
Consume Time：6 ms 2021-07-06 16:50:30
Execute SQL：SELECT id,type,description,create_time,update_time,`system`,remarks,del_flag FROM sys_dict WHERE del_flag='0' LIMIT 20