use of com.alibaba.druid.sql.visitor.SQLASTOutputVisitor in project druid by alibaba.
the class SQLUtils method toSQLString.
public static String toSQLString(List<SQLStatement> statementList, String dbType, List<Object> parameters, FormatOption option, Map<String, String> tableMapping) {
StringBuilder out = new StringBuilder();
SQLASTOutputVisitor visitor = createFormatOutputVisitor(out, statementList, dbType);
if (parameters != null) {
visitor.setParameters(parameters);
}
if (option == null) {
option = DEFAULT_FORMAT_OPTION;
}
visitor.setUppCase(option.isUppCase());
visitor.setPrettyFormat(option.isPrettyFormat());
visitor.setParameterized(option.isParameterized());
if (tableMapping != null) {
visitor.setTableMapping(tableMapping);
}
for (int i = 0; i < statementList.size(); i++) {
SQLStatement stmt = statementList.get(i);
if (i > 0) {
visitor.print(";");
SQLStatement preStmt = statementList.get(i - 1);
List<String> comments = preStmt.getAfterCommentsDirect();
if (comments != null) {
for (int j = 0; j < comments.size(); ++j) {
String comment = comments.get(j);
if (j != 0) {
visitor.println();
}
visitor.print(comment);
}
}
visitor.println();
if (!(stmt instanceof SQLSetStatement)) {
visitor.println();
}
}
{
List<String> comments = stmt.getBeforeCommentsDirect();
if (comments != null) {
for (String comment : comments) {
visitor.println(comment);
}
}
}
stmt.accept(visitor);
if (i == statementList.size() - 1) {
Boolean semi = (Boolean) stmt.getAttribute("format.semi");
if (semi != null && semi.booleanValue()) {
// if (stmt.hasAfterComment()) {
// visitor.println();
// }
visitor.print(";");
}
List<String> comments = stmt.getAfterCommentsDirect();
if (comments != null) {
for (int j = 0; j < comments.size(); ++j) {
String comment = comments.get(j);
if (j != 0) {
visitor.println();
}
visitor.print(comment);
}
}
}
}
return out.toString();
}
use of com.alibaba.druid.sql.visitor.SQLASTOutputVisitor 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.visitor.SQLASTOutputVisitor 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.visitor.SQLASTOutputVisitor in project druid by alibaba.
the class TestUtils method output.
public static String output(List<SQLStatement> stmtList) {
StringBuilder out = new StringBuilder();
SQLASTOutputVisitor visitor = new SQLASTOutputVisitor(out);
for (SQLStatement stmt : stmtList) {
stmt.accept(visitor);
}
return out.toString();
}
use of com.alibaba.druid.sql.visitor.SQLASTOutputVisitor in project druid by alibaba.
the class Oracle_param_0 method test_for_parameterize.
public void test_for_parameterize() throws Exception {
final DbType dbType = JdbcConstants.MYSQL;
String sql = "select * from t where rownum = 1";
SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, dbType);
List<SQLStatement> stmtList = parser.parseStatementList();
StringBuilder out = new StringBuilder();
SQLASTOutputVisitor visitor = SQLUtils.createOutputVisitor(out, JdbcConstants.ORACLE);
List<Object> parameters = new ArrayList<Object>();
visitor.setParameterized(true);
visitor.setParameterizedMergeInList(true);
visitor.setParameters(parameters);
visitor.setExportTables(true);
/*visitor.setPrettyFormat(false);*/
SQLStatement stmt = stmtList.get(0);
stmt.accept(visitor);
// System.out.println(parameters);
assertEquals(0, parameters.size());
StringBuilder buf = new StringBuilder();
SQLASTOutputVisitor visitor1 = SQLUtils.createOutputVisitor(buf, dbType);
visitor1.addTableMapping("a_entry", "a_entry_2664");
visitor1.setParameters(visitor.getParameters());
stmt.accept(visitor1);
assertEquals("SELECT *\n" + "FROM t\n" + "WHERE rownum = 1", buf.toString());
}
Aggregations