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

Example 11 with SQLSelectQueryBlock

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

the class PagerUtils method limitDB2.

private static String limitDB2(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.DB2);
    SQLBinaryOpExpr lteq = new //
    SQLBinaryOpExpr(//
    new SQLIdentifierExpr("ROWNUM"), //
    SQLBinaryOperator.LessThanOrEqual, //
    new SQLNumberExpr(count + offset), JdbcConstants.DB2);
    SQLBinaryOpExpr pageCondition = new SQLBinaryOpExpr(gt, SQLBinaryOperator.BooleanAnd, lteq, JdbcConstants.DB2);
    if (query instanceof SQLSelectQueryBlock) {
        DB2SelectQueryBlock queryBlock = (DB2SelectQueryBlock) query;
        if (offset <= 0) {
            queryBlock.setFirst(new SQLNumberExpr(count));
            return SQLUtils.toSQLString(select, dbType);
        }
        SQLAggregateExpr aggregateExpr = new SQLAggregateExpr("ROW_NUMBER");
        SQLOrderBy orderBy = select.getOrderBy();
        if (orderBy == null && select.getQuery() instanceof SQLSelectQueryBlock) {
            SQLSelectQueryBlock selectQueryBlcok = (SQLSelectQueryBlock) select.getQuery();
            orderBy = selectQueryBlcok.getOrderBy();
            selectQueryBlcok.setOrderBy(null);
        } else {
            select.setOrderBy(null);
        }
        aggregateExpr.setOver(new SQLOver(orderBy));
        queryBlock.getSelectList().add(new SQLSelectItem(aggregateExpr, "ROWNUM"));
        DB2SelectQueryBlock countQueryBlock = new DB2SelectQueryBlock();
        countQueryBlock.getSelectList().add(new SQLSelectItem(new SQLAllColumnExpr()));
        countQueryBlock.setFrom(new SQLSubqueryTableSource(select, "XX"));
        countQueryBlock.setWhere(pageCondition);
        return SQLUtils.toSQLString(countQueryBlock, dbType);
    }
    DB2SelectQueryBlock countQueryBlock = new DB2SelectQueryBlock();
    countQueryBlock.getSelectList().add(new SQLSelectItem(new SQLPropertyExpr(new SQLIdentifierExpr("XX"), "*")));
    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"));
    countQueryBlock.setFrom(new SQLSubqueryTableSource(select, "XX"));
    if (offset <= 0) {
        return SQLUtils.toSQLString(countQueryBlock, dbType);
    }
    DB2SelectQueryBlock offsetQueryBlock = new DB2SelectQueryBlock();
    offsetQueryBlock.getSelectList().add(new SQLSelectItem(new SQLAllColumnExpr()));
    offsetQueryBlock.setFrom(new SQLSubqueryTableSource(new SQLSelect(countQueryBlock), "XXX"));
    offsetQueryBlock.setWhere(pageCondition);
    return SQLUtils.toSQLString(offsetQueryBlock, dbType);
}

Example 12 with SQLSelectQueryBlock

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

the class PagerUtils method clearOrderBy.

private static void clearOrderBy(SQLSelectQuery query) {
    if (query instanceof SQLSelectQueryBlock) {
        SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) query;
        if (queryBlock instanceof MySqlSelectQueryBlock) {
            MySqlSelectQueryBlock mysqlQueryBlock = (MySqlSelectQueryBlock) queryBlock;
            if (mysqlQueryBlock.getOrderBy() != null) {
                mysqlQueryBlock.setOrderBy(null);
            }
        } else if (queryBlock instanceof PGSelectQueryBlock) {
            PGSelectQueryBlock pgQueryBlock = (PGSelectQueryBlock) queryBlock;
            if (pgQueryBlock.getOrderBy() != null) {
                pgQueryBlock.setOrderBy(null);
            }
        }
        return;
    }
    if (query instanceof SQLUnionQuery) {
        SQLUnionQuery union = (SQLUnionQuery) query;
        if (union.getOrderBy() != null) {
            union.setOrderBy(null);
        }
        clearOrderBy(union.getLeft());
        clearOrderBy(union.getRight());
    }
}

Example 13 with SQLSelectQueryBlock

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

Example 14 with SQLSelectQueryBlock

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

the class SQLUtils method addSelectItem.

public static void addSelectItem(SQLStatement stmt, SQLExpr expr, String alias, boolean first) {
    if (expr == null) {
        return;
    }
    if (stmt instanceof SQLSelectStatement) {
        SQLSelectQuery query = ((SQLSelectStatement) stmt).getSelect().getQuery();
        if (query instanceof SQLSelectQueryBlock) {
            SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) query;
            addSelectItem(queryBlock, expr, alias, first);
        } else {
            throw new IllegalArgumentException("add condition not support " + stmt.getClass().getName());
        }
        return;
    }
    throw new IllegalArgumentException("add selectItem not support " + stmt.getClass().getName());
}

Example 15 with SQLSelectQueryBlock

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