Mybatis插件开发(拦截SQL并改写SQL)

电玩女神 2023-01-10 14:44 147阅读 0赞

### 文章目录 ###

*   *  拦截器接口介绍
     *  SQL拦截改写
     *   *  定义拦截器接口 Interceptor
         *   *  添加拦截器

## 拦截器接口介绍 ##

Mybatis 允许在以映射SQL语句执行过程中的某一点进行拦截调用。默认情况下，Mybatis允许使用插件来拦截的接口和方法有以下几个

*  Executor(update、query、flushStatements、commit、rollback、getTransaction、clos、isClosed)
 *  ParameterHandler(getParameterObject、setParameters)
 *  ResultSetHandler(handlerResultSets、handleCursorResultSets、handleOutPutParameters)
 *  StatementHandler(prepare、parameterize、batch、update、query)

要实现我们自己的拦截器我们就需要实现拦截器接口`Interceptor`,其中`Interceptor`接口中的方法如下

public interface Interceptor { 
    	/** * 执行的拦截方法，Invocation 中可以拿到很多我们需要的属性，比如拦截方法名(getMethod())，方法参数(getArgs())，SQL等 */
        Object intercept(Invocation var1) throws Throwable;
    	/** * var1代表要拦截的对象，这里实现自己要拦截对象的逻辑 */
        Object plugin(Object var1);
    	/** * 插件属性传递 */
        void setProperties(Properties var1);
    }

如果配置多个拦截器Mybatis会按照顺序依次执行

实现拦截器接口的同时我们还需要对实现类加注解`@Intercepts`进行配置  
![在这里插入图片描述][20210125084901398.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjUxOTA0_size_16_color_FFFFFF_t_70]

注解中的Signature主要包含的属性如下

*  type：设置拦截的接口，可选参数为前面的四个接口
 *  method:设置拦截接口中的方法名，可选值是前面四个接口的方法
 *  args：设置拦截方法的参数类型，通过方法名和参数类型可唯一确定一个方法

其中最为常用的配置是如下

@Intercepts(@Signature(type = Executor.class, method = "update",
            args = { MappedStatement.class, Object.class}))

该签名会拦截所有的INSERT、UPDATE、DELETE

## SQL拦截改写 ##

接下来我们就来实现我们的SQL拦截改写,要改写的SQL很简单，对所有的Select 表名后面加Final

### 定义拦截器接口 Interceptor ###

> 这里要说明的是实现方式由两种，一种是直接使用 replaceAll替换表名 在加final关键字，这种出错几率很小，所有SQL都支持，第二种使用SQL解析器解析SQL然后再拼接SQL，这种实现方式对不同的数据库需要不同的实现类，可以定义一个SQL改写接口，对于SQL改写可以自己选择不同的开源框架，比如sqlparser、Apache Calcite、druid等，这里我使用的druid解析的SQL，只是做了简单的SQL解析，没有测试大量的SQL，所以一下demo仅供参考

