OracleListAggTest1.java

package com.alibaba.druid.bvt.sql.oracle;

import com.alibaba.druid.sql.OracleTest;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.visitor.SchemaStatVisitor;
import com.alibaba.druid.stat.TableStat;
import com.alibaba.druid.util.JdbcConstants;
import com.alibaba.druid.wall.WallUtils;
import org.junit.Assert;

import java.util.List;

public class OracleListAggTest1 extends OracleTest {
    private final String dbType = JdbcConstants.ORACLE;

    public void test_0() throws Exception {
        String sql = "SELECT count(0) " +
                "FROM WEP_USER T " +
                "LEFT JOIN (" +
                "   SELECT T.ID, LISTAGG(T2.ROLE_NAME, ',') WITHIN GROUP (ORDER BY T.ID) AS ROLENAMES FROM WEP_USER T " +
                "   LEFT JOIN WEP_USER_ROLE T1 ON T.ID = T1.USER_ID " +
                "   LEFT JOIN WEP_ROLE T2 ON T1.ROLE_ID = T2.ID " +
                "   WHERE t.IS_DELETED = 0 " +
                "       AND T1.IS_DELETED = 0 " +
                "       AND T2.IS_DELETED = 0 " +
                "   GROUP BY T.ID" +
                ") T1 ON T.ID = T1.ID " +
                "WHERE t.IS_DELETED = 0";

        List<SQLStatement> statementList = SQLUtils.parseStatements(sql, dbType);
        SQLStatement stmt = statementList.get(0);
        // print(statementList);

        Assert.assertEquals(1, statementList.size());

        Assert.assertEquals("SELECT COUNT(0)\n" +
                        "FROM WEP_USER T\n" +
                        "\tLEFT JOIN (\n" +
                        "\t\tSELECT T.ID, LISTAGG(T2.ROLE_NAME, ',') WITHIN GROUP (ORDER BY T.ID) AS ROLENAMES\n" +
                        "\t\tFROM WEP_USER T\n" +
                        "\t\tLEFT JOIN WEP_USER_ROLE T1 ON T.ID = T1.USER_ID \n" +
                        "\t\t\tLEFT JOIN WEP_ROLE T2 ON T1.ROLE_ID = T2.ID \n" +
                        "\t\tWHERE t.IS_DELETED = 0\n" +
                        "\t\t\tAND T1.IS_DELETED = 0\n" +
                        "\t\t\tAND T2.IS_DELETED = 0\n" +
                        "\t\tGROUP BY T.ID\n" +
                        "\t) T1 ON T.ID = T1.ID \n" +
                        "WHERE t.IS_DELETED = 0",//
                            SQLUtils.toSQLString(stmt, dbType));

        SchemaStatVisitor visitor = SQLUtils.createSchemaStatVisitor(dbType);
        stmt.accept(visitor);

        System.out.println("Tables : " + visitor.getTables());
        System.out.println("fields : " + visitor.getColumns());
        System.out.println("coditions : " + visitor.getConditions());
        System.out.println("relationships : " + visitor.getRelationships());

        Assert.assertEquals(3, visitor.getTables().size());
        Assert.assertEquals(8, visitor.getColumns().size());

        Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("WEP_USER_ROLE")));
        Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("WEP_ROLE")));

        Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("WEP_USER_ROLE", "IS_DELETED")));
        Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("WEP_ROLE", "IS_DELETED")));

        WallUtils.isValidateOracle(sql);
    }

}