Examples with DB2SelectQueryBlock com.alibaba.druid.sql.dialect.db2.ast.stmt.DB2SelectQueryBlock used on opensource projects

Example 6 with DB2SelectQueryBlock

use of com.alibaba.druid.sql.dialect.db2.ast.stmt.DB2SelectQueryBlock in project druid by alibaba.

the class DB2SelectParser method query.

@Override
public SQLSelectQuery query(SQLObject parent, boolean acceptUnion) {
    if (lexer.token() == Token.LPAREN) {
        lexer.nextToken();
        SQLSelectQuery select = query();
        accept(Token.RPAREN);
        return queryRest(select, acceptUnion);
    }
    accept(Token.SELECT);
    if (lexer.token() == Token.COMMENT) {
        lexer.nextToken();
    }
    DB2SelectQueryBlock queryBlock = new DB2SelectQueryBlock();
    if (lexer.token() == Token.DISTINCT) {
        queryBlock.setDistionOption(SQLSetQuantifier.DISTINCT);
        lexer.nextToken();
    } else if (lexer.token() == Token.UNIQUE) {
        queryBlock.setDistionOption(SQLSetQuantifier.UNIQUE);
        lexer.nextToken();
    } else if (lexer.token() == Token.ALL) {
        queryBlock.setDistionOption(SQLSetQuantifier.ALL);
        lexer.nextToken();
    }
    parseSelectList(queryBlock);
    if (lexer.token() == Token.INTO) {
        lexer.nextToken();
        SQLExpr expr = expr();
        if (lexer.token() != Token.COMMA) {
            queryBlock.setInto(expr);
        }
    }
    parseFrom(queryBlock);
    parseWhere(queryBlock);
    parseHierachical(queryBlock);
    parseGroupBy(queryBlock);
    if (lexer.token() == Token.ORDER) {
        SQLOrderBy orderBy = parseOrderBy();
        queryBlock.setOrderBy(orderBy);
    }
    for (; ; ) {
        if (lexer.token() == Token.FETCH) {
            lexer.nextToken();
            accept(Token.FIRST);
            SQLExpr first = this.exprParser.primary();
            queryBlock.setFirst(first);
            if (lexer.identifierEquals("ROW") || lexer.identifierEquals("ROWS")) {
                lexer.nextToken();
            }
            accept(Token.ONLY);
            continue;
        }
        if (lexer.token() == Token.WITH) {
            lexer.nextToken();
            if (lexer.identifierEquals("RR")) {
                queryBlock.setIsolation(Isolation.RR);
            } else if (lexer.identifierEquals("RS")) {
                queryBlock.setIsolation(Isolation.RS);
            } else if (lexer.identifierEquals("CS")) {
                queryBlock.setIsolation(Isolation.CS);
            } else if (lexer.identifierEquals("UR")) {
                queryBlock.setIsolation(Isolation.UR);
            } else {
                throw new ParserException("TODO. " + lexer.info());
            }
            lexer.nextToken();
            continue;
        }
        if (lexer.token() == Token.FOR) {
            lexer.nextToken();
            if (lexer.token() == Token.UPDATE) {
                queryBlock.setForUpdate(true);
                lexer.nextToken();
            } else {
                acceptIdentifier("READ");
                accept(Token.ONLY);
                queryBlock.setForReadOnly(true);
            }
        }
        if (lexer.token() == Token.OPTIMIZE) {
            lexer.nextToken();
            accept(Token.FOR);
            queryBlock.setOptimizeFor(this.expr());
            if (lexer.identifierEquals("ROW")) {
                lexer.nextToken();
            } else {
                acceptIdentifier("ROWS");
            }
        }
        break;
    }
    return queryRest(queryBlock, acceptUnion);
}

Example 7 with DB2SelectQueryBlock

use of com.alibaba.druid.sql.dialect.db2.ast.stmt.DB2SelectQueryBlock 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();
}

Example 8 with DB2SelectQueryBlock

use of com.alibaba.druid.sql.dialect.db2.ast.stmt.DB2SelectQueryBlock in project Mycat_plus by coderczp.

the class DruidSelectParser method parseAggGroupCommon.

