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

水深无声 2023-10-10 12:37 87阅读 0赞

### 前言 ###

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