Examples with SQLServerTop com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerTop used on opensource projects

Example 1 with SQLServerTop

use of com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerTop in project druid by alibaba.

the class SQLServerExprParser method parseTop.

public SQLServerTop parseTop() {
    if (lexer.token() == Token.TOP) {
        SQLServerTop top = new SQLServerTop();
        lexer.nextToken();
        boolean paren = false;
        if (lexer.token() == Token.LPAREN) {
            paren = true;
            lexer.nextToken();
        }
        if (lexer.token() == Token.LITERAL_INT) {
            top.setExpr(lexer.integerValue().intValue());
            lexer.nextToken();
        } else {
            top.setExpr(primary());
        }
        if (paren) {
            accept(Token.RPAREN);
        }
        if (lexer.token() == Token.PERCENT) {
            lexer.nextToken();
            top.setPercent(true);
        }
        return top;
    }
    return null;
}

Example 2 with SQLServerTop

use of com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerTop in project druid by alibaba.

the class SQLServerSelectParser method query.

public SQLSelectQuery query(SQLObject parent, boolean acceptUnion) {
    if (lexer.token() == Token.LPAREN) {
        lexer.nextToken();
        SQLSelectQuery select = query();
        accept(Token.RPAREN);
        return queryRest(select, acceptUnion);
    }
    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);
    queryBlock.setOrderBy(this.exprParser.parseOrderBy());
    parseFetchClause(queryBlock);
    return queryRest(queryBlock, acceptUnion);
}

Example 3 with SQLServerTop

use of com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerTop in project druid by alibaba.

the class SQLServerStatementParser method parseInsert0.

protected void parseInsert0(SQLInsertInto insert, boolean acceptSubQuery) {
    SQLServerInsertStatement insertStatement = (SQLServerInsertStatement) insert;
    SQLServerTop top = this.getExprParser().parseTop();
    if (top != null) {
        insertStatement.setTop(top);
    }
    if (lexer.token() == Token.INTO) {
        lexer.nextToken();
    }
    SQLName tableName = this.exprParser.name();
    insertStatement.setTableName(tableName);
    if (lexer.token() == Token.LITERAL_ALIAS) {
        insertStatement.setAlias(tableAlias());
    }
    parseInsert0_hinits(insertStatement);
    if (lexer.token() == Token.IDENTIFIER && !lexer.stringVal().equalsIgnoreCase("OUTPUT")) {
        insertStatement.setAlias(lexer.stringVal());
        lexer.nextToken();
    }
    if (lexer.token() == (Token.LPAREN)) {
        lexer.nextToken();
        this.exprParser.exprList(insertStatement.getColumns(), insertStatement);
        accept(Token.RPAREN);
    }
    SQLServerOutput output = this.getExprParser().parserOutput();
    if (output != null) {
        insertStatement.setOutput(output);
    }
    if (lexer.token() == Token.VALUES) {
        lexer.nextToken();
        for (; ; ) {
            accept(Token.LPAREN);
            SQLInsertStatement.ValuesClause values = new SQLInsertStatement.ValuesClause();
            this.exprParser.exprList(values.getValues(), values);
            insertStatement.addValueCause(values);
            accept(Token.RPAREN);
            if (!parseCompleteValues && insertStatement.getValuesList().size() >= parseValuesSize) {
                lexer.skipToEOF();
                break;
            }
            if (lexer.token() == Token.COMMA) {
                lexer.nextToken();
                continue;
            } else {
                break;
            }
        }
    } else if (acceptSubQuery && (lexer.token() == Token.SELECT || lexer.token() == Token.LPAREN)) {
        SQLQueryExpr queryExpr = (SQLQueryExpr) this.exprParser.expr();
        insertStatement.setQuery(queryExpr.getSubQuery());
    } else if (lexer.token() == Token.DEFAULT) {
        lexer.nextToken();
        accept(Token.VALUES);
        insertStatement.setDefaultValues(true);
    }
}

Example 4 with SQLServerTop

use of com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerTop in project druid by alibaba.

the class PagerUtils method limitSQLServer.

private static boolean limitSQLServer(SQLSelect select, DbType dbType, int offset, int count, boolean check) {
    SQLSelectQuery query = select.getQuery();
    SQLBinaryOpExpr gt = new // 
    SQLBinaryOpExpr(// 
    new SQLIdentifierExpr("ROWNUM"), // 
    SQLBinaryOperator.GreaterThan, // 
    new SQLNumberExpr(offset), DbType.sqlserver);
    SQLBinaryOpExpr lteq = new // 
    SQLBinaryOpExpr(// 
    new SQLIdentifierExpr("ROWNUM"), // 
    SQLBinaryOperator.LessThanOrEqual, // 
    new SQLNumberExpr(count + offset), DbType.sqlserver);
    SQLBinaryOpExpr pageCondition = new SQLBinaryOpExpr(gt, SQLBinaryOperator.BooleanAnd, lteq, DbType.sqlserver);
    if (query instanceof SQLSelectQueryBlock) {
        SQLServerSelectQueryBlock queryBlock = (SQLServerSelectQueryBlock) query;
        if (offset <= 0) {
            SQLServerTop top = queryBlock.getTop();
            if (check && top != null && !top.isPercent() && top.getExpr() instanceof SQLNumericLiteralExpr) {
                int rowCount = ((SQLNumericLiteralExpr) top.getExpr()).getNumber().intValue();
                if (rowCount <= count) {
                    return false;
                }
            }
            queryBlock.setTop(new SQLServerTop(new SQLNumberExpr(count)));
            return true;
        }
        SQLAggregateExpr aggregateExpr = new SQLAggregateExpr("ROW_NUMBER");
        SQLOrderBy orderBy = select.getOrderBy();
        if (orderBy != null) {
            aggregateExpr.setOver(new SQLOver(orderBy));
            select.setOrderBy(null);
        } else if (queryBlock.getOrderBy() != null) {
            aggregateExpr.setOver(new SQLOver(queryBlock.getOrderBy()));
            queryBlock.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.clone(), "XX"));
        countQueryBlock.setWhere(pageCondition);
        select.setQuery(countQueryBlock);
        return true;
    }
    SQLServerSelectQueryBlock countQueryBlock = new SQLServerSelectQueryBlock();
    countQueryBlock.getSelectList().add(new SQLSelectItem(new SQLPropertyExpr(new SQLIdentifierExpr("XX"), "*")));
    countQueryBlock.setFrom(new SQLSubqueryTableSource(select.clone(), "XX"));
    if (offset <= 0) {
        countQueryBlock.setTop(new SQLServerTop(new SQLNumberExpr(count)));
        select.setQuery(countQueryBlock);
        return true;
    }
    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);
    select.setQuery(offsetQueryBlock);
    return true;
}

Example 5 with SQLServerTop

use of com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerTop 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);
}