jsqlparser或druid解析SQL语句

1.使用jsqlparser解析

1. 引入jsqlparser包：

<!--jsqlparser-->
<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>4.6</version>
</dependency>

2. 使用工具类解析

/**
 * SQL语句解析
 *
 * @param sql 原SQL
 * @return select:[table1,table2]
 */
public static Map<String, Object> sqlParser(String sql) {
    Map<String, Object> result = new HashMap<>();
    if (StringUtils.isEmpty(sql)) {
        return result;
    }
    try {
        String operation = "";
        Set<String> tableList = new HashSet<>();
        Statement statement = CCJSqlParserUtil.parse(new StringReader(sql));
        if (statement instanceof Select) {
            operation = "SELECT";
            Select select = (Select) statement;
        } else if (statement instanceof Delete) {
            operation = "DELETE";
            Delete delete = (Delete) statement;
            Table table = delete.getTable();
            String tableName = table.getName();
            tableList.add(tableName);
        } else if (statement instanceof Update) {
            operation = "UPDATE";
            Update update = (Update) statement;
            Table table = update.getTable();
            String tableName = table.getName();
            tableList.add(tableName);
        } else if (statement instanceof Insert) {
            operation = "INSERT";
            Insert insert = (Insert) statement;
            Table table = insert.getTable();
            String tableName = table.getName();
            tableList.add(tableName);
        } else if (statement instanceof Merge) {
            operation = "Merge";
            Merge merge = (Merge) statement;
            Table table = merge.getTable();
            String tableName = table.getName();
            tableList.add(tableName);
        }
        result.put("operation", operation);
        result.put("tableList", tableList);
        return result;
    } catch (JSQLParserException e) {
        log.error("SQL解析异常，语句为{}，原因：", sql, e);
        return result;
    }
}

/**
 * 对select语句解析
 *
 * @param result     表名集合
 * @param selectBody select体
 */
public static void getSelectTables(Set<String> result, SelectBody selectBody) {
    if (selectBody instanceof PlainSelect) {
        PlainSelect plainSelect = (PlainSelect) selectBody;
        FromItem fromItem = plainSelect.getFromItem();
        // 单表
        if (fromItem instanceof Table) {
            Table table = (Table) fromItem;
            String tableName = table.getName();
            result.add(tableName);
        }
        // 子查询
        else if (fromItem instanceof SubSelect) {
            SubSelect subSelect = (SubSelect) fromItem;
            getSelectTables(result, subSelect.getSelectBody());
        }
        // 多表连接查询
        List<Join> joinList = plainSelect.getJoins();
        if (joinList != null) {
            for (Join joinJoin : joinList) {
                FromItem rightFromItem = joinJoin.getRightItem();
                if (!(rightFromItem instanceof Table)) {
                    continue;
                }
                Table table = (Table) rightFromItem;
                String tableName = table.getName();
                result.add(tableName);
            }
        }
    }
    // union连接
    else if (selectBody instanceof SetOperationList) {
        SetOperationList setOperationList = (SetOperationList) selectBody;
        List<SelectBody> selectBodyList = setOperationList.getSelects();
        selectBodyList.forEach(e -> getSelectTables(result, e));
    }
}

2.使用Druid解析

1. 引入依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.22</version>
</dependency>

2. 工具方法解析

public static Map<String, Object> parser(String sql) {
    Map<String, Object> result = new HashMap<>();

    MySqlStatementParser parser = new MySqlStatementParser(sql);
    // 是否保留注释
    parser.setKeepComments(false);
    SQLStatement stmt = parser.parseStatement();
    if (stmt instanceof SQLCreateProcedureStatement) {
        SQLCreateProcedureStatement procStmt = (SQLCreateProcedureStatement) stmt;
        SQLStatement blockStmt = procStmt.getBlock();
        if (blockStmt instanceof SQLBlockStatement) {
            SQLBlockStatement sqlBlockStatement = (SQLBlockStatement) blockStmt;
            List<SQLStatement> sqlStatementList = sqlBlockStatement.getStatementList();
            for (SQLStatement statement : sqlStatementList) {
                List<String> tableList = getTableList(statement);
            }
        }
        // List<SQLStatement> statementList = blockStmt.getStatementList();
        // for (SQLStatement sqlStatement : statementList) {
        //     if (sqlStatement instanceof SQLSelectStatement) {
        //         SQLSelectStatement selectStatement = (SQLSelectStatement) sqlStatement;
        //         SQLSelectQuery query = selectStatement.getSelect().getQuery();
        //
        //         // 处理SELECT语句
        //         System.out.println(query.toString());
        //     }
        // }
    }

    return result;
}

public static List<String> getTableList(SQLStatement statement) {
    List<String> result = new ArrayList<>();
    if (statement instanceof SQLSelectStatement) {
        SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) statement;
        SQLSelect sqlSelect = sqlSelectStatement.getSelect();
        SQLSelectQuery sqlSelectQuery = sqlSelect.getQuery();
        if (sqlSelectQuery instanceof MySqlSelectQueryBlock) {
            MySqlSelectQueryBlock mySqlSelectQueryBlock = (MySqlSelectQueryBlock) sqlSelectQuery;
            SQLTableSource sqlTableSource = mySqlSelectQueryBlock.getFrom();
            result.add(sqlTableSource.toString());
        }
    }
    return result;
}