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

Example 41 with SQLSelectStatement

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

the class OracleToMySqlOutputVisitor method visit.

public boolean visit(OracleSelectQueryBlock x) {
    boolean parentIsSelectStatment = false;
    {
        if (x.getParent() instanceof SQLSelect) {
            SQLSelect select = (SQLSelect) x.getParent();
            if (select.getParent() instanceof SQLSelectStatement || select.getParent() instanceof SQLSubqueryTableSource) {
                parentIsSelectStatment = true;
            }
        }
    }
    if (!parentIsSelectStatment) {
        return super.visit(x);
    }
    if (//
    x.getWhere() instanceof SQLBinaryOpExpr && //
    x.getFrom() instanceof SQLSubqueryTableSource) {
        int rownum;
        String ident;
        SQLBinaryOpExpr where = (SQLBinaryOpExpr) x.getWhere();
        if (where.getRight() instanceof SQLIntegerExpr && where.getLeft() instanceof SQLIdentifierExpr) {
            rownum = ((SQLIntegerExpr) where.getRight()).getNumber().intValue();
            ident = ((SQLIdentifierExpr) where.getLeft()).getName();
        } else {
            return super.visit(x);
        }
        SQLSelect select = ((SQLSubqueryTableSource) x.getFrom()).getSelect();
        SQLSelectQueryBlock queryBlock = null;
        SQLSelect subSelect = null;
        SQLBinaryOpExpr subWhere = null;
        boolean isSubQueryRowNumMapping = false;
        if (select.getQuery() instanceof SQLSelectQueryBlock) {
            queryBlock = (SQLSelectQueryBlock) select.getQuery();
            if (queryBlock.getWhere() instanceof SQLBinaryOpExpr) {
                subWhere = (SQLBinaryOpExpr) queryBlock.getWhere();
            }
            for (SQLSelectItem selectItem : queryBlock.getSelectList()) {
                if (isRowNumber(selectItem.getExpr())) {
                    if (where.getLeft() instanceof SQLIdentifierExpr && ((SQLIdentifierExpr) where.getLeft()).getName().equals(selectItem.getAlias())) {
                        isSubQueryRowNumMapping = true;
                    }
                }
            }
            SQLTableSource subTableSource = queryBlock.getFrom();
            if (subTableSource instanceof SQLSubqueryTableSource) {
                subSelect = ((SQLSubqueryTableSource) subTableSource).getSelect();
            }
        }
        if ("ROWNUM".equalsIgnoreCase(ident)) {
            SQLBinaryOperator op = where.getOperator();
            Integer limit = null;
            if (op == SQLBinaryOperator.LessThanOrEqual) {
                limit = rownum;
            } else if (op == SQLBinaryOperator.LessThan) {
                limit = rownum - 1;
            }
            if (limit != null) {
                select.accept(this);
                println();
                print0(ucase ? "LIMIT " : "limit ");
                print(limit);
                return false;
            }
        } else if (isSubQueryRowNumMapping) {
            SQLBinaryOperator op = where.getOperator();
            SQLBinaryOperator subOp = subWhere.getOperator();
            if (//
            isRowNumber(subWhere.getLeft()) && subWhere.getRight() instanceof SQLIntegerExpr) {
                int subRownum = ((SQLIntegerExpr) subWhere.getRight()).getNumber().intValue();
                Integer offset = null;
                if (op == SQLBinaryOperator.GreaterThanOrEqual) {
                    offset = rownum + 1;
                } else if (op == SQLBinaryOperator.GreaterThan) {
                    offset = rownum;
                }
                if (offset != null) {
                    Integer limit = null;
                    if (subOp == SQLBinaryOperator.LessThanOrEqual) {
                        limit = subRownum - offset;
                    } else if (subOp == SQLBinaryOperator.LessThan) {
                        limit = subRownum - 1 - offset;
                    }
                    if (limit != null) {
                        subSelect.accept(this);
                        println();
                        print0(ucase ? "LIMIT " : "limit ");
                        print(offset);
                        print0(", ");
                        print(limit);
                        return false;
                    }
                }
            }
        }
    }
    return super.visit(x);
}

Example 42 with SQLSelectStatement

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

the class DefaultDruidParser method visitorParse.

/**
	 * 子类可覆盖（如果该方法解析得不到表名、字段等信息的，就覆盖该方法，覆盖成空方法，然后通过statementPparse去解析）
	 * 通过visitor解析：有些类型的Statement通过visitor解析得不到表名、
	 * @param stmt
	 */
