Am working on migrating a Mule 3 application to Mule 4. We are using Java code to generate an Oracle database query Here is the existing Java function:
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;
}
Here are the actual outputs from the Java code when I pass a List of keys of different sizes:
* 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')
* 09 = ('a0','a1','a2','a3','a4','a5','a6','a7','a8')
* 01 = ('a0')
Now In Mule 4 we do not want to use any Java methods / classes so need to convert this function into DataWeave
Here is what I have tried:
%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
This script works fine and generates the same result when I have a list up to 10 elements. It does not produce the same result as the Java method when I have > 10 elements in the list of keys.
Input to the DataWeave script:
{
"tableAlias": "xyz",
"fieldName": "abc",
"keys" : ["a0","a1","a2","a3","a4","a5","a6","a7","a8","a9","a10"]
}
In the output there is an additional comma , before or which will cause SQL query to fail.
Actual output:
('a0','a1','a2','a3','a4','a5','a6','a7','a8','a9'), or xyz.abc in ('a10')
You are trying to migrate the logic from the Java method but you need to take into account that Java is an imperative language and DataWeave a functional language. A 1 to 1 migration may not work or be too difficult to maintain. For example the variable inClause does absolutely nothing in the DataWeave script. You can not accumulate a value over a loop like in Java. You should think of what you are trying to achieve as the output and then think on how to express that.
As an example, I choose first separate the keys into blocks of n. I only need a condition to identify the first block. I don't need to transform each value after I know if it is the first block or one of the rest. That way I don't need to concern myself with matching commas or parenthesis. Then I transform each block more naturally in my view. I added an auxiliary function to format the values in each block. I use a reduce() at the end to concatenate the string resulting of each block. The intention of the code should be more clear than in the Java code.
I encapsulated the inputs to the functions in parameters so it is more reusable and clean. The block size is also a parameter. Magic numbers in code are not a good practice.
%dw 2.0
output application/java
import * from dw::core::Arrays
fun quoteArray(a) = "(" ++ (a map ("'" ++ $ ++ "'") joinBy ",") ++ ")"
fun generateStringInClause(tableAlias, fieldName, keys, size) =
keys divideBy size
map ((item, index) ->
if (index == 0) quoteArray(item)
else (" or "++ tableAlias ++ "." ++ fieldName ++ " in " ++ quoteArray(item) )
)
reduce ((item, accumulator="") -> accumulator ++ item)
---
generateStringInClause(payload.tableAlias, payload.fieldName, payload.keys, 10)
Input:
{
"tableAlias": "xyz",
"fieldName": "abc",
"keys" : ["a0","a1","a2","a3","a4","a5","a6","a7","a8","a9","a10","a11", "a12","a13","a14","a15","a16","a17","a18","a19","a20"]
}
Output:
('a0','a1','a2','a3','a4','a5','a6','a7','a8','a9') or xyz.abc in ('a10','a11','a12','a13','a14','a15','a16','a17','a18','a19') or xyz.abc in ('a20')