package io.mycat.route.parser.druid.impl; import java.util.List; import java.util.Map; import com.alibaba.druid.sql.ast.SQLExpr; import com.alibaba.druid.sql.ast.SQLOrderBy; import com.alibaba.druid.sql.ast.SQLStatement; import com.alibaba.druid.sql.ast.expr.SQLAggregateExpr; import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr; import com.alibaba.druid.sql.ast.expr.SQLBinaryOperator; import com.alibaba.druid.sql.ast.expr.SQLIntegerExpr; import com.alibaba.druid.sql.ast.statement.SQLSelectItem; import com.alibaba.druid.sql.ast.statement.SQLSelectOrderByItem; import com.alibaba.druid.sql.ast.statement.SQLSelectQuery; import com.alibaba.druid.sql.ast.statement.SQLSelectStatement; import com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource; import com.alibaba.druid.sql.ast.statement.SQLTableSource; import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock; import com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock; import com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerTop; import com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser; import com.alibaba.druid.util.JdbcConstants; import io.mycat.config.model.SchemaConfig; import io.mycat.route.RouteResultset; public class DruidSelectSqlServerParser extends DruidSelectParser { public DruidSelectSqlServerParser(){ super(); isNeedParseOrderAgg=true; } @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(); MySqlSelectQueryBlock.Limit limit=mysqlSelectQuery.getLimit(); if(limit==null) { sqlserverParse(schema, rrs); } if(isNeedParseOrderAgg) { parseOrderAggGroupMysql(schema, stmt,rrs, mysqlSelectQuery); //更改canRunInReadDB属性 if ((mysqlSelectQuery.isForUpdate() || mysqlSelectQuery.isLockInShareMode()) && rrs.isAutocommit() == false) { rrs.setCanRunInReadDB(false); } } } } protected String getCurentDbType() { return JdbcConstants.SQL_SERVER; } 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); } } } private void parseOrderAggGroupSqlServer(SchemaConfig schema, SQLStatement stmt, RouteResultset rrs, SQLServerSelectQueryBlock mysqlSelectQuery) { Map<String, String> aliaColumns = parseAggGroupCommon(schema, stmt,rrs, mysqlSelectQuery); SQLServerSelectQueryBlock oracleSelect= (SQLServerSelectQueryBlock) mysqlSelectQuery.getParent(); if(oracleSelect.getOrderBy() != null) { List<SQLSelectOrderByItem> orderByItems = oracleSelect.getOrderBy().getItems(); rrs.setOrderByCols(buildOrderByCols(orderByItems,aliaColumns)); } } private void parseSqlServerPageSql(SQLStatement stmt, RouteResultset rrs, SQLServerSelectQueryBlock sqlserverSelectQuery, SchemaConfig schema) { //第一层子查询 SQLExpr where= sqlserverSelectQuery.getWhere(); SQLTableSource from= sqlserverSelectQuery.getFrom(); if(sqlserverSelectQuery.getTop()!=null) { SQLServerTop top= sqlserverSelectQuery.getTop() ; SQLExpr sqlExpr= top.getExpr() ; if(sqlExpr instanceof SQLIntegerExpr) { int topValue=((SQLIntegerExpr) sqlExpr).getNumber().intValue(); rrs.setLimitStart(0); rrs.setLimitSize(topValue); } } else if(where instanceof SQLBinaryOpExpr &&from instanceof SQLSubqueryTableSource) { SQLBinaryOpExpr one= (SQLBinaryOpExpr) where; SQLExpr left=one.getLeft(); SQLBinaryOperator operator =one.getOperator(); SQLSelectQuery subSelect = ((SQLSubqueryTableSource) from).getSelect().getQuery(); SQLOrderBy orderBy=null; if (subSelect instanceof SQLServerSelectQueryBlock) { boolean hasRowNumber=false; boolean hasSubTop=false; int subTop=0; SQLServerSelectQueryBlock subSelectOracle = (SQLServerSelectQueryBlock) subSelect; List<SQLSelectItem> sqlSelectItems= subSelectOracle.getSelectList(); for (SQLSelectItem sqlSelectItem : sqlSelectItems) { SQLExpr sqlExpr= sqlSelectItem.getExpr() ; if(sqlExpr instanceof SQLAggregateExpr ) { SQLAggregateExpr agg= (SQLAggregateExpr) sqlExpr; if("row_number".equalsIgnoreCase(agg.getMethodName())&&agg.getOver()!=null) { hasRowNumber=true; orderBy= agg.getOver().getOrderBy(); } } } if(subSelectOracle.getFrom() instanceof SQLSubqueryTableSource) { SQLSubqueryTableSource subFrom= (SQLSubqueryTableSource) subSelectOracle.getFrom(); if (subFrom.getSelect().getQuery() instanceof SQLServerSelectQueryBlock) { SQLServerSelectQueryBlock sqlSelectQuery = (SQLServerSelectQueryBlock) subFrom.getSelect().getQuery(); if(sqlSelectQuery.getTop()!=null) { SQLExpr sqlExpr= sqlSelectQuery.getTop().getExpr() ; if(sqlExpr instanceof SQLIntegerExpr) { hasSubTop=true; subTop=((SQLIntegerExpr) sqlExpr).getNumber().intValue(); orderBy= subFrom.getSelect().getOrderBy(); } } } } if(hasRowNumber) { if(hasSubTop&&(operator==SQLBinaryOperator.GreaterThan||operator==SQLBinaryOperator.GreaterThanOrEqual)&& one.getRight() instanceof SQLIntegerExpr) { SQLIntegerExpr right = (SQLIntegerExpr) one.getRight(); int firstrownum = right.getNumber().intValue(); if (operator == SQLBinaryOperator.GreaterThanOrEqual&&firstrownum!=0) { firstrownum = firstrownum - 1; } int lastrownum =subTop; setLimitIFChange(stmt, rrs, schema, one, firstrownum, lastrownum); if(orderBy!=null) { SQLServerSelectQueryBlock oracleSelect= (SQLServerSelectQueryBlock) subSelect.getParent(); oracleSelect.setOrderBy(orderBy); } parseOrderAggGroupSqlServer(schema, stmt,rrs, (SQLServerSelectQueryBlock) subSelect); isNeedParseOrderAgg=false; } else if((operator==SQLBinaryOperator.LessThan||operator==SQLBinaryOperator.LessThanOrEqual) && one.getRight() instanceof SQLIntegerExpr ) { SQLIntegerExpr right = (SQLIntegerExpr) one.getRight(); int firstrownum = right.getNumber().intValue(); if (operator == SQLBinaryOperator.LessThan&&firstrownum!=0) { firstrownum = firstrownum - 1; } if (subSelect instanceof SQLServerSelectQueryBlock) { rrs.setLimitStart(0); rrs.setLimitSize(firstrownum); sqlserverSelectQuery = (SQLServerSelectQueryBlock) subSelect; //为了继续解出order by 等 if(orderBy!=null) { SQLServerSelectQueryBlock oracleSelect= (SQLServerSelectQueryBlock) subSelect.getParent(); oracleSelect.setOrderBy(orderBy); } parseOrderAggGroupSqlServer(schema, stmt,rrs, sqlserverSelectQuery); isNeedParseOrderAgg=false; } } else if(operator==SQLBinaryOperator.BooleanAnd && left instanceof SQLBinaryOpExpr&&one.getRight() instanceof SQLBinaryOpExpr ) { SQLBinaryOpExpr leftE= (SQLBinaryOpExpr) left; SQLBinaryOpExpr rightE= (SQLBinaryOpExpr) one.getRight(); SQLBinaryOpExpr small=null ; SQLBinaryOpExpr larger=null ; int firstrownum =0; int lastrownum =0; if(leftE.getRight() instanceof SQLIntegerExpr&&(leftE.getOperator()==SQLBinaryOperator.GreaterThan||leftE.getOperator()==SQLBinaryOperator.GreaterThanOrEqual)) { small=leftE; firstrownum=((SQLIntegerExpr) leftE.getRight()).getNumber().intValue(); if(leftE.getOperator()==SQLBinaryOperator.GreaterThanOrEqual &&firstrownum!=0) { firstrownum = firstrownum - 1; } } else if(leftE.getRight() instanceof SQLIntegerExpr&&(leftE.getOperator()==SQLBinaryOperator.LessThan||leftE.getOperator()==SQLBinaryOperator.LessThanOrEqual)) { larger=leftE; lastrownum=((SQLIntegerExpr) leftE.getRight()).getNumber().intValue(); if(leftE.getOperator()==SQLBinaryOperator.LessThan&&lastrownum!=0) { lastrownum = lastrownum - 1; } } if(rightE.getRight() instanceof SQLIntegerExpr&&(rightE.getOperator()==SQLBinaryOperator.GreaterThan||rightE.getOperator()==SQLBinaryOperator.GreaterThanOrEqual)) { small=rightE; firstrownum=((SQLIntegerExpr) rightE.getRight()).getNumber().intValue(); if(rightE.getOperator()==SQLBinaryOperator.GreaterThanOrEqual&&firstrownum!=0) { firstrownum = firstrownum - 1; } } else if(rightE.getRight() instanceof SQLIntegerExpr&&(rightE.getOperator()==SQLBinaryOperator.LessThan||rightE.getOperator()==SQLBinaryOperator.LessThanOrEqual)) { larger=rightE; lastrownum=((SQLIntegerExpr) rightE.getRight()).getNumber().intValue(); if(rightE.getOperator()==SQLBinaryOperator.LessThan&&lastrownum!=0) { lastrownum = lastrownum - 1; } } if(small!=null&&larger!=null) { setLimitIFChange(stmt, rrs, schema, small, firstrownum, lastrownum); if(orderBy!=null) { SQLServerSelectQueryBlock oracleSelect= (SQLServerSelectQueryBlock) subSelect.getParent(); oracleSelect.setOrderBy(orderBy); } parseOrderAggGroupSqlServer(schema, stmt,rrs, (SQLServerSelectQueryBlock) subSelect); isNeedParseOrderAgg=false; } } } } } } }