通过mybatis的拦截器对SQL进行打标

1、背景

在我们开发的过程中，一般需要编写各种SQL语句，万一生产环境出现了慢查询，那么我们如何快速定位到底是程序中的那个SQL出现的问题呢？

2、解决方案

如果我们的数据访问层使用的是mybatis的话，那么我们可以通过mybatis提供的拦截器拦截系统中的SQL，然后将 mapper的命名空间和id追加到原始SQL的末尾，当作一个注释，这样不就可以实现吗？ 类似效果如下：

select * from customer where phone = 'aaaaa';/**com.huan.study.mybatis.mappers.CustomerMapper.findCustomer*/

3、核心实现步骤

1、拦截器拦截SQL进行打标

package com.huan.study.mybatis.plugin;import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;import java.sql.Connection;/*** 在原始的Sql语句后面追加 sql id,方面知道当前查询语句是那个mapper文件中的** @author huan* @date 2025/3/11 - 00:30*/
@Slf4j
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})}
)
public class PrintSqlIdInterceptor implements Interceptor {@Overridepublic Object intercept(Invocation invocation) throws Throwable {StatementHandler statementHandler = (StatementHandler) invocation.getTarget();MetaObject metaStatementHandler = MetaObject.forObject(statementHandler,SystemMetaObject.DEFAULT_OBJECT_FACTORY,SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY,SystemMetaObject.NULL_META_OBJECT.getReflectorFactory());BoundSql boundSql = statementHandler.getBoundSql();MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");String id = mappedStatement.getId();log.info("sql语句的id : {}", id);String sql = boundSql.getSql();if (!sql.endsWith(";")) {sql += ";";}sql = sql + "/**" + id + "*/";metaStatementHandler.setValue("delegate.boundSql.sql", sql);return invocation.proceed();}@Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this);}
}

2、配置插件

在mybatis-config.xml中进行插件的配置

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration><plugins><plugin interceptor="com.huan.study.mybatis.plugin.PrintSqlIdInterceptor"/></plugins></configuration>

4、实现效果如下

select * from customer where phone = 'aaaaa';/**com.huan.study.mybatis.mappers.CustomerMapper.findCustomer*/
insert into customer(phone,address) values ('12345','湖北');/**com.huan.study.mybatis.mappers.CustomerMapper.addCustomer*/

可以看到我们对每个SQL都进行了打标，方便SQL的追踪

5、完整代码

https://gitee.com/huan1993/spring-cloud-parent/tree/master/mybatis/mybatis-sql-marking