Mule 4 DW 2.0动态Select SQL查询构建方案求助

我的API接收以下查询参数：

{
    "PERSON_ID": "123456",
    "LAST_MODIFIED_DATE": "2011-12-15T00:00:00",
    "START_DATE": "2011-12-15T00:00:00",
    "END_DATE": "2012-12-15T00:00:00"
}

需要根据输入参数构建动态SQL，规则如下：

• 仅传入LAST_MODIFIED_DATE，或同时传入START_DATE和END_DATE
• PERSON_ID可与上述任一参数组合传入
• 无参数时执行全表查询

预期生成的SQL示例：

select * from Person where PERSON_ID = '123456' 
select * from Person where LAST_MODIFIED_DATE = '2011-12-15T00:00:00' 
select * from Person where PERSON_ID = '123456' and LAST_MODIFIED_DATE = '2011-12-15T00:00:00' 
select * from Person where PERSON_ID = '123456' and LAST_MODIFIED_DATE between '2011-12-15T00:00:00' and '2012-12-15T00:00:00'
select * from Person where LAST_MODIFIED_DATE between '2011-12-15T00:00:00' and '2012-12-15T00:00:00'
-- 无参数时
select * from Person

我之前写的DataWeave逻辑无法满足需求，代码如下：

%dw 2.0
output application/json
var data = {
    "PERSON_ID": "123456",
    "LAST_MODIFIED_DATE": "2011-12-15T00:00:00",
    "START_DATE": "2011-12-15T00:00:00",
    "END_DATE": "2012-12-15T00:00:00"
}
"Select * from Person Where " ++
((data filterObject ((value, key, index) -> (value != null and value != "")) mapObject ((value, key, index) -> {
    (myData : "LAST_MODIFIED_DATE between " ++ (key as String)) if ((key as String) == "START_DATE"),
    (myData : "AND " ++ (key as String)) if ((key as String) == "END_DATE"),
    (myData : (key as String) ++ " = :" ++ (key as String)) if ((key as String) == "PERSON_ID"),
     myData : (key as String) ++ " = :" ++ (key as String)
})).*myData joinBy  " AND ")

后续会将输出变量设置为数据库连接器中的SQL字符串，求可行方案或最佳实践。

以下代码严格遵循参数规则，生成符合要求的动态SQL，同时处理了日期值的语法规范和参数组合逻辑：

%dw 2.0
output application/java // 输出字符串适配数据库连接器
var inputParams = payload // 实际使用时直接用payload，测试可替换为示例data变量
// 过滤空值参数
var filteredParams = inputParams filterObject ((value) -> value != null and value != "")
// 构建WHERE子句条件列表
var conditions = []
    // 添加PERSON_ID条件
    ++ (if (filteredParams.PERSON_ID != null) [ "PERSON_ID = '" ++ filteredParams.PERSON_ID ++ "'" ] else [])
    // 处理日期条件：优先判断START/END_DATE组合，再判断单个LAST_MODIFIED_DATE
    ++ (if (filteredParams.START_DATE != null and filteredParams.END_DATE != null) 
        [ "LAST_MODIFIED_DATE between '" ++ filteredParams.START_DATE ++ "' and '" ++ filteredParams.END_DATE ++ "'" ]
        else if (filteredParams.LAST_MODIFIED_DATE != null) 
        [ "LAST_MODIFIED_DATE = '" ++ filteredParams.LAST_MODIFIED_DATE ++ "'" ]
        else [])
// 拼接最终SQL语句
var sql = if (sizeOf(conditions) > 0) 
    "select * from Person where " ++ (conditions joinBy " and ") 
else 
    "select * from Person"
---
sql

代码说明 #

1. 参数过滤：先剔除空值/空字符串参数，避免生成无效SQL条件
2. 条件构建：
   • 单独处理PERSON_ID，存在则生成等值匹配条件
   • 优先识别START_DATE和END_DATE的组合，生成between范围条件；否则处理单个LAST_MODIFIED_DATE的等值条件
3. SQL拼接：根据条件列表是否为空，决定是否添加WHERE子句，无参数时直接返回全表查询
4. 语法规范：所有日期值用单引号包裹，符合SQL语法要求

测试验证 #

• 仅传PERSON_ID：select * from Person where PERSON_ID = '123456'
• 仅传LAST_MODIFIED_DATE：select * from Person where LAST_MODIFIED_DATE = '2011-12-15T00:00:00'
• 传PERSON_ID+LAST_MODIFIED_DATE：select * from Person where PERSON_ID = '123456' and LAST_MODIFIED_DATE = '2011-12-15T00:00:00'
• 传PERSON_ID+START_DATE+END_DATE：select * from Person where PERSON_ID = '123456' and LAST_MODIFIED_DATE between '2011-12-15T00:00:00' and '2012-12-15T00:00:00'
• 仅传START_DATE+END_DATE：select * from Person where LAST_MODIFIED_DATE between '2011-12-15T00:00:00' and '2012-12-15T00:00:00'
• 无参数：select * from Person

最佳实践提示 #

• 参数化查询：如果数据库连接器支持，建议改用参数绑定形式（如PERSON_ID = :PERSON_ID），同时将参数传入连接器的参数列表，避免SQL注入风险
• 输入校验：在API层先校验参数规则（比如禁止同时传入LAST_MODIFIED_DATE和START_DATE/END_DATE），减少无效参数组合进入DataWeave逻辑
• 输出类型：设置output application/java直接返回字符串，更适配数据库连接器的SQL输入要求

内容的提问来源于stack exchange，提问作者Sunny85