目的

       希望对数据库访问进行监控或控制时，一般通过两张方式进行。一是在数据库服务端增加访问控制，二是在数据库客户端进行访问控制。需要根据业务场景选择更优的方案。

• 当要对某些服务的数据访问sql进行统计时，就不能通过服务端的调整进行。
• 当需要修改或添加sql中的某个参数时，需要调取其他服务活动，也不是适合使用服务端处理。

拦截器处理sql

注解方式

• 自定义注解

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
 
/**
 * 使用SQLInterceptAnnotation注解进行sql拦截
 * */
@Target({ElementType.METHOD,ElementType.PARAMETER})
@Retention(RetentionPolicy.RUNTIME)
public @interface SQLInterceptAnnotation {
    boolean flag() default  true;
}

• 自定义sql拦截器

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.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.ResultHandler;
import org.springframework.context.annotation.Configuration;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Statement;
import java.util.Properties;

/**
 * @description: FwSqlInterceptor
 * Intercepts拦截StatementHandler中的query/batch/update/方法
 * @author: lizz
 * @date: 2022/6/6 10:16
 */
@Intercepts({@Signature(type = StatementHandler.class, method = "query", args = {Statement.class, ResultHandler.class}),
        @Signature(type = StatementHandler.class, method = "update", args = {Statement.class}),
        @Signature(type = StatementHandler.class, method = "batch", args = {Statement.class})})
@Component
@Configuration
@Slf4j
public class FwSqlInterceptor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY,
                SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());
        //先拦截到RoutingStatementHandler，里面有个StatementHandler类型的delegate变量，
        //其实现类是BaseStatementHandler，然后就到BaseStatementHandler的成员变量mappedStatement
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
        //id为执行的mapper方法的全路径名，如com.uv.dao.UserMapper.insertUser
        String id = mappedStatement.getId();
        log.info("mapper method {} ", id);
        //sql语句类型 select、delete、insert、update
        String sqlCommandType = mappedStatement.getSqlCommandType().toString();
        BoundSql boundSql = statementHandler.getBoundSql();
        //获取到原始sql语句
        String sql = boundSql.getSql();

        //获取参数
        Object parameter = statementHandler.getParameterHandler().getParameterObject();
        log.info("{} SQL:{} PARAMS:{} ", sqlCommandType, sql, parameter);
        // 对添加注解sql进行修改
        String mSql = matchSQLInterceptAnnotation(mappedStatement, sql);
        // todo 其他修改 
        //通过反射修改sql语句
        Field field = boundSql.getClass().getDeclaredField("sql");
        field.setAccessible(true);
        field.set(boundSql, mSql);

        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        log.info("FwSqlInterceptor.plugin:{}", target);
        return Plugin.wrap(target, new FwSqlInterceptor());
    }

    @Override
    public void setProperties(Properties properties) {
        log.info("FwSqlInterceptor.setProperties:{}", properties);
    }

    /**
     * 对添加注解的方法sql进行修改
     */
    private String matchSQLInterceptAnnotation(MappedStatement mappedStatement, String sql) throws Throwable {
        String mSql = sql;
        Class<?> classType = Class.forName(mappedStatement.getId().substring(0, mappedStatement.getId().lastIndexOf(".")));
        String mName = mappedStatement.getId().substring(mappedStatement.getId().lastIndexOf(".") + 1, mappedStatement.getId().length());
        for (Method method : classType.getDeclaredMethods()) {
            if (method.isAnnotationPresent(SQLInterceptAnnotation.class) && mName.equals(method.getName())) {
                SQLInterceptAnnotation interceptorAnnotation = method.getAnnotation(SQLInterceptAnnotation.class);
                if (interceptorAnnotation.flag()) {
                    // sql调整 只返回10条数据
                    mSql = sql + " limit 10";
                }
            }
        }
        return mSql;
    }
}

• 使用注解开启sql修改

@Mapper
public interface DqcConfigMapper {
    @Select("select id,name from user ")
    @SQLInterceptAnnotation
    List selectAll();
}