import ballerina/io;
import ballerinax/java.jdbc;
jdbc:Client testDB1 = new({
    url: "jdbc:h2:file:./xa-transactions/Testdb1",
    username: "test",
    password: "test",
    poolOptions: { maximumPoolSize: 5, isXA: true }
});
jdbc:Client testDB2 = new({
    url: "jdbc:h2:file:./xa-transactions/Testdb2",
    username: "test",
    password: "test",
    poolOptions: { maximumPoolSize: 5, isXA: true }
});public function main() {
    var ret = testDB1->update("CREATE TABLE CUSTOMER (ID INTEGER
                    AUTO_INCREMENT, NAME VARCHAR(30))");
    handleUpdate(ret, "Create CUSTOMER table");
    ret = testDB2->update("CREATE TABLE SALARY (ID INT, VALUE FLOAT)");
    handleUpdate(ret, "Create SALARY table");
    transaction {
        var result = testDB1->update("INSERT INTO CUSTOMER(NAME)
                                        VALUES ('Anne')");
        int key = -1;
        if (result is jdbc:UpdateResult) {
            int count = result.updatedRowCount;
            key = <int>result.generatedKeys["ID"];
            io:println("Inserted row count: ", count);
            io:println("Generated key: ", key);
        } else {
            io:println("Insert to student table failed: ", result.reason());
        }
        ret = testDB2->update("INSERT INTO SALARY (ID, VALUE) VALUES (?, ?)",
                                    key, 2500);
        handleUpdate(ret, "Insert to SALARY table");
    } onretry {
        io:println("Retrying transaction");
    } committed {
        io:println("Transaction committed");
    } aborted {
        io:println("Transaction aborted");
    }
    ret = testDB1->update("DROP TABLE CUSTOMER");
    handleUpdate(ret, "Drop Table CUSTOMER");    ret = testDB2->update("DROP TABLE SALARY");
    handleUpdate(ret, "Drop Table SALARY");
    stopClient(testDB1);
    stopClient(testDB2);
}function onCommitFunction(string transactionId) {
    io:println("Transaction: ", transactionId, " committed");
}function onAbortFunction(string transactionId) {
    io:println("Transaction: ", transactionId, " aborted");
}
function handleUpdate(jdbc:UpdateResult|error returned, string message) {
    if (returned is jdbc:UpdateResult) {
        io:println(message, " status: ", returned.updatedRowCount);
    } else {
        io:println(message, " failed: ", returned.reason());
    }
}function stopClient(jdbc:Client db) {
    var stopRet = db.stop();
    if (stopRet is error) {
        io:println(stopRet.detail()["message"]);
    }
}

XA Transactions

Ballerina XA transactions are used when the transaction is happening over two or more databases. This example uses two H2 DBs (these are created when executing the example). Before running the example, change the DB connection properties as required. Ballerina transactions are at the experimental stage. Therefore, use the –experimental flag to enable them.

import ballerina/io;
import ballerinax/java.jdbc;
jdbc:Client testDB1 = new({
    url: "jdbc:h2:file:./xa-transactions/Testdb1",
    username: "test",
    password: "test",
    poolOptions: { maximumPoolSize: 5, isXA: true }
});

Create san endpoint for the first database named testDB1. Since this endpoint participates in a distributed transaction, the isXA property should be true.

jdbc:Client testDB2 = new({
    url: "jdbc:h2:file:./xa-transactions/Testdb2",
    username: "test",
    password: "test",
    poolOptions: { maximumPoolSize: 5, isXA: true }
});

Creates an endpoint for the second database named testDB2. Since this endpoint participates in a distributed transaction, the isXA property should be true.

public function main() {
    var ret = testDB1->update("CREATE TABLE CUSTOMER (ID INTEGER
                    AUTO_INCREMENT, NAME VARCHAR(30))");
    handleUpdate(ret, "Create CUSTOMER table");

Creates the table named CUSTOMER in the first database.

    ret = testDB2->update("CREATE TABLE SALARY (ID INT, VALUE FLOAT)");
    handleUpdate(ret, "Create SALARY table");

Creates the table named SALARY in the second database.

    transaction {

Begins the transaction.

        var result = testDB1->update("INSERT INTO CUSTOMER(NAME)
                                        VALUES ('Anne')");
        int key = -1;
        if (result is jdbc:UpdateResult) {
            int count = result.updatedRowCount;
            key = <int>result.generatedKeys["ID"];
            io:println("Inserted row count: ", count);
            io:println("Generated key: ", key);
        } else {
            io:println("Insert to student table failed: ", result.reason());
        }

This is the first remote function to participate in the transaction. It inserts the customer name to the first DB and gets the generated key.

        ret = testDB2->update("INSERT INTO SALARY (ID, VALUE) VALUES (?, ?)",
                                    key, 2500);
        handleUpdate(ret, "Insert to SALARY table");
    } onretry {
        io:println("Retrying transaction");
    } committed {
        io:println("Transaction committed");
    } aborted {
        io:println("Transaction aborted");
    }

This is the second remote function to participate in the transaction. It inserts the salary info to the second DB along with the key generated in the first DB.

    ret = testDB1->update("DROP TABLE CUSTOMER");
    handleUpdate(ret, "Drop Table CUSTOMER");

Drops the tables created for this sample.

    ret = testDB2->update("DROP TABLE SALARY");
    handleUpdate(ret, "Drop Table SALARY");
    stopClient(testDB1);
    stopClient(testDB2);
}

Stops the database clients.

function onCommitFunction(string transactionId) {
    io:println("Transaction: ", transactionId, " committed");
}
function onAbortFunction(string transactionId) {
    io:println("Transaction: ", transactionId, " aborted");
}
function handleUpdate(jdbc:UpdateResult|error returned, string message) {
    if (returned is jdbc:UpdateResult) {
        io:println(message, " status: ", returned.updatedRowCount);
    } else {
        io:println(message, " failed: ", returned.reason());
    }
}

This function handles the return values of the update() remote function.

function stopClient(jdbc:Client db) {
    var stopRet = db.stop();
    if (stopRet is error) {
        io:println(stopRet.detail()["message"]);
    }
}
# To run this sample, navigate to the directory that contains the
# `.bal` file, and execute the `ballerina run` command.
$ ballerina run --experimental xa_transactions.bal
[ballerina/http] started HTTP/WS listener 172.17.0.1:53599
Create CUSTOMER table status: 0
Create SALARY table status: 0
2018-12-04 20:12:03,507 INFO  [ballerina/log] - Created transaction: aec7b091-cbac-4d61-8834-bed8e1a3c9ec
Inserted row count: 1
Generated key: 1
Insert to SALARY table status: 1
2018-12-04 20:12:03,541 INFO  [ballerina/log] - Running 2-phase commit for transaction: aec7b091-cbac-4d61-8834-bed8e1a3c9ec:$anon$.$1
Transaction committed
Drop Table CUSTOMER status: 0
Drop Table SALARY status: 0
[ballerina/http] started HTTP/WS listener 10.100.7.118:64331
[ballerina/http] stopped HTTP/WS listener 10.100.7.118:64331