protected Map<String, String> parseAggGroupCommon(SchemaConfig schema, SQLStatement stmt, RouteResultset rrs, SQLSelectQueryBlock mysqlSelectQuery) {
    Map<String, String> aliaColumns = new HashMap<String, String>();
    Map<String, Integer> aggrColumns = new HashMap<String, Integer>();
    // Added by winbill, 20160314, for having clause, Begin ==>
    List<String> havingColsName = new ArrayList<String>();
    // Added by winbill, 20160314, for having clause, End  <==
    List<SQLSelectItem> selectList = mysqlSelectQuery.getSelectList();
    boolean isNeedChangeSql = false;
    int size = selectList.size();
    boolean isDistinct = mysqlSelectQuery.getDistionOption() == 2;
    for (int i = 0; i < size; i++) {
        SQLSelectItem item = selectList.get(i);
        if (item.getExpr() instanceof SQLAggregateExpr) {
            SQLAggregateExpr expr = (SQLAggregateExpr) item.getExpr();
            String method = expr.getMethodName();
            boolean isHasArgument = !expr.getArguments().isEmpty();
            if (isHasArgument) {
                // Added by winbill, 20160314, for having clause
                String aggrColName = method + "(" + expr.getArguments().get(0) + ")";
                // Added by winbill, 20160314, for having clause
                havingColsName.add(aggrColName);
            }
            // 只处理有别名的情况，无别名添加别名，否则某些数据库会得不到正确结果处理
            int mergeType = MergeCol.getMergeType(method);
            if (MergeCol.MERGE_AVG == mergeType && isRoutMultiNode(schema, rrs)) {
                // 跨分片avg需要特殊处理，直接avg结果是不对的
                String colName = item.getAlias() != null ? item.getAlias() : method + i;
                SQLSelectItem sum = new SQLSelectItem();
                String sumColName = colName + "SUM";
                sum.setAlias(sumColName);
                SQLAggregateExpr sumExp = new SQLAggregateExpr("SUM");
                ObjectUtil.copyProperties(expr, sumExp);
                sumExp.getArguments().addAll(expr.getArguments());
                sumExp.setMethodName("SUM");
                sum.setExpr(sumExp);
                selectList.set(i, sum);
                aggrColumns.put(sumColName, MergeCol.MERGE_SUM);
                // Added by winbill, 20160314, for having clause
                havingColsName.add(sumColName);
                // Added by winbill, 20160314, two aliases for AVG
                havingColsName.add(item.getAlias() != null ? item.getAlias() : "");
                SQLSelectItem count = new SQLSelectItem();
                String countColName = colName + "COUNT";
                count.setAlias(countColName);
                SQLAggregateExpr countExp = new SQLAggregateExpr("COUNT");
                ObjectUtil.copyProperties(expr, countExp);
                countExp.getArguments().addAll(expr.getArguments());
                countExp.setMethodName("COUNT");
                count.setExpr(countExp);
                selectList.add(count);
                aggrColumns.put(countColName, MergeCol.MERGE_COUNT);
                isNeedChangeSql = true;
                aggrColumns.put(colName, mergeType);
                rrs.setHasAggrColumn(true);
            } else if (MergeCol.MERGE_UNSUPPORT != mergeType) {
                String aggColName = null;
                StringBuilder sb = new StringBuilder();
                if (mysqlSelectQuery instanceof MySqlSelectQueryBlock) {
                    expr.accept(new MySqlOutputVisitor(sb));
                } else if (mysqlSelectQuery instanceof OracleSelectQueryBlock) {
                    expr.accept(new OracleOutputVisitor(sb));
                } else if (mysqlSelectQuery instanceof PGSelectQueryBlock) {
                    expr.accept(new PGOutputVisitor(sb));
                } else if (mysqlSelectQuery instanceof SQLServerSelectQueryBlock) {
                    expr.accept(new SQLASTOutputVisitor(sb));
                } else if (mysqlSelectQuery instanceof DB2SelectQueryBlock) {
                    expr.accept(new DB2OutputVisitor(sb));
                }
                aggColName = sb.toString();
                if (item.getAlias() != null && item.getAlias().length() > 0) {
                    aggrColumns.put(item.getAlias(), mergeType);
                    aliaColumns.put(aggColName, item.getAlias());
                } else {
                    // 如果不加，jdbc方式时取不到正确结果   ;修改添加别名
                    item.setAlias(method + i);
                    aggrColumns.put(method + i, mergeType);
                    aliaColumns.put(aggColName, method + i);
                    isNeedChangeSql = true;
                }
                rrs.setHasAggrColumn(true);
                // Added by winbill, 20160314, for having clause
                havingColsName.add(item.getAlias());
                // Added by winbill, 20160314, one alias for non-AVG
                havingColsName.add("");
            }
        } else {
            if (!(item.getExpr() instanceof SQLAllColumnExpr)) {
                String alia = item.getAlias();
                String field = getFieldName(item);
                if (alia == null) {
                    alia = field;
                }
                aliaColumns.put(field, alia);
            }
        }
    }
    if (aggrColumns.size() > 0) {
        rrs.setMergeCols(aggrColumns);
    }
    // 通过优化转换成group by来实现
    if (isDistinct) {
        mysqlSelectQuery.setDistionOption(0);
        SQLSelectGroupByClause groupBy = new SQLSelectGroupByClause();
        for (String fieldName : aliaColumns.keySet()) {
            groupBy.addItem(new SQLIdentifierExpr(fieldName));
        }
        mysqlSelectQuery.setGroupBy(groupBy);
        isNeedChangeSql = true;
    }
    // setGroupByCols
    if (mysqlSelectQuery.getGroupBy() != null) {
        List<SQLExpr> groupByItems = mysqlSelectQuery.getGroupBy().getItems();
        String[] groupByCols = buildGroupByCols(groupByItems, aliaColumns);
        rrs.setGroupByCols(groupByCols);
        rrs.setHavings(buildGroupByHaving(mysqlSelectQuery.getGroupBy().getHaving(), aliaColumns));
        rrs.setHasAggrColumn(true);
        // Added by winbill, 20160314, for having clause
        rrs.setHavingColsName(havingColsName.toArray());
    }
    if (isNeedChangeSql) {
        String sql = stmt.toString();
        rrs.changeNodeSqlAfterAddLimit(schema, getCurentDbType(), sql, 0, -1, false);
        getCtx().setSql(sql);
    }
    return aliaColumns;
}

