Examples with SQLSelect com.alibaba.druid.sql.ast.statement.SQLSelect used on opensource projects

Example 16 with SQLSelect

use of com.alibaba.druid.sql.ast.statement.SQLSelect 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);
}

Example 17 with SQLSelect

use of com.alibaba.druid.sql.ast.statement.SQLSelect 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);
}

Example 18 with SQLSelect

use of com.alibaba.druid.sql.ast.statement.SQLSelect in project druid by alibaba.

the class PagerUtils method limitOracle.

private static String limitOracle(SQLSelect select, String dbType, int offset, int count) {
    SQLSelectQuery query = select.getQuery();
    if (query instanceof SQLSelectQueryBlock) {
        OracleSelectQueryBlock queryBlock = (OracleSelectQueryBlock) query;
        if (queryBlock.getGroupBy() == null && select.getOrderBy() == null && offset <= 0) {
            SQLExpr condition = new //
            SQLBinaryOpExpr(//
            new SQLIdentifierExpr("ROWNUM"), //
            SQLBinaryOperator.LessThanOrEqual, //
            new SQLNumberExpr(count), JdbcConstants.ORACLE);
            if (queryBlock.getWhere() == null) {
                queryBlock.setWhere(condition);
            } else {
                queryBlock.setWhere(new //
                SQLBinaryOpExpr(//
                queryBlock.getWhere(), //
                SQLBinaryOperator.BooleanAnd, //
                condition, JdbcConstants.ORACLE));
            }
            return SQLUtils.toSQLString(select, dbType);
        }
    }
    OracleSelectQueryBlock countQueryBlock = new OracleSelectQueryBlock();
    countQueryBlock.getSelectList().add(new SQLSelectItem(new SQLPropertyExpr(new SQLIdentifierExpr("XX"), "*")));
    countQueryBlock.getSelectList().add(new SQLSelectItem(new SQLIdentifierExpr("ROWNUM"), "RN"));
    countQueryBlock.setFrom(new SQLSubqueryTableSource(select, "XX"));
    countQueryBlock.setWhere(new //
    SQLBinaryOpExpr(//
    new SQLIdentifierExpr("ROWNUM"), //
    SQLBinaryOperator.LessThanOrEqual, //
    new SQLNumberExpr(count + offset), JdbcConstants.ORACLE));
    if (offset <= 0) {
        return SQLUtils.toSQLString(countQueryBlock, dbType);
    }
    OracleSelectQueryBlock offsetQueryBlock = new OracleSelectQueryBlock();
    offsetQueryBlock.getSelectList().add(new SQLSelectItem(new SQLAllColumnExpr()));
    offsetQueryBlock.setFrom(new SQLSubqueryTableSource(new SQLSelect(countQueryBlock), "XXX"));
    offsetQueryBlock.setWhere(new //
    SQLBinaryOpExpr(//
    new SQLIdentifierExpr("RN"), //
    SQLBinaryOperator.GreaterThan, //
    new SQLNumberExpr(offset), JdbcConstants.ORACLE));
    return SQLUtils.toSQLString(offsetQueryBlock, dbType);
}

Example 19 with SQLSelect

use of com.alibaba.druid.sql.ast.statement.SQLSelect in project druid by alibaba.

the class PagerUtils method createCountUseSubQuery.

private static String createCountUseSubQuery(SQLSelect select, String dbType) {
    SQLSelectQueryBlock countSelectQuery = createQueryBlock(dbType);
    SQLSelectItem countItem = createCountItem(dbType);
    countSelectQuery.getSelectList().add(countItem);
    SQLSubqueryTableSource fromSubquery = new SQLSubqueryTableSource(select);
    fromSubquery.setAlias("ALIAS_COUNT");
    countSelectQuery.setFrom(fromSubquery);
    SQLSelect countSelect = new SQLSelect(countSelectQuery);
    SQLSelectStatement countStmt = new SQLSelectStatement(countSelect);
    return SQLUtils.toSQLString(countStmt, dbType);
}

Example 20 with SQLSelect

use of com.alibaba.druid.sql.ast.statement.SQLSelect in project druid by alibaba.

the class PagerUtils method limitSQLServer.

private static String limitSQLServer(SQLSelect select, String dbType, int offset, int count) {
    SQLSelectQuery query = select.getQuery();
    SQLBinaryOpExpr gt = new //
    SQLBinaryOpExpr(//
    new SQLIdentifierExpr("ROWNUM"), //
    SQLBinaryOperator.GreaterThan, //
    new SQLNumberExpr(offset), JdbcConstants.SQL_SERVER);
    SQLBinaryOpExpr lteq = new //
    SQLBinaryOpExpr(//
    new SQLIdentifierExpr("ROWNUM"), //
    SQLBinaryOperator.LessThanOrEqual, //
    new SQLNumberExpr(count + offset), JdbcConstants.SQL_SERVER);
    SQLBinaryOpExpr pageCondition = new SQLBinaryOpExpr(gt, SQLBinaryOperator.BooleanAnd, lteq, JdbcConstants.SQL_SERVER);
    if (query instanceof SQLSelectQueryBlock) {
        SQLServerSelectQueryBlock queryBlock = (SQLServerSelectQueryBlock) query;
        if (offset <= 0) {
            queryBlock.setTop(new SQLServerTop(new SQLNumberExpr(count)));
            return SQLUtils.toSQLString(select, dbType);
        }
        SQLAggregateExpr aggregateExpr = new SQLAggregateExpr("ROW_NUMBER");
        SQLOrderBy orderBy = select.getOrderBy();
        aggregateExpr.setOver(new SQLOver(orderBy));
        select.setOrderBy(null);
        queryBlock.getSelectList().add(new SQLSelectItem(aggregateExpr, "ROWNUM"));
        SQLServerSelectQueryBlock countQueryBlock = new SQLServerSelectQueryBlock();
        countQueryBlock.getSelectList().add(new SQLSelectItem(new SQLAllColumnExpr()));
        countQueryBlock.setFrom(new SQLSubqueryTableSource(select, "XX"));
        countQueryBlock.setWhere(pageCondition);
        return SQLUtils.toSQLString(countQueryBlock, dbType);
    }
    SQLServerSelectQueryBlock countQueryBlock = new SQLServerSelectQueryBlock();
    countQueryBlock.getSelectList().add(new SQLSelectItem(new SQLPropertyExpr(new SQLIdentifierExpr("XX"), "*")));
    countQueryBlock.setFrom(new SQLSubqueryTableSource(select, "XX"));
    if (offset <= 0) {
        countQueryBlock.setTop(new SQLServerTop(new SQLNumberExpr(count)));
        return SQLUtils.toSQLString(countQueryBlock, dbType);
    }
    SQLAggregateExpr aggregateExpr = new SQLAggregateExpr("ROW_NUMBER");
    SQLOrderBy orderBy = select.getOrderBy();
    aggregateExpr.setOver(new SQLOver(orderBy));
    select.setOrderBy(null);
    countQueryBlock.getSelectList().add(new SQLSelectItem(aggregateExpr, "ROWNUM"));
    SQLServerSelectQueryBlock offsetQueryBlock = new SQLServerSelectQueryBlock();
    offsetQueryBlock.getSelectList().add(new SQLSelectItem(new SQLAllColumnExpr()));
    offsetQueryBlock.setFrom(new SQLSubqueryTableSource(new SQLSelect(countQueryBlock), "XXX"));
    offsetQueryBlock.setWhere(pageCondition);
    return SQLUtils.toSQLString(offsetQueryBlock, dbType);
}