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-ui-react/src/pages/ai/workflowDesign/customNode/sqlNode.ts | 4 +-
aiflowy-modules/aiflowy-module-ai/src/main/java/tech/aiflowy/ai/node/SqlNode.java | 95 ++++++++++++++++++++++++++++++++++++-----------
2 files changed, 74 insertions(+), 25 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 +
+ '}';
+ }
}
diff --git a/aiflowy-ui-react/src/pages/ai/workflowDesign/customNode/sqlNode.ts b/aiflowy-ui-react/src/pages/ai/workflowDesign/customNode/sqlNode.ts
index 76c250c..01a5b2e 100644
--- a/aiflowy-ui-react/src/pages/ai/workflowDesign/customNode/sqlNode.ts
+++ b/aiflowy-ui-react/src/pages/ai/workflowDesign/customNode/sqlNode.ts
@@ -7,7 +7,7 @@
icon:'<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24" fill="rgba(37,99,235,1)"><path d="M5 12.5C5 12.8134 5.46101 13.3584 6.53047 13.8931C7.91405 14.5849 9.87677 15 12 15C14.1232 15 16.0859 14.5849 17.4695 13.8931C18.539 13.3584 19 12.8134 19 12.5V10.3287C17.35 11.3482 14.8273 12 12 12C9.17273 12 6.64996 11.3482 5 10.3287V12.5ZM19 15.3287C17.35 16.3482 14.8273 17 12 17C9.17273 17 6.64996 16.3482 5 15.3287V17.5C5 17.8134 5.46101 18.3584 6.53047 18.8931C7.91405 19.5849 9.87677 20 12 20C14.1232 20 16.0859 19.5849 17.4695 18.8931C18.539 18.3584 19 17.8134 19 17.5V15.3287ZM3 17.5V7.5C3 5.01472 7.02944 3 12 3C16.9706 3 21 5.01472 21 7.5V17.5C21 19.9853 16.9706 22 12 22C7.02944 22 3 19.9853 3 17.5ZM12 10C14.1232 10 16.0859 9.58492 17.4695 8.89313C18.539 8.3584 19 7.81342 19 7.5C19 7.18658 18.539 6.6416 17.4695 6.10687C16.0859 5.41508 14.1232 5 12 5C9.87677 5 7.91405 5.41508 6.53047 6.10687C5.46101 6.6416 5 7.18658 5 7.5C5 7.81342 5.46101 8.3584 6.53047 8.89313C7.91405 9.58492 9.87677 10 12 10Z"></path></svg>',
sortNo: 803,
parametersAddEnable: true,
- outputDefsAddEnable: false,
+ outputDefsAddEnable: true,
parameters: [],
forms:[
{
@@ -21,7 +21,7 @@
{
name: 'queryData',
title: '鏌ヨ缁撴灉',
- dataType: 'String',
+ dataType: 'Array',
dataTypeDisabled:true,
required: true,
parametersAddEnable: false,
--
Gitblit v1.8.0