import ballerina/io;
import ballerinax/java.jdbc;
import ballerina/sql;

jdbc:Client dbClient = check new (url = "jdbc:h2:file:./master/orderdb",
                           user = "test", password = "test");

public function main() returns error? {
    // Uses a raw template to create `Orders` table.
    _ = check dbClient->execute(`CREATE TABLE IF NOT EXISTS Orders
                                (orderId INTEGER NOT NULL,
                                customerId INTEGER, noOfItems INTEGER,
                                PRIMARY KEY (orderId))`);
    // Uses a raw template to insert values to `Orders` table.
    _ = check dbClient->execute(`INSERT INTO Orders (orderId, customerId,
                                noOfItems) VALUES (1, 1, 20)`);
    _ = check dbClient->execute(`INSERT INTO Orders (orderId, customerId,
                                noOfItems) VALUES (2, 1, 15)`);

    stream<record {| anydata...; |}, sql:Error?> strm = getOrders(1);
    record {|record {} value;|}|sql:Error? v = strm.next();
    while (v is record {|record {} value;|}) {
        record {} value = v.value;
        io:println(value);
        v = strm.next();
    }
}

function getOrders(int customerId)
    returns stream<record {| anydata...; |}, sql:Error?> {
    // In this raw template, the `customerId` variable is interpolated in the literal.
    return dbClient->query(`SELECT * FROM orders
                          WHERE customerId = ${customerId}`);

}

Raw templates

A raw template is a backtick template without a tag. Exposes result of phase 1 without further processing. Raw template is evaluated by evaluating each expression and creating an object containing.

  • an array of the strings separated by insertions
  • an array of the results of expression evaluation and an array of strings separating


Important use case: SQL parameters.

Note that the relevant database driver JAR should be defined in the Ballerina.toml file as a dependency. This sample is based on an H2 database and the H2 database driver JAR need to be added to Ballerina.toml file. For a sample configuration and more information on the underlying module, see the JDBC module .

This sample is written using H2 2.1.210 and it is recommended to use H2 JAR with versions higher than 2.1.210.

 
import ballerina/io;
import ballerinax/java.jdbc;
import ballerina/sql;

jdbc:Client dbClient = check new (url = "jdbc:h2:file:./master/orderdb",
                           user = "test", password = "test");

public function main() returns error? {

    _ = check dbClient->execute(`CREATE TABLE IF NOT EXISTS Orders
                                (orderId INTEGER NOT NULL,
                                customerId INTEGER, noOfItems INTEGER,
                                PRIMARY KEY (orderId))`);

Uses a raw template to create Orders table.

    _ = check dbClient->execute(`INSERT INTO Orders (orderId, customerId,
                                noOfItems) VALUES (1, 1, 20)`);
    _ = check dbClient->execute(`INSERT INTO Orders (orderId, customerId,
                                noOfItems) VALUES (2, 1, 15)`);

Uses a raw template to insert values to Orders table.

    stream<record {| anydata...; |}, sql:Error?> strm = getOrders(1);
    record {|record {} value;|}|sql:Error? v = strm.next();
    while (v is record {|record {} value;|}) {
        record {} value = v.value;
        io:println(value);
        v = strm.next();
    }
}

function getOrders(int customerId)
    returns stream<record {| anydata...; |}, sql:Error?> {

    return dbClient->query(`SELECT * FROM orders
                          WHERE customerId = ${customerId}`);

In this raw template, the customerId variable is interpolated in the literal.

}

[[platform.java11.dependency]]
path = "h2-2.1.210.jar"

# 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

{"ORDERID":1,"CUSTOMERID":1,"NOOFITEMS":20}
{"ORDERID":2,"CUSTOMERID":1,"NOOFITEMS":15}