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());
}
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());
}
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;
}
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());
}
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());
}
Aggregations