How to Java code to generate a string from array to DataWeave 2?

Question

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 can just try eliminating that comma var result = test() joinBy "," replace ")," with ")" @GettingStarted With123 — Karthik
– Karthik, Commented Jan 5, 2023 at 9:11

aled · Accepted Answer · 2023-01-05 12:58:02Z

2

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')

answered Jan 5, 2023 at 12:58

aled

26.5k4 gold badges36 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Harshank Bansal Over a year ago

A suggestion. You can use the quoteArray in a separate map function and then joinBy the in operator. It will avoid the if/else on index and the extra reduce Like keys divideBy size map quoteArray($) joinBy (" OR $(tableAlias).$(fieldName) IN ")

aled Over a year ago

That's an interesting approach.

GettingStarted With123 Over a year ago

Thanks a lot @aled for the extensive details and the solution ! Spent nearly the entire day yesterday and got frustrated . While I still do not understand the entire logic of your solution ( I will read and reread and spend some time in understanding Dataweave ! ) but yes it works ! Big thanks and you are correct , coming from the Java world I was trying to force dataweave to do it the Java way , I definitely need to learn and change my 'Java' style of thinking , once again big thanks !

aled Over a year ago

@GettingStartedWith123 think on what would be the logic to build the output from the input without trying to write code. In that sense it would be similar to how you would built a complex SQL query, if that helps. For example: I need to take every n elements (that's the divideBy() for) then for the first n I need to do so, and for the next ones I need to do so and so.

Harshank Bansal · Accepted Answer · 2023-01-05 21:57:36Z

2

Here is what I came up with. It is similar to aled's version, with small modifications to make the code smaller

%dw 2.0
import divideBy from dw::core::Arrays
output application/java

fun quoteArray(a) = "(" ++ (a map ("'$'") joinBy  ",") ++ ")"

fun generateInClause(tableAlias, fieldName, keys, size) = 
    keys divideBy size 
        map quoteArray($)
        joinBy (" OR $(tableAlias).$(fieldName) IN ")
---
"(" ++ generateInClause(payload.tableAlias, payload.fieldName, payload.keys, 10) ++ ")"

edited Jan 5, 2023 at 21:57

answered Jan 5, 2023 at 13:49

Harshank Bansal

3,4202 gold badges11 silver badges30 bronze badges

2 Comments

GettingStarted With123 Over a year ago

Thanks @Harshank will give it a try as well . Also apologies for a newbie like me to make this comment but I often feel that less code is not good as it makes readability and maintainability difficult , but then again this could be because I definitely am not a Dataweave expert . I will try this solution as well , thank you !

aled Over a year ago

I like how it removes the need for an if.

Collectives™ on Stack Overflow

How to Java code to generate a string from array to DataWeave 2?

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related