Calcite和Jsqlparser解析Sql中的表名,Calcite效率更高

目的是为了解析视图中的表名，构建血缘关系，下面是入门的demo
Calcite解析 有不能解析出来的情况，需要进一步了解javacc，
Jsqparser解析，都能正常解析，然而面对复杂子查询，效率很低

使用Calcite解析

package sqlparser;

import org.apache.calcite.avatica.util.Casing;
import org.apache.calcite.avatica.util.Quoting;
import org.apache.calcite.config.Lex;
import org.apache.calcite.sql.*;
import org.apache.calcite.sql.parser.SqlParseException;
import org.apache.calcite.sql.parser.SqlParser;

import java.util.ArrayList;
import java.util.List;

public class CalctieParserTablesDemo3 {


    public static void main(String[] args) {
//        String sql = "select a.id,b.name,b.label from person a, person b where a.id = b.id";
        String sql =
                "select  t.id,t.name,t.label from " +
                        "(select id,name,label from  person  " +
                        " union all " +
                        "select id,name,label from  person   ) t";





        try {
            List<String> strings = extractTableNameList(sql);
            String tables = String.join(",", strings);
            System.out.println(tables);
        } catch (SqlParseException e) {
            throw new RuntimeException(e);
        }
    }


    public static List<String> extractTableNameList(String sql) throws SqlParseException {
//        SqlParser.Config config = SqlParser.configBuilder().setLex(Lex.MYSQL).build();

        SqlParser.Config config = SqlParser.configBuilder()
                .setQuotedCasing(Casing.UNCHANGED)
                .setUnquotedCasing(Casing.UNCHANGED)
                .setQuoting(Quoting.BACK_TICK)
//                .setParserFactory(QuarkParserImpl.FACTORY)
                .setLex(Lex.MYSQL).build();


        SqlParser parser = SqlParser.create(sql,config);
        SqlNode parsed = parser.parseQuery();
//        SqlNode parsed = parser.parseStmt();
        List<String> tableNameList = new ArrayList<>();

        parseSqlNode(parsed, tableNameList);

        return tableNameList;
    }

    private static void parseFromNode(SqlNode from, List<String> tableNameList){
        SqlKind kind = from.getKind();
        switch (kind) {
            case IDENTIFIER:
                //最终的表名
                SqlIdentifier sqlIdentifier = (SqlIdentifier) from;
                tableNameList.add(sqlIdentifier.toString());
                break;
            case AS:
                SqlBasicCall sqlBasicCall = (SqlBasicCall) from;
                SqlNode selectNode = sqlBasicCall.getOperandList().get(0);
                parseSqlNode(selectNode, tableNameList);
                break;
            case JOIN:
                SqlJoin sqlJoin = (SqlJoin) from;
                SqlNode left = sqlJoin.getLeft();
                parseFromNode(left, tableNameList);
                SqlNode right = sqlJoin.getRight();
                parseFromNode(right, tableNameList);
                break;
            case SELECT:
                parseSqlNode(from, tableNameList);
                break;
        }
    }

    private static void parseSqlNode(SqlNode sqlNode, List<String> tableNameList) {
        SqlKind kind = sqlNode.getKind();
        switch (kind) {
            case IDENTIFIER:
                parseFromNode(sqlNode, tableNameList);
                break;
            case SELECT:
                SqlSelect select = (SqlSelect) sqlNode;
                parseFromNode(select.getFrom(), tableNameList);
                break;
            case UNION:
                ((SqlBasicCall) sqlNode).getOperandList().forEach(node -> {
                    parseSqlNode(node, tableNameList);
                });

                break;
            case ORDER_BY:
                handlerOrderBy(sqlNode, tableNameList);
                break;
        }
    }

    private static void handlerOrderBy(SqlNode node, List<String> tableNameList) {
        SqlOrderBy sqlOrderBy = (SqlOrderBy) node;
        SqlNode query = sqlOrderBy.query;
        parseSqlNode(query, tableNameList);
    }



}


使用jsqlparser的Demo部分代码



public static void sqlParser(String sql, String viewName) {
        Select selectStatement = null;
        CCJSqlParserManager parserManager = null;
        try {
            parserManager = new CCJSqlParserManager();
            selectStatement = (Select) parserManager.parse(new StringReader(sql));
//            new CCJSqlParser();
//            if(sql.contains("union")){
//                parserManager = new CCJSqlParserManager();
//                selectStatement = (Select) parserManager.parse(new StringReader(sql));
//            }else{
//                selectStatement = (Select) CCJSqlParserUtil.parse(sql);
//            }


        } catch (JSQLParserException e) {
            throw new RuntimeException(e);
        }


        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
        List<String> tableList = tablesNamesFinder.getTableList(selectStatement);


        for (Iterator iter = tableList.iterator(); iter.hasNext(); ) {
            String tableName = (String) iter.next();
//            如果是包含view的，这种不能让显示，可以让显示，
            System.out.println(viewName + "===========" + tableName.replace("`", ""));
            List<String> tables = tableMapLists.get(tableName.replace("`", ""));
            if (tables == null) {
                List<String> tableLists = new ArrayList<>();
                tableLists.add(viewName);
                tableMapLists.put(tableName.replace("`", ""), tableLists);
            } else {
                if (tables.contains(viewName)) {
                    continue;
                } else {
                    tables.add(viewName);
                }
            }
//            tables.add(tableName.substring(tableName.lastIndexOf(".")+1).replaceAll("`",""));

        }

    }