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

Example 21 with SQLSelectQuery

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

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

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

Example 24 with SQLSelectQuery

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

the class PGSelectParser method query.

@Override
public SQLSelectQuery query() {
    if (lexer.token() == Token.VALUES) {
        lexer.nextToken();
        accept(Token.LPAREN);
        PGValuesQuery valuesQuery = new PGValuesQuery();
        this.exprParser.exprList(valuesQuery.getValues(), valuesQuery);
        accept(Token.RPAREN);
        return queryRest(valuesQuery);
    }
    if (lexer.token() == Token.LPAREN) {
        lexer.nextToken();
        SQLSelectQuery select = query();
        if (select instanceof SQLSelectQueryBlock) {
            ((SQLSelectQueryBlock) select).setParenthesized(true);
        }
        accept(Token.RPAREN);
        return queryRest(select);
    }
    PGSelectQueryBlock queryBlock = new PGSelectQueryBlock();
    if (lexer.token() == Token.SELECT) {
        lexer.nextToken();
        if (lexer.token() == Token.COMMENT) {
            lexer.nextToken();
        }
        if (lexer.token() == Token.DISTINCT) {
            queryBlock.setDistionOption(SQLSetQuantifier.DISTINCT);
            lexer.nextToken();
            if (lexer.token() == Token.ON) {
                lexer.nextToken();
                for (; ; ) {
                    SQLExpr expr = this.createExprParser().expr();
                    queryBlock.getDistinctOn().add(expr);
                    if (lexer.token() == Token.COMMA) {
                        lexer.nextToken();
                        continue;
                    } else {
                        break;
                    }
                }
            }
        } else if (lexer.token() == Token.ALL) {
            queryBlock.setDistionOption(SQLSetQuantifier.ALL);
            lexer.nextToken();
        }
        parseSelectList(queryBlock);
        if (lexer.token() == Token.INTO) {
            lexer.nextToken();
            if (lexer.token() == Token.TEMPORARY) {
                lexer.nextToken();
                queryBlock.setIntoOption(IntoOption.TEMPORARY);
            } else if (lexer.token() == Token.TEMP) {
                lexer.nextToken();
                queryBlock.setIntoOption(IntoOption.TEMP);
            } else if (lexer.token() == Token.UNLOGGED) {
                lexer.nextToken();
                queryBlock.setIntoOption(IntoOption.UNLOGGED);
            }
            if (lexer.token() == Token.TABLE) {
                lexer.nextToken();
            }
            SQLExpr name = this.createExprParser().name();
            queryBlock.setInto(new SQLExprTableSource(name));
        }
    }
    parseFrom(queryBlock);
    parseWhere(queryBlock);
    parseGroupBy(queryBlock);
    if (lexer.token() == Token.WINDOW) {
        lexer.nextToken();
        PGSelectQueryBlock.WindowClause window = new PGSelectQueryBlock.WindowClause();
        window.setName(this.expr());
        accept(Token.AS);
        for (; ; ) {
            SQLExpr expr = this.createExprParser().expr();
            window.getDefinition().add(expr);
            if (lexer.token() == Token.COMMA) {
                lexer.nextToken();
                continue;
            } else {
                break;
            }
        }
        queryBlock.setWindow(window);
    }
    queryBlock.setOrderBy(this.createExprParser().parseOrderBy());
    for (; ; ) {
        if (lexer.token() == Token.LIMIT) {
            SQLLimit limit = new SQLLimit();
            lexer.nextToken();
            if (lexer.token() == Token.ALL) {
                limit.setRowCount(new SQLIdentifierExpr("ALL"));
                lexer.nextToken();
            } else {
                limit.setRowCount(expr());
            }
            queryBlock.setLimit(limit);
        } else if (lexer.token() == Token.OFFSET) {
            SQLLimit limit = queryBlock.getLimit();
            if (limit == null) {
                limit = new SQLLimit();
                queryBlock.setLimit(limit);
            }
            lexer.nextToken();
            SQLExpr offset = expr();
            limit.setOffset(offset);
            if (lexer.token() == Token.ROW || lexer.token() == Token.ROWS) {
                lexer.nextToken();
            }
        } else {
            break;
        }
    }
    if (lexer.token() == Token.FETCH) {
        lexer.nextToken();
        PGSelectQueryBlock.FetchClause fetch = new PGSelectQueryBlock.FetchClause();
        if (lexer.token() == Token.FIRST) {
            fetch.setOption(PGSelectQueryBlock.FetchClause.Option.FIRST);
        } else if (lexer.token() == Token.NEXT) {
            fetch.setOption(PGSelectQueryBlock.FetchClause.Option.NEXT);
        } else {
            throw new ParserException("expect 'FIRST' or 'NEXT'");
        }
        SQLExpr count = expr();
        fetch.setCount(count);
        if (lexer.token() == Token.ROW || lexer.token() == Token.ROWS) {
            lexer.nextToken();
        } else {
            throw new ParserException("expect 'ROW' or 'ROWS'");
        }
        if (lexer.token() == Token.ONLY) {
            lexer.nextToken();
        } else {
            throw new ParserException("expect 'ONLY'");
        }
        queryBlock.setFetch(fetch);
    }
    if (lexer.token() == Token.FOR) {
        lexer.nextToken();
        PGSelectQueryBlock.ForClause forClause = new PGSelectQueryBlock.ForClause();
        if (lexer.token() == Token.UPDATE) {
            forClause.setOption(PGSelectQueryBlock.ForClause.Option.UPDATE);
            lexer.nextToken();
        } else if (lexer.token() == Token.SHARE) {
            forClause.setOption(PGSelectQueryBlock.ForClause.Option.SHARE);
            lexer.nextToken();
        } else {
            throw new ParserException("expect 'FIRST' or 'NEXT'");
        }
        if (lexer.token() == Token.OF) {
            for (; ; ) {
                SQLExpr expr = this.createExprParser().expr();
                forClause.getOf().add(expr);
                if (lexer.token() == Token.COMMA) {
                    lexer.nextToken();
                    continue;
                } else {
                    break;
                }
            }
        }
        if (lexer.token() == Token.NOWAIT) {
            lexer.nextToken();
            forClause.setNoWait(true);
        }
        queryBlock.setForClause(forClause);
    }
    return queryRest(queryBlock);
}

