jsqlparser解析sql获取表名，字段等

场景

在项目中需要多sql进行统一拦截处理，但是传入的sql千奇百怪，并有一些逻辑判断，如只对哪张表处理之类，因此需要对sql进行解析后，进行相关逻辑处理。

这里使用 jsqlparser 工具进行sql解析。

代码实现

• maven组件引入

        <dependency>
            <groupId>com.github.jsqlparser</groupId>
            <artifactId>jsqlparser</artifactId>
            <version>1.4</version>
        </dependency>

• 使用方式

        String sql = "SELECT u.id,u.NAME,o.d,a.money FROM USER u,ORDERs o LEFT JOIN account a ON a.id=u.uid WHERE u.id=o.uid GROUP BY u.id,u.NAME ORDER BY u.id"
        Statement statement = null;
        try {
            statement =  CCJSqlParserUtil.parse(sql);
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
        // 获取table
        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
        List<String> tableList = tablesNamesFinder.getTableList(statement);
        log.info("tableList:{}", JSON.toJSONString(tableList));

        // 获取 join中的table
        Select selectStatement = (Select) statement;
        PlainSelect plain = (PlainSelect) selectStatement.getSelectBody();
        List<Join> joinList = plain.getJoins();
        List<String> tablewithjoin = new ArrayList<>();
        if (joinList != null) {
            for (int i = 0; i < joinList.size(); i++) {
                tablewithjoin.add(joinList.get(i).toString());
            }
        }
        log.info("tablewithjoin:{}", JSON.toJSONString(tablewithjoin));

        // 获取 where条件
        Expression where_expression = plain.getWhere();
        String whereStr = where_expression.toString();
        log.info("whereStr:{}", whereStr);

        // 获取group by
        List<Expression> GroupByColumnReferences = plain
                .getGroupByColumnReferences();
        List<String> groupBys = new ArrayList<>();
        if (GroupByColumnReferences != null) {
            for (int i = 0; i < GroupByColumnReferences.size(); i++) {
                groupBys.add(GroupByColumnReferences.get(i).toString());
            }
        }

        log.info("str_groupby:{}",  JSON.toJSONString(groupBys));

        // 获取order by
        List<OrderByElement> OrderByElements = plain.getOrderByElements();
        List<String> orderBys = new ArrayList<>();
        if (OrderByElements != null) {
            for (int i = 0; i < OrderByElements.size(); i++) {
                orderBys.add(OrderByElements.get(i).toString());
            }
        }
        log.info("str_orderby:{}", JSON.toJSONString(orderBys));

输出结果：

tableList:["user","orders","account"]
tablewithjoin:["ORDERs o","account a ON a.id=u.uid"]
whereStr:u.id = o.uid
str_groupby:["u.id","u.name"]
str_orderby:["u.id"]