Search in sources :

Example 6 with OracleOutputVisitor

use of com.alibaba.druid.sql.dialect.oracle.visitor.OracleOutputVisitor in project druid by alibaba.

the class OracleOutputVisitorTest_forupdate method test_0.

public void test_0() throws Exception {
    String sql = "SELECT salary from employee for update";
    OracleStatementParser parser = new OracleStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    Assert.assertEquals(1, statementList.size());
    OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
    stmt.accept(visitor);
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(true, visitor.containsTable("employee"));
    Assert.assertEquals(1, visitor.getColumns().size());
    Assert.assertEquals(true, visitor.getColumns().contains(new Column("employee", "salary")));
    StringBuilder buf = new StringBuilder();
    OracleOutputVisitor outputVisitor = new OracleOutputVisitor(buf);
    stmt.accept(outputVisitor);
    Assert.assertEquals("SELECT salary\nFROM employee\nFOR UPDATE", buf.toString());
}
Also used : OracleOutputVisitor(com.alibaba.druid.sql.dialect.oracle.visitor.OracleOutputVisitor) Column(com.alibaba.druid.stat.TableStat.Column) OracleSchemaStatVisitor(com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) OracleStatementParser(com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser)

Example 7 with OracleOutputVisitor

use of com.alibaba.druid.sql.dialect.oracle.visitor.OracleOutputVisitor in project druid by alibaba.

the class OracleOutputVisitorTest_orderBy method test_0.

public void test_0() throws Exception {
    String sql = "SELECT salary from employee order by name";
    OracleStatementParser parser = new OracleStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    Assert.assertEquals(1, statementList.size());
    OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
    stmt.accept(visitor);
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(true, visitor.containsTable("employee"));
    Assert.assertEquals(2, visitor.getColumns().size());
    Assert.assertEquals(true, visitor.getColumns().contains(new Column("employee", "salary")));
    Assert.assertEquals(true, visitor.getColumns().contains(new Column("employee", "name")));
    StringBuilder buf = new StringBuilder();
    OracleOutputVisitor outputVisitor = new OracleOutputVisitor(buf);
    stmt.accept(outputVisitor);
    Assert.assertEquals("SELECT salary\nFROM employee\nORDER BY name", buf.toString());
}
Also used : OracleOutputVisitor(com.alibaba.druid.sql.dialect.oracle.visitor.OracleOutputVisitor) Column(com.alibaba.druid.stat.TableStat.Column) OracleSchemaStatVisitor(com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) OracleStatementParser(com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser)

Example 8 with OracleOutputVisitor

use of com.alibaba.druid.sql.dialect.oracle.visitor.OracleOutputVisitor 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;
}
Also used : SQLSelectGroupByClause(com.alibaba.druid.sql.ast.statement.SQLSelectGroupByClause) DB2OutputVisitor(com.alibaba.druid.sql.dialect.db2.visitor.DB2OutputVisitor) HashMap(java.util.HashMap) LinkedHashMap(java.util.LinkedHashMap) ArrayList(java.util.ArrayList) PGSelectQueryBlock(com.alibaba.druid.sql.dialect.postgresql.ast.stmt.PGSelectQueryBlock) SQLSelectItem(com.alibaba.druid.sql.ast.statement.SQLSelectItem) SQLAllColumnExpr(com.alibaba.druid.sql.ast.expr.SQLAllColumnExpr) SQLASTOutputVisitor(com.alibaba.druid.sql.visitor.SQLASTOutputVisitor) PGOutputVisitor(com.alibaba.druid.sql.dialect.postgresql.visitor.PGOutputVisitor) SQLIdentifierExpr(com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr) MySqlSelectQueryBlock(com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock) SQLExpr(com.alibaba.druid.sql.ast.SQLExpr) OracleOutputVisitor(com.alibaba.druid.sql.dialect.oracle.visitor.OracleOutputVisitor) OracleSelectQueryBlock(com.alibaba.druid.sql.dialect.oracle.ast.stmt.OracleSelectQueryBlock) SQLServerSelectQueryBlock(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock) MySqlOutputVisitor(com.alibaba.druid.sql.dialect.mysql.visitor.MySqlOutputVisitor) DB2SelectQueryBlock(com.alibaba.druid.sql.dialect.db2.ast.stmt.DB2SelectQueryBlock) SQLAggregateExpr(com.alibaba.druid.sql.ast.expr.SQLAggregateExpr)

Example 9 with OracleOutputVisitor

use of com.alibaba.druid.sql.dialect.oracle.visitor.OracleOutputVisitor in project druid by alibaba.

the class OracleOutputVisitorTest_PrettyFormat method testConnectByParserAndPrettyFormatOutput.

@Test
public void testConnectByParserAndPrettyFormatOutput() {
    String sql = "select * from ge_rms_company start with comcode = '00' connect by nocycle prior comcode = uppercomcode";
    SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, JdbcUtils.ORACLE);
    List<SQLStatement> stmtList = parser.parseStatementList();
    StringBuilder out = new StringBuilder();
    // PrettyFormat use default : true
    SQLASTOutputVisitor visitor = new OracleOutputVisitor(out);
    for (SQLStatement statement : stmtList) {
        statement.accept(visitor);
    }
    String expectResult = "SELECT *\nFROM ge_rms_company\nSTART WITH comcode = '00'\nCONNECT BY NOCYCLE PRIOR comcode = uppercomcode";
    Assert.assertEquals(expectResult, out.toString());
    out.setLength(0);
    visitor = new OracleOutputVisitor(out);
    // setPrettyFormat : false
    visitor.setPrettyFormat(false);
    for (SQLStatement statement : stmtList) {
        statement.accept(visitor);
    }
    expectResult = "SELECT * FROM ge_rms_company START WITH comcode = '00' CONNECT BY NOCYCLE PRIOR comcode = uppercomcode";
    Assert.assertEquals(expectResult, out.toString());
}
Also used : OracleOutputVisitor(com.alibaba.druid.sql.dialect.oracle.visitor.OracleOutputVisitor) SQLStatementParser(com.alibaba.druid.sql.parser.SQLStatementParser) SQLASTOutputVisitor(com.alibaba.druid.sql.visitor.SQLASTOutputVisitor) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) Test(org.junit.Test)

Example 10 with OracleOutputVisitor

use of com.alibaba.druid.sql.dialect.oracle.visitor.OracleOutputVisitor in project druid by alibaba.

the class OracleOutputVisitorTest_delete method test_0.

public void test_0() throws Exception {
    String sql = "DELETE FROM employee where id = 3";
    OracleStatementParser parser = new OracleStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    Assert.assertEquals(1, statementList.size());
    OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
    stmt.accept(visitor);
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(true, visitor.containsTable("employee"));
    Assert.assertEquals(1, visitor.getColumns().size());
    Assert.assertEquals(true, visitor.getColumns().contains(new Column("employee", "id")));
    StringBuilder buf = new StringBuilder();
    OracleOutputVisitor outputVisitor = new OracleOutputVisitor(buf);
    stmt.accept(outputVisitor);
    Assert.assertEquals("DELETE FROM employee\nWHERE id = 3", buf.toString());
}
Also used : OracleOutputVisitor(com.alibaba.druid.sql.dialect.oracle.visitor.OracleOutputVisitor) Column(com.alibaba.druid.stat.TableStat.Column) OracleSchemaStatVisitor(com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) OracleStatementParser(com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser)

Aggregations

OracleOutputVisitor (com.alibaba.druid.sql.dialect.oracle.visitor.OracleOutputVisitor)20 SQLStatement (com.alibaba.druid.sql.ast.SQLStatement)18 OracleStatementParser (com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser)11 OracleSchemaStatVisitor (com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor)6 Column (com.alibaba.druid.stat.TableStat.Column)5 SQLASTOutputVisitor (com.alibaba.druid.sql.visitor.SQLASTOutputVisitor)3 SQLExpr (com.alibaba.druid.sql.ast.SQLExpr)2 SQLAggregateExpr (com.alibaba.druid.sql.ast.expr.SQLAggregateExpr)2 SQLAllColumnExpr (com.alibaba.druid.sql.ast.expr.SQLAllColumnExpr)2 SQLIdentifierExpr (com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr)2 SQLSelectGroupByClause (com.alibaba.druid.sql.ast.statement.SQLSelectGroupByClause)2 SQLSelectItem (com.alibaba.druid.sql.ast.statement.SQLSelectItem)2 DB2SelectQueryBlock (com.alibaba.druid.sql.dialect.db2.ast.stmt.DB2SelectQueryBlock)2 DB2OutputVisitor (com.alibaba.druid.sql.dialect.db2.visitor.DB2OutputVisitor)2 MySqlSelectQueryBlock (com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock)2 MySqlOutputVisitor (com.alibaba.druid.sql.dialect.mysql.visitor.MySqlOutputVisitor)2 OracleSelectQueryBlock (com.alibaba.druid.sql.dialect.oracle.ast.stmt.OracleSelectQueryBlock)2 PGSelectQueryBlock (com.alibaba.druid.sql.dialect.postgresql.ast.stmt.PGSelectQueryBlock)2 PGOutputVisitor (com.alibaba.druid.sql.dialect.postgresql.visitor.PGOutputVisitor)2 SQLServerSelectQueryBlock (com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock)2