Ballerina: How to connect Oracle database with Ballerina?

Question

I am new to Ballerina. How to create a connection to an Oracle database?

Answer 1

In ballerina, to connect to a database, we have to create a client (or rather an endpoint) of type jdbc. For example, if we want to connect to a mysql database you can create a jdbc client as below

endpoint jdbc:Client testDB {
   url: "jdbc:mysql://localhost:3306/testdb",
   username: "root",
   password: "root",
   poolOptions: { maximumPoolSize: 5 }
};

In the above example, the url field specify the database connection url. So if the database is oracle, then we can use the oracle based url scheme instead as below.

endpoint jdbc:Client testDB {
   url: "jdbc:oracle:thin:@localhost:1521/testdb",
   username: "test",
   password: "test",
   poolOptions: { maximumPoolSize: 5 }
};

Once you have created a client, then you can use it to do your operations based on the requirement. For example, the below code segment will create a new table in the database connected from the client.

import ballerina/io;
import ballerina/jdbc;

endpoint jdbc:Client testDB {
    url: "jdbc:oracle:thin:@localhost:1521/testdb",
    username: "test",
    password: "test",
    poolOptions: { maximumPoolSize: 5 }
};

function main(string... args) {
    var result = testDB->update("CREATE TABLE customers (customer_id number(10) NOT NULL,
                                customer_name varchar2(50) NOT NULL, city varchar2(50))");

    match result {
        int retInt => io:println("status: " + retInt);
        error e => io:println("failed: " + e.message);
    }
}

You also have to drop the Oracle JDBC driver jar into BALLERINA_HOME/bre/lib to get this working.

For a complete set of operations, you can refer this guide - https://ballerina.io/learn/by-example/jdbc-client.html