在不改動(dòng)系統(tǒng)代碼的情況下如何解決以下問(wèn)題

1. select a from tb_a 變?yōu)?select a from tb_a where c=2
2. select a,c from tb_a 變?yōu)?select a from tb_a

解決思路mybatis攔截器+net.sf.jsqlparser

創(chuàng)建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 里面有三個(gè)方法，兩個(gè)query 一個(gè)update 因?yàn)閿r截的是查詢(xún)，每個(gè)query方法里面的參數(shù)不一樣，一定要精確到參數(shù)，否則會(huì)攔截失效，攔截器只要重寫(xiě)intercept方法就行(本章不詳細(xì)講解myabtis攔截器，后續(xù)單獨(dú)開(kāi)一章)

net.sf.jsqlparser包

這個(gè)包主要是關(guān)于sql語(yǔ)句如何獲取語(yǔ)句里面的表和查詢(xún)的列具體案例如下

  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());
       }
    }

       // 當(dāng) sql 為 select * from a，b   且sql中不止一張表 joins 中獲取的表為所有的表
        List<Join> joins = plainSelect.getJoins();
      // 第一個(gè)表 當(dāng)為單表的情況下，joins ==null
        FromItem fromItem = plainSelect.getFromItem();
      // 當(dāng)select 為 select *  from a join b 那么joins中的table為除第一張表以外的其余所有表
 if (joinFromItem instanceof Table) {
                // 是個(gè)表   
    }
    // 如果獲取的表是個(gè)子查詢(xún)
    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);
    }
//如何獲取查詢(xún)字段
List<SelectItem> selectItems = plainSelect.getSelectItems();
 Expression expression = ((SelectExpressionItem) selectItem).getExpression();
//查詢(xún)字段可以是個(gè)字段也可以是子查詢(xún)
 if (expression instanceof Column) {
// 查詢(xún)字段
}
// 子查詢(xún)
  if (expression instanceof SubSelect) {
// 子查詢(xún) 
}

主要是SubSelect，SelectItem，F(xiàn)romItem，Join，PlainSelect ，Table 這些class之間的轉(zhuǎn)換

Java 如何獲取系統(tǒng)配置的數(shù)據(jù)庫(kù)url中的schema

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