Insert batch of data to DB using Ballerina

In the previous post [1] we discussed how to perform various operations using the Ballerina JDBC client. In this post I am going to share a detailed sample code where we can use batchUpdate operation to insert a batch of data to the database. The advantage of this operation is multiple number of data rows can be inserted using this operation and it will reduce the overhead of DB operations. If the same operation is performed using update operation multiple DB calls are send and it is not the most efficient way to do that.

In this sample I have created a sample http service which accepts a json as an input. That json contains set of data that we need to insert into the Customers table. I have used MySQL database for this and sample table can be created using below SQL script.

CREATE TABLE Customers (firstName varchar(255),lastName varchar(255),registrationID int,creditLimit decimal(7,2),country varchar(255));

The sample code given below is based on ballerina 0.970.0 release.

The above service can be invoked as follows :

The Sample output is as follows:

Size of batch:4
data row -0 status: 1
data row -1 status: 1
data row -2 status: 1
data row -3 status: 1

References:

[1] Ballerina JDBC Client — Performing DB Operations