mybatis拦截sql+改造sql且执行【原创】

在不改动系统代码的情况下如何解决以下问题

1. select a from tb_a 变为 select a from tb_a where c=2
2. select a,c from tb_a 变为 select a from tb_a

解决思路mybatis拦截器+net.sf.jsqlparser

创建mybatis 拦截器

@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 MyInterceptor  implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object[] args = invocation.getArgs();
        MappedStatement mappedStatement = (MappedStatement) args[0];
        BoundSql boundSql = mappedStatement.getBoundSql(args[1]);
        //  获取到原始sql
        String sql = boundSql.getSql();
        Field sqlField = boundSql.getClass().getDeclaredField("sql");
        sqlField.setAccessible(true);
     // 改造的sql塞回去  newSql 为改造之后的sql
        sqlField.set(boundSql, newSql);
        BoundSqlSqlSource boundSqlSqlSource = new BoundSqlSqlSource();
        boundSqlSqlSource.setBoundSql(boundSql);
        args[0] = copyFromMappedStatement(mappedStatement, boundSqlSqlSource);
        return invocation.proceed();
    }

    /**
     * 回塞sql
     *
     * @param ms           MappedStatement
     * @param newSqlSource SqlSource
     * @return MappedStatement
     */
    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();
    }
}

注意

Executor 里面有三个方法，两个query 一个update 因为拦截的是查询，每个query方法里面的参数不一样，一定要精确到参数，否则会拦截失效，拦截器只要重写intercept方法就行(本章不详细讲解myabtis拦截器，后续单独开一章)

net.sf.jsqlparser包

这个包主要是关于sql语句如何获取语句里面的表和查询的列具体案例如下

  public String deleteOrAnd(String sql) throws JSQLParserException {
        Select select = (Select) new CCJSqlParserManager().parse(new StringReader(sql));
        try {
            // 单句sql 如 select a from ta_b
            PlainSelect plainSelect = (PlainSelect) select.getSelectBody();      
        } catch (ClassCastException exception) {
            // 使用了union 如
            // select a from ta_b
          // union all
          // select b from tb_a
            SetOperationList setOperationList = (SetOperationList) select.getSelectBody();
            List<SelectBody> selectBodys = setOperationList.getSelects();
            List<SelectBody> newSelectBodys = new ArrayList<>();
            for (SelectBody selectBody : selectBodys) {
                PlainSelect plainSelect = (PlainSelect) selectBody;
                PlainSelect newPlainSelect = this.deleteOrAnd(plainSelect, sqlParam);
                newSelectBodys.add(newPlainSelect);
            }
            setOperationList.setBracketsOpsAndSelects(setOperationList.getBrackets(), newSelectBodys, setOperationList.getOperations());
       }
    }

       // 当 sql 为 select * from a，b   且sql中不止一张表 joins 中获取的表为所有的表
        List<Join> joins = plainSelect.getJoins();
      // 第一个表 当为单表的情况下，joins ==null
        FromItem fromItem = plainSelect.getFromItem();
      // 当select 为 select *  from a join b 那么joins中的table为除第一张表以外的其余所有表
 if (joinFromItem instanceof Table) {
                // 是个表   
    }
    // 如果获取的表是个子查询
    if (joinFromItem instanceof SubSelect) {
 PlainSelect joinPlainSelect = (PlainSelect) ((SubSelect)fromItem).getSelectBody();

// 如何在sql后面追加where条件
 Expression  whereParamExpression = CCJSqlParserUtil.parseCondExpression(“c=1”);
// 原sql的where条件
 Expression where = plainSelect.getWhere();
   if (where == null) {
       plainSelect.setWhere(whereParamExpression);
    } else {
     AndExpression andExpression = new AndExpression(where, whereParamExpression);
       plainSelect.setWhere(andExpression);
    }
//如何获取查询字段
List<SelectItem> selectItems = plainSelect.getSelectItems();
 Expression expression = ((SelectExpressionItem) selectItem).getExpression();
//查询字段可以是个字段也可以是子查询
 if (expression instanceof Column) {
// 查询字段
}
// 子查询
  if (expression instanceof SubSelect) {
// 子查询 
}

主要是SubSelect，SelectItem，FromItem，Join，PlainSelect ，Table 这些class之间的转换

Java 如何获取系统配置的数据库url中的schema

          Connection connection = dataSource.getConnection();
          return connection.getCatalog();