@Component
    @Intercepts(@Signature(type = Executor.class, method = "query",
            args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}))
    public class MybatisInterceptor implements Interceptor { 
    
        static int MAPPED_STATEMENT_INDEX = 0;// 这是对应上面的args的序号
    
        @Override
        public Object intercept(Invocation invocation) throws Throwable { 
            // 获取拦截方法的参数
            Object[] args = invocation.getArgs();
            MappedStatement mappedStatement = (MappedStatement) args[0];
            // 拦截方法参数
            MapperMethod.ParamMap method = (MapperMethod.ParamMap)args[1];
    
            Integer customerId = (Integer)method.get(("customerId"));
            if (!filterObject(customerId)) { 
                //只处理拦截SQL
                return invocation.proceed();
            }
            // 通过StatementHandler 获取执行SQL
            BoundSql boundSql = mappedStatement.getBoundSql(args[1]);
            String sql = boundSql.getSql();
    // System.out.println("获取到的SQL: " + sql);
            String newSql = updateSql(sql);
            BoundSql newBoundSql = new BoundSql(mappedStatement.getConfiguration(), newSql,
                    boundSql.getParameterMappings(), boundSql.getParameterObject());
            // 把新的查询放到statement里
            MappedStatement newMs = copyFromMappedStatement(mappedStatement, new BoundSqlSqlSource(newBoundSql));
            for (ParameterMapping mapping : boundSql.getParameterMappings()) { 
                String prop = mapping.getProperty();
                if (boundSql.hasAdditionalParameter(prop)) { 
                    newBoundSql.setAdditionalParameter(prop, boundSql.getAdditionalParameter(prop));
                }
            }
            final Object[] queryArgs = invocation.getArgs();
            queryArgs[MAPPED_STATEMENT_INDEX] = newMs;
            return invocation.proceed();
        }
    
    
        private MappedStatement copyFromMappedStatement(MappedStatement ms, SqlSource newSqlSource) { 
            MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());
            builder.resource(ms.getResource());
            builder.fetchSize(ms.getFetchSize());
            builder.statementType(ms.getStatementType());
            builder.keyGenerator(ms.getKeyGenerator());
            if (ms.getKeyProperties() != null && ms.getKeyProperties().length > 0) { 
                builder.keyProperty(ms.getKeyProperties()[0]);
            }
            builder.timeout(ms.getTimeout());
            builder.parameterMap(ms.getParameterMap());
            builder.resultMaps(ms.getResultMaps());
            builder.resultSetType(ms.getResultSetType());
            builder.cache(ms.getCache());
            builder.flushCacheRequired(ms.isFlushCacheRequired());
            builder.useCache(ms.isUseCache());
            return builder.build();
        }
    
        public static class BoundSqlSqlSource implements SqlSource { 
    
            private final BoundSql boundSql;
    
            public BoundSqlSqlSource(BoundSql boundSql) { 
                this.boundSql = boundSql;
            }
            public BoundSql getBoundSql(Object parameterObject) { 
                return boundSql;
            }
        }
    
    
        /** * SQL改写 * @param sql * @return java.lang.String * @author wh * @date 2021/1/21 */
        private String updateSql(String sql) { 
    
    // String convertedSql = sql.replaceAll("t_order", "order_detail final");
            StringBuilder newSql = new StringBuilder("select ");
            PGSQLStatementParser mySqlStatementParser = new PGSQLStatementParser(sql);
            SQLSelectStatement sqlSelectStatement = mySqlStatementParser.parseSelect();
            SQLSelect sqlSelect = sqlSelectStatement.getSelect();
            SQLSelectQuery sqlSelectQuery = sqlSelect.getQuery();
            if (sqlSelectQuery instanceof PGSelectQueryBlock) { 
                PGSelectQueryBlock pgSelectQueryBlock = (PGSelectQueryBlock) sqlSelectQuery;
                List<SQLSelectItem> coulums = pgSelectQueryBlock.getSelectList();
                for (int i = 0; i < coulums.size(); i++) { 
                    if (i == coulums.size() - 1) { 
                        newSql.append(coulums.get(i));
                    } else { 
                        newSql.append(coulums.get(i)).append(",");
                    }
                }
                new PGOutputVisitor(new StringBuilder());
                PGOutputVisitor tableName = new PGOutputVisitor(new StringBuilder());
                pgSelectQueryBlock.getFrom().accept(tableName);
    // System.out.println(tableName.getAppender());
                newSql.append(" from ").append(tableName.getAppender()).append(" final");
                PGOutputVisitor where = new PGOutputVisitor(new StringBuilder());
                // 获取where 条件
                pgSelectQueryBlock.getWhere().accept(where);
                newSql.append(" where ").append(where.getAppender());
                PGOutputVisitor groupBy = new PGOutputVisitor(new StringBuilder());
                SQLSelectGroupByClause sqlGroup = pgSelectQueryBlock.getGroupBy();
                if (Objects.nonNull(sqlGroup)) { 
                    sqlGroup.accept(groupBy);
                    newSql.append(" ").append(groupBy.getAppender());
                }
        }
            return newSql.toString();
        }
    
    
        /** * 要拦截的对象 * @param target * @return java.lang.Object * @author wh * @date 2021/1/21 */
        @Override
        public Object plugin(Object target) { 
            return Plugin.wrap(target, this);
        }
    
        /** * 插件属性传递 * @param properties * @return void * @author wh * @date 2021/1/21 */
        @Override
        public void setProperties(Properties properties) { 
    
        }
    
        /** * 默认拦截所有SQL * @param * @return boolean * @author wh * @date 2021/1/21 */
        public boolean filterObject(Integer customerId) { 
            return true;
        }
    
    }

#### 添加拦截器 ####

@Bean
        public SqlSessionFactory clickHouseSqlSessionFactoryBean() throws Exception { 
            SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
            factory.setDataSource(clickhouseDataSource());
            factory.setTypeAliasesPackage(MODEL_PACKAGE);
            //添加拦截器
            factory.setPlugins(new Interceptor[]{ mybatisInterceptor});
            //添加XML目录
            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            factory.setMapperLocations(resolver.getResources("classpath:mapper/clickhouse/*.xml"));
            //开启驼峰命名转换
            factory.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
            return factory.getObject();
        }

[20210125084901398.png]: /images/20221119/2e1d5d76d5cd40ea9d8965e3442f87cc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjUxOTA0_size_16_color_FFFFFF_t_70]: /images/20221119/6dcced9f709440afb17272a76c1fb29d.png