Search in sources :

Example 6 with SQLServerTop

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

the class SQLSelectBuilderImpl method limit.

@Override
public SQLSelectBuilderImpl limit(int rowCount, int offset) {
    SQLSelectQueryBlock queryBlock = getQueryBlock();
    if (queryBlock instanceof MySqlSelectQueryBlock) {
        MySqlSelectQueryBlock mySqlQueryBlock = (MySqlSelectQueryBlock) queryBlock;
        SQLLimit limit = new SQLLimit();
        limit.setRowCount(new SQLIntegerExpr(rowCount));
        if (offset > 0) {
            limit.setOffset(new SQLIntegerExpr(offset));
        }
        mySqlQueryBlock.setLimit(limit);
        return this;
    }
    if (queryBlock instanceof SQLServerSelectQueryBlock) {
        SQLServerSelectQueryBlock sqlserverQueryBlock = (SQLServerSelectQueryBlock) queryBlock;
        if (offset <= 0) {
            SQLServerTop top = new SQLServerTop();
            top.setExpr(new SQLIntegerExpr(rowCount));
            sqlserverQueryBlock.setTop(top);
        } else {
            throw new UnsupportedOperationException("not support offset");
        }
        return this;
    }
    if (queryBlock instanceof PGSelectQueryBlock) {
        PGSelectQueryBlock pgQueryBlock = (PGSelectQueryBlock) queryBlock;
        SQLLimit limit = new SQLLimit();
        if (offset > 0) {
            limit.setOffset(new SQLIntegerExpr(offset));
        }
        limit.setRowCount(new SQLIntegerExpr(rowCount));
        pgQueryBlock.setLimit(limit);
        return this;
    }
    if (queryBlock instanceof DB2SelectQueryBlock) {
        DB2SelectQueryBlock db2QueryBlock = (DB2SelectQueryBlock) queryBlock;
        if (offset <= 0) {
            SQLExpr rowCountExpr = new SQLIntegerExpr(rowCount);
            db2QueryBlock.setFirst(rowCountExpr);
        } else {
            throw new UnsupportedOperationException("not support offset");
        }
        return this;
    }
    if (queryBlock instanceof OracleSelectQueryBlock) {
        OracleSelectQueryBlock oracleQueryBlock = (OracleSelectQueryBlock) queryBlock;
        if (offset <= 0) {
            SQLExpr rowCountExpr = new SQLIntegerExpr(rowCount);
            SQLExpr newCondition = SQLUtils.buildCondition(SQLBinaryOperator.BooleanAnd, rowCountExpr, false, oracleQueryBlock.getWhere());
            queryBlock.setWhere(newCondition);
        } else {
            throw new UnsupportedOperationException("not support offset");
        }
        return this;
    }
    if (queryBlock instanceof OdpsSelectQueryBlock) {
        OdpsSelectQueryBlock odpsQueryBlock = (OdpsSelectQueryBlock) queryBlock;
        if (offset > 0) {
            throw new UnsupportedOperationException("not support offset");
        }
        odpsQueryBlock.setLimit(new SQLLimit(new SQLIntegerExpr(rowCount)));
        return this;
    }
    throw new UnsupportedOperationException();
}
Also used : SQLServerTop(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerTop) OracleSelectQueryBlock(com.alibaba.druid.sql.dialect.oracle.ast.stmt.OracleSelectQueryBlock) SQLServerSelectQueryBlock(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock) SQLLimit(com.alibaba.druid.sql.ast.SQLLimit) DB2SelectQueryBlock(com.alibaba.druid.sql.dialect.db2.ast.stmt.DB2SelectQueryBlock) SQLSelectQueryBlock(com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock) SQLIntegerExpr(com.alibaba.druid.sql.ast.expr.SQLIntegerExpr) OdpsSelectQueryBlock(com.alibaba.druid.sql.dialect.odps.ast.OdpsSelectQueryBlock) MySqlSelectQueryBlock(com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock) PGSelectQueryBlock(com.alibaba.druid.sql.dialect.postgresql.ast.stmt.PGSelectQueryBlock) SQLExpr(com.alibaba.druid.sql.ast.SQLExpr)

Example 7 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() {
    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);
}
Also used : SQLServerTop(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerTop) SQLServerSelectQueryBlock(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock) SQLSelectQuery(com.alibaba.druid.sql.ast.statement.SQLSelectQuery) SQLTableSource(com.alibaba.druid.sql.ast.statement.SQLTableSource)

