JSqlParser入门系列(4)-JSqlParser之SQL解析

前言

JSqlParser可以解析SQL为JAVA对象，以便于获取SQL中的相关信息并进行修改。

一般使用CCJSqlParserUtil工具类直接解析SQ;，根据SQL类型转换为增删改查对象，再获取或修改对象中相关信息。

查询解析

    /**
     * SQL 解析
     *
     * @throws JSQLParserException
     */
    @Test
    public void testSelectParser() throws JSQLParserException {
        String SQL002 = "SELECT t1.a , t1.b  FROM tab1 AS t1 JOIN tab2 t2 ON t1.user_id  = t2.user_id";   // 多表SQL

        // 1.解析表名
        CCJSqlParserManager parserManager = new CCJSqlParserManager();
        Statement statement = parserManager.parse(new StringReader(SQL002)); // 解析SQL为Statement对象
        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder(); // 创建表名发现者对象
        List<String> tableNameList = tablesNamesFinder.getTableList(statement); // 获取到表名列表
        if (!CollectionUtils.isEmpty(tableNameList)) {
            tableNameList.forEach(System.err::println); // 循环打印解析到的表名 tab1 tab2
        }
        // 2.解析查询元素=》 列，函数等
        Select select = (Select) CCJSqlParserUtil.parse(SQL002);
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
        List<SelectItem> selectItems = plainSelect.getSelectItems();
        selectItems.forEach(System.err::println); // t1.a , t1.b

        // 3.解析WHERE条件
        String SQL_WHERE = "SELECT *  FROM tableName WHERE ID = 8";
        PlainSelect plainSelectWhere = (PlainSelect) ((Select) CCJSqlParserUtil.parse(SQL_WHERE)).getSelectBody();
        EqualsTo equalsTo = (EqualsTo) plainSelectWhere.getWhere();
        Expression leftExpression = equalsTo.getLeftExpression();
        Expression rightExpression = equalsTo.getRightExpression();
        System.err.println(leftExpression); // ID
        System.err.println(rightExpression); // 8

        // 4.解析Join
        List<Join> joins = plainSelect.getJoins();
        joins.forEach(e -> {
            Expression onExpression = e.getOnExpression();
            System.err.println(onExpression); // 获取ON 表达式 t1.user_id = t2.user_id
        });

        // 5.解析IN
        String SQL_IN = "SELECT *  FROM tableName WHERE ID IN (8,9,10)";
        PlainSelect plainSelectIn = (PlainSelect) ((Select) CCJSqlParserUtil.parse(SQL_IN)).getSelectBody();
        InExpression inExpression = (InExpression) plainSelectIn.getWhere();
        ItemsList rightItemsList = inExpression.getRightItemsList();
        System.err.println(rightItemsList); // (8, 9, 10)

        // plainSelect.getDistinct();
        // plainSelect.getFetch();
        // plainSelect.getFirst();
        // plainSelect.getGroupBy();
        // .......
    }
    

插入解析

    /**
     * Insert 解析
     *
     * @throws JSQLParserException
     */
    @Test
    public void testInsertParser() throws JSQLParserException {
        // 3.解析WHERE条件
        String insertSql = "INSERT INTO test ( c1,c2) VALUES ( 001,002)";
        Statement statement = CCJSqlParserUtil.parse(insertSql);
        if (statement instanceof Insert) {
            Insert insert = (Insert) statement;
            // 添加新列
            insert.addColumns(new Column("c3 "));
            // 添加新插入值
            ExpressionList expressionList = (ExpressionList) insert.getItemsList();
            expressionList.getExpressions().add(new StringValue("003"));
            System.err.println(insert); //INSERT INTO test (c1, c2, c3 ) VALUES (001, 002, '003')
        }
    }

修改解析

    /**
     * Update 解析
     *
     * @throws JSQLParserException
     */
    @Test
    public void testUpdateParser() throws JSQLParserException {
        String updateSql = "UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson' ";
        Statement statement = CCJSqlParserUtil.parse(updateSql);
        if (statement instanceof Update) {
            Update update = (Update) statement;
            Expression where = update.getWhere(); // 获取WHERE表达式=》LastName = 'Wilson'
            System.err.println(where);
            List<Column> columns = update.getColumns(); // 获取修改列=》 FirstName
            columns.forEach(System.out::println);
        }
    }