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