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

// Defines a record to load the query result schema as shown below in the
// 'getDataWithTypedQuery' function. In this example, all columns of the 
// customer table will be loaded. Therefore, the `Customer` record will be 
// created with all the columns. The column name of the result and the 
// defined field name of the record will be matched regardless of the letters' case.
type Customer record {|
    int customerId;
    string lastName;
    string firstName;
    int registrationId;
    float creditLimit;
    string country;
|};

public function main() returns error? {
    // Initializes the JDBC client.
    jdbc:Client jdbcClient = check new ("jdbc:h2:file:./target/bbes/java_jdbc",
        "rootUser", "rootPass");
    // Runs the prerequisite setup for the example.
    check beforeExample(jdbcClient);

    // Selects the rows in the database table via the query remote operation.
    // The result is returned as a stream and the elements of the stream can
    // be either a record or an error. The name and type of the attributes 
    // within the record from the `resultStream` will be automatically 
    // identified based on the column name and type of the query result.
    stream<Customer, error?> resultStream =
            jdbcClient->query(`SELECT * FROM Customers`);

    // If there is any error during the execution of the SQL query or
    // iteration of the result stream, the result stream will terminate and
    // return the error.
    check from Customer customer in resultStream
        do {
            io:println("Full Customer details: ", customer);
        };

    int customerId = 1;
    // Select a row in the database table via the query row operation.
    // This will return utmost one record. If no record is found, it will
    // throw an `sql:NoRowsError`.
    Customer customer = check jdbcClient->queryRow(
        `SELECT * FROM Customers where customerId = ${customerId}`);
    io:println("\nCustomer (customerId = 1) : ", customer);

    // The result of the count operation is provided as an int variable.
    // As this query returns only a single column on top of a single row,
    // this can be provided as an int variable.
    int totalCustomers = check jdbcClient->queryRow(
                    `SELECT COUNT(*) AS total FROM Customers`);
    io:println("\nTotal customers in the table : ", totalCustomers);

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

// Initializes the database as a prerequisite to the example.
function beforeExample(jdbc:Client jdbcClient) returns sql:Error? {
    // Creates a table in the database.
    _ = check jdbcClient->execute(`CREATE TABLE Customers(customerId INTEGER
            NOT NULL GENERATED BY DEFAULT AS IDENTITY, firstName  VARCHAR(300),
            lastName  VARCHAR(300), registrationID INTEGER, creditLimit DOUBLE,
            country  VARCHAR(300), PRIMARY KEY (customerId))`);

    // Adds records to the newly-created table.
    _ = check jdbcClient->execute(`INSERT INTO Customers (firstName,
            lastName, registrationID,creditLimit,country) VALUES ('Peter',
            'Stuart', 1, 5000.75, 'USA')`);
    _ = check jdbcClient->execute(`INSERT INTO Customers (firstName,
            lastName, registrationID,creditLimit,country) VALUES
            ('Dan', 'Brown', 2, 10000, 'UK')`);
}

// Cleans up the database after running the example.
function afterExample(jdbc:Client jdbcClient) returns sql:Error? {
    // Cleans the database.
    _ = check jdbcClient->execute(`DROP TABLE Customers`);

    // Closes the JDBC client.
    check jdbcClient.close();
}

Simple query

This BBE demonstrates how to use the JDBC client select query operations with the stream return type. 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.0.6 and it is recommended to use H2 JAR with versions higher than 2.0.2.

import ballerina/io;
import ballerinax/java.jdbc;
import ballerina/sql;
type Customer record {|
    int customerId;
    string lastName;
    string firstName;
    int registrationId;
    float creditLimit;
    string country;
|};

Defines a record to load the query result schema as shown below in the ‘getDataWithTypedQuery’ function. In this example, all columns of the customer table will be loaded. Therefore, the Customer record will be created with all the columns. The column name of the result and the defined field name of the record will be matched regardless of the letters’ case.

public function main() returns error? {
    jdbc:Client jdbcClient = check new ("jdbc:h2:file:./target/bbes/java_jdbc",
        "rootUser", "rootPass");

Initializes the JDBC client.

    check beforeExample(jdbcClient);

Runs the prerequisite setup for the example.

    stream<Customer, error?> resultStream =
            jdbcClient->query(`SELECT * FROM Customers`);

Selects the rows in the database table via the query remote operation. The result is returned as a stream and the elements of the stream can be either a record or an error. The name and type of the attributes within the record from the resultStream will be automatically identified based on the column name and type of the query result.

    check from Customer customer in resultStream
        do {
            io:println("Full Customer details: ", customer);
        };

If there is any error during the execution of the SQL query or iteration of the result stream, the result stream will terminate and return the error.

    int customerId = 1;
    Customer customer = check jdbcClient->queryRow(
        `SELECT * FROM Customers where customerId = ${customerId}`);
    io:println("\nCustomer (customerId = 1) : ", customer);

Select a row in the database table via the query row operation. This will return utmost one record. If no record is found, it will throw an sql:NoRowsError.

    int totalCustomers = check jdbcClient->queryRow(
                    `SELECT COUNT(*) AS total FROM Customers`);
    io:println("\nTotal customers in the table : ", totalCustomers);

The result of the count operation is provided as an int variable. As this query returns only a single column on top of a single row, this can be provided as an int variable.

    check afterExample(jdbcClient);
}

Performs the cleanup after the example.

function beforeExample(jdbc:Client jdbcClient) returns sql:Error? {

Initializes the database as a prerequisite to the example.

    _ = check jdbcClient->execute(`CREATE TABLE Customers(customerId INTEGER
            NOT NULL GENERATED BY DEFAULT AS IDENTITY, firstName  VARCHAR(300),
            lastName  VARCHAR(300), registrationID INTEGER, creditLimit DOUBLE,
            country  VARCHAR(300), PRIMARY KEY (customerId))`);

Creates a table in the database.

    _ = check jdbcClient->execute(`INSERT INTO Customers (firstName,
            lastName, registrationID,creditLimit,country) VALUES ('Peter',
            'Stuart', 1, 5000.75, 'USA')`);
    _ = check jdbcClient->execute(`INSERT INTO Customers (firstName,
            lastName, registrationID,creditLimit,country) VALUES
            ('Dan', 'Brown', 2, 10000, 'UK')`);
}

Adds records to the newly-created table.

function afterExample(jdbc:Client jdbcClient) returns sql:Error? {

Cleans up the database after running the example.

    _ = check jdbcClient->execute(`DROP TABLE Customers`);

Cleans the database.

    check jdbcClient.close();
}

Closes the JDBC 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
Full Customer details: {"customerId":1,"firstName":"Peter","lastName":"Stuart","registrationId":1,"creditLimit":5000.75,"country":"USA"}
Full Customer details: {"customerId":2,"firstName":"Dan","lastName":"Brown","registrationId":2,"creditLimit":10000.0,"country":"UK"}
Customer (customerId = 1) : {"customerId":1,"firstName":"Peter","lastName":"Stuart","registrationId":1,"creditLimit":5000.75,"country":"USA"}
Total customers in the table : 2