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

Example 6 with SQLSelectItem

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

the class PagerUtils method limitDB2.

private static String limitDB2(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.DB2);
    SQLBinaryOpExpr lteq = new //
    SQLBinaryOpExpr(//
    new SQLIdentifierExpr("ROWNUM"), //
    SQLBinaryOperator.LessThanOrEqual, //
    new SQLNumberExpr(count + offset), JdbcConstants.DB2);
    SQLBinaryOpExpr pageCondition = new SQLBinaryOpExpr(gt, SQLBinaryOperator.BooleanAnd, lteq, JdbcConstants.DB2);
    if (query instanceof SQLSelectQueryBlock) {
        DB2SelectQueryBlock queryBlock = (DB2SelectQueryBlock) query;
        if (offset <= 0) {
            queryBlock.setFirst(new SQLNumberExpr(count));
            return SQLUtils.toSQLString(select, dbType);
        }
        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, "XX"));
        countQueryBlock.setWhere(pageCondition);
        return SQLUtils.toSQLString(countQueryBlock, dbType);
    }
    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, "XX"));
    if (offset <= 0) {
        return SQLUtils.toSQLString(countQueryBlock, dbType);
    }
    DB2SelectQueryBlock offsetQueryBlock = new DB2SelectQueryBlock();
    offsetQueryBlock.getSelectList().add(new SQLSelectItem(new SQLAllColumnExpr()));
    offsetQueryBlock.setFrom(new SQLSubqueryTableSource(new SQLSelect(countQueryBlock), "XXX"));
    offsetQueryBlock.setWhere(pageCondition);
    return SQLUtils.toSQLString(offsetQueryBlock, dbType);
}

Example 7 with SQLSelectItem

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

the class SQLUtils method toSelectItem.

public static SQLSelectItem toSelectItem(String sql, String dbType) {
    SQLExprParser parser = SQLParserUtils.createExprParser(sql, dbType);
    SQLSelectItem selectItem = parser.parseSelectItem();
    if (parser.getLexer().token() != Token.EOF) {
        throw new ParserException("illegal sql expr : " + sql);
    }
    return selectItem;
}

Example 8 with SQLSelectItem

use of com.alibaba.druid.sql.ast.statement.SQLSelectItem in project Mycat_plus by coderczp.

the class JoinParser method parserFields.

private void parserFields(List<SQLSelectItem> mysqlSelectList) {
    // 显示的字段
    String key = "";
    String value = "";
    String exprfield = "";
    for (SQLSelectItem item : mysqlSelectList) {
        if (item.getExpr() instanceof SQLAllColumnExpr) {
            // *解析
            setField(item.toString(), item.toString());
        } else {
            if (item.getExpr() instanceof SQLAggregateExpr) {
                SQLAggregateExpr expr = (SQLAggregateExpr) item.getExpr();
                key = getExprFieldName(expr);
                setField(key, value);
            } else if (item.getExpr() instanceof SQLMethodInvokeExpr) {
                key = getMethodInvokeFieldName(item);
                exprfield = getFieldName(item);
                // value=item.getAlias();
                setField(key, value, exprfield);
            } else {
                key = getFieldName(item);
                value = item.getAlias();
                setField(key, value);
            }
        }
    }
}

Example 9 with SQLSelectItem

use of com.alibaba.druid.sql.ast.statement.SQLSelectItem in project Mycat_plus by coderczp.

the class MycatSchemaStatVisitor method visit.

/* 
     *  遇到 all 将子查询改写成  SELECT MAX(name) FROM subtest1
     *  例如:
     *        select * from subtest where id > all (select name from subtest1);
     *    		>/>= all ----> >/>= max
     *    		</<= all ----> </<= min
     *    		<>   all ----> not in
     *          =    all ----> id = 1 and id = 2
     *          other  不改写
     */
