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

Example 16 with SQLSelectQueryBlock

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

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

the class PagerUtils method count.

private static String count(SQLSelect select, String dbType) {
    if (select.getOrderBy() != null) {
        select.setOrderBy(null);
    }
    SQLSelectQuery query = select.getQuery();
    clearOrderBy(query);
    if (query instanceof SQLSelectQueryBlock) {
        SQLSelectItem countItem = createCountItem(dbType);
        SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) query;
        if (queryBlock.getGroupBy() != null && queryBlock.getGroupBy().getItems().size() > 0) {
            return createCountUseSubQuery(select, dbType);
        }
        int option = queryBlock.getDistionOption();
        if (option == SQLSetQuantifier.DISTINCT && queryBlock.getSelectList().size() == 1) {
            SQLSelectItem firstItem = queryBlock.getSelectList().get(0);
            SQLAggregateExpr exp = new SQLAggregateExpr("COUNT", SQLAggregateOption.DISTINCT);
            exp.addArgument(firstItem.getExpr());
            firstItem.setExpr(exp);
            queryBlock.setDistionOption(0);
        } else {
            queryBlock.getSelectList().clear();
            queryBlock.getSelectList().add(countItem);
        }
        return SQLUtils.toSQLString(select, dbType);
    } else if (query instanceof SQLUnionQuery) {
        return createCountUseSubQuery(select, dbType);
    }
    throw new IllegalStateException();
}

Example 18 with SQLSelectQueryBlock

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

Example 19 with SQLSelectQueryBlock

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

the class SQLUtils method addCondition.

public static void addCondition(SQLStatement stmt, SQLBinaryOperator op, SQLExpr condition, boolean left) {
    if (stmt instanceof SQLSelectStatement) {
        SQLSelectQuery query = ((SQLSelectStatement) stmt).getSelect().getQuery();
        if (query instanceof SQLSelectQueryBlock) {
            SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) query;
            SQLExpr newCondition = buildCondition(op, condition, left, queryBlock.getWhere());
            queryBlock.setWhere(newCondition);
        } else {
            throw new IllegalArgumentException("add condition not support " + stmt.getClass().getName());
        }
        return;
    }
    if (stmt instanceof SQLDeleteStatement) {
        SQLDeleteStatement delete = (SQLDeleteStatement) stmt;
        SQLExpr newCondition = buildCondition(op, condition, left, delete.getWhere());
        delete.setWhere(newCondition);
        return;
    }
    if (stmt instanceof SQLUpdateStatement) {
        SQLUpdateStatement update = (SQLUpdateStatement) stmt;
        SQLExpr newCondition = buildCondition(op, condition, left, update.getWhere());
        update.setWhere(newCondition);
        return;
    }
    throw new IllegalArgumentException("add condition not support " + stmt.getClass().getName());
}

Example 20 with SQLSelectQueryBlock

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

the class SQLSelectBuilderImpl method getQueryBlock.

protected SQLSelectQueryBlock getQueryBlock() {
    SQLSelect select = getSQLSelect();
    SQLSelectQuery query = select.getQuery();
    if (query == null) {
        query = createSelectQueryBlock();
        select.setQuery(query);
    }
    if (!(query instanceof SQLSelectQueryBlock)) {
        throw new IllegalStateException("not support from, class : " + query.getClass().getName());
    }
    SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) query;
    return queryBlock;
}