druid 使用自定义visitor无法获取原表名,列的别名又该如何获取??

pkmbmrz7  于 4个月前  发布在  Druid
关注(0)|答案(2)|浏览(59)

我的环境:druid版本1.1.16
1、 对于sql : select name na from mytable a where a.id = 3;当我使用自定义visitor-ExportTableAliasVisitor ,使用SQLTableSource tableSource = visitor.getAliasMap().get("a")可以拿到mytable.但是visitor.getTables()返回null,visitor.getColumns()返回值为[UNKNOWN.mytable, mytable.name, mytable.id]也不对

2、无法获取列对应的别名。列如:name对应的别名为na

@Test
    public void testalias(){
        final String dbType = JdbcConstants.MYSQL; // JdbcConstants.MYSQL或者JdbcConstants.POSTGRESQL
        String sql = "select name na from mytable a where a.id = 3";
        List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);

        SQLStatement stmt = stmtList.get(0);
        ExportTableAliasVisitor visitor = new ExportTableAliasVisitor();
        stmt.accept(visitor);

//        SQLTableSource tableSource = visitor.getAliasMap().get("a");
//        System.out.println(tableSource);

        //获取操作方法名称,依赖于表名称
        System.out.println("涉及到的所有表 : " + visitor.getTables());
        Map<TableStat.Name, TableStat> table_map = visitor.getTables();
        for(Map.Entry<TableStat.Name, TableStat> entry : table_map.entrySet()){
            TableStat.Name name = entry.getKey();
            name.getName();
            //存储表的调度次数,包括select ,update等
            TableStat ts = entry.getValue();

        }
        //获取字段名称
        System.out.println( visitor.getParameters());
        //获取列名
        System.out.println("查询的列信息 : " + visitor.getColumns());
        Collection<TableStat.Column> cc = visitor.getColumns();
        //column 存储了表名,列名,以及列是出现的位置,where,select,groupby ,order
        for(TableStat.Column column : cc){
            column.getAttributes().forEach((k,v) -> System.out.println(k+";v="+v));
            System.out.println(column.getTable());

        }
        System.out.println("conditions : " + visitor.getConditions() );
        List<TableStat.Condition> conditions = visitor.getConditions();
        System.out.println("----------------------------");
        for(TableStat.Condition cond : conditions){
            System.out.println( "column : " + cond.getColumn());
            System.out.println( "operator : " + cond.getOperator());
            System.out.println( "values  : " + cond.getValues());

            System.out.println("----------------------------");
        }
        System.out.println("group by : " + visitor.getGroupByColumns() );
        System.out.println("order by : " + visitor.getOrderByColumns() );
        System.out.println("relations ships  : " + visitor.getRelationships() );
    }

    public static class ExportTableAliasVisitor extends MySqlSchemaStatVisitor {
        private Map<String, SQLTableSource> aliasMap = new HashMap<String, SQLTableSource>();
        public boolean visit(SQLExprTableSource x) {
            String alias = x.getAlias();
            aliasMap.put(alias, x);
            return true;
        }

        public Map<String, SQLTableSource> getAliasMap() {
            return aliasMap;
        }
    }
6bc51xsx

6bc51xsx1#

/**

  • Created by Qi.qingshan on 2020/1/20
    */
    public class TableVisitorDemo extends MySqlSchemaStatVisitor {
    }

/**

  • Created by Qi.qingshan on 2020/1/20
    */
    public class TableVisitorMain {
    public static void main(String[] args) {
    final String dbType = JdbcConstants.MYSQL; // JdbcConstants.MYSQL或者JdbcConstants.POSTGRESQL
    String sql = "select name na from mytable a where a.id = 3";
    List stmtList = SQLUtils.parseStatements(sql, dbType);
    SQLStatement stmt = stmtList.get(0);
    TableVisitorDemo visitorDemo = new TableVisitorDemo();
    stmt.accept(visitorDemo);
Map<TableStat.Name, TableStat> tables = visitorDemo.getTables();
 Collection<TableStat.Column> columns = visitorDemo.getColumns();

}
}

如果想实现获取列别名等信息,可以实现其中的某些方法,或者是重新实现visitor,我这里偷懒没自己实现,继承了已经实现的

4zcjmb1e

4zcjmb1e2#

SQLSelect select = sqlSelectStatement.getSelect(); MySqlSelectQueryBlock query = (MySqlSelectQueryBlock) select.getQuery(); List<SQLSelectItem> selectList = query.getSelectList(); System.out.println("获取原始字段 =================="); selectList.forEach(System.out::println); System.out.println("获取原始字段 end==================");

这个 可以得到 带有 别名的 字段 我的方案是使用字符串截取 目前只研究到这

相关问题