@Override
public boolean visit(SQLAllExpr x) {
    setSubQueryRelationOrFlag(x);
    List<SQLSelectItem> itemlist = ((SQLSelectQueryBlock) (x.getSubQuery().getQuery())).getSelectList();
    SQLExpr sexpr = itemlist.get(0).getExpr();
    if (x.getParent() instanceof SQLBinaryOpExpr) {
        SQLBinaryOpExpr parentExpr = (SQLBinaryOpExpr) x.getParent();
        SQLAggregateExpr saexpr = null;
        switch(parentExpr.getOperator()) {
            case GreaterThan:
            case GreaterThanOrEqual:
            case NotLessThan:
                this.hasChange = true;
                if (sexpr instanceof SQLIdentifierExpr || (sexpr instanceof SQLPropertyExpr && ((SQLPropertyExpr) sexpr).getOwner() instanceof SQLIdentifierExpr)) {
                    saexpr = new SQLAggregateExpr("MAX");
                    saexpr.getArguments().add(sexpr);
                    saexpr.setParent(itemlist.get(0));
                    itemlist.get(0).setExpr(saexpr);
                }
                SQLQueryExpr maxSubQuery = new SQLQueryExpr(x.getSubQuery());
                x.getSubQuery().setParent(x.getParent());
                // 生成新的SQLQueryExpr 替换当前 SQLAllExpr 节点
                if (x.getParent() instanceof SQLBinaryOpExpr) {
                    if (((SQLBinaryOpExpr) x.getParent()).getLeft().equals(x)) {
                        ((SQLBinaryOpExpr) x.getParent()).setLeft(maxSubQuery);
                    } else if (((SQLBinaryOpExpr) x.getParent()).getRight().equals(x)) {
                        ((SQLBinaryOpExpr) x.getParent()).setRight(maxSubQuery);
                    }
                }
                addSubQuerys(x.getSubQuery());
                return super.visit(x.getSubQuery());
            case LessThan:
            case LessThanOrEqual:
            case NotGreaterThan:
                this.hasChange = true;
                if (sexpr instanceof SQLIdentifierExpr || (sexpr instanceof SQLPropertyExpr && ((SQLPropertyExpr) sexpr).getOwner() instanceof SQLIdentifierExpr)) {
                    saexpr = new SQLAggregateExpr("MIN");
                    saexpr.getArguments().add(sexpr);
                    saexpr.setParent(itemlist.get(0));
                    itemlist.get(0).setExpr(saexpr);
                    x.subQuery.setParent(x.getParent());
                }
                // 生成新的SQLQueryExpr 替换当前 SQLAllExpr 节点
                SQLQueryExpr minSubQuery = new SQLQueryExpr(x.getSubQuery());
                if (x.getParent() instanceof SQLBinaryOpExpr) {
                    if (((SQLBinaryOpExpr) x.getParent()).getLeft().equals(x)) {
                        ((SQLBinaryOpExpr) x.getParent()).setLeft(minSubQuery);
                    } else if (((SQLBinaryOpExpr) x.getParent()).getRight().equals(x)) {
                        ((SQLBinaryOpExpr) x.getParent()).setRight(minSubQuery);
                    }
                }
                addSubQuerys(x.getSubQuery());
                return super.visit(x.getSubQuery());
            case LessThanOrGreater:
            case NotEqual:
                this.hasChange = true;
                SQLInSubQueryExpr notInSubQueryExpr = new SQLInSubQueryExpr(x.getSubQuery());
                x.getSubQuery().setParent(notInSubQueryExpr);
                notInSubQueryExpr.setNot(true);
                // 生成新的SQLQueryExpr 替换当前 SQLAllExpr 节点
                if (x.getParent() instanceof SQLBinaryOpExpr) {
                    SQLBinaryOpExpr xp = (SQLBinaryOpExpr) x.getParent();
                    if (xp.getLeft().equals(x)) {
                        notInSubQueryExpr.setExpr(xp.getRight());
                    } else if (xp.getRight().equals(x)) {
                        notInSubQueryExpr.setExpr(xp.getLeft());
                    }
                    if (xp.getParent() instanceof MySqlSelectQueryBlock) {
                        ((MySqlSelectQueryBlock) xp.getParent()).setWhere(notInSubQueryExpr);
                    } else if (xp.getParent() instanceof SQLBinaryOpExpr) {
                        SQLBinaryOpExpr pp = ((SQLBinaryOpExpr) xp.getParent());
                        if (pp.getLeft().equals(xp)) {
                            pp.setLeft(notInSubQueryExpr);
                        } else if (pp.getRight().equals(xp)) {
                            pp.setRight(notInSubQueryExpr);
                        }
                    }
                }
                addSubQuerys(x.getSubQuery());
                return super.visit(notInSubQueryExpr);
            default:
                break;
        }
    }
    addSubQuerys(x.getSubQuery());
    return super.visit(x);
}

Example 10 with SQLSelectItem

use of com.alibaba.druid.sql.ast.statement.SQLSelectItem in project Mycat-Server by MyCATApache.

the class JoinParser method parserFields.

private void parserFields(List<SQLSelectItem> mysqlSelectList) {
    // 显示的字段
    String key = "";
    String value = "";
    String exprfield = "";
    for (SQLSelectItem item : mysqlSelectList) {
        if (item.getExpr() instanceof SQLAllColumnExpr) {
            // *解析
            setField(item.toString(), item.toString());
        } else {
            if (item.getExpr() instanceof SQLAggregateExpr) {
                SQLAggregateExpr expr = (SQLAggregateExpr) item.getExpr();
                key = getExprFieldName(expr);
                setField(key, value);
            } else if (item.getExpr() instanceof SQLMethodInvokeExpr) {
                key = getMethodInvokeFieldName(item);
                exprfield = getFieldName(item);
                // value=item.getAlias();
                setField(key, value, exprfield);
            } else {
                key = getFieldName(item);
                value = item.getAlias();
                setField(key, value);
            }
        }
    }
}