SQL拦截（二）InnerInterceptor

一、介绍

1、简介

InnerInterceptor 接口是 MyBatis-Plus 提供的一个拦截器接口，用于实现一些常用的 SQL 处理逻辑。

二、API

InnerInterceptor 接口继承自 MyBatis 的 Interceptor 接口，并添加了一些新的方法，用于处理 MyBatis-Plus 的特定功能。

java">package com.baomidou.mybatisplus.extension.plugins.inner;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

public interface InnerInterceptor {
    default boolean willDoQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        return true;
    }

    default void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
    }

    default boolean willDoUpdate(Executor executor, MappedStatement ms, Object parameter) throws SQLException {
        return true;
    }

    default void beforeUpdate(Executor executor, MappedStatement ms, Object parameter) throws SQLException {
    }

    default void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {
    }

    default void beforeGetBoundSql(StatementHandler sh) {
    }

    default void setProperties(Properties properties) {
    }
}

willDoQuery: 当执行查询操作时，MyBatis 会调用该方法，判断是否需要执行查询操作。默认返回true，表示继续执行查询操作，如果需要阻止查询操作，则可以在实现该方法时返回false。
beforeQuery: 在执行查询操作之前，MyBatis 会调用该方法。通过实现该方法，可以在查询之前进行一些必要的操作，例如设置数据范围、修改 SQL 等。
willDoUpdate: 当执行更新操作时，MyBatis 会调用该方法，判断是否需要执行更新操作。默认返回true，表示继续执行更新操作，如果需要阻止更新操作，则可以在实现该方法时返回false。
beforeUpdate: 在执行更新操作之前，MyBatis 会调用该方法。通过实现该方法，可以在更新之前进行一些必要的操作，例如设置更新时间、加密数据等。
beforePrepare: 在执行 SQL 之前，MyBatis 会调用该方法。通过实现该方法，可以在 SQL 执行之前进行一些必要的操作，例如设置事务隔离级别、设置查询超时时间等。
beforeGetBoundSql: 在获取 BoundSql 对象之前，MyBatis 会调用该方法。通过实现该方法，可以在获取 BoundSql 对象之前进行一些必要的操作，例如设置参数、修改 SQL 等。
setProperties: 设置拦截器属性。该方法在创建拦截器实例时调用，用于设置拦截器的属性。
pluginExecutor: 在创建 Executor 对象时调用，可以在这里对 Executor 对象进行包装或其他处理。

三、使用方法

1、自定义拦截类

implements Interceptor, InnerInterceptor 

（1）类头需要有@Intercepts注解；

（2）需要重写setProperties方法（方法体为空即可），否则会报错：

inherits unrelated defaults for setProperties(Properties) from types org.apache.ibatis.plugin.Interceptor and com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor

2、注册

和spring的拦截器org.springframework.web.servlet.HandlerInterceptor 需要注册一样，mybatis的拦截器也需要注册

java">import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.BlockAttackInnerInterceptor;
import com.pluscache.demo.interceptor.SqlExplainInterceptor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MybatisPlusConfig {

    @Autowired
    private SqlExplainInterceptor sqlExplainInterceptor;

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(sqlExplainInterceptor);
        //interceptor.addInnerInterceptor(xxx);
        return interceptor;
    }
}

四、demo

1、执行explain打印结果

@Data
public class ExplainResultVo {
    private String id;
    private String selectType;
    private String table;
    private String partitions;
    private String type;
    private String possibleKeys;
    private String key;
    private String keyLen;
    private String ref;
    private String rows;
    private String filtered;
    private String extra;
}

import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import com.pluscache.demo.dto.ExplainResultVo;
import io.micrometer.common.util.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Properties;
import java.util.regex.Matcher;

@Slf4j
@Component
@Intercepts({
        @Signature(type = Executor.class, method = "query",
                args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
        @Signature(type = Executor.class, method = "query",
                args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class})
})
public class SqlExplainInterceptor implements Interceptor, InnerInterceptor {

    @Override
    public void setProperties(Properties properties) {
        //无需改动
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        log.info("进入sql分析");
        try {
            Object target = invocation.getTarget();
            Object[] args = invocation.getArgs();
            if (target instanceof Executor) {
                final Executor executor = (Executor) target;
                Object parameter = args[1];
                log.info("参数,executor={},parameter={}", executor, parameter);
                boolean isUpdate = args.length == 2;
                MappedStatement ms = (MappedStatement) args[0];
                if (!isUpdate && ms.getSqlCommandType() == SqlCommandType.SELECT){
                    BoundSql boundSql;
                    if (args.length == 4) {
                        boundSql = ms.getBoundSql(parameter);
                    } else {
                        boundSql = (BoundSql) args[5];
                    }
                    String sql = getSql(boundSql, ms);
                    log.info("原来 sql 为 {}", sql);
                    this.handleExplain(executor, sql);
                    return invocation.proceed();
                }
            }
            return invocation.proceed();
        }catch (Exception e){
            return invocation.proceed();
        }
    }

    private String getSql(BoundSql boundSql, MappedStatement ms) {
        String sql = boundSql.getSql();
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        if (!CollectionUtils.isEmpty(parameterMappings) && parameterObject != null) {
            for (ParameterMapping parameterMapping : parameterMappings) {
                if (parameterMapping.getMode() != ParameterMode.OUT) {
                    Object value;
                    String propertyName = parameterMapping.getProperty();
                    if (boundSql.hasAdditionalParameter(propertyName)) {
                        value = boundSql.getAdditionalParameter(propertyName);
                    } else if (ms.getConfiguration().getTypeHandlerRegistry().hasTypeHandler(parameterObject.getClass())) {
                        value = parameterObject;
                    } else {
                        MetaObject metaObject = ms.getConfiguration().newMetaObject(parameterObject);
                        value = metaObject.getValue(propertyName);
                    }
                    sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameter(value)));
                }
            }
        }
        return sql;
    }

    public String getParameter(Object parameter) {
        if (parameter instanceof String) {
            return "'" + parameter + "'";
        }
        return parameter.toString();
    }

    private void handleExplain(Executor executor, String sql) throws SQLException {
        Statement stmt = executor.getTransaction().getConnection().createStatement();
        stmt.execute("EXPLAIN " + sql + " ;");
        ResultSet rs = stmt.getResultSet();
        while (rs.next()) {
            ExplainResultVo explainResultVo = new ExplainResultVo();
            explainResultVo.setId(rs.getString("id"));
            explainResultVo.setSelectType(rs.getString("select_type"));
            explainResultVo.setTable(rs.getString("table"));
            explainResultVo.setPartitions(rs.getString("partitions"));
            explainResultVo.setType(rs.getString("type"));
            explainResultVo.setPossibleKeys(rs.getString("possible_keys"));
            explainResultVo.setKey(rs.getString("key"));
            explainResultVo.setKeyLen(rs.getString("key_len"));
            explainResultVo.setRef(rs.getString("ref"));
            String rows = rs.getString("rows");
            explainResultVo.setRows(rows);
            explainResultVo.setFiltered(rs.getString("filtered"));
            explainResultVo.setExtra(rs.getString("Extra"));
            boolean isSimple = "SIMPLE".equals(rs.getString("select_type"));
            int rowsInt = 0;
            if (StringUtils.isNotBlank(rows)) {
                try {
                    rowsInt = Integer.parseInt(rows);
                } catch (Exception e) {
                    rowsInt = 0;
                }
            }
            log.info("explain语句: {}", explainResultVo);
        }
    }
}