用mybatis插件抓取最终sql

Z时代
2024-01-10
分类：综合

然后手工一个一个的替换问号占位符后，去MySQL 执行，看sql有木有什么问题。如果sql简单，那比较好说，如果是个复杂sql，手动替换N个问号占位符，这种痛相信大家都经历过。今天介绍的 MybatisFinalSqlPlugin 插件正是解决了这样的痛点，可以直接抓到最终sql。

环境

mybatis 3.4.2

实现思路

最近在走读mybatis 核心源码时，发现mybatis自己有构建最终sql，就想着把代码抠出来用，后面找到一个切入点，就是通过mybatis 插件机制，在插件里面获取 mybatis 的几大核心对象，然后获取到 mybatis 自己构建的最终sql，大功告成，详细代码如下。

MybatisFinalSqlPlugin 插件完整代码

package com.anuo.app.common.datalayer.mybatisplugin;
import com.google.common.base.Stopwatch;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.Statement;
import java.util.Properties;
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class,Integer.class }) })
@Slf4j
public  class MybatisFinalSqlPlugin implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Stopwatch stopwatch = Stopwatch.createStarted();
        if (invocation.getTarget() instanceof StatementHandler) {
            //获取Statement 对象
            Connection conn = (Connection) invocation.getArgs()[0];
            StatementHandler handler = (StatementHandler) invocation.getTarget();
            Statement stmt = handler.prepare(conn, 30);
            handler.parameterize(stmt);
            //获取真实对象
            MetaObject metaObject = SystemMetaObject.forObject(stmt);
            while (metaObject.hasGetter("h")) {
                Object obj = metaObject.getValue("h");
                // 将对象包装成MetaObject对象后就成了真实对象，然后就可以通过反射技术可以操作真实对象的所有属性
                metaObject = SystemMetaObject.forObject(obj);
            }
            //通过反射获取 Statement 对象上的FinalSql
            //todo:后续用 metaObject.getValue() 重构下面的反射代码，提升性能
            Field hField = metaObject.getClass().getDeclaredField("originalObject");
            hField.setAccessible(true);
            Object hObj = hField.get(metaObject);
            Field statementField = hObj.getClass().getDeclaredField("statement");
            statementField.setAccessible(true);
            Object statementObj = statementField.get(hObj);
            Field stmtField = statementObj.getClass().getDeclaredField("stmt");
            stmtField.setAccessible(true);
            Object stmtObj = stmtField.get(statementObj);
            Field statementArrivedField = stmtObj.getClass().getDeclaredField("statement");
            statementArrivedField.setAccessible(true);
            Object statementArrivedFieldObj = statementArrivedField.get(stmtObj);
            String finalSql = statementArrivedFieldObj.toString();
            //去掉不要的字符串
            finalSql = finalSql.substring(finalSql.lastIndexOf(":") + 1, finalSql.length() - 1);
            log.info("最终sql： 
 " + finalSql);
        }
        //做了下性能测试 平均耗时为 1，2毫秒，非常低，不错！
        log.debug("抓取最终sql 耗时：" + stopwatch);
        return invocation.proceed();
    }
    @Override
    public Object plugin(Object target) {
        if (target instanceof StatementHandler) {
            return Plugin.wrap(target, this);
        } else {
            return target;
        }
    }
    @Override
    public void setProperties(Properties properties) {
    }
}

写个单侧看下效果

直接普通的 crud 控制台就会输出最终sql

 @Test
    public void studyMybatisFinalSqlPlugin() {
        UserAccount userAccount = userMapperSpringBoot.getUserById("c2c413f3b2e94ce9b57c20da8f605062");
        log.info("userAccountSpringBoot:【" + JSON.toJSONString(userAccount) + "】");
        for (int i = 0; i < 3; i++) {
            UserFilter userFilter = new UserFilter();
            userFilter.setCurrentPage(1);
            userFilter.setPageSize(15);
            userFilter.setPet_name("蒋奎");
            List<UserAccount> userList = userMapperSpringBoot.getUserList(userFilter);
        }
        UserAccount userAccountUpdate = new UserAccount();
        userAccountUpdate.setId("1");
        userAccountUpdate.setCard_name("jk");
        userMapperSpringBoot.updateUserAccount(userAccountUpdate);
    }