Example 9 with DB2SelectQueryBlock

use of com.alibaba.druid.sql.dialect.db2.ast.stmt.DB2SelectQueryBlock in project druid by alibaba.

the class Issue2038 method test_for_demo.

public void test_for_demo() throws Exception {
    String sql = "select * from (select * from t union all select * from t1 union all select * from t3) xx";
    List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, DbType.db2);
    SQLASTVisitor visitor = new DB2ASTVisitorAdapter() {

        public boolean visit(SQLUnionQuery x) {
            System.out.println("union");
            return true;
        }

        public boolean visit(DB2SelectQueryBlock x) {
            System.out.println("select");
            return true;
        }
    };
    for (SQLStatement stmt : stmtList) {
        stmt.accept(visitor);
    }
}

Example 10 with DB2SelectQueryBlock

use of com.alibaba.druid.sql.dialect.db2.ast.stmt.DB2SelectQueryBlock in project druid by alibaba.

the class PagerUtils method limitDB2.

private static boolean limitDB2(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.db2);
    SQLBinaryOpExpr lteq = new // 
    SQLBinaryOpExpr(// 
    new SQLIdentifierExpr("ROWNUM"), // 
    SQLBinaryOperator.LessThanOrEqual, // 
    new SQLNumberExpr(count + offset), DbType.db2);
    SQLBinaryOpExpr pageCondition = new SQLBinaryOpExpr(gt, SQLBinaryOperator.BooleanAnd, lteq, DbType.db2);
    if (query instanceof SQLSelectQueryBlock) {
        DB2SelectQueryBlock queryBlock = (DB2SelectQueryBlock) query;
        if (offset <= 0) {
            SQLExpr first = queryBlock.getFirst();
            if (check && first != null && first instanceof SQLNumericLiteralExpr) {
                int rowCount = ((SQLNumericLiteralExpr) first).getNumber().intValue();
                if (rowCount < count) {
                    return false;
                }
            }
            queryBlock.setFirst(new SQLIntegerExpr(count));
            return true;
        }
        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.clone(), "XX"));
        countQueryBlock.setWhere(pageCondition);
        select.setQuery(countQueryBlock);
        return true;
    }
    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.clone(), "XX"));
    if (offset <= 0) {
        select.setQuery(countQueryBlock);
        return true;
    }
    DB2SelectQueryBlock offsetQueryBlock = new DB2SelectQueryBlock();
    offsetQueryBlock.getSelectList().add(new SQLSelectItem(new SQLAllColumnExpr()));
    offsetQueryBlock.setFrom(new SQLSubqueryTableSource(new SQLSelect(countQueryBlock), "XXX"));
    offsetQueryBlock.setWhere(pageCondition);
    select.setQuery(offsetQueryBlock);
    return true;
}