自定义mybatis插件之全局数据过滤

目录

• 一、介绍
• 二、实现
• 三、效果
• 四、源码

一、介绍

通过开发mybatis的插件来实现对全局的sql查询语句进行拦截，并新增全局的过滤条件做到无感知的数据过滤，比如全局过滤某个租户的数据。

二、实现

• 实现思路

  1、通过mybatis的拦截器拦截所有查询的sql

  2、使用Druid里面的工具类解析sql结构树

  3、通过各种递归遍历sql结构树获取所有表名和别名，包含子查询、with语句等等

  4、根据表名的黑名单和白名单判断是否需要增加过滤条件sql

  5、替换成新的sql

  6、执行sql获取新的结果集

• 创建mybatis-plugin类GlobalConditionFilterPlugin.java

  @Intercepts({
          @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
  })
  public class GlobalConditionFilterPlugin implements Interceptor {
      private static final Logger log = LoggerFactory.getLogger(GlobalConditionFilterPlugin.class);
      private GlobalConditionFilterHandler globalConditionFilterHandler;
  
      public GlobalConditionFilterPlugin(GlobalConditionFilterHandler globalConditionFilterHandler) {
          this.globalConditionFilterHandler = globalConditionFilterHandler;
      }
  
      @Override
      public Object intercept(Invocation invocation) throws Throwable {
          Object[] args = invocation.getArgs();
  
          MappedStatement mappedStatement = (MappedStatement) args[0];
          Object parameter = args[1];
          BoundSql originBoundSql = mappedStatement.getBoundSql(parameter);
  
          // 获取原始sql
          String sql = originBoundSql.getSql();
          log.info("原始sql：\n{}", sql);
  
          // 添加过滤条件转换新的sql
          DruidSql druidSql = new DruidSql(sql, JdbcConstants.MYSQL);
          druidSql.appendFilterCondition(
                  new WhereConditionAppender(
                          globalConditionFilterHandler.getColumn(),
                          globalConditionFilterHandler.getColumnValue(),
                          (tableName) -> globalConditionFilterHandler.support(tableName)
                  )
          );
          sql = druidSql.getSql();
          log.info("转换sql：\n{}", sql);
  
          MappedStatement newMappedStatement = MybatisPluginHelper.newMappedStatement(
                  mappedStatement,
                  MybatisPluginHelper.newBoundSql(mappedStatement, originBoundSql, sql)
          );
          args[0] = newMappedStatement;
          return invocation.proceed();
      }
  
      @Override
      public Object plugin(Object target) {
          return Plugin.wrap(target, this);
      }
  
      @Override
      public void setProperties(Properties properties) {
      }
  }
  

• 定义mybatis的拦截器bean

  	@Bean
      public Interceptor[] interceptors() {
          return new Interceptor[]{globalConditionFilterPlugin()};
      }
  
      private GlobalConditionFilterPlugin globalConditionFilterPlugin() {
          return new GlobalConditionFilterPlugin(new GlobalConditionFilterHandler() {
              @Override
              public String getColumn() {
                  return "project_id";
              }
  
              @Override
              public Object getColumnValue() {
                  return 1L;
              }
  
              @Override
              public String[] getIncludeTable() {
                  return new String[]{"t_table1", "t_table3"};
              }
  
              @Override
              public String[] getExcludeTable() {
                  return new String[0];
              }
          });
      }
  

• 完整代码见【四、源码】

三、效果

以下将展示通过插件改变前后的sql，如有特殊sql并且当前不符合的可以联系我。

原始sql	转换后sql
select t1.* from t_table1 as t1 join t_table2 as t2 on t1.id=t2.id WHERE t1.id=1 order by t1.id asc limit 10	SELECT t1.* FROM t_table1 t1 JOIN t_table2 t2 ON t1.id = t2.id WHERE t1.id = 1 AND t1.project_id = 1 ORDER BY t1.id ASC LIMIT 10
select t1.* from (select * from t_table1) as t1 join t_table2 as t2 on t1.id=t2.id WHERE t1.id in ( select id from t_table3 )	SELECT t1.* FROM ( SELECT * FROM t_table1 WHERE t_table1.project_id = 1 ) t1 JOIN t_table2 t2 ON t1.id = t2.id WHERE t1.id IN ( SELECT id FROM t_table3 WHERE t_table3.project_id = 1 )
with table_4 as ( select * from t_table1 ) select * from table_4	WITH table_4 AS ( SELECT * FROM t_table1 WHERE t_table1.project_id = 1 ) SELECT * FROM table_4
select * from t_table1 union all select * from t_table2 union all select * from t_table3	SELECT * FROM t_table1 WHERE t_table1.project_id = 1 UNION ALL SELECT * FROM t_table2 UNION ALL SELECT * FROM t_table3 WHERE t_table3.project_id = 1

四、源码

https://github.com/1277463718lmt/mybatis-plugins.git