将Java生成Oracle IN子句逻辑迁移为DataWeave 2.0脚本的问题

需求背景 #

将Mule3应用迁移至Mule4，需移除原有Java依赖，把生成Oracle数据库查询IN子句的Java函数转换为DataWeave 2.0脚本。原Java函数会将超过10个元素的列表拆分为多个IN子句，用or连接，规避Oracle对IN子句元素数量的限制。

原Java实现代码 #

public static String generateStringInClause(String tableAlias, String fieldName, List<String> keys) {
    String inClause = "";
    for (int i = 0; i < keys.size(); i++){
        
        if (i==0) {
            inClause += "('" + keys.get(i) + "'";
        } else if ((i+1)%10==0) {
            inClause += ",'" + keys.get(i) + "')";
        } else if (i%10==0) {
            inClause += " or "+tableAlias+"."+fieldName+" in ('" + keys.get(i) + "'";
        } else if (i == keys.size()-1){
            inClause += ",'" + keys.get(i) + "')";
        } else {
            inClause += ",'" + keys.get(i) + "'";
        }
    }
    if (keys.size() % 10 == 1 || keys.size() == 1) {
        inClause = inClause + ")";
    }       
    return inClause;
}  

预期输出示例 #

根据keys列表长度不同，生成对应格式的IN子句：

• 21个元素：('a0','a1','a2'...'a8','a9') or xyz.abc in ('a10','a11',...'a19') or xyz.abc in ('a20')
• 12个元素：('a0','a1','a2','a3','a4','a5','a6','a7','a8','a9') or xyz.abc in ('a10','a11')
• 11个元素：('a0','a1','a2','a3','a4','a5','a6','a7','a8','a9') or xyz.abc in ('a10')
• 10个元素：('a0','a1','a2','a3','a4','a5','a6','a7','a8','a9')
• 9个元素：('a0','a1','a2','a3','a4','a5','a6','a7','a8')
• 1个元素：('a0')

现有DataWeave脚本问题 #

尝试的脚本在keys数量≤10时正常，但数量>10时，or前会出现多余逗号，导致SQL语法错误。

尝试的脚本 #

%dw 2.0
output application/java
var inClause = ""
var size = sizeOf(payload.keys)
fun test() = payload.keys map ((item, index) -> 
    if (index ==0) ( inClause ++ "('" ++ item ++ "'") 
    else if (mod((index+1),10)==0) ( "'" ++ item ++ "')")
    else if (mod((index),10)==0) ( " or "++ payload.tableAlias ++ "." ++ payload.fieldName ++ " in ('" ++ item ++ "'") 
    else if (index == (size-1) ) ( "'" ++ item ++ "')")
    else ("'" ++ item ++ "'")
)
var result = test() joinBy ","
var result1 = if((mod(size,10) == 1) or (size == 1)) (result ++ ")") else (result)
---
result1

错误输出示例 #

输入11个元素时，输出出现多余逗号：

('a0','a1','a2','a3','a4','a5','a6','a7','a8','a9'), or xyz.abc in ('a10')

修正后的DataWeave脚本 #

通过分组处理，先将keys按每10个一组拆分，再为每组生成IN子句，最后用or连接，彻底避免多余逗号问题：

%dw 2.0
output application/java

// 为单个分组生成IN子句
fun buildInClause(group: Array<String>, tableAlias: String, fieldName: String, isFirstGroup: Boolean) = 
    if (isEmpty(group)) ""
    else 
        (if (isFirstGroup) "" else tableAlias ++ "." ++ fieldName ++ " in ") 
        ++ "('" ++ (group joinBy "','") ++ "')"
---
// 将keys按每10个分组，生成所有子句后用or连接
(payload.keys groupBy (_, idx) -> floor(idx / 10)) 
    pluck ((value, key, index) -> buildInClause(value, payload.tableAlias, payload.fieldName, index == 0)) 
    filter ((item) -> !isEmpty(item))
    joinBy " or "

脚本逻辑说明 #

1. 分组处理：用groupBy结合索引计算，将keys列表拆分为每10个元素一组
2. 生成单组子句：buildInClause函数根据是否为第一组，决定是否添加表别名和字段名前缀
3. 过滤空分组：避免keys为空时生成无效内容
4. 连接子句：用" or "连接所有有效IN子句

正确输出示例 #

输入11个元素时，输出符合预期：

('a0','a1','a2','a3','a4','a5','a6','a7','a8','a9') or xyz.abc in ('a10')

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