快捷導(dǎo)航

Apache Calcite進(jìn)行SQL解析(java代碼實(shí)例)

更新時(shí)間：2022年01月26日 10:19:02 作者：追求無悔

Calcite是一款開源SQL解析工具, 可以將各種SQL語句解析成抽象語法樹AST(Abstract Syntax Tree), 之后通過操作AST就可以把SQL中所要表達(dá)的算法與關(guān)系體現(xiàn)在具體代碼之中，今天通過代碼實(shí)例給大家介紹Apache Calcite進(jìn)行SQL解析問題，感興趣的朋友一起看看吧

背景

當(dāng)一個(gè)項(xiàng)目分了很多模塊，很多個(gè)服務(wù)的時(shí)候，一些公共的配置就需要統(tǒng)一管理了，于是就有了元數(shù)據(jù)驅(qū)動(dòng)！

簡介

什么是Calcite？
是一款開源SQL解析工具, 可以將各種SQL語句解析成抽象語法樹AST(Abstract Syntax Tree), 之后通過操作AST就可以把SQL中所要表達(dá)的算法與關(guān)系體現(xiàn)在具體代碼之中。
Calcite能做啥？

SQL 解析
SQL 校驗(yàn)
查詢優(yōu)化
SQL 生成器
數(shù)據(jù)連接

實(shí)例

今天主要是貼出一個(gè)java代碼實(shí)例，實(shí)現(xiàn)了：解析SQL語句中的表名
上代碼：
SQL語句轉(zhuǎn)化：

public static SqlNode parseStatement(String sql) {
        SqlParser parser = SqlParser.create(sql, config.getParserConfig());
        try {
            return parser.parseQuery();
        } catch (Exception e) {
            e.printStackTrace();
            throw new UnsupportedOperationException("operation not allowed");
        }
    }

解析Select中的表名：

private static Set<String> extractSourceTableInSelectSql(SqlNode sqlNode, boolean fromOrJoin) {
        if (sqlNode == null) {
            return new HashSet<>();
        }
        final SqlKind sqlKind = sqlNode.getKind();
        if (SqlKind.SELECT.equals(sqlKind)) {
            SqlSelect selectNode = (SqlSelect) sqlNode;
            Set<String> selectList = new HashSet<>(extractSourceTableInSelectSql(selectNode.getFrom(), true));
            selectNode.getSelectList().getList().stream().filter(node -> node instanceof SqlCall)
                .forEach(node -> selectList.addAll(extractSourceTableInSelectSql(node, false)));
            selectList.addAll(extractSourceTableInSelectSql(selectNode.getWhere(), false));
            selectList.addAll(extractSourceTableInSelectSql(selectNode.getHaving(), false));
            return selectList;
        if (SqlKind.JOIN.equals(sqlKind)) {
            SqlJoin sqlJoin = (SqlJoin) sqlNode;
            Set<String> joinList = new HashSet<>();
            joinList.addAll(extractSourceTableInSelectSql(sqlJoin.getLeft(), true));
            joinList.addAll(extractSourceTableInSelectSql(sqlJoin.getRight(), true));
            return joinList;

        if (SqlKind.AS.equals(sqlKind)) {
            SqlCall sqlCall = (SqlCall) sqlNode;
            return extractSourceTableInSelectSql(sqlCall.getOperandList().get(0), fromOrJoin);
        if (SqlKind.IDENTIFIER.equals(sqlKind)) {
            Set<String> identifierList = new HashSet<>();
            if (fromOrJoin) {
                SqlIdentifier sqlIdentifier = (SqlIdentifier) sqlNode;
                identifierList.add(sqlIdentifier.toString());
            }
            return identifierList;
        Set<String> defaultList = new HashSet<>();
        if (sqlNode instanceof SqlCall) {
            SqlCall call = (SqlCall) sqlNode;
            call.getOperandList()
                .forEach(node -> defaultList.addAll(extractSourceTableInSelectSql(node, false)));
        return defaultList;
    }

解析Insert語句中的表名：

private static Set<String> extractSourceTableInInsertSql(SqlNode sqlNode, boolean fromOrJoin) {
        SqlInsert sqlInsert = (SqlInsert) sqlNode;
        Set<String> insertList = new HashSet<>(extractSourceTableInSelectSql(sqlInsert.getSource(), false));
        final SqlNode targetTable = sqlInsert.getTargetTable();
        if (targetTable instanceof SqlIdentifier) {
            insertList.add(((SqlIdentifier) targetTable).toString());
        }
        return insertList;
    }

執(zhí)行效果

private static final String sql0 = "SELECT MIN(relation_id) FROM tableA JOIN TableB  GROUP BY account_instance_id, follow_account_instance_id HAVING COUNT(*)>1";

private static final String sql1 = "SELECT * FROM blog_user_relation a WHERE (a.account_instance_id,a.follow_account_instance_id) IN (SELECT account_instance_id,follow_account_instance_id FROM Blogs_info GROUP BY account_instance_id, follow_account_instance_id HAVING COUNT(*) > 1)";
private static final String sql2 = "select name from (select * from student)";
private static final String sql3 = "SELECT * FROM Student LEFT JOIN Grade ON Student.sID = Grade.gID\n" +
            "UNION\n" +
            "SELECT * FROM Student RIGHT JOIN Grade ON Student.sID = Grade.gID";
private static final String sql4 = "SELECT *\n" +
            "FROM teacher\n" +
            "WHERE birth = (SELECT MIN(birth)\n" +
            "               FROM employee)";
private static final String sql5 = "SELECT sName\n" +
            "FROM Student\n" +
            "WHERE '450' NOT IN (SELECT courseID\n" +
            "                    FROM Course\n" +
            "                    WHERE sID = Student.sID)";
final SqlNode sqlNode0 = parseStatement(sql0);
System.out.println("sqlNode0: " + extractSourceTableInSelectSql(sqlNode0, false));

結(jié)果為：