SpringBoot集成MyBatis实现SQL拦截器实战指南

一、SQL拦截器核心原理

1.1 MyBatis执行流程与拦截点

graph TD
    A[Executor] --> B[StatementHandler]
    B --> C[ParameterHandler]
    B --> D[ResultSetHandler]
    
    style A fill:#f9f,stroke:#333
    style B fill:#bbf,stroke:#333
    style C fill:#f96,stroke:#333
    style D fill:#6f9,stroke:#333

MyBatis四大核心组件均可被拦截：

Executor：执行SQL操作的核心接口
StatementHandler：处理SQL语句预编译
ParameterHandler：处理SQL参数
ResultSetHandler：处理结果集映射

1.2 拦截器接口定义

public interface Interceptor {
    Object intercept(Invocation invocation) throws Throwable;
    Object plugin(Object target);
    void setProperties(Properties properties);
}

二、基础拦截器实现

2.1 实现SQL日志拦截器

@Intercepts({
    @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})
})
public class SqlLogInterceptor implements Interceptor {
    
    private static final Logger log = LoggerFactory.getLogger(SqlLogInterceptor.class);

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement ms = (MappedStatement) invocation.getArgs()[0];
        Object parameter = invocation.getArgs()[1];
        
        // 获取SQL命令类型
        String sqlCommandType = ms.getSqlCommandType().toString();
        
        // 获取绑定SQL
        BoundSql boundSql = ms.getBoundSql(parameter);
        String sql = boundSql.getSql();
        
        long start = System.currentTimeMillis();
        Object result = invocation.proceed();
        long timing = System.currentTimeMillis() - start;
        
        log.info("执行 {} SQL [{}] 耗时 {}ms", 
                sqlCommandType, sql, timing);
        
        return result;
    }

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

    @Override
    public void setProperties(Properties properties) {
        // 可读取配置文件参数
    }
}

三、SpringBoot集成配置

3.1 自动配置类

@Configuration
public class MyBatisInterceptorConfig {

    @Bean
    public SqlLogInterceptor sqlLogInterceptor() {
        return new SqlLogInterceptor();
    }
    
    @Bean
    public ConfigurationCustomizer mybatisConfigurationCustomizer() {
        return configuration -> {
            // 注册拦截器
            configuration.addInterceptor(sqlLogInterceptor());
            // 可添加更多拦截器
        };
    }
}

3.2 多拦截器顺序控制

@Bean
public ConfigurationCustomizer mybatisConfigurationCustomizer() {
    return configuration -> {
        // 注意拦截器执行顺序与添加顺序相反
        configuration.addInterceptor(new SlowSqlInterceptor());
        configuration.addInterceptor(new SqlLogInterceptor());
    };
}

四、高级拦截器实战

4.1 慢SQL监控拦截器

@Intercepts(@Signature(type = Executor.class, method = "query", 
        args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}))
public class SlowSqlInterceptor implements Interceptor {
    
    private long threshold = 1000; // 默认1秒
    
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        long start = System.currentTimeMillis();
        Object result = invocation.proceed();
        long timing = System.currentTimeMillis() - start;
        
        if (timing > threshold) {
            MappedStatement ms = (MappedStatement) invocation.getArgs()[0];
            BoundSql boundSql = ms.getBoundSql(invocation.getArgs()[1]);
            warnSlowSql(boundSql.getSql(), timing);
        }
        
        return result;
    }
    
    private void warnSlowSql(String sql, long timing) {
        // 发送告警通知
        String message = String.format("慢SQL警告: 执行耗时 %dms\nSQL: %s", 
                                     timing, sql);
        System.err.println(message);
        // 可集成邮件/短信告警
    }

    @Override
    public void setProperties(Properties properties) {
        String thresholdStr = properties.getProperty("threshold");
        if (thresholdStr != null) {
            this.threshold = Long.parseLong(thresholdStr);
        }
    }
}

4.2 SQL注入防护拦截器

@Intercepts({
    @Signature(type = StatementHandler.class, 
              method = "prepare", 
              args = {Connection.class, Integer.class})
})
public class SqlInjectionInterceptor implements Interceptor {
    
    private static final String[] KEYWORDS = {
        "delete", "drop", "truncate", "exec", "xp_"
    };

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler handler = (StatementHandler) invocation.getTarget();
        BoundSql boundSql = handler.getBoundSql();
        String sql = boundSql.getSql().toLowerCase();
        
        for (String keyword : KEYWORDS) {
            if (sql.contains(keyword)) {
                throw new RuntimeException("检测到危险SQL操作: " + keyword);
            }
        }
        
        return invocation.proceed();
    }
}

五、动态SQL改写实战

5.1 分表策略拦截器

@Intercepts(@Signature(type = StatementHandler.class, 
        method = "prepare", args = {Connection.class, Integer.class}))
public class ShardingInterceptor implements Interceptor {
    
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler handler = (StatementHandler) invocation.getTarget();
        MetaObject metaObject = SystemMetaObject.forObject(handler);
        
        // 获取原始SQL
        BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");
        String originalSql = boundSql.getSql();
        
