JZTXT

Calcite解析Sql中的表名

发布时间 2023-07-20 10:18:16作者: 堕落先锋

目的是为了解析视图中的表名，构建血缘关系，下面只是一个入门的demo

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



}