@Override
public void visitorParse(RouteResultset rrs, SQLStatement stmt, MycatSchemaStatVisitor visitor) throws SQLNonTransientException {
    stmt.accept(visitor);
    ctx.setVisitor(visitor);
    if (stmt instanceof SQLSelectStatement) {
        SQLSelectQuery query = ((SQLSelectStatement) stmt).getSelect().getQuery();
        if (query instanceof MySqlSelectQueryBlock) {
            if (((MySqlSelectQueryBlock) query).isForUpdate()) {
                rrs.setSelectForUpdate(true);
            }
        }
    }
    List<List<Condition>> mergedConditionList = new ArrayList<List<Condition>>();
    if (visitor.hasOrCondition()) {
        //包含or语句
        //TODO
        //根据or拆分
        mergedConditionList = visitor.splitConditions();
    } else {
        //不包含OR语句
        mergedConditionList.add(visitor.getConditions());
    }
    if (visitor.getAliasMap() != null) {
        for (Map.Entry<String, String> entry : visitor.getAliasMap().entrySet()) {
            String key = entry.getKey();
            String value = entry.getValue();
            if (key != null && key.indexOf("`") >= 0) {
                key = key.replaceAll("`", "");
            }
            if (value != null && value.indexOf("`") >= 0) {
                value = value.replaceAll("`", "");
            }
            //表名前面带database的，去掉
            if (key != null) {
                int pos = key.indexOf(".");
                if (pos > 0) {
                    key = key.substring(pos + 1);
                }
                tableAliasMap.put(key.toUpperCase(), value);
            }
        //				else {
        //					tableAliasMap.put(key, value);
        //				}
        }
        ctx.addTables(visitor.getTables());
        visitor.getAliasMap().putAll(tableAliasMap);
        ctx.setTableAliasMap(tableAliasMap);
    }
    ctx.setRouteCalculateUnits(this.buildRouteCalculateUnits(visitor, mergedConditionList));
}

Example 43 with SQLSelectStatement

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

the class DruidSelectOracleParser method statementParse.

@Override
public void statementParse(SchemaConfig schema, RouteResultset rrs, SQLStatement stmt) {
    SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
    SQLSelectQuery sqlSelectQuery = selectStmt.getSelect().getQuery();
    //从mysql解析过来
    if (sqlSelectQuery instanceof MySqlSelectQueryBlock) {
        MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock) selectStmt.getSelect().getQuery();
        Limit limit = mysqlSelectQuery.getLimit();
        if (limit == null) {
            //使用oracle的解析，否则会有部分oracle语法识别错误
            OracleStatementParser oracleParser = new OracleStatementParser(getCtx().getSql());
            SQLSelectStatement oracleStmt = (SQLSelectStatement) oracleParser.parseStatement();
            selectStmt = oracleStmt;
            SQLSelectQuery oracleSqlSelectQuery = oracleStmt.getSelect().getQuery();
            if (oracleSqlSelectQuery instanceof OracleSelectQueryBlock) {
                parseNativePageSql(oracleStmt, rrs, (OracleSelectQueryBlock) oracleSqlSelectQuery, schema);
            }
        }
        if (isNeedParseOrderAgg) {
            parseOrderAggGroupMysql(schema, selectStmt, rrs, mysqlSelectQuery);
            //更改canRunInReadDB属性
            if ((mysqlSelectQuery.isForUpdate() || mysqlSelectQuery.isLockInShareMode()) && rrs.isAutocommit() == false) {
                rrs.setCanRunInReadDB(false);
            }
        }
    }
}

Example 44 with SQLSelectStatement

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

the class DruidSelectParser method changeSql.

/**
	 * 改写sql：需要加limit的加上
	 */
