Group database records using ESB mule

0
votes

I have mule flow which selects few records from database tables which looks like below.

StudentID   Subject     Mark 
1           Maths        98  
2           Literature   62
1           Science      56  
1           Anatomy      63
3           Zoology      38
2           Algebra      63

Here i need to group the records based on studentID and need to send for further processing. Hence i have placed splitter component immediate after database node. But in MEL expression how can we group the records ?

Is there any other best way to do this ESB mule ?

Update - I need to split the message based on StudentID (group by). I found groovy can do grouping. But whether we can split the messages using groovy.

3
groovymuleesbmule-elsplitter
Can you please post your desired output - Satheesh Kumar
Are you able to use the enterprise $$ datawave or just the community edition? - JRichardsz

3 Answers

0
votes

I would create a Java object and implement callable. Use this method to alter the payload. Then I would use a second database component and use the payload object (MEL) in the second query.

This object transforms data from a Database component output flow to a hybrid object that's used to display JSON with an embedded array of child data.

https://github.com/dlwhitehurst/modusbox-orders/blob/master/src/main/java/org/dlw/transport/OrdersTransformSingleton.java

Check out the return on the callable method and see how you can "transform" the data yourself.

Here's the snippet in the mule config that instances the needed Java component.

    <spring:beans>
  <spring:bean id="ordersTransform" name="OrdersTransformSingleton"
    class="org.dlw.transport.OrdersTransformSingleton" scope="singleton">
  </spring:bean>
  <spring:bean id="jdbcDataSource" class="org.enhydra.jdbc.standard.StandardDataSource" destroy-method="shutdown">
    <spring:property name="driverName" value="com.mysql.jdbc.Driver"/>
    <spring:property name="url" value="${database.url}"/>
  </spring:bean>      
</spring:beans>

The object is used in the flow like this ...

    <flow name="get:/orders:api-config">
    <set-payload value="[&#xD;&#xA;  {&#xD;&#xA;    &quot;orderId&quot;: 1233,&#xD;&#xA;    &quot;placementDate&quot;: &quot;2016-06-02&quot;,&#xD;&#xA;    &quot;customerName&quot;: &quot;Sally Hansen&quot;,&#xD;&#xA;     &quot;orderItems&quot;:[&#xD;&#xA;       {&#xD;&#xA;         &quot;orderItemId&quot;: 1323,&#xD;&#xA;         &quot;orderId&quot;: 438577,&#xD;&#xA;         &quot;itemId&quot;: 23058,&#xD;&#xA;         &quot;itemName&quot;: &quot;Salt&quot;,&#xD;&#xA;         &quot;itemCount&quot;: 3,&#xD;&#xA;         &quot;qtyItemCost&quot;: &quot;$2.76&quot;&#xD;&#xA;       },&#xD;&#xA;       {&#xD;&#xA;         &quot;orderItemId&quot;: 1323,&#xD;&#xA;         &quot;orderId&quot;: 438577,&#xD;&#xA;         &quot;itemId&quot;: 23058,&#xD;&#xA;         &quot;itemName&quot;: &quot;Pepper&quot;,&#xD;&#xA;         &quot;itemCount&quot;: 3,&#xD;&#xA;         &quot;qtyItemCost&quot;: &quot;$8.79&quot;&#xD;&#xA;       }&#xD;&#xA;     ]  &#xD;&#xA;  },&#xD;&#xA;  {&#xD;&#xA;    &quot;orderId&quot;: 1233,&#xD;&#xA;    &quot;placementDate&quot;: &quot;2016-06-02&quot;,&#xD;&#xA;    &quot;customerName&quot;: &quot;Billy Wilson&quot;,&#xD;&#xA;     &quot;orderItems&quot;:[&#xD;&#xA;       {&#xD;&#xA;         &quot;orderItemId&quot;: 1323,&#xD;&#xA;         &quot;orderId&quot;: 438577,&#xD;&#xA;         &quot;itemId&quot;: 23058,&#xD;&#xA;         &quot;itemName&quot;: &quot;Wheat Flour&quot;,&#xD;&#xA;         &quot;itemCount&quot;: 3,&#xD;&#xA;         &quot;qtyItemCost&quot;: &quot;$10.12&quot;&#xD;&#xA;       },&#xD;&#xA;       {&#xD;&#xA;         &quot;orderItemId&quot;: 1323,&#xD;&#xA;         &quot;orderId&quot;: 438577,&#xD;&#xA;         &quot;itemId&quot;: 23058,&#xD;&#xA;         &quot;itemName&quot;: &quot;Tomato Paste&quot;,&#xD;&#xA;         &quot;itemCount&quot;: 3,&#xD;&#xA;         &quot;qtyItemCost&quot;: &quot;$9.21&quot;&#xD;&#xA;       }&#xD;&#xA;     ]  &#xD;&#xA;  }&#xD;&#xA;]" doc:name="Set Payload"/>
    <db:select config-ref="MySQL_Configuration" doc:name="Database">
        <db:parameterized-query><![CDATA[SELECT a.orderId, a.customerName, a.placementDate, b.orderItemId, b.itemId, c.itemName, b.itemCount, c.itemCost FROM modusbox.orders a, modusbox.orderitems b, modusbox.items c WHERE a.orderId = b.orderId AND b.itemId = c.itemId]]></db:parameterized-query>
    </db:select>
    <component doc:name="Java">
        <spring-object bean="OrdersTransformSingleton" />
    </component>
    <json:object-to-json-transformer doc:name="Object to JSON"/>
    <logger level="INFO" doc:name="Logger"/>
</flow>
0
votes

DataWeave is the right option for groupBy. If you don't have DataWeave as choice (using Community Edition), you can have a quick win with script engine groovy.

List which should be groupBy attribute mail

[
      {
        "mail": "[email protected]",
        "name": "lastname",
        "value": "Smith"
      },
      {
        "mail": "[email protected]",
        "name": "firstname",
        "value": "John"
      },
      {
        "mail": "[email protected]",
        "name": "lastname",
        "value": "Doe"
      },
      {
        "mail": "[email protected]",
        "name": "firstname",
        "value": "Lisa"
      }
]

Mule script component

<scripting:component>
    <scripting:script engine="groovy">
        <![CDATA[flowVars['recipients'].groupBy{it.mail}]]>
    </scripting:script>
</scripting:component>

Result of groupBy mail

{
    "[email protected]": [
        {
        "mail": "[email protected]",
        "name": "lastname",
        "value": "Smith"
        },
        {
        "mail": "[email protected]",
        "name": "firstname",
        "value": "John"
        }
    ],
    "[email protected]": [
        {
        "mail": "[email protected]",
        "name": "lastname",
        "value": "Doe"
        },
        {
        "mail": "[email protected]",
        "name": "firstname",
        "value": "Lisa"
        }
    ]
}

Works fine with Mule 3.8.1 CE.