use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock.Limit in project Mycat-Server by MyCATApache.
the class GlobalTableUtil method convertUpdateSQL.
/**
* UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
update user, tuser set user.name='dddd',tuser.pwd='aaa'
where user.id=2 and tuser.id=0;
* @param sql update tuser set pwd='aaa', name='digdee' where id=0;
* @return
*/
public static String convertUpdateSQL(String sql) {
try {
MySqlStatementParser parser = new MySqlStatementParser(sql);
SQLStatement stmt = parser.parseStatement();
MySqlUpdateStatement update = (MySqlUpdateStatement) stmt;
SQLTableSource ts = update.getTableSource();
if (ts != null && ts.toString().contains(",")) {
System.out.println(ts.toString());
LOGGER.warn("Do not support Multiple-table udpate syntax...");
return sql;
}
String tableName = StringUtil.removeBackquote(update.getTableName().getSimpleName());
if (!isGlobalTable(tableName))
return sql;
if (!isInnerColExist(tableName))
// 没有内部列
return sql;
StringBuilder sb = new StringBuilder(150);
SQLExpr se = update.getWhere();
// where中有子查询: update company set name='com' where id in (select id from xxx where ...)
if (se instanceof SQLInSubQueryExpr) {
// return sql;
int idx = sql.toUpperCase().indexOf(" SET ") + 5;
sb.append(sql.substring(0, idx)).append(GLOBAL_TABLE_MYCAT_COLUMN).append("=").append(operationTimestamp).append(",").append(sql.substring(idx));
return sb.toString();
}
String where = null;
if (update.getWhere() != null)
where = update.getWhere().toString();
SQLOrderBy orderBy = update.getOrderBy();
Limit limit = update.getLimit();
sb.append("update ").append(tableName).append(" set ");
List<SQLUpdateSetItem> items = update.getItems();
boolean flag = false;
for (int i = 0; i < items.size(); i++) {
SQLUpdateSetItem item = items.get(i);
String col = item.getColumn().toString();
String val = item.getValue().toString();
if (StringUtil.removeBackquote(col).equalsIgnoreCase(GLOBAL_TABLE_MYCAT_COLUMN)) {
flag = true;
sb.append(col).append("=");
if (i != items.size() - 1)
sb.append(operationTimestamp).append(",");
else
sb.append(operationTimestamp);
} else {
sb.append(col).append("=");
if (i != items.size() - 1)
sb.append(val).append(",");
else
sb.append(val);
}
}
if (!flag) {
sb.append(",").append(GLOBAL_TABLE_MYCAT_COLUMN).append("=").append(operationTimestamp);
}
sb.append(" where ").append(where);
if (orderBy != null && orderBy.getItems() != null && orderBy.getItems().size() > 0) {
sb.append(" order by ");
for (int i = 0; i < orderBy.getItems().size(); i++) {
SQLSelectOrderByItem item = orderBy.getItems().get(i);
SQLOrderingSpecification os = item.getType();
sb.append(item.getExpr().toString());
if (i < orderBy.getItems().size() - 1) {
if (os != null)
sb.append(" ").append(os.toString());
sb.append(",");
} else {
if (os != null)
sb.append(" ").append(os.toString());
}
}
}
if (limit != null) {
// 分为两种情况: limit 10; limit 10,10;
sb.append(" limit ");
if (limit.getOffset() != null)
sb.append(limit.getOffset().toString()).append(",");
sb.append(limit.getRowCount().toString());
}
return sb.toString();
} catch (Exception e) {
LOGGER.warn(e.getMessage());
return sql;
}
}
use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock.Limit in project Mycat-Server by MyCATApache.
the class DruidSelectOracleParser method statementParse.
@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();
Limit limit = mysqlSelectQuery.getLimit();
if (limit == null) {
//使用oracle的解析,否则会有部分oracle语法识别错误
OracleStatementParser oracleParser = new OracleStatementParser(getCtx().getSql());
SQLSelectStatement oracleStmt = (SQLSelectStatement) oracleParser.parseStatement();
selectStmt = oracleStmt;
SQLSelectQuery oracleSqlSelectQuery = oracleStmt.getSelect().getQuery();
if (oracleSqlSelectQuery instanceof OracleSelectQueryBlock) {
parseNativePageSql(oracleStmt, rrs, (OracleSelectQueryBlock) oracleSqlSelectQuery, schema);
}
}
if (isNeedParseOrderAgg) {
parseOrderAggGroupMysql(schema, selectStmt, rrs, mysqlSelectQuery);
//更改canRunInReadDB属性
if ((mysqlSelectQuery.isForUpdate() || mysqlSelectQuery.isLockInShareMode()) && rrs.isAutocommit() == false) {
rrs.setCanRunInReadDB(false);
}
}
}
}
use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock.Limit in project Mycat-Server by MyCATApache.
the class DruidSelectParser method changeSql.
/**
* 改写sql:需要加limit的加上
*/
@Override
public void changeSql(SchemaConfig schema, RouteResultset rrs, SQLStatement stmt, LayerCachePool cachePool) throws SQLNonTransientException {
tryRoute(schema, rrs, cachePool);
rrs.copyLimitToNodes();
SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
SQLSelectQuery sqlSelectQuery = selectStmt.getSelect().getQuery();
if (sqlSelectQuery instanceof MySqlSelectQueryBlock) {
MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock) selectStmt.getSelect().getQuery();
int limitStart = 0;
int limitSize = schema.getDefaultMaxLimit();
//clear group having
SQLSelectGroupByClause groupByClause = mysqlSelectQuery.getGroupBy();
// Modified by winbill, 20160614, do NOT include having clause when routing to multiple nodes
if (groupByClause != null && groupByClause.getHaving() != null && isRoutMultiNode(schema, rrs)) {
groupByClause.setHaving(null);
}
Map<String, Map<String, Set<ColumnRoutePair>>> allConditions = getAllConditions();
boolean isNeedAddLimit = isNeedAddLimit(schema, rrs, mysqlSelectQuery, allConditions);
if (isNeedAddLimit) {
Limit limit = new Limit();
limit.setRowCount(new SQLIntegerExpr(limitSize));
mysqlSelectQuery.setLimit(limit);
rrs.setLimitSize(limitSize);
String sql = getSql(rrs, stmt, isNeedAddLimit);
rrs.changeNodeSqlAfterAddLimit(schema, getCurentDbType(), sql, 0, limitSize, true);
}
Limit limit = mysqlSelectQuery.getLimit();
if (limit != null && !isNeedAddLimit) {
SQLIntegerExpr offset = (SQLIntegerExpr) limit.getOffset();
SQLIntegerExpr count = (SQLIntegerExpr) limit.getRowCount();
if (offset != null) {
limitStart = offset.getNumber().intValue();
rrs.setLimitStart(limitStart);
}
if (count != null) {
limitSize = count.getNumber().intValue();
rrs.setLimitSize(limitSize);
}
if (isNeedChangeLimit(rrs)) {
Limit changedLimit = new Limit();
changedLimit.setRowCount(new SQLIntegerExpr(limitStart + limitSize));
if (offset != null) {
if (limitStart < 0) {
String msg = "You have an error in your SQL syntax; check the manual that " + "corresponds to your MySQL server version for the right syntax to use near '" + limitStart + "'";
throw new SQLNonTransientException(ErrorCode.ER_PARSE_ERROR + " - " + msg);
} else {
changedLimit.setOffset(new SQLIntegerExpr(0));
}
}
mysqlSelectQuery.setLimit(changedLimit);
String sql = getSql(rrs, stmt, isNeedAddLimit);
rrs.changeNodeSqlAfterAddLimit(schema, getCurentDbType(), sql, 0, limitStart + limitSize, true);
//设置改写后的sql
ctx.setSql(sql);
} else {
rrs.changeNodeSqlAfterAddLimit(schema, getCurentDbType(), getCtx().getSql(), rrs.getLimitStart(), rrs.getLimitSize(), true);
// ctx.setSql(nativeSql);
}
}
if (rrs.isDistTable()) {
SQLTableSource from = mysqlSelectQuery.getFrom();
for (RouteResultsetNode node : rrs.getNodes()) {
SQLIdentifierExpr sqlIdentifierExpr = new SQLIdentifierExpr();
sqlIdentifierExpr.setParent(from);
sqlIdentifierExpr.setName(node.getSubTableName());
SQLExprTableSource from2 = new SQLExprTableSource(sqlIdentifierExpr);
mysqlSelectQuery.setFrom(from2);
node.setStatement(stmt.toString());
}
}
rrs.setCacheAble(isNeedCache(schema, rrs, mysqlSelectQuery, allConditions));
}
}
Aggregations