Example 8 with SQLServerTop

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

the class DruidSelectSqlServerParser method parseSqlServerPageSql.

private void parseSqlServerPageSql(SQLStatement stmt, RouteResultset rrs, SQLServerSelectQueryBlock sqlserverSelectQuery, SchemaConfig schema) {
    // 第一层子查询
    SQLExpr where = sqlserverSelectQuery.getWhere();
    SQLTableSource from = sqlserverSelectQuery.getFrom();
    if (sqlserverSelectQuery.getTop() != null) {
        SQLServerTop top = sqlserverSelectQuery.getTop();
        SQLExpr sqlExpr = top.getExpr();
        if (sqlExpr instanceof SQLIntegerExpr) {
            int topValue = ((SQLIntegerExpr) sqlExpr).getNumber().intValue();
            rrs.setLimitStart(0);
            rrs.setLimitSize(topValue);
        }
    } else if (where instanceof SQLBinaryOpExpr && from instanceof SQLSubqueryTableSource) {
        SQLBinaryOpExpr one = (SQLBinaryOpExpr) where;
        SQLExpr left = one.getLeft();
        SQLBinaryOperator operator = one.getOperator();
        SQLSelectQuery subSelect = ((SQLSubqueryTableSource) from).getSelect().getQuery();
        SQLOrderBy orderBy = null;
        if (subSelect instanceof SQLServerSelectQueryBlock) {
            boolean hasRowNumber = false;
            boolean hasSubTop = false;
            int subTop = 0;
            SQLServerSelectQueryBlock subSelectOracle = (SQLServerSelectQueryBlock) subSelect;
            List<SQLSelectItem> sqlSelectItems = subSelectOracle.getSelectList();
            for (SQLSelectItem sqlSelectItem : sqlSelectItems) {
                SQLExpr sqlExpr = sqlSelectItem.getExpr();
                if (sqlExpr instanceof SQLAggregateExpr) {
                    SQLAggregateExpr agg = (SQLAggregateExpr) sqlExpr;
                    if ("row_number".equalsIgnoreCase(agg.getMethodName()) && agg.getOver() != null) {
                        hasRowNumber = true;
                        orderBy = agg.getOver().getOrderBy();
                    }
                }
            }
            if (subSelectOracle.getFrom() instanceof SQLSubqueryTableSource) {
                SQLSubqueryTableSource subFrom = (SQLSubqueryTableSource) subSelectOracle.getFrom();
                if (subFrom.getSelect().getQuery() instanceof SQLServerSelectQueryBlock) {
                    SQLServerSelectQueryBlock sqlSelectQuery = (SQLServerSelectQueryBlock) subFrom.getSelect().getQuery();
                    if (sqlSelectQuery.getTop() != null) {
                        SQLExpr sqlExpr = sqlSelectQuery.getTop().getExpr();
                        if (sqlExpr instanceof SQLIntegerExpr) {
                            hasSubTop = true;
                            subTop = ((SQLIntegerExpr) sqlExpr).getNumber().intValue();
                            orderBy = subFrom.getSelect().getOrderBy();
                        }
                    }
                }
            }
            if (hasRowNumber) {
                if (hasSubTop && (operator == SQLBinaryOperator.GreaterThan || operator == SQLBinaryOperator.GreaterThanOrEqual) && one.getRight() instanceof SQLIntegerExpr) {
                    SQLIntegerExpr right = (SQLIntegerExpr) one.getRight();
                    int firstrownum = right.getNumber().intValue();
                    if (operator == SQLBinaryOperator.GreaterThanOrEqual && firstrownum != 0) {
                        firstrownum = firstrownum - 1;
                    }
                    int lastrownum = subTop;
                    setLimitIFChange(stmt, rrs, schema, one, firstrownum, lastrownum);
                    if (orderBy != null) {
                        SQLServerSelect oracleSelect = (SQLServerSelect) subSelect.getParent();
                        oracleSelect.setOrderBy(orderBy);
                    }
                    parseOrderAggGroupSqlServer(schema, stmt, rrs, (SQLServerSelectQueryBlock) subSelect);
                    isNeedParseOrderAgg = false;
                } else if ((operator == SQLBinaryOperator.LessThan || operator == SQLBinaryOperator.LessThanOrEqual) && one.getRight() instanceof SQLIntegerExpr) {
                    SQLIntegerExpr right = (SQLIntegerExpr) one.getRight();
                    int firstrownum = right.getNumber().intValue();
                    if (operator == SQLBinaryOperator.LessThan && firstrownum != 0) {
                        firstrownum = firstrownum - 1;
                    }
                    if (subSelect instanceof SQLServerSelectQueryBlock) {
                        rrs.setLimitStart(0);
                        rrs.setLimitSize(firstrownum);
                        // 为了继续解出order by 等
                        sqlserverSelectQuery = (SQLServerSelectQueryBlock) subSelect;
                        if (orderBy != null) {
                            SQLServerSelect oracleSelect = (SQLServerSelect) subSelect.getParent();
                            oracleSelect.setOrderBy(orderBy);
                        }
                        parseOrderAggGroupSqlServer(schema, stmt, rrs, sqlserverSelectQuery);
                        isNeedParseOrderAgg = false;
                    }
                } else if (operator == SQLBinaryOperator.BooleanAnd && left instanceof SQLBinaryOpExpr && one.getRight() instanceof SQLBinaryOpExpr) {
                    SQLBinaryOpExpr leftE = (SQLBinaryOpExpr) left;
                    SQLBinaryOpExpr rightE = (SQLBinaryOpExpr) one.getRight();
                    SQLBinaryOpExpr small = null;
                    SQLBinaryOpExpr larger = null;
                    int firstrownum = 0;
                    int lastrownum = 0;
                    if (leftE.getRight() instanceof SQLIntegerExpr && (leftE.getOperator() == SQLBinaryOperator.GreaterThan || leftE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual)) {
                        small = leftE;
                        firstrownum = ((SQLIntegerExpr) leftE.getRight()).getNumber().intValue();
                        if (leftE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual && firstrownum != 0) {
                            firstrownum = firstrownum - 1;
                        }
                    } else if (leftE.getRight() instanceof SQLIntegerExpr && (leftE.getOperator() == SQLBinaryOperator.LessThan || leftE.getOperator() == SQLBinaryOperator.LessThanOrEqual)) {
                        larger = leftE;
                        lastrownum = ((SQLIntegerExpr) leftE.getRight()).getNumber().intValue();
                        if (leftE.getOperator() == SQLBinaryOperator.LessThan && lastrownum != 0) {
                            lastrownum = lastrownum - 1;
                        }
                    }
                    if (rightE.getRight() instanceof SQLIntegerExpr && (rightE.getOperator() == SQLBinaryOperator.GreaterThan || rightE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual)) {
                        small = rightE;
                        firstrownum = ((SQLIntegerExpr) rightE.getRight()).getNumber().intValue();
                        if (rightE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual && firstrownum != 0) {
                            firstrownum = firstrownum - 1;
                        }
                    } else if (rightE.getRight() instanceof SQLIntegerExpr && (rightE.getOperator() == SQLBinaryOperator.LessThan || rightE.getOperator() == SQLBinaryOperator.LessThanOrEqual)) {
                        larger = rightE;
                        lastrownum = ((SQLIntegerExpr) rightE.getRight()).getNumber().intValue();
                        if (rightE.getOperator() == SQLBinaryOperator.LessThan && lastrownum != 0) {
                            lastrownum = lastrownum - 1;
                        }
                    }
                    if (small != null && larger != null) {
                        setLimitIFChange(stmt, rrs, schema, small, firstrownum, lastrownum);
                        if (orderBy != null) {
                            SQLServerSelect oracleSelect = (SQLServerSelect) subSelect.getParent();
                            oracleSelect.setOrderBy(orderBy);
                        }
                        parseOrderAggGroupSqlServer(schema, stmt, rrs, (SQLServerSelectQueryBlock) subSelect);
                        isNeedParseOrderAgg = false;
                    }
                }
            }
        }
    }
}
Also used : SQLSubqueryTableSource(com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource) SQLOrderBy(com.alibaba.druid.sql.ast.SQLOrderBy) SQLBinaryOperator(com.alibaba.druid.sql.ast.expr.SQLBinaryOperator) SQLServerSelect(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelect) SQLSelectQuery(com.alibaba.druid.sql.ast.statement.SQLSelectQuery) SQLExpr(com.alibaba.druid.sql.ast.SQLExpr) SQLTableSource(com.alibaba.druid.sql.ast.statement.SQLTableSource) SQLSelectItem(com.alibaba.druid.sql.ast.statement.SQLSelectItem) SQLServerTop(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerTop) SQLServerSelectQueryBlock(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock) SQLIntegerExpr(com.alibaba.druid.sql.ast.expr.SQLIntegerExpr) SQLBinaryOpExpr(com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr) List(java.util.List) SQLAggregateExpr(com.alibaba.druid.sql.ast.expr.SQLAggregateExpr)

