Examples with DB2StatementParser com.alibaba.druid.sql.dialect.db2.parser.DB2StatementParser used on opensource projects

Example 1 with DB2StatementParser

use of com.alibaba.druid.sql.dialect.db2.parser.DB2StatementParser in project Mycat-Server by MyCATApache.

the class PageSQLUtil method convertLimitToNativePageSql.

public static String convertLimitToNativePageSql(String dbType, String sql, int offset, int count) {
    if (JdbcConstants.ORACLE.equalsIgnoreCase(dbType)) {
        OracleStatementParser oracleParser = new OracleStatementParser(sql);
        SQLSelectStatement oracleStmt = (SQLSelectStatement) oracleParser.parseStatement();
        return PagerUtils.limit(oracleStmt.getSelect(), JdbcConstants.ORACLE, offset, count);
    } else if (JdbcConstants.SQL_SERVER.equalsIgnoreCase(dbType)) {
        SQLServerStatementParser oracleParser = new SQLServerStatementParser(sql);
        SQLSelectStatement sqlserverStmt = (SQLSelectStatement) oracleParser.parseStatement();
        SQLSelect select = sqlserverStmt.getSelect();
        SQLOrderBy orderBy = select.getOrderBy();
        if (orderBy == null) {
            SQLSelectQuery sqlSelectQuery = select.getQuery();
            if (sqlSelectQuery instanceof SQLServerSelectQueryBlock) {
                SQLServerSelectQueryBlock sqlServerSelectQueryBlock = (SQLServerSelectQueryBlock) sqlSelectQuery;
                SQLTableSource from = sqlServerSelectQueryBlock.getFrom();
                if ("limit".equalsIgnoreCase(from.getAlias())) {
                    from.setAlias(null);
                }
            }
            SQLOrderBy newOrderBy = new SQLOrderBy(new SQLIdentifierExpr("(select 0)"));
            select.setOrderBy(newOrderBy);
        }
        return PagerUtils.limit(select, JdbcConstants.SQL_SERVER, offset, count);
    } else if (JdbcConstants.DB2.equalsIgnoreCase(dbType)) {
        DB2StatementParser db2Parser = new DB2StatementParser(sql);
        SQLSelectStatement db2Stmt = (SQLSelectStatement) db2Parser.parseStatement();
        return limitDB2(db2Stmt.getSelect(), JdbcConstants.DB2, offset, count);
    } else if (JdbcConstants.POSTGRESQL.equalsIgnoreCase(dbType)) {
        PGSQLStatementParser pgParser = new PGSQLStatementParser(sql);
        SQLSelectStatement pgStmt = (SQLSelectStatement) pgParser.parseStatement();
        SQLSelect select = pgStmt.getSelect();
        SQLSelectQuery query = select.getQuery();
        if (query instanceof PGSelectQueryBlock) {
            PGSelectQueryBlock pgSelectQueryBlock = (PGSelectQueryBlock) query;
            pgSelectQueryBlock.setOffset(null);
            pgSelectQueryBlock.setLimit(null);
        }
        return PagerUtils.limit(select, JdbcConstants.POSTGRESQL, offset, count);
    } else if (JdbcConstants.MYSQL.equalsIgnoreCase(dbType)) {
        MySqlStatementParser pgParser = new MySqlStatementParser(sql);
        SQLSelectStatement pgStmt = (SQLSelectStatement) pgParser.parseStatement();
        SQLSelect select = pgStmt.getSelect();
        SQLSelectQuery query = select.getQuery();
        if (query instanceof MySqlSelectQueryBlock) {
            MySqlSelectQueryBlock pgSelectQueryBlock = (MySqlSelectQueryBlock) query;
            pgSelectQueryBlock.setLimit(null);
        }
        return PagerUtils.limit(select, JdbcConstants.MYSQL, offset, count);
    }
    return sql;
}

Example 2 with DB2StatementParser

use of com.alibaba.druid.sql.dialect.db2.parser.DB2StatementParser in project druid by alibaba.

the class DB2DropSequenceTest_0 method test_0.

public void test_0() throws Exception {
    String sql = "drop sequence sales1_seq";
    DB2StatementParser parser = new DB2StatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    print(statementList);
    Assert.assertEquals(1, statementList.size());
    DB2SchemaStatVisitor visitor = new DB2SchemaStatVisitor();
    stmt.accept(visitor);
    //        System.out.println("Tables : " + visitor.getTables());
    //        System.out.println("fields : " + visitor.getColumns());
    //        System.out.println("coditions : " + visitor.getConditions());
    //        System.out.println("orderBy : " + visitor.getOrderByColumns());
    Assert.assertEquals(0, visitor.getTables().size());
    Assert.assertEquals(0, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());
    //        Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("A")));
    //         Assert.assertTrue(visitor.getColumns().contains(new Column("A", "F_0201")));
    // Assert.assertTrue(visitor.getColumns().contains(new Column("mytable", "first_name")));
    // Assert.assertTrue(visitor.getColumns().contains(new Column("mytable", "full_name")));
    //
    Assert.assertEquals(//
    "DROP SEQUENCE sales1_seq", SQLUtils.toSQLString(stmt, JdbcConstants.DB2));
    //
    Assert.assertEquals(//
    "drop sequence sales1_seq", SQLUtils.toSQLString(stmt, JdbcConstants.DB2, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
}

Example 3 with DB2StatementParser

use of com.alibaba.druid.sql.dialect.db2.parser.DB2StatementParser in project druid by alibaba.

the class DB2MergeTest method test_0.

public void test_0() throws Exception {
    String sql = "MERGE INTO product AS T " + "      USING sales AS S" + "    ON S.id = T.id" + "  WHEN MATCHED THEN UPDATE SET inventory = T.inventory - S.sold;";
    DB2StatementParser parser = new DB2StatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    print(statementList);
    Assert.assertEquals(1, statementList.size());
    DB2SchemaStatVisitor visitor = new DB2SchemaStatVisitor();
    stmt.accept(visitor);
    //        System.out.println("Tables : " + visitor.getTables());
    //        System.out.println("fields : " + visitor.getColumns());
    //        System.out.println("coditions : " + visitor.getConditions());
    //        System.out.println("orderBy : " + visitor.getOrderByColumns());
    Assert.assertEquals(2, visitor.getTables().size());
    Assert.assertEquals(4, visitor.getColumns().size());
    Assert.assertEquals(2, visitor.getConditions().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("sales")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("product")));
    //         Assert.assertTrue(visitor.getColumns().contains(new Column("DSN8B10.EMP", "WORKDEPT")));
    // Assert.assertTrue(visitor.getColumns().contains(new Column("mytable", "first_name")));
    // Assert.assertTrue(visitor.getColumns().contains(new Column("mytable", "full_name")));
    Assert.assertEquals("MERGE INTO product T" + "\nUSING sales S ON (S.id = T.id) " + //
    "\nWHEN MATCHED THEN UPDATE SET inventory = T.inventory - S.sold", SQLUtils.toSQLString(stmt, JdbcConstants.DB2));
    Assert.assertEquals("merge into product T" + "\nusing sales S on (S.id = T.id) " + //
    "\nwhen matched then update set inventory = T.inventory - S.sold", SQLUtils.toSQLString(stmt, JdbcConstants.DB2, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
}

Example 4 with DB2StatementParser

use of com.alibaba.druid.sql.dialect.db2.parser.DB2StatementParser in project druid by alibaba.

the class DB2SelectTest_6 method test_0.

public void test_0() throws Exception {
    String sql = "SELECT all id FROM DSN81010.EMP;";
    DB2StatementParser parser = new DB2StatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    print(statementList);
    Assert.assertEquals(1, statementList.size());
    DB2SchemaStatVisitor visitor = new DB2SchemaStatVisitor();
    stmt.accept(visitor);
    //        System.out.println("Tables : " + visitor.getTables());
    //        System.out.println("fields : " + visitor.getColumns());
    //        System.out.println("coditions : " + visitor.getConditions());
    //        System.out.println("orderBy : " + visitor.getOrderByColumns());
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(1, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("DSN81010.EMP")));
    // Assert.assertTrue(visitor.getColumns().contains(new Column("mytable", "last_name")));
    // Assert.assertTrue(visitor.getColumns().contains(new Column("mytable", "first_name")));
    // Assert.assertTrue(visitor.getColumns().contains(new Column("mytable", "full_name")));
    Assert.assertEquals(//
    "SELECT ALL id" + //
    "\nFROM DSN81010.EMP", SQLUtils.toSQLString(stmt, JdbcConstants.DB2));
    Assert.assertEquals(//
    "select all id" + //
    "\nfrom DSN81010.EMP", SQLUtils.toSQLString(stmt, JdbcConstants.DB2, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
}

Example 5 with DB2StatementParser

use of com.alibaba.druid.sql.dialect.db2.parser.DB2StatementParser in project druid by alibaba.

the class DB2SelectTest_7 method test_0.

public void test_0() throws Exception {
    String sql = "SELECT distinct id FROM DSN81010.EMP;";
    DB2StatementParser parser = new DB2StatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    print(statementList);
    Assert.assertEquals(1, statementList.size());
    DB2SchemaStatVisitor visitor = new DB2SchemaStatVisitor();
    stmt.accept(visitor);
    //        System.out.println("Tables : " + visitor.getTables());
    //        System.out.println("fields : " + visitor.getColumns());
    //        System.out.println("coditions : " + visitor.getConditions());
    //        System.out.println("orderBy : " + visitor.getOrderByColumns());
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(1, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("DSN81010.EMP")));
    // Assert.assertTrue(visitor.getColumns().contains(new Column("mytable", "last_name")));
    // Assert.assertTrue(visitor.getColumns().contains(new Column("mytable", "first_name")));
    // Assert.assertTrue(visitor.getColumns().contains(new Column("mytable", "full_name")));
    Assert.assertEquals(//
    "SELECT DISTINCT id" + //
    "\nFROM DSN81010.EMP", SQLUtils.toSQLString(stmt, JdbcConstants.DB2));
    Assert.assertEquals(//
    "select distinct id" + //
    "\nfrom DSN81010.EMP", SQLUtils.toSQLString(stmt, JdbcConstants.DB2, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
}