Author: Aditya Veer
Upsert activity in the database updates existing data and inserts new data in the database.
Upsert Query in the Database in Mule
INSERT INTO Table_Name VALUES (….) into (…) ON DUPLICATE KEY UPDATE col1=val1, col2=val2.
Components
Select = Select records from database
Transform Message =
Transform Message =
Define metadata according to your requirements and map them
Batch Job =
Messages can be processed in batches using Mule. Within an application, you can enable a Batch Job scope, which divides messages into separate records, performs actions on each record, relays on the results, and moves the processed outcome to specific other systems or queues.
Reasons for using Batch Job
Batch Jobs include the ability to generate good reports, handle exceptions easily, and make data aggregation easier.
Batch Aggrigator =
A Batch Aggregator keeps adding data to a collection until a size equal to the Batch Aggregator’s size attribute is added.Â
In ‘Batch Job’ enter the details mentioned or according to your usecase
Add a ‘Batch Aggregator’ in the ‘Batch Step’
Keep the Aggregator Size to 200 and add a ‘Bulk Insert’ in it
Make the necessary configuration changes and enter ‘payload’ in the ‘Input Parameters’ field, as well as the above-mentioned upsert query.
Add a logger at ‘On Complete’ to log the data
Open the ‘Run Configuration’ window, then navigate to your mule application and select the ‘Always’ option in the ‘Clear Application Data’ window.
Run the application,
Your records will be successfully Upserted