        // 分表逻辑 - 根据用户ID分表
        if (originalSql.contains("user_table")) {
            Object paramObj = boundSql.getParameterObject();
            Long userId = getUserId(paramObj);
            String newSql = originalSql.replace("user_table", 
                              "user_table_" + (userId % 4));
            
            // 重写SQL
            metaObject.setValue("delegate.boundSql.sql", newSql);
        }
        
        return invocation.proceed();
    }
    
    private Long getUserId(Object parameterObject) {
        // 根据实际情况解析用户ID
        if (parameterObject instanceof Map) {
            return (Long) ((Map<?,?>) parameterObject).get("userId");
        }
        return 0L;
    }
}

5.2 多租户隔离实现

@Intercepts(@Signature(type = StatementHandler.class, 
        method = "prepare", args = {Connection.class, Integer.class}))
public class TenantInterceptor implements Interceptor {
    
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler handler = (StatementHandler) invocation.getTarget();
        BoundSql boundSql = handler.getBoundSql();
        String originalSql = boundSql.getSql();
        
        // 获取当前租户ID
        String tenantId = TenantContext.getCurrentTenant();
        
        // 改写SQL添加租户条件
        if (shouldIntercept(originalSql)) {
            String newSql = addTenantCondition(originalSql, tenantId);
            resetSql(handler, boundSql, newSql);
        }
        
        return invocation.proceed();
    }
    
    private boolean shouldIntercept(String sql) {
        // 排除特定表或操作
        return !sql.contains("tenant_config") 
            && !sql.startsWith("select count(");
    }
    
    private String addTenantCondition(String sql, String tenantId) {
        // 简单实现：在所有查询后添加租户条件
        if (sql.toLowerCase().contains("where")) {
            return sql + " AND tenant_id = '" + tenantId + "'";
        } else {
            return sql + " WHERE tenant_id = '" + tenantId + "'";
        }
    }
    
    private void resetSql(StatementHandler handler, 
                         BoundSql boundSql, String newSql) {
        MetaObject metaObject = SystemMetaObject.forObject(handler);
        metaObject.setValue("delegate.boundSql.sql", newSql);
    }
}

六、性能分析与优化

6.1 监控指标收集

@Intercepts(@Signature(type = Executor.class, method = "update", 
        args = {MappedStatement.class, Object.class}))
public class MetricsInterceptor implements Interceptor {
    
    private MeterRegistry meterRegistry;
    
    public MetricsInterceptor(MeterRegistry meterRegistry) {
        this.meterRegistry = meterRegistry;
    }
    
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement ms = (MappedStatement) invocation.getArgs()[0];
        String statementId = ms.getId();
        
        Timer.Sample sample = Timer.start(meterRegistry);
        try {
            return invocation.proceed();
        } finally {
            sample.stop(meterRegistry.timer("mybatis.sql.execution",
                "statement", statementId,
                "type", ms.getSqlCommandType().name()));
        }
    }
}

6.2 结果集缓存拦截

@Intercepts(@Signature(type = Executor.class, method = "query", 
        args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}))
public class ResultCacheInterceptor implements Interceptor {
    
    private CacheManager cacheManager;
    
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement ms = (MappedStatement) invocation.getArgs()[0];
        Object parameter = invocation.getArgs()[1];
        
        // 生成缓存Key
        String cacheKey = generateCacheKey(ms, parameter);
        
        // 尝试从缓存获取
        Object cached = cacheManager.get(cacheKey);
        if (cached != null) {
            return cached;
        }
        
        // 执行查询并缓存结果
        Object result = invocation.proceed();
        cacheManager.put(cacheKey, result);
        
        return result;
    }
    
    private String generateCacheKey(MappedStatement ms, Object parameter) {
        BoundSql boundSql = ms.getBoundSql(parameter);
        return ms.getId() + ":" + boundSql.getSql() + ":" + 
               Objects.hashCode(boundSql.getParameterObject());
    }
}

七、生产环境最佳实践

7.1 拦截器配置管理

# application.yml
mybatis:
  interceptors:
    - com.example.SqlLogInterceptor
    - com.example.SlowSqlInterceptor:
        threshold: 500
    - com.example.TenantInterceptor

7.2 性能注意事项

拦截器链长度：每个SQL执行会经过所有匹配的拦截器，建议不超过5个
反射开销：频繁使用MetaObject会影响性能，建议缓存反射结果
线程安全：确保拦截器实现是线程安全的

7.3 常见问题排查

// 调试拦截器执行顺序
@Bean
public ConfigurationCustomizer mybatisConfigurationCustomizer() {
    return configuration -> {
        // 打印已注册的拦截器
        configuration.getInterceptors().forEach(interceptor -> {
            System.out.println("已注册拦截器: " + interceptor.getClass());
        });
    };
}

通过本指南，您可以在SpringBoot项目中灵活使用MyBatis拦截器实现SQL监控、改写、防护等高级功能。建议根据实际业务需求选择合适的拦截点，并注意性能影响与线程安全问题。

文中内容均来源于公开资料，受限于信息的时效性和复杂性，可能存在误差或遗漏。我们已尽力确保内容的准确性，但对于因信息变更或错误导致的任何后果，本站不承担任何责任。如需引用本文内容，请注明出处并尊重原作者的版权。

THE END