Example 25 with SQLSelectQuery

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

the class SQLServerSelectParser method query.

public SQLSelectQuery query() {
    if (lexer.token() == Token.LPAREN) {
        lexer.nextToken();
        SQLSelectQuery select = query();
        accept(Token.RPAREN);
        return queryRest(select);
    }
    SQLServerSelectQueryBlock queryBlock = new SQLServerSelectQueryBlock();
    if (lexer.token() == Token.SELECT) {
        lexer.nextToken();
        if (lexer.token() == Token.COMMENT) {
            lexer.nextToken();
        }
        if (lexer.token() == Token.DISTINCT) {
            queryBlock.setDistionOption(SQLSetQuantifier.DISTINCT);
            lexer.nextToken();
        } else if (lexer.token() == Token.ALL) {
            queryBlock.setDistionOption(SQLSetQuantifier.ALL);
            lexer.nextToken();
        }
        if (lexer.token() == Token.TOP) {
            SQLServerTop top = this.createExprParser().parseTop();
            queryBlock.setTop(top);
        }
        parseSelectList(queryBlock);
    }
    if (lexer.token() == Token.INTO) {
        lexer.nextToken();
        SQLTableSource into = this.parseTableSource();
        queryBlock.setInto((SQLExprTableSource) into);
    }
    parseFrom(queryBlock);
    parseWhere(queryBlock);
    parseGroupBy(queryBlock);
    parseFetchClause(queryBlock);
    return queryRest(queryBlock);
}