Search in sources :

Example 31 with SQLSelectStatement

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);
}
Also used : SQLSelectStatement(com.alibaba.druid.sql.ast.statement.SQLSelectStatement) OracleStatementParser(com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser)

Example 32 with SQLSelectStatement

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);
}
Also used : SQLServerSchemaStatVisitor(com.alibaba.druid.sql.dialect.sqlserver.visitor.SQLServerSchemaStatVisitor) SQLServerStatementParser(com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser) SQLSelect(com.alibaba.druid.sql.ast.statement.SQLSelect) SQLServerSelectQueryBlock(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock) SQLSelectStatement(com.alibaba.druid.sql.ast.statement.SQLSelectStatement) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement)

Example 33 with SQLSelectStatement

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);
}
Also used : SQLServerSchemaStatVisitor(com.alibaba.druid.sql.dialect.sqlserver.visitor.SQLServerSchemaStatVisitor) SQLServerStatementParser(com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser) SQLSelect(com.alibaba.druid.sql.ast.statement.SQLSelect) SQLServerSelectQueryBlock(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock) SQLSelectStatement(com.alibaba.druid.sql.ast.statement.SQLSelectStatement) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement)

Example 34 with SQLSelectStatement

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");
}
Also used : SQLStatementParser(com.alibaba.druid.sql.parser.SQLStatementParser) SQLException(java.sql.SQLException) SQLSelect(com.alibaba.druid.sql.ast.statement.SQLSelect) CobarShowStatus(com.alibaba.druid.sql.dialect.mysql.ast.statement.CobarShowStatus) SQLSelectQuery(com.alibaba.druid.sql.ast.statement.SQLSelectQuery) SQLSelectStatement(com.alibaba.druid.sql.ast.statement.SQLSelectStatement) SQLSelectQueryBlock(com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock) MySqlStatementParser(com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement)

Example 35 with SQLSelectStatement

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;
}
Also used : SQLNumericLiteralExpr(com.alibaba.druid.sql.ast.expr.SQLNumericLiteralExpr) SQLLimit(com.alibaba.druid.sql.ast.SQLLimit) SQLSelectQuery(com.alibaba.druid.sql.ast.statement.SQLSelectQuery) SQLSelectStatement(com.alibaba.druid.sql.ast.statement.SQLSelectStatement) SQLSelectQueryBlock(com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock) OdpsSelectQueryBlock(com.alibaba.druid.sql.dialect.odps.ast.OdpsSelectQueryBlock) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) MySqlSelectQueryBlock(com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock) SQLExpr(com.alibaba.druid.sql.ast.SQLExpr)

Aggregations

SQLSelectStatement (com.alibaba.druid.sql.ast.statement.SQLSelectStatement)78 OracleStatementParser (com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser)39 SQLStatement (com.alibaba.druid.sql.ast.SQLStatement)25 MySqlSelectQueryBlock (com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock)23 SQLSelect (com.alibaba.druid.sql.ast.statement.SQLSelect)21 MySqlStatementParser (com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser)17 MySqlSchemaStatVisitor (com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor)15 SQLSelectQuery (com.alibaba.druid.sql.ast.statement.SQLSelectQuery)14 SQLSelectQueryBlock (com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock)10 SQLServerStatementParser (com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser)6 SQLExpr (com.alibaba.druid.sql.ast.SQLExpr)4 SQLBinaryOpExpr (com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr)4 SQLStatementParser (com.alibaba.druid.sql.parser.SQLStatementParser)4 SQLServerSelectQueryBlock (com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock)3 SQLDeleteStatement (com.alibaba.druid.sql.ast.statement.SQLDeleteStatement)2 SQLSelectItem (com.alibaba.druid.sql.ast.statement.SQLSelectItem)2 SQLSubqueryTableSource (com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource)2 SQLTableSource (com.alibaba.druid.sql.ast.statement.SQLTableSource)2 SQLUpdateStatement (com.alibaba.druid.sql.ast.statement.SQLUpdateStatement)2 Limit (com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock.Limit)2