From 72d30b7e3023bd8e42eb41b113f76038d4e3ee89 Mon Sep 17 00:00:00 2001
From: 黑炭儿呀 <983823671@qq.com>
Date: 星期四, 22 五月 2025 11:14:40 +0800
Subject: [PATCH] fix:调整sql解析，表名、列名等不可用"?"占位的地方使用{{...}}传参，可使用"?"展位的地方使用{{?...}} 传参
---
aiflowy-modules/aiflowy-module-ai/src/main/java/tech/aiflowy/ai/node/SqlNode.java | 95 ++++++++++++++++++++++++++++++++++++-----------
1 files changed, 72 insertions(+), 23 deletions(-)
diff --git a/aiflowy-modules/aiflowy-module-ai/src/main/java/tech/aiflowy/ai/node/SqlNode.java b/aiflowy-modules/aiflowy-module-ai/src/main/java/tech/aiflowy/ai/node/SqlNode.java
index 0cfe1b5..031615b 100644
--- a/aiflowy-modules/aiflowy-module-ai/src/main/java/tech/aiflowy/ai/node/SqlNode.java
+++ b/aiflowy-modules/aiflowy-module-ai/src/main/java/tech/aiflowy/ai/node/SqlNode.java
@@ -1,5 +1,6 @@
package tech.aiflowy.ai.node;
import com.agentsflex.core.chain.Chain;
+import com.agentsflex.core.chain.Parameter;
import com.agentsflex.core.chain.node.BaseNode;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
@@ -13,6 +14,7 @@
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;
+import tech.aiflowy.common.util.Maps;
import tech.aiflowy.common.web.exceptions.BusinessException;
import java.util.*;
@@ -41,7 +43,9 @@
Map<String, Object> map = chain.getParameterValues(this);
Map<String, Object> res = new HashMap<>();
- Map<String, Object> formatSqlMap = formatSql(sql);
+
+
+ Map<String, Object> formatSqlMap = formatSql(sql,map);
String formatSql = (String)formatSqlMap.get("replacedSql");
Statement statement = null;
@@ -68,46 +72,91 @@
List<Row> rows = Db.selectListBySql(formatSql, paramValues.toArray());
- res.put("queryData", rows);
+ if (rows == null || rows.isEmpty()) {
+ return Collections.emptyMap();
+ }
+
+ res.put("queryData",rows);
return res;
}
- private Map<String,Object> formatSql(String sql) {
+ private Map<String, Object> formatSql(String rawSql, Map<String, Object> paramMap) {
- if (!StringUtils.hasLength(sql)){
+ if (!StringUtils.hasLength(rawSql)) {
logger.error("sql瑙ｆ瀽鎶ラ敊锛歴ql涓虹┖");
throw new BusinessException("sql 涓嶈兘涓虹┖锛�");
}
- // 鐢ㄦ潵鎻愬彇鍙傛暟鍚�
- Pattern pattern = Pattern.compile("\\{\\{([^}]+)}}");
- Matcher matcher = pattern.matcher(sql);
+ // 鍖归厤 {{?...}} 琛ㄧず鍙敤鍗犱綅绗︾殑鍙傛暟
+ Pattern paramPattern = Pattern.compile("\\{\\{\\?([^}]+)}}");
+
+ // 鍖归厤 {{...}} 琛ㄧず鐩存帴鏇挎崲鐨勫弬鏁帮紙闈炲崰浣嶇锛�
+ Pattern directPattern = Pattern.compile("\\{\\{([^}?][^}]*)}}");
List<String> paramNames = new ArrayList<>();
+ StringBuffer sqlBuffer = new StringBuffer();
- // 鏋勫缓鏇挎崲鍚庣殑 SQL
- StringBuffer replacedSql = new StringBuffer();
- while (matcher.find()) {
- paramNames.add(matcher.group(1)); // 鑾峰彇 {{...}} 涓殑鍐呭
- matcher.appendReplacement(replacedSql, "?");
+ // 鏇挎崲 {{?...}} -> ?
+ Matcher paramMatcher = paramPattern.matcher(rawSql);
+ while (paramMatcher.find()) {
+ String paramName = paramMatcher.group(1).trim();
+ paramNames.add(paramName);
+ paramMatcher.appendReplacement(sqlBuffer, "?");
}
+ paramMatcher.appendTail(sqlBuffer);
+ String intermediateSql = sqlBuffer.toString();
- matcher.appendTail(replacedSql);
- HashMap<String, Object> formatSqlMap = new HashMap<>();
- String formatSql = replacedSql.toString();
+ // 鏇挎崲 {{...}} -> 瀹為檯鍊硷紙鐢ㄤ簬琛ㄥ悕/鍒楀悕绛夛級
+ sqlBuffer = new StringBuffer(); // 娓呯┖ buffer 閲嶆柊澶勭悊
+ Matcher directMatcher = directPattern.matcher(intermediateSql);
+ while (directMatcher.find()) {
+ String key = directMatcher.group(1).trim();
+ Object value = paramMap.get(key);
+ if (value == null) {
+ logger.error("鏈壘鍒板弬鏁帮細" + key);
+ throw new BusinessException("sql瑙ｆ瀽澶辫触锛岃纭繚sql璇硶姝ｇ‘锛�");
+ }
- if (formatSql.endsWith(";") || formatSql.endsWith("锛�")) {
- formatSql = formatSql.substring(0, formatSql.length() - 1);
+ String safeValue = value.toString();
+
+ directMatcher.appendReplacement(sqlBuffer, Matcher.quoteReplacement(safeValue));
}
+ directMatcher.appendTail(sqlBuffer);
- formatSql = formatSql.replace("鈥�" ,"\"").replace("鈥�","\"");
+ String finalSql = sqlBuffer.toString().trim();
- logger.info("Replaced SQL: {}", replacedSql);
- logger.info("Parameter names: {}", paramNames);
- formatSqlMap.put("replacedSql",formatSql );
- formatSqlMap.put("paramNames", paramNames);
- return formatSqlMap;
+ // 娓呯悊鏈熬鍒嗗彿涓庝腑鏂囧紩鍙�
+ if (finalSql.endsWith(";") || finalSql.endsWith("锛�")) {
+ finalSql = finalSql.substring(0, finalSql.length() - 1);
+ }
+ finalSql = finalSql.replace("鈥�", "\"").replace("鈥�", "\"");
+
+ logger.info("Final SQL: {}", finalSql);
+ logger.info("Param names: {}", paramNames);
+
+ Map<String, Object> result = new HashMap<>();
+ result.put("replacedSql", finalSql);
+ result.put("paramNames", paramNames);
+ return result;
}
+
+ @Override
+ public String toString() {
+ return "SqlNode{" +
+ "sql='" + sql + '\'' +
+ ", outputDefs=" + outputDefs +
+ ", parameters=" + parameters +
+ ", id='" + id + '\'' +
+ ", name='" + name + '\'' +
+ ", description='" + description + '\'' +
+ ", async=" + async +
+ ", inwardEdges=" + inwardEdges +
+ ", outwardEdges=" + outwardEdges +
+ ", condition=" + condition +
+ ", memory=" + memory +
+ ", nodeStatus=" + nodeStatus +
+ '}';
+ }
}
--
Gitblit v1.8.0