Bulk Insert with database connector with different payload and queries

Question

I am using mule database connector to insert update in database . now i have different queries like insert and update in different table , and payload for them will be different as well . how can i achieve bulk operations in this. can i save the queries in a flow variable as list , and accordingly save the values in another list and pass it both to database flow ? will it work .

so i want to generate raw sql queries and save it to file and then use bulk execute for that . does mule provide any tostring method to just convert the query with placeholders to actual raw query ?

like i have query

update table mytable set column1 = #[payload.column1], column2 = #[payload.id]

to

update table mytable set column1 = 'stringvalue', column2 = 1234 ; 

Answer 1

Mule's database component does support bulk operations. You can select Bulk Execute in the Operation. The implementation is descriptive when you select the operation. With regards to making the query dynamic, you can pass the values from variables or property files, as per your convenience.

Answer 2

You can have stored procedure for insert and update accepting input parameters as array.Send the records in blocks inside for loop by setting batch size. This will result in less round trips.

Below is the link to article and has all the details https://dzone.com/articles/passing-java-arrays-in-oracle-stored-procedure-fro