use of com.alibaba.druid.sql.dialect.db2.parser.DB2StatementParser in project druid by alibaba.
the class DB2SelectTest_10 method test_0.
public void test_0() throws Exception {
String sql = "select * from ( select inner2_.*, rownumber() over(order by order of inner2_) as rownumber_ from (" + " select tsuserstb0_.USERID as USERID1_1_, tsuserstb0_.CREASTAF as CREASTAF2_1_, tsuserstb0_.CREATIME as CREATIME3_1_" + " , tsuserstb0_.LOCORGNO as LOCORGNO4_1_, tsuserstb0_.PWDMODIFYTIME as PWDMODIF5_1_, tsuserstb0_.REMARK01 as REMARK6_1_" + " , tsuserstb0_.REMARK02 as REMARK7_1_, tsuserstb0_.STAORGNO as STAORGNO8_1_, tsuserstb0_.UPDASTAF as UPDASTAF9_1_" + " , tsuserstb0_.UPDATIME as UPDATIM10_1_, tsuserstb0_.USERNAME as USERNAM11_1_, tsuserstb0_.USERPWD as USERPWD12_1_" + " , tsuserstb0_.USERSTATE as USERSTA13_1_ from TS_USERSTB tsuserstb0_ fetch first 20 rows only ) as inner2_ " + ") as inner1_" + " where rownumber_ > 10 order by rownumber_";
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(13, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getConditions().size());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("TS_USERSTB")));
// 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 *" + "\nFROM (SELECT inner2_.*, ROWNUMBER() OVER (ORDER BY order OF inner2_) AS rownumber_" + "\n\tFROM (SELECT tsuserstb0_.USERID AS USERID1_1_, tsuserstb0_.CREASTAF AS CREASTAF2_1_, tsuserstb0_.CREATIME AS CREATIME3_1_, tsuserstb0_.LOCORGNO AS LOCORGNO4_1_, tsuserstb0_.PWDMODIFYTIME AS PWDMODIF5_1_" + "\n\t\t\t, tsuserstb0_.REMARK01 AS REMARK6_1_, tsuserstb0_.REMARK02 AS REMARK7_1_, tsuserstb0_.STAORGNO AS STAORGNO8_1_, tsuserstb0_.UPDASTAF AS UPDASTAF9_1_, tsuserstb0_.UPDATIME AS UPDATIM10_1_" + "\n\t\t\t, tsuserstb0_.USERNAME AS USERNAM11_1_, tsuserstb0_.USERPWD AS USERPWD12_1_, tsuserstb0_.USERSTATE AS USERSTA13_1_" + "\n\t\tFROM TS_USERSTB tsuserstb0_" + "\n\t\tFETCH FIRST 20 ROWS ONLY" + "\n\t\t) inner2_" + "\n\t) inner1_" + "\nWHERE rownumber_ > 10" + //
"\nORDER BY rownumber_", SQLUtils.toSQLString(stmt, JdbcConstants.DB2));
Assert.assertEquals("select *" + "\nfrom (select inner2_.*, rownumber() over (order by order of inner2_) as rownumber_" + "\n\tfrom (select tsuserstb0_.USERID as USERID1_1_, tsuserstb0_.CREASTAF as CREASTAF2_1_, tsuserstb0_.CREATIME as CREATIME3_1_, tsuserstb0_.LOCORGNO as LOCORGNO4_1_, tsuserstb0_.PWDMODIFYTIME as PWDMODIF5_1_" + "\n\t\t\t, tsuserstb0_.REMARK01 as REMARK6_1_, tsuserstb0_.REMARK02 as REMARK7_1_, tsuserstb0_.STAORGNO as STAORGNO8_1_, tsuserstb0_.UPDASTAF as UPDASTAF9_1_, tsuserstb0_.UPDATIME as UPDATIM10_1_" + "\n\t\t\t, tsuserstb0_.USERNAME as USERNAM11_1_, tsuserstb0_.USERPWD as USERPWD12_1_, tsuserstb0_.USERSTATE as USERSTA13_1_" + "\n\t\tfrom TS_USERSTB tsuserstb0_" + "\n\t\tfetch first 20 rows only" + "\n\t\t) inner2_" + "\n\t) inner1_" + "\nwhere rownumber_ > 10" + //
"\norder by rownumber_", SQLUtils.toSQLString(stmt, JdbcConstants.DB2, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
}
use of com.alibaba.druid.sql.dialect.db2.parser.DB2StatementParser in project druid by alibaba.
the class DB2SelectTest_12 method test_0.
public void test_0() throws Exception {
String sql = "SELECT * FROM TASK_POLICE_QUERY_BATCH WHERE STATUS = '0' ORDER BY PRIORITY FETCH FIRST 100 ROWS ONLY";
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(2, visitor.getColumns().size());
Assert.assertEquals(1, visitor.getConditions().size());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("TASK_POLICE_QUERY_BATCH")));
Assert.assertTrue(visitor.getColumns().contains(new Column("TASK_POLICE_QUERY_BATCH", "STATUS")));
// Assert.assertTrue(visitor.getColumns().contains(new Column("mytable", "first_name")));
// Assert.assertTrue(visitor.getColumns().contains(new Column("mytable", "full_name")));
Assert.assertEquals("SELECT *" + "\nFROM TASK_POLICE_QUERY_BATCH" + "\nWHERE STATUS = '0'" + "\nORDER BY PRIORITY" + //
"\nFETCH FIRST 100 ROWS ONLY", SQLUtils.toSQLString(stmt, JdbcConstants.DB2));
Assert.assertEquals("select *" + "\nfrom TASK_POLICE_QUERY_BATCH" + "\nwhere STATUS = '0'" + "\norder by PRIORITY" + //
"\nfetch first 100 rows only", SQLUtils.toSQLString(stmt, JdbcConstants.DB2, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
}
use of com.alibaba.druid.sql.dialect.db2.parser.DB2StatementParser in project druid by alibaba.
the class DB2SelectTest_13 method test_0.
public void test_0() throws Exception {
String sql = //
"SELECT WORKDEPT, EMPNO, SALARY, BONUS, COMM " + //
" FROM DSN8B10.EMP" + //
" WHERE WORKDEPT IN ('D11','D21')" + " FOR UPDATE;";
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(5, visitor.getColumns().size());
Assert.assertEquals(1, visitor.getConditions().size());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("DSN8B10.EMP")));
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("SELECT WORKDEPT, EMPNO, SALARY, BONUS, COMM" + "\nFROM DSN8B10.EMP" + "\nWHERE WORKDEPT IN ('D11', 'D21')" + //
"\nFOR UPDATE", SQLUtils.toSQLString(stmt, JdbcConstants.DB2));
Assert.assertEquals("select WORKDEPT, EMPNO, SALARY, BONUS, COMM" + "\nfrom DSN8B10.EMP" + "\nwhere WORKDEPT in ('D11', 'D21')" + //
"\nfor update", SQLUtils.toSQLString(stmt, JdbcConstants.DB2, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
}
use of com.alibaba.druid.sql.dialect.db2.parser.DB2StatementParser in project druid by alibaba.
the class DB2SelectTest_14 method test_0.
public void test_0() throws Exception {
String sql = "SELECT (NEXTVAL FOR TEST_SEQ) FROM SYSIBM.SYSDUMMY1";
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(0, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getConditions().size());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("SYSIBM.SYSDUMMY1")));
// 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("SELECT TEST_SEQ.NEXTVAL" + //
"\nFROM SYSIBM.SYSDUMMY1", SQLUtils.toSQLString(stmt, JdbcConstants.DB2));
Assert.assertEquals("select TEST_SEQ.nextval" + //
"\nfrom SYSIBM.SYSDUMMY1", SQLUtils.toSQLString(stmt, JdbcConstants.DB2, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
}
use of com.alibaba.druid.sql.dialect.db2.parser.DB2StatementParser in project druid by alibaba.
the class DB2SelectTest_16 method test_0.
public void test_0() throws Exception {
String sql = "SELECT (CURRVAL FOR TEST_SEQ) FROM SYSIBM.SYSDUMMY1";
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(0, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getConditions().size());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("SYSIBM.SYSDUMMY1")));
// 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("SELECT TEST_SEQ.CURRVAL" + //
"\nFROM SYSIBM.SYSDUMMY1", SQLUtils.toSQLString(stmt, JdbcConstants.DB2));
Assert.assertEquals("select TEST_SEQ.currval" + //
"\nfrom SYSIBM.SYSDUMMY1", SQLUtils.toSQLString(stmt, JdbcConstants.DB2, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
}
Aggregations