import ballerina/io;
import ballerinax/mysql;
import ballerina/sql;

public function main() returns error? {
    // Runs the prerequisite setup for the example.
    check beforeExample();

    // Initializes the MySQL client.
    mysql:Client mysqlClient = check new (user = "root", 
            password = "Test@123", database = "MYSQL_BBE");

    // The records to be inserted.
    var insertRecords = [
        {
            firstName: "Peter",
            lastName: "Stuart",
            registrationID: 1,
            creditLimit: 5000.75,
            country: "USA"
        }, 
        {
            firstName: "Stephanie",
            lastName: "Mike",
            registrationID: 2,
            creditLimit: 8000.00,
            country: "USA"
        }, 
        {
            firstName: "Bill",
            lastName: "John",
            registrationID: 3,
            creditLimit: 3000.25,
            country: "USA"
        }
    ];

    // Creates a batch parameterized query.
    sql:ParameterizedQuery[] insertQueries = 
        from var data in insertRecords
        select `INSERT INTO Customers
                (firstName, lastName, registrationID, creditLimit, country)
                VALUES (${data.firstName}, ${data.lastName},
                ${data.registrationID}, ${data.creditLimit}, ${data.country})`;

    // Inserts the records with the auto-generated ID.
    sql:ExecutionResult[] result = 
                            check mysqlClient->batchExecute(insertQueries);

    int[] generatedIds = [];
    foreach var summary in result {
        generatedIds.push(<int>summary.lastInsertId);
    }
    io:println("\nInsert success, generated IDs are: ", generatedIds, "\n");

    // Checks the data after the batch execution.
    stream<record {}, error?> resultStream =
        mysqlClient->query(`SELECT * FROM Customers`);

    io:println("Data in Customers table:");
    check from record {} resultRecord in resultStream
        do {
            io:println(resultRecord.toString());
        };

    // Performs the cleanup after the example.
    check afterExample(mysqlClient);
}

// Initializes the database as a prerequisite to the example.
function beforeExample() returns sql:Error? {
    mysql:Client mysqlClient = check new (user = "root", password = "Test@123");

    // Creates a database.
    _ = check mysqlClient->execute(`CREATE DATABASE MYSQL_BBE`);

    // Creates a table in the database.
    _ = check mysqlClient->execute(`CREATE TABLE MYSQL_BBE.Customers
            (customerId INTEGER NOT NULL AUTO_INCREMENT,
            firstName VARCHAR(300), lastName  VARCHAR(300),
            registrationID INTEGER, creditLimit DOUBLE,
            country  VARCHAR(300), PRIMARY KEY (customerId))`);

    check mysqlClient.close();
}

// Cleans up the database after running the example.
function afterExample(mysql:Client mysqlClient) returns sql:Error? {
    // Cleans the database.
    _ = check mysqlClient->execute(`DROP DATABASE MYSQL_BBE`);

    // Closes the MySQL client.
    check mysqlClient.close();
}

Batch execute

This BBE demonstrates how to use the MySQL client to execute a batch of DDL/DML operations. Note that the MySQL database driver JAR should be defined in the Ballerina.toml file as a dependency. For a sample configuration and more information on the underlying module, see the MySQL module. The MySQL connector uses database properties from MySQL version 8.0.13 onwards. Therefore, it is recommended to use a MySQL driver version greater than 8.0.13.

import ballerina/io;
import ballerinax/mysql;
import ballerina/sql;
public function main() returns error? {
    check beforeExample();

Runs the prerequisite setup for the example.

    mysql:Client mysqlClient = check new (user = "root", 
            password = "Test@123", database = "MYSQL_BBE");

Initializes the MySQL client.

    var insertRecords = [
        {
            firstName: "Peter",
            lastName: "Stuart",
            registrationID: 1,
            creditLimit: 5000.75,
            country: "USA"
        }, 
        {
            firstName: "Stephanie",
            lastName: "Mike",
            registrationID: 2,
            creditLimit: 8000.00,
            country: "USA"
        }, 
        {
            firstName: "Bill",
            lastName: "John",
            registrationID: 3,
            creditLimit: 3000.25,
            country: "USA"
        }
    ];

The records to be inserted.

    sql:ParameterizedQuery[] insertQueries = 
        from var data in insertRecords
        select `INSERT INTO Customers
                (firstName, lastName, registrationID, creditLimit, country)
                VALUES (${data.firstName}, ${data.lastName},
                ${data.registrationID}, ${data.creditLimit}, ${data.country})`;

Creates a batch parameterized query.

    sql:ExecutionResult[] result = 
                            check mysqlClient->batchExecute(insertQueries);

Inserts the records with the auto-generated ID.

    int[] generatedIds = [];
    foreach var summary in result {
        generatedIds.push(<int>summary.lastInsertId);
    }
    io:println("\nInsert success, generated IDs are: ", generatedIds, "\n");
    stream<record {}, error?> resultStream =
        mysqlClient->query(`SELECT * FROM Customers`);

Checks the data after the batch execution.

    io:println("Data in Customers table:");
    check from record {} resultRecord in resultStream
        do {
            io:println(resultRecord.toString());
        };
    check afterExample(mysqlClient);
}

Performs the cleanup after the example.

function beforeExample() returns sql:Error? {
    mysql:Client mysqlClient = check new (user = "root", password = "Test@123");

Initializes the database as a prerequisite to the example.

    _ = check mysqlClient->execute(`CREATE DATABASE MYSQL_BBE`);

Creates a database.

    _ = check mysqlClient->execute(`CREATE TABLE MYSQL_BBE.Customers
            (customerId INTEGER NOT NULL AUTO_INCREMENT,
            firstName VARCHAR(300), lastName  VARCHAR(300),
            registrationID INTEGER, creditLimit DOUBLE,
            country  VARCHAR(300), PRIMARY KEY (customerId))`);

Creates a table in the database.

    check mysqlClient.close();
}
function afterExample(mysql:Client mysqlClient) returns sql:Error? {

Cleans up the database after running the example.

    _ = check mysqlClient->execute(`DROP DATABASE MYSQL_BBE`);

Cleans the database.

    check mysqlClient.close();
}

Closes the MySQL client.

# Create a Ballerina project.
# Copy the example to the project and add relevant database driver jar details to the `Ballerina.toml` file.
# Execute the command below to build and run the project.
bal run
Insert success, generated IDs are: [1,2,3]
Data in Customers table:
{"customerId":1,"firstName":"Peter","lastName":"Stuart","registrationID":1,"creditLimit":5000.75,"country":"USA"}
{"customerId":2,"firstName":"Stephanie","lastName":"Mike","registrationID":2,"creditLimit":8000.0,"country":"USA"}
{"customerId":3,"firstName":"Bill","lastName":"John","registrationID":3,"creditLimit":3000.25,"country":"USA"}