1. 前言
Mysql的DDL与DML都有各自的语法规范。
如查询语句，以SELECT开头，紧跟着是查询的字段 A ，B， C 再者就是表名、 WHERE、条件、 GROUP BY、ORDER BY、LIMIT 等
其他修改、插入语句同理可以。都有各自的语句逻辑规范。
2. AST语法树解析
阿里巴巴的druid线程池中，存在对SQL语句的语法树解析，依赖如下
https://github.com/alibaba/druid
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.15</version>
</dependency>
3. Java中使用Druid_SQL_AST 动态修改语句
3.1 解析查询语句
提取查询语句的出入参(WHERE条件参数)，提取查询语句的出参(SELECT后面的字段)
在以下代码中，能够提取语句中入参为c，出参为A，b
SELECT a as A, b from table where c = #{c}
调用方式
public static String replaceSql(String sql, Map<String, Object> dataMap, DbType dbType) {
SQLStatement sqlStatement = SQLUtils.parseSingleStatement(sql, dbType);
ReplaceSqlASTVisitorAdapter visitor = new ReplaceSqlASTVisitorAdapter (dataMap, dbType);
sqlStatement.accept(visitor);
// 转义 \' 为 '
return sqlStatement.toString().replaceAll("\\\\'", "'");
}
解析适配器
@Data
private static class ReplaceSqlASTVisitorAdapter extends SQLASTVisitorAdapter implements SQLASTVisitor, MySqlASTVisitor, PGASTVisitor {
// 提取 #{} 参数正则表达式
private Pattern pattern = Pattern.compile("(?<=#\\{).*?(?=})");
Map<String, Object> dataMap;
private List<String> removeSQLExprList = new ArrayList<>();
ReplaceSqlASTVisitorAdapter(Map<String, Object> dataMap, DbType dbType) {
this.dataMap = dataMap;
this.dbType = dbType;
}
/**
* 规则路由
*
* @param x x
* @return boolean
* @author
* @date 2023/5/30
*/
public boolean route(SQLExpr x) {
if (x == null) {
return false;
}
if (x instanceof SQLBinaryOpExpr) {
visit((SQLBinaryOpExpr) x);
}
if (x instanceof SQLInListExpr) {
visit((SQLInListExpr) x);
}
if (x instanceof SQLMethodInvokeExpr) {
visit((SQLMethodInvokeExpr) x);
}
if (x instanceof PGExtractExpr) {
visit((PGExtractExpr) x);
}
if (x instanceof SQLCastExpr) {
visit((SQLCastExpr) x);
}
if (x instanceof SQLBetweenExpr) {
visit((SQLBetweenExpr) x);
}
if (x instanceof SQLInSubQueryExpr) {
visit((SQLInSubQueryExpr) x);
}
if (x instanceof SQLVariantRefExpr) {
visit((SQLVariantRefExpr) x);
}
return false;
}
// 删除 查询 条件
@Override
public void endVisit(SQLSelectQueryBlock x) {
// 移除数据
removeSQLExprList.forEach(x::removeCondition);
}
// 删除 Update 条件
@Override
public void endVisit(SQLUpdateStatement x) {
// 移除数据
removeSQLExprList.forEach(x::removeCondition);
}
// 删除 delete 条件
@Override
public void endVisit(SQLDeleteStatement x) {
// 移除数据
removeSQLExprList.forEach(x::removeCondition);
}
@Override
public boolean visit(SQLUpdateSetItem x) {
this.route(x.getColumn());
this.route(x.getValue());
SQLExpr sqlExprByColumn = this.getSQLExpr(x.getColumn().toString(), x.toString());
if (ObjectUtil.isNotNull(sqlExprByColumn)) {
x.setColumn(sqlExprByColumn);
}
SQLExpr sqlExprByValue = this.getSQLExpr(x.getValue().toString(), x.toString());
if (ObjectUtil.isNotNull(sqlExprByValue)) {
x.setValue(sqlExprByValue);
}
return false;
}
// 替换 IN
@Override
public boolean visit(SQLInListExpr x) {
SQLExpr expr = x.getExpr();
this.route(expr);
List<SQLExpr> targetList = x.getTargetList();
List<SQLExpr> replaceTargetList = new ArrayList<>();
for (SQLExpr sqlExpr : targetList) {
this.route(sqlExpr);
SQLExpr inSqlExpr = getSQLExpr(sqlExpr.toString(), x.toString());
if (ObjectUtil.isNotNull(inSqlExpr)) {
replaceTargetList.add(inSqlExpr);
}
}
if (CollectionUtil.isNotEmpty(replaceTargetList)) {
x.setTargetList(replaceTargetList);
}
return true;
}
// 替换 between
@Override
public boolean visit(SQLBetweenExpr x) {
this.route(x.getTestExpr());
this.route(x.getBeginExpr());
this.route(x.getEndExpr());
SQLExpr beginExpr = this.getSQLExpr(x.getBeginExpr().toString(), x.toString());
if (ObjectUtil.isNotNull(beginExpr)) {
x.setBeginExpr(beginExpr);
}
SQLExpr endExpr = this.getSQLExpr(x.getEndExpr().toString(), x.toString());
if (ObjectUtil.isNotNull(endExpr)) {
x.setEndExpr(endExpr);
}
return true;
}
// 替换where
public boolean visit(SQLBinaryOpExpr x) {
this.route(x.getLeft());
this.route(x.getRight());
SQLExpr sqlExprByLeft = this.getSQLExpr(x.getLeft().toString(), x.toString());
if (ObjectUtil.isNotNull(sqlExprByLeft)) {
x.setLeft(sqlExprByLeft);
}
SQLExpr sqlExprByRight = this.getSQLExpr(x.getRight().toString(), x.toString());
if (ObjectUtil.isNotNull(sqlExprByRight)) {
x.setRight(sqlExprByRight);
}
return true;
}
// 提取 group by
@Override
public boolean visit(SQLSelectGroupByClause x) {
List<SQLExpr> groupItems = x.getItems();
if (CollectionUtil.isEmpty(groupItems)) {
return true;
}
for (int i = 0; i < groupItems.size(); i++) {
SQLExpr item = groupItems.get(i);
this.route(item);
SQLExpr sqlExpr = this.getSQLExpr(item.toString(), item.toString());
if (ObjectUtil.isNotNull(sqlExpr)) {
groupItems.set(0, sqlExpr);
}
}
return true;
}
// 提取 order by
public boolean visit(SQLSelectOrderByItem x) {
SQLExpr orderFieldSqlExpr = this.getSQLExpr(x.getExpr().toString(), x.toString());
if (ObjectUtil.isNotNull(orderFieldSqlExpr)) {
x.setExpr(orderFieldSqlExpr);
}
if (ObjectUtil.isNotNull(x.getType())) {
SQLExpr orderSqlExpr = this.getSQLExpr(x.getType().toString(), x.toString());
if (ObjectUtil.isNotNull(orderSqlExpr)) {
SQLOrderingSpecification sqlOrderingSpecification = SQLOrderingSpecification.DESC;
if (ObjectUtil.equal(orderFieldSqlExpr.toString().toLowerCase(), SQLOrderingSpecification.ASC.nameLCase)) {
sqlOrderingSpecification = SQLOrderingSpecification.ASC;
}
x.setType(sqlOrderingSpecification);
}
}
return true;
}
// 提取 limit 条件
public boolean visit(SQLLimit x) {
SQLExpr offSetSqlExpr = this.getSQLExpr(x.getOffset().toString(), x.toString());
if (ObjectUtil.isNotNull(offSetSqlExpr)) {
x.setOffset(offSetSqlExpr);
}
SQLExpr rowCountSqlExpr = this.getSQLExpr(x.getRowCount().toString(), x.toString());
if (ObjectUtil.isNotNull(rowCountSqlExpr)) {
x.setRowCount(rowCountSqlExpr);
}
return true;
}
@Override
public boolean visit(SQLCastExpr x) {
List<SQLObject> arguments = x.getChildren();
for (int i = 0; i < arguments.size(); i++) {
SQLObject item = arguments.get(i);
if (item instanceof SQLBinaryOpExpr) {
return visit((SQLBinaryOpExpr) item);
} else if (item instanceof SQLMethodInvokeExpr) {
return visit((SQLMethodInvokeExpr) item);
} else {
SQLExpr sqlExpr = this.getSQLExpr(item.toString(), item.toString());
if (ObjectUtil.isNotNull(sqlExpr)) {
arguments.set(0, sqlExpr);
return true;
}
}
}
return false;
}
@Override
public boolean visit(SQLVariantRefExpr x) {
SQLExpr sqlExprByName = this.getSQLExpr(x.getName(), x.toString());
if (ObjectUtil.isNotNull(sqlExprByName)) {
x.setName(sqlExprByName.toString());
}
List<SQLObject> arguments = x.getChildren();
for (int i = 0; i < arguments.size(); i++) {
SQLObject item = arguments.get(i);
if (item instanceof SQLBinaryOpExpr) {
return visit((SQLBinaryOpExpr) item);
} else if (item instanceof SQLMethodInvokeExpr) {
return visit((SQLMethodInvokeExpr) item);
} else {
SQLExpr sqlExpr = this.getSQLExpr(item.toString(), item.toString());
if (ObjectUtil.isNotNull(sqlExpr)) {
arguments.set(0, sqlExpr);
return true;
}
}
}
return false;
}
@Override
public boolean visit(PGExtractExpr x) {
List<SQLObject> arguments = x.getChildren();
for (int i = 0; i < arguments.size(); i++) {
SQLObject item = arguments.get(i);
if (item instanceof SQLBinaryOpExpr) {
return visit((SQLBinaryOpExpr) item);
} else if (item instanceof SQLMethodInvokeExpr) {
return visit((SQLMethodInvokeExpr) item);
} else {
SQLExpr sqlExpr = this.getSQLExpr(item.toString(), item.toString());
if (ObjectUtil.isNotNull(sqlExpr)) {
arguments.set(0, sqlExpr);
return true;
}
}
}
return false;
}
@Override
public boolean visit(SQLMethodInvokeExpr x) {
List<SQLExpr> arguments = x.getArguments();
for (int i = 0; i < arguments.size(); i++) {
SQLExpr item = arguments.get(i);
this.route(item);
SQLExpr sqlExpr = this.getSQLExpr(item.toString(), item.toString());
if (ObjectUtil.isNotNull(sqlExpr)) {
arguments.set(i, sqlExpr);
}
}
return false;
}
@Override
public boolean visit(SQLInSubQueryExpr x) {
SQLSelect subQuery = x.getSubQuery();
if (ObjectUtil.isNotNull(subQuery)) {
SQLStatement sqlStatement = SQLUtils.parseSingleStatement(subQuery.toString(), this.dbType);
ApaasReplaceSqlASTVisitorAdapter visitor = new ApaasReplaceSqlASTVisitorAdapter(dataMap, this.dbType);
sqlStatement.accept(visitor);
SQLQueryExpr sqlQueryExpr = (SQLQueryExpr) SQLUtils.toSQLExpr(sqlStatement.toString(), this.dbType);
x.setSubQuery(sqlQueryExpr.getSubQuery());
}
return false;
}
/**
* //#{} 正则表达式
*/
private String extraColumn(String value) {
if (StringUtil.isBlank(value)) {
return SymbolConstants.BLANK_STR;
}
Matcher matcher = pattern.matcher(value);
if (matcher.find()) {
String group = matcher.group().trim();
String[] splitItem = group.split(SymbolConstants.COMMA);
for (String item : splitItem) {
return item;
}
}
return SymbolConstants.BLANK_STR;
}
/**
* @param SQLExprStr
* @param parentSqlExprStr
* @return
*/
private SQLExpr getSQLExpr(String SQLExprStr, String parentSqlExprStr) {
String column = extraColumn(SQLExprStr);
if (!dataMap.containsKey(column)) {
return null;
}
// 获取字段
Object value = dataMap.get(column);
boolean isArray = (ObjectUtil.isNotNull(value)) && (value instanceof Collection || JSONUtil.isJsonArray(value.toString()));
List<Object> valueList = Collections.emptyList();
if (isArray) {
valueList = JSONUtil.parseArray(value.toString()).toList(Object.class);
}
// 值不存在 如要替换
if (ObjectUtil.isNull(value) || StringUtil.isBlank(value.toString()) || (isArray && CollectionUtil.isEmpty(valueList))) {
// 收集需要移除的 SQLBinaryOpExpr
removeSQLExprList.add(parentSqlExprStr);
return null;
}
// 数据填充
SQLExpr returnSqlExpr = null;
if (isArray) {
// 如果是数组
StringJoiner stringJoiner = new StringJoiner(SymbolConstants.COMMA);
for (Object o : valueList) {
stringJoiner.add(parseValue(o).toString());
}
returnSqlExpr = new SQLVariantRefExpr(stringJoiner.toString());
} else {
// 当字段转成数据
returnSqlExpr = new SQLVariantRefExpr(parseValue(value).toString());
}
return returnSqlExpr;
}
private Object parseValue(Object o) {
if (null == o) {
return null;
}
if (o instanceof Number) {
return o;
}
if (o instanceof String) {
return String.format("'%s'", o);
}
return o;
}
}
3.2 替换查询语句
上述demo已将出入参数解析出来了,如得入参为#{c}, 将#{c}动态注入替换到SQL中
SELECT a as A, b from table where c = #{c}
经过以下代码，假设c=3,则SQL变为如下
SELECT a as A, b from table where c = 3
调用方式
public static ParseCustomApiBO parseParamsList(String customizeSql, DbType dbType) {
// 去除<!-- --> 无法解析识别
customizeSql = customizeSql.replaceAll("<!--.*?-->", SymbolConstants.BLANK_STR);
// 转义SQL
SQLStatement sqlStatement = SQLUtils.parseSingleStatement(customizeSql, dbType,
SQLParserFeature.KeepSourceLocation,
SQLParserFeature.KeepSelectListOriginalString,
SQLParserFeature.SelectItemGenerateAlias,
SQLParserFeature.PrintSQLWhileParsingFailed,
SQLParserFeature.KeepComments,
SQLParserFeature.TDDLHint,
SQLParserFeature.DRDSAsyncDDL,
SQLParserFeature.DRDSBaseline,
SQLParserFeature.MySQLSupportStandardComment
);
ParseSqlASTVisitorAdapter visitor = new ParseSqlASTVisitorAdapter();
sqlStatement.accept(visitor);
ParseCustomApiBO ParseCustomApiBO = new ParseCustomApiBO();
ParseCustomApiBO.setCustomizeSql(customizeSql)
.setEnterParams(visitor.toApiParam(visitor.getRequestColumnList()))
.setOutParams(visitor.toApiParam(visitor.getResponseColumnList()));
return ParseCustomApiBO;
}
解析适配器
@Data
private static class ParseSqlASTVisitorAdapter extends SQLASTVisitorAdapter implements SQLASTVisitor, MySqlASTVisitor, PGASTVisitor {
private List<String> responseColumnList = new ArrayList<>();
private List<String> requestColumnList = new ArrayList<>();
private Map<Integer, String> lineColumnListMap = new HashMap<>();
// 提取 #{} 参数正则表达式
private Pattern pattern = Pattern.compile("(?<=#\\{).*?(?=})");
// 提取SELECT
public boolean visit(SQLSelect x) {
if (CollectionUtil.isEmpty(responseColumnList)) {
responseColumnList = x.computeSelecteListAlias();
}
return true;
}
// 提取行号对应关键字段
@Override
public boolean visit(SQLSelectItem x) {
SQLExpr expr = x.getExpr();
if (expr instanceof SQLIdentifierExpr) {
int sourceLine = ((SQLIdentifierExpr) expr).getSourceLine();
lineColumnListMap.putIfAbsent(sourceLine, x.computeAlias());
}
return true;
}
// 提取Update SET语句
public boolean visit(SQLUpdateSetItem x) {
this.extraColumn(x.getValue().toString());
return false;
}
// 提取where
public boolean visit(SQLBinaryOpExpr x) {
this.extraColumn(x.getRight().toString());
return true;
}
@Override
public boolean visit(SQLCommitStatement x) {
return false;
}
@Override
public boolean visit(SQLCommentHint x) {
return false;
}
// 提取 group by
public boolean visit(SQLSelectGroupByClause x) {
List<SQLExpr> groupItems = x.getItems();
if (CollectionUtil.isEmpty(groupItems)) {
return true;
}
for (SQLExpr sqlExpr : groupItems) {
this.extraColumn(sqlExpr.toString());
}
return true;
}
// 提取 order by
public boolean visit(SQLSelectOrderByItem x) {
this.extraColumn(x.getExpr().toString());
return true;
}
// 提取 limit 条件
public boolean visit(SQLLimit x) {
this.extraColumn(x.getOffset().toString());
this.extraColumn(x.getRowCount().toString());
return true;
}
/**
* //#{} 正则表达式
*/
private void extraColumn(String value) {
if (StringUtil.isBlank(value)) {
return;
}
Matcher matcher = pattern.matcher(value);
if (matcher.find()) {
String group = matcher.group().trim();
String[] splitItem = group.split(SymbolConstants.COMMA);
for (String item : splitItem) {
item = item.trim();
if (!requestColumnList.contains(item)) {
requestColumnList.add(item);
}
}
}
}
public List<ApiParam> toApiParam(List<String> columnList) {
if (CollectionUtil.isEmpty(columnList)) {
return Collections.emptyList();
}
return columnList.stream().map(column -> {
ApiParam apiParam = new ApiParam();
apiParam.setParam(column);
return apiParam;
}).collect(Collectors.toList());
}
}
3219
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