@Override
public void changeSql(SchemaConfig schema, RouteResultset rrs, SQLStatement stmt, LayerCachePool cachePool) throws SQLNonTransientException {
    tryRoute(schema, rrs, cachePool);
    rrs.copyLimitToNodes();
    SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
    SQLSelectQuery sqlSelectQuery = selectStmt.getSelect().getQuery();
    if (sqlSelectQuery instanceof MySqlSelectQueryBlock) {
        MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock) selectStmt.getSelect().getQuery();
        int limitStart = 0;
        int limitSize = schema.getDefaultMaxLimit();
        //clear group having
        SQLSelectGroupByClause groupByClause = mysqlSelectQuery.getGroupBy();
        // Modified by winbill, 20160614, do NOT include having clause when routing to multiple nodes
        if (groupByClause != null && groupByClause.getHaving() != null && isRoutMultiNode(schema, rrs)) {
            groupByClause.setHaving(null);
        }
        Map<String, Map<String, Set<ColumnRoutePair>>> allConditions = getAllConditions();
        boolean isNeedAddLimit = isNeedAddLimit(schema, rrs, mysqlSelectQuery, allConditions);
        if (isNeedAddLimit) {
            Limit limit = new Limit();
            limit.setRowCount(new SQLIntegerExpr(limitSize));
            mysqlSelectQuery.setLimit(limit);
            rrs.setLimitSize(limitSize);
            String sql = getSql(rrs, stmt, isNeedAddLimit);
            rrs.changeNodeSqlAfterAddLimit(schema, getCurentDbType(), sql, 0, limitSize, true);
        }
        Limit limit = mysqlSelectQuery.getLimit();
        if (limit != null && !isNeedAddLimit) {
            SQLIntegerExpr offset = (SQLIntegerExpr) limit.getOffset();
            SQLIntegerExpr count = (SQLIntegerExpr) limit.getRowCount();
            if (offset != null) {
                limitStart = offset.getNumber().intValue();
                rrs.setLimitStart(limitStart);
            }
            if (count != null) {
                limitSize = count.getNumber().intValue();
                rrs.setLimitSize(limitSize);
            }
            if (isNeedChangeLimit(rrs)) {
                Limit changedLimit = new Limit();
                changedLimit.setRowCount(new SQLIntegerExpr(limitStart + limitSize));
                if (offset != null) {
                    if (limitStart < 0) {
                        String msg = "You have an error in your SQL syntax; check the manual that " + "corresponds to your MySQL server version for the right syntax to use near '" + limitStart + "'";
                        throw new SQLNonTransientException(ErrorCode.ER_PARSE_ERROR + " - " + msg);
                    } else {
                        changedLimit.setOffset(new SQLIntegerExpr(0));
                    }
                }
                mysqlSelectQuery.setLimit(changedLimit);
                String sql = getSql(rrs, stmt, isNeedAddLimit);
                rrs.changeNodeSqlAfterAddLimit(schema, getCurentDbType(), sql, 0, limitStart + limitSize, true);
                //设置改写后的sql
                ctx.setSql(sql);
            } else {
                rrs.changeNodeSqlAfterAddLimit(schema, getCurentDbType(), getCtx().getSql(), rrs.getLimitStart(), rrs.getLimitSize(), true);
            //	ctx.setSql(nativeSql);
            }
        }
        if (rrs.isDistTable()) {
            SQLTableSource from = mysqlSelectQuery.getFrom();
            for (RouteResultsetNode node : rrs.getNodes()) {
                SQLIdentifierExpr sqlIdentifierExpr = new SQLIdentifierExpr();
                sqlIdentifierExpr.setParent(from);
                sqlIdentifierExpr.setName(node.getSubTableName());
                SQLExprTableSource from2 = new SQLExprTableSource(sqlIdentifierExpr);
                mysqlSelectQuery.setFrom(from2);
                node.setStatement(stmt.toString());
            }
        }
        rrs.setCacheAble(isNeedCache(schema, rrs, mysqlSelectQuery, allConditions));
    }
}

Example 45 with SQLSelectStatement

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

the class DruidSelectParser method statementParse.

@Override
public void statementParse(SchemaConfig schema, RouteResultset rrs, SQLStatement stmt) {
    SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
    SQLSelectQuery sqlSelectQuery = selectStmt.getSelect().getQuery();
    if (sqlSelectQuery instanceof MySqlSelectQueryBlock) {
        MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock) selectStmt.getSelect().getQuery();
        parseOrderAggGroupMysql(schema, stmt, rrs, mysqlSelectQuery);
        //更改canRunInReadDB属性
        if ((mysqlSelectQuery.isForUpdate() || mysqlSelectQuery.isLockInShareMode()) && rrs.isAutocommit() == false) {
            rrs.setCanRunInReadDB(false);
        }
    } else if (sqlSelectQuery instanceof MySqlUnionQuery) {
    //			MySqlUnionQuery unionQuery = (MySqlUnionQuery)sqlSelectQuery;
    //			MySqlSelectQueryBlock left = (MySqlSelectQueryBlock)unionQuery.getLeft();
    //			MySqlSelectQueryBlock right = (MySqlSelectQueryBlock)unionQuery.getLeft();
    //			System.out.println();
    }
}