use of com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock in project druid by alibaba.
the class SQLServerSelectTest_cross_apply method test_0.
public void test_0() throws Exception {
String sql = "SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary FROM Departments d CROSS APPLY dbo.GetReports(d.DeptMgrID)";
SQLServerStatementParser parser = new SQLServerStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
SQLSelect select = selectStmt.getSelect();
Assert.assertNotNull(select.getQuery());
SQLServerSelectQueryBlock queryBlock = (SQLServerSelectQueryBlock) select.getQuery();
Assert.assertNull(queryBlock.getGroupBy());
String fomatSQL = SQLUtils.toSQLString(statementList, JdbcUtils.SQL_SERVER);
// System.out.println(fomatSQL);
Assert.assertEquals(1, statementList.size());
SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
stmt.accept(visitor);
// System.out.println("Tables : " + visitor.getTables());
// System.out.println("fields : " + visitor.getColumns());
// System.out.println("coditions : " + visitor.getConditions());
// System.out.println("orderBy : " + visitor.getOrderByColumns());
Assert.assertEquals(1, visitor.getTables().size());
Assert.assertEquals(7, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getConditions().size());
Assert.assertEquals(0, visitor.getOrderByColumns().size());
String expected = //
"SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName" + //
"\n\t, EmpSalary" + //
"\nFROM Departments d" + "\n\tCROSS APPLY dbo.GetReports(d.DeptMgrID)";
Assert.assertEquals(expected, fomatSQL);
}
use of com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock in project druid by alibaba.
the class SQLServerSelectTest_outer_apply method test_0.
public void test_0() throws Exception {
String sql = "SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary FROM Departments d OUTER APPLY dbo.GetReports(d.DeptMgrID)";
SQLServerStatementParser parser = new SQLServerStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
SQLSelect select = selectStmt.getSelect();
Assert.assertNotNull(select.getQuery());
SQLServerSelectQueryBlock queryBlock = (SQLServerSelectQueryBlock) select.getQuery();
Assert.assertNull(queryBlock.getGroupBy());
String fomatSQL = SQLUtils.toSQLString(statementList, JdbcUtils.SQL_SERVER);
// System.out.println(fomatSQL);
Assert.assertEquals(1, statementList.size());
SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
stmt.accept(visitor);
// System.out.println("Tables : " + visitor.getTables());
// System.out.println("fields : " + visitor.getColumns());
// System.out.println("coditions : " + visitor.getConditions());
// System.out.println("orderBy : " + visitor.getOrderByColumns());
Assert.assertEquals(1, visitor.getTables().size());
Assert.assertEquals(7, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getConditions().size());
Assert.assertEquals(0, visitor.getOrderByColumns().size());
String expected = //
"SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName" + //
"\n\t, EmpSalary" + //
"\nFROM Departments d" + "\n\tOUTER APPLY dbo.GetReports(d.DeptMgrID)";
Assert.assertEquals(expected, fomatSQL);
}
use of com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock in project Mycat_plus by coderczp.
the class DruidSelectSqlServerParser method sqlserverParse.
private void sqlserverParse(SchemaConfig schema, RouteResultset rrs) {
// 使用sqlserver的解析,否则会有部分语法识别错误
SQLServerStatementParser oracleParser = new SQLServerStatementParser(getCtx().getSql());
SQLSelectStatement oracleStmt = (SQLSelectStatement) oracleParser.parseStatement();
SQLSelectQuery oracleSqlSelectQuery = oracleStmt.getSelect().getQuery();
if (oracleSqlSelectQuery instanceof SQLServerSelectQueryBlock) {
parseSqlServerPageSql(oracleStmt, rrs, (SQLServerSelectQueryBlock) oracleSqlSelectQuery, schema);
if (isNeedParseOrderAgg) {
parseOrderAggGroupSqlServer(schema, oracleStmt, rrs, (SQLServerSelectQueryBlock) oracleSqlSelectQuery);
}
}
}
use of com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock in project Mycat_plus by coderczp.
the class PageSQLUtil method convertLimitToNativePageSql.
public static String convertLimitToNativePageSql(String dbType, String sql, int offset, int count) {
if (JdbcConstants.ORACLE.equalsIgnoreCase(dbType)) {
OracleStatementParser oracleParser = new OracleStatementParser(sql);
SQLSelectStatement oracleStmt = (SQLSelectStatement) oracleParser.parseStatement();
return PagerUtils.limit(oracleStmt.getSelect(), JdbcConstants.ORACLE, offset, count);
} else if (JdbcConstants.SQL_SERVER.equalsIgnoreCase(dbType)) {
SQLServerStatementParser oracleParser = new SQLServerStatementParser(sql);
SQLSelectStatement sqlserverStmt = (SQLSelectStatement) oracleParser.parseStatement();
SQLSelect select = sqlserverStmt.getSelect();
SQLOrderBy orderBy = select.getOrderBy();
if (orderBy == null) {
SQLSelectQuery sqlSelectQuery = select.getQuery();
if (sqlSelectQuery instanceof SQLServerSelectQueryBlock) {
SQLServerSelectQueryBlock sqlServerSelectQueryBlock = (SQLServerSelectQueryBlock) sqlSelectQuery;
SQLTableSource from = sqlServerSelectQueryBlock.getFrom();
if ("limit".equalsIgnoreCase(from.getAlias())) {
from.setAlias(null);
}
}
SQLOrderBy newOrderBy = new SQLOrderBy(new SQLIdentifierExpr("(select 0)"));
select.setOrderBy(newOrderBy);
}
return PagerUtils.limit(select, JdbcConstants.SQL_SERVER, offset, count);
} else if (JdbcConstants.DB2.equalsIgnoreCase(dbType)) {
DB2StatementParser db2Parser = new DB2StatementParser(sql);
SQLSelectStatement db2Stmt = (SQLSelectStatement) db2Parser.parseStatement();
return limitDB2(db2Stmt.getSelect(), JdbcConstants.DB2, offset, count);
} else if (JdbcConstants.POSTGRESQL.equalsIgnoreCase(dbType)) {
PGSQLStatementParser pgParser = new PGSQLStatementParser(sql);
SQLSelectStatement pgStmt = (SQLSelectStatement) pgParser.parseStatement();
SQLSelect select = pgStmt.getSelect();
SQLSelectQuery query = select.getQuery();
if (query instanceof PGSelectQueryBlock) {
PGSelectQueryBlock pgSelectQueryBlock = (PGSelectQueryBlock) query;
pgSelectQueryBlock.setOffset(null);
pgSelectQueryBlock.setLimit(null);
}
return PagerUtils.limit(select, JdbcConstants.POSTGRESQL, offset, count);
} else if (JdbcConstants.MYSQL.equalsIgnoreCase(dbType)) {
MySqlStatementParser pgParser = new MySqlStatementParser(sql);
SQLSelectStatement pgStmt = (SQLSelectStatement) pgParser.parseStatement();
SQLSelect select = pgStmt.getSelect();
SQLSelectQuery query = select.getQuery();
if (query instanceof MySqlSelectQueryBlock) {
MySqlSelectQueryBlock pgSelectQueryBlock = (MySqlSelectQueryBlock) query;
pgSelectQueryBlock.setLimit(null);
}
return PagerUtils.limit(select, JdbcConstants.MYSQL, offset, count);
}
return sql;
}
use of com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock in project druid by alibaba.
the class SQLServerSelectParser method query.
public SQLSelectQuery query(SQLObject parent, boolean acceptUnion) {
if (lexer.token() == Token.LPAREN) {
lexer.nextToken();
SQLSelectQuery select = query();
accept(Token.RPAREN);
return queryRest(select, acceptUnion);
}
SQLServerSelectQueryBlock queryBlock = new SQLServerSelectQueryBlock();
if (lexer.token() == Token.SELECT) {
lexer.nextToken();
if (lexer.token() == Token.COMMENT) {
lexer.nextToken();
}
if (lexer.token() == Token.DISTINCT) {
queryBlock.setDistionOption(SQLSetQuantifier.DISTINCT);
lexer.nextToken();
} else if (lexer.token() == Token.ALL) {
queryBlock.setDistionOption(SQLSetQuantifier.ALL);
lexer.nextToken();
}
if (lexer.token() == Token.TOP) {
SQLServerTop top = this.createExprParser().parseTop();
queryBlock.setTop(top);
}
parseSelectList(queryBlock);
}
if (lexer.token() == Token.INTO) {
lexer.nextToken();
SQLTableSource into = this.parseTableSource();
queryBlock.setInto((SQLExprTableSource) into);
}
parseFrom(queryBlock);
parseWhere(queryBlock);
parseGroupBy(queryBlock);
queryBlock.setOrderBy(this.exprParser.parseOrderBy());
parseFetchClause(queryBlock);
return queryRest(queryBlock, acceptUnion);
}
Aggregations