use of com.alibaba.druid.sql.ast.statement.SQLSelectStatement in project druid by alibaba.
the class OraclePivotClauseTest method test_pivot_1.
public void test_pivot_1() throws Exception {
String sql = "SELECT *\n" + "FROM (SELECT EXTRACT(YEAR FROM order_date) as year, order_mode, order_total FROM orders)\n" + "PIVOT (SUM(order_total) FOR order_mode IN ('direct' AS Store, 'online' AS Internet));\n";
String expected = "SELECT *\n" + "FROM (\n\tSELECT EXTRACT(YEAR FROM order_date) AS year, order_mode, order_total\n" + "\tFROM orders\n" + ")\n" + "PIVOT (SUM(order_total) FOR order_mode IN ('direct' AS Store, 'online' AS Internet));\n";
OracleStatementParser parser = new OracleStatementParser(sql);
SQLSelectStatement stmt = (SQLSelectStatement) parser.parseStatementList().get(0);
String text = TestUtils.outputOracle(stmt);
Assert.assertEquals(expected, text);
System.out.println(text);
}
use of com.alibaba.druid.sql.ast.statement.SQLSelectStatement in project druid by alibaba.
the class SQLServerSelectTest_cross_apply method test_0.
public void test_0() throws Exception {
String sql = "SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary FROM Departments d CROSS APPLY dbo.GetReports(d.DeptMgrID)";
SQLServerStatementParser parser = new SQLServerStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
SQLSelect select = selectStmt.getSelect();
Assert.assertNotNull(select.getQuery());
SQLServerSelectQueryBlock queryBlock = (SQLServerSelectQueryBlock) select.getQuery();
Assert.assertNull(queryBlock.getGroupBy());
String fomatSQL = SQLUtils.toSQLString(statementList, JdbcUtils.SQL_SERVER);
// System.out.println(fomatSQL);
Assert.assertEquals(1, statementList.size());
SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
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(7, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getConditions().size());
Assert.assertEquals(0, visitor.getOrderByColumns().size());
String expected = //
"SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName" + //
"\n\t, EmpSalary" + //
"\nFROM Departments d" + "\n\tCROSS APPLY dbo.GetReports(d.DeptMgrID)";
Assert.assertEquals(expected, fomatSQL);
}
use of com.alibaba.druid.sql.ast.statement.SQLSelectStatement in project druid by alibaba.
the class SQLServerSelectTest_outer_apply method test_0.
public void test_0() throws Exception {
String sql = "SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary FROM Departments d OUTER APPLY dbo.GetReports(d.DeptMgrID)";
SQLServerStatementParser parser = new SQLServerStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
SQLSelect select = selectStmt.getSelect();
Assert.assertNotNull(select.getQuery());
SQLServerSelectQueryBlock queryBlock = (SQLServerSelectQueryBlock) select.getQuery();
Assert.assertNull(queryBlock.getGroupBy());
String fomatSQL = SQLUtils.toSQLString(statementList, JdbcUtils.SQL_SERVER);
// System.out.println(fomatSQL);
Assert.assertEquals(1, statementList.size());
SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
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(7, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getConditions().size());
Assert.assertEquals(0, visitor.getOrderByColumns().size());
String expected = //
"SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName" + //
"\n\t, EmpSalary" + //
"\nFROM Departments d" + "\n\tOUTER APPLY dbo.GetReports(d.DeptMgrID)";
Assert.assertEquals(expected, fomatSQL);
}
use of com.alibaba.druid.sql.ast.statement.SQLSelectStatement in project druid by alibaba.
the class MySqlMockExecuteHandlerImpl method executeQuery.
@Override
public ResultSet executeQuery(MockStatementBase statement, String sql) throws SQLException {
SQLStatementParser parser = new MySqlStatementParser(sql);
//
List<SQLStatement> stmtList = parser.parseStatementList();
if (stmtList.size() > 1) {
throw new SQLException("not support multi-statment. " + sql);
}
if (stmtList.size() == 0) {
throw new SQLException("executeQueryError : " + sql);
}
SQLStatement stmt = stmtList.get(0);
if (stmt instanceof CobarShowStatus) {
return showStatus(statement);
}
if (!(stmt instanceof SQLSelectStatement)) {
throw new SQLException("executeQueryError : " + sql);
}
SQLSelect select = ((SQLSelectStatement) stmt).getSelect();
SQLSelectQuery query = select.getQuery();
if (query instanceof SQLSelectQueryBlock) {
return executeQuery(statement, (SQLSelectQueryBlock) query);
}
throw new SQLException("TODO");
}
use of com.alibaba.druid.sql.ast.statement.SQLSelectStatement in project druid by alibaba.
the class PagerUtils method getLimit.
/**
*
* @param sql
* @param dbType
* @return if not exists limit, return -1;
*/
public static int getLimit(String sql, String dbType) {
List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);
if (stmtList.size() != 1) {
return -1;
}
SQLStatement stmt = stmtList.get(0);
if (stmt instanceof SQLSelectStatement) {
SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
SQLSelectQuery query = selectStmt.getSelect().getQuery();
if (query instanceof SQLSelectQueryBlock) {
if (query instanceof MySqlSelectQueryBlock) {
SQLLimit limit = ((MySqlSelectQueryBlock) query).getLimit();
if (limit == null) {
return -1;
}
SQLExpr rowCountExpr = limit.getRowCount();
if (rowCountExpr instanceof SQLNumericLiteralExpr) {
int rowCount = ((SQLNumericLiteralExpr) rowCountExpr).getNumber().intValue();
return rowCount;
}
return Integer.MAX_VALUE;
}
if (query instanceof OdpsSelectQueryBlock) {
SQLLimit limit = ((OdpsSelectQueryBlock) query).getLimit();
SQLExpr rowCountExpr = limit != null ? limit.getRowCount() : null;
if (rowCountExpr instanceof SQLNumericLiteralExpr) {
int rowCount = ((SQLNumericLiteralExpr) rowCountExpr).getNumber().intValue();
return rowCount;
}
return Integer.MAX_VALUE;
}
return -1;
}
}
return -1;
}
Aggregations