Example 9 with SQLServerTop

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

the class SQLServerStatementParser method parseUpdateStatement.

public SQLUpdateStatement parseUpdateStatement() {
    SQLServerUpdateStatement udpateStatement = createUpdateStatement();
    accept(Token.UPDATE);
    SQLServerTop top = this.getExprParser().parseTop();
    if (top != null) {
        udpateStatement.setTop(top);
    }
    SQLTableSource tableSource = this.exprParser.createSelectParser().parseTableSource();
    udpateStatement.setTableSource(tableSource);
    parseUpdateSet(udpateStatement);
    SQLServerOutput output = this.getExprParser().parserOutput();
    if (output != null) {
        udpateStatement.setOutput(output);
    }
    if (lexer.token() == Token.FROM) {
        lexer.nextToken();
        SQLTableSource from = this.exprParser.createSelectParser().parseTableSource();
        udpateStatement.setFrom(from);
    }
    if (lexer.token() == (Token.WHERE)) {
        lexer.nextToken();
        udpateStatement.setWhere(this.exprParser.expr());
    }
    return udpateStatement;
}
Also used : SQLServerTop(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerTop) SQLServerOutput(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerOutput)

Example 10 with SQLServerTop

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

the class SQLServerOutputVisitor method visit.

@Override
public boolean visit(SQLServerUpdateStatement x) {
    print0(ucase ? "UPDATE " : "update ");
    SQLServerTop top = x.getTop();
    if (top != null) {
        top.accept(this);
        print(' ');
    }
    printTableSource(x.getTableSource());
    println();
    print0(ucase ? "SET " : "set ");
    for (int i = 0, size = x.getItems().size(); i < size; ++i) {
        if (i != 0) {
            print0(", ");
        }
        SQLUpdateSetItem item = x.getItems().get(i);
        visit(item);
    }
    SQLServerOutput output = x.getOutput();
    if (output != null) {
        println();
        visit(output);
    }
    SQLTableSource from = x.getFrom();
    if (from != null) {
        println();
        print0(ucase ? "FROM " : "from ");
        printTableSource(from);
    }
    SQLExpr where = x.getWhere();
    if (where != null) {
        println();
        indentCount++;
        print0(ucase ? "WHERE " : "where ");
        printExpr(where);
        indentCount--;
    }
    return false;
}
Also used : SQLServerTop(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerTop) SQLServerOutput(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerOutput)

Aggregations

SQLServerTop (com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerTop)12 SQLServerSelectQueryBlock (com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock)7 SQLSelectQuery (com.alibaba.druid.sql.ast.statement.SQLSelectQuery)4 SQLExpr (com.alibaba.druid.sql.ast.SQLExpr)3 SQLOrderBy (com.alibaba.druid.sql.ast.SQLOrderBy)3 SQLAggregateExpr (com.alibaba.druid.sql.ast.expr.SQLAggregateExpr)3 SQLBinaryOpExpr (com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr)3 SQLIntegerExpr (com.alibaba.druid.sql.ast.expr.SQLIntegerExpr)3 SQLSelectItem (com.alibaba.druid.sql.ast.statement.SQLSelectItem)3 SQLSubqueryTableSource (com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource)3 SQLTableSource (com.alibaba.druid.sql.ast.statement.SQLTableSource)3 SQLServerOutput (com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerOutput)3 SQLBinaryOperator (com.alibaba.druid.sql.ast.expr.SQLBinaryOperator)2 SQLSelectQueryBlock (com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock)2 SQLServerSelect (com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelect)2 List (java.util.List)2 SQLLimit (com.alibaba.druid.sql.ast.SQLLimit)1 SQLOver (com.alibaba.druid.sql.ast.SQLOver)1 SQLAllColumnExpr (com.alibaba.druid.sql.ast.expr.SQLAllColumnExpr)1 SQLIdentifierExpr (com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr)1