Ballerina JDBC Client— Connecting to DB

Ballerina[1] JDBC Client allows to connect to SQL based relational database systems and perform data definition, data access and data manipulation operations on the database. The JDBC Client for Ballerina is a native endpoint which is written in Java and it can connect to any database which has JDBC driver support.

Connecting With Database

The most important part of using the JDBC Client is properly initialize the connector. Users need to have a good understanding about the various configurations and connection options provided. Properly initializing the connector is the key point which affects all the actions and overall performance as well. Hence in this post I will discuss all the details on how to initialize the JDBC Client endpoint. The JDBC client endpoint is packaged in ballerina jdbc package [2]. When using the JDBC package, user may required to include ballerina sql package [3] as well, as it contains all common record types used across all the data clients like sql:Parameter, sql:PoolOptions etc.

Step 1 : The first step in connecting any database is copying the relevant JDBC driver provided for the particular database into the lib directory of the ballerina runtime which is located in BALLERINA_HOME/bre/lib.

Step 2 : Then JDBC Client endpoint can be initialized in ballerina code using different configuration options. Initializing the connector does not mean initializing a single connection to the DB. Instead it initialize a connection pool for the given database and the pool size is configurable.

JDBC Client Endpoint Definition

The definition of Ballerina JDBC Client endpoint configuration is as follows [4].

public type ClientEndpointConfiguration {
    string url,
    string username,
    string password,
    sql:PoolOptions poolOptions,
    map dbOptions,
};

The details of the endpoint configuration parameters are as follows.

url— The JDBC URL for the database.

username — The username to connect with database

password — The password to connect with database

poolOptions — The is a record in ballerina/sql package with the following fields [5]. Users can create a record by giving values to the required fields only and use it in the endpoint initialization.

• dataSourceClassName — Name of the DataSource class provided by the JDBC driver
• connectionInitSql — SQL statement that will be executed after every new connection creation before adding it to the pool
• autoCommit — Auto-commit behavior of connections returned from the pool and the default value is true.
• isXA — Whether Connections are used for a distributed transaction. Default: false
• maximumPoolSize — Maximum size that the pool is allowed to reach, including both idle and in-use connections. Default: 10
• connectionTimeout — Maximum number of milliseconds that a client will wait for a connection from the pool. Lowest acceptable connection timeout is 250 ms. Default: 30000 (30 seconds)
• idleTimeout — Maximum amount of time that a connection is allowed to sit idle in the pool. The minimum allowed value is 10000ms (10 seconds). Default value: 600000 (10 minutes)
• minimumIdle — Minimum number of idle connections that pool tries to maintain in the pool. Default: same as maximumPoolSize
• maxLifetime — Maximum lifetime of a connection in the pool. A value of 0 indicates no maximum lifetime (infinite lifetime), subject of course to the idleTimeout setting. Default: 1800000 (30 minutes)
• validationTimeout — Maximum amount of time that a connection will be tested for aliveness. Lowest acceptable validation timeout is 250 ms. Default: 5000

dbOptions— A map of data source specific properties which are used along with the dataSourceClassName. These properties are applied only if the dataSourceClassName is given. This allows to set any DB specific properties when initializing the connections in the connection pool.

SQL Client Endpoint Initialization Examples

Since we have looked into the detailed definition of the JDBC endpoint, now let’s look at some examples on different ways of connecting to the DB. These examples are based on MySQL DB and MySQL JDBC driver [6] should be copied into bre/lib folder.

• By using default pool properties:

This is the simplest way to initialize a JDBC Client endpoint where the connection pool will be initialized with the default values.

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

• By using customized pool properties:

The pool options can be specified by initializing the poolOptions field. In the below sample two properties “maximumPoolSize” and “ConnectionTimeout” is specified. The connection pool will be initialized with these two properties along with the default values for the other properties.

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

A simple example of using JDBC Client endpoint is as follows.

Details on JDBC client operations will be covered in a future post.

References:

[1] Ballerina Language

[2] Ballerina JDBC package

[3] Ballerina SQL package

[4] JDBC Client endpoint configuration

[5] SQL PoolOptions

[6] MySQL JDBC Driver