ballerina/jdbc package
Package overview
This package provides the functionality required to access and manipulate data stored in any type of relational database that is accessible via Java Database Connectivity (JDBC).
Endpoint
To access a database, you must first create an Endpoint
, which is a virtual representation of the physical endpoint that you are trying to connect to. Create an endpoint of the JDBC client type (i.e., jdbc:Client
) and provide the necessary connection parameters. This will create a pool of connections to the specified database. A sample for creating an endpoint with JDBC client can be found below.
NOTE: Even though JDBC client type supports connecting to any type of relational database that is accessible via JDBC, if you are using a MySQL or H2 database, it is recommended to use endpoints that are created using the client types specific to them via the relevant ballerina packages.
Database operations
Once the endpoint is created, database operations can be executed through that endpoint. This package provides support for creating tables and executing stored procedures. It also supports selecting, inserting, deleting, updating, and batch updating data. Samples for these operations could be found below. Details of the SQL data types and query parameters relevant for these database operations could be found in the documentation of SQL package. Once the operation is completed, stop
function must be called to terminate the connection pool of the endpoint.
Samples
Creating an endpoint
endpoint jdbc:Client testDB {
url:"jdbc:hsqldb:file:./target/tempdb/TEST_SQL_CONNECTOR",
username:"SA",
poolOptions:{maximumPoolSize:1}
};
The full list of endpoint properties can be found in the sql:PoolOptions
type.
Creating tables
Following is an example of creating a table with two fields. One field is of type int and the other of varchar. The CREATE statement is executed via the update
operation of the endpoint.
//Create ‘Students’ table with fields ‘StudentID’ and ‘LastName’.
int returnValue = check testDB->update("CREATE TABLE IF NOT EXISTS Students(StudentID int, LastName varchar(255))");
//Terminate the connection pool.
testDB.stop();
Selecting data
Following is an example of selecting data. First, a type is created to represent the returned result set. Then the SELECT query is execute via the select
operation of the endpoint by passing that result set type. Once the query is executed, each data record can be retrieved by looping the result set.
//Define a type to represent the results set.
type ResultCustomers {
string FIRSTNAME,
};
//Retrieve the FirstName from the Customers table by providing the registrationID.
table dt = check testDB->select("SELECT FirstName from Customers where registrationID = 1", ResultCustomers);
string firstName;
//Iterate the results-set and retrieve the first name.
while (dt.hasNext()) {
ResultCustomers rs = check <ResultCustomers>dt.getNext();
firstName = rs.FIRSTNAME;
}
testDB.stop();
Inserting data
Following are two examples of data insertion by executing an INSERT statement using the update
operation of the endpoint.
In the first example, query parameter values are directly passed into the update
operation:
//Insert data into Customers table
var insertCount = check testDB->update("Insert into Customers (firstName,lastName,registrationID,creditLimit,country)
values ('James', 'Clerk', 2, 5000.75, 'USA')");
testDB.stop();
In the following second example, parameter values are first assigned to local variables of type sql:Parameter
and then passed into the update
operation.
string s1 = "Anne";
sql:Parameter para1 = {sqlType:sql:TYPE_VARCHAR, value:s1, direction:sql:DIRECTION_IN};
sql:Parameter para2 = {sqlType:sql:TYPE_VARCHAR, value:"James", direction:sql:DIRECTION_IN};
sql:Parameter para3 = {sqlType:sql:TYPE_INTEGER, value:3, direction:sql:DIRECTION_IN};
sql:Parameter para4 = {sqlType:sql:TYPE_DOUBLE, value:5000.75, direction:sql:DIRECTION_IN};
sql:Parameter para5 = {sqlType:sql:TYPE_VARCHAR, value:"UK", direction:sql:DIRECTION_IN};
//Insert data into Customers table
int insertCount = check testDB->update("Insert into Customers (firstName,lastName,registrationID,creditLimit,country)
values (?,?,?,?,?)", para1, para2, para3, para4, para5);
testDB.stop();
Updating data
Following is an example of modifying data by executing an UPDATE statement via the update
operation of the endpoint.
var updateCount = check testDB->update("Update Customers set country = 'UK' where registrationID = 1");
testDB.stop();
Batch updating data
Following example demonstrates how to insert multiple records with a single INSERT statement executed via the batchUpdate
operation of the endpoint. This is done by first creating multiple parameter arrays, each representing a single record, and then providing those to the batchUpdate
operation. Similarly, multiple UPDATE statements could be also executed via batchUpdate
.
//Create the first batch of parameters
sql:Parameter para1 = {sqlType:sql:TYPE_VARCHAR, value:"Alex"};
sql:Parameter para2 = {sqlType:sql:TYPE_VARCHAR, value:"Smith"};
sql:Parameter para3 = {sqlType:sql:TYPE_INTEGER, value:20};
sql:Parameter para4 = {sqlType:sql:TYPE_DOUBLE, value:3400.5};
sql:Parameter para5 = {sqlType:sql:TYPE_VARCHAR, value:"USA"};
sql:Parameter[] parameters1 = [para1, para2, para3, para4, para5];
//Create the second batch of parameters
para1 = {sqlType:sql:TYPE_VARCHAR, value:"John"};
para2 = {sqlType:sql:TYPE_VARCHAR, value:"Wick"};
para3 = {sqlType:sql:TYPE_INTEGER, value:30};
para4 = {sqlType:sql:TYPE_DOUBLE, value:8000.5};
para5 = {sqlType:sql:TYPE_VARCHAR, value:"India"};
sql:Parameter[] parameters2 = [para1, para2, para3, para4, para5];
//Do the batch update by passing the multiple parameter arrays
int[] updateCount = check testDB->batchUpdate("Insert into Customers (firstName,lastName,registrationID,creditLimit,country)
values (?,?,?,?,?)", parameters1, parameters2);
testDB.stop();
Calling stored procedures
Following are three examples of executing stored procedures via the call
operation of the endpoint.
In the first example, a simple stored procedure that inserts data are called:
_ = testDB->call("{call InsertPersonData(100,'James')}", ());
testDB.stop();
In the next example, a stored procedure that does a data retrieval is called. It needs to pass a type for the returned result set. Similar to the executing a SELECT statement, data can be retrieved by iteratively reading the returned result set.
//Define a type to represent the results set.
type ResultCustomers {
string FIRSTNAME,
};
table[] dts = check testDB->call("{call SelectPersonData()}", [ResultCustomers]);
string firstName;
while (dts[0].hasNext()) {
ResultCustomers rs = check <ResultCustomers>dts[0].getNext();
firstName = rs.FIRSTNAME;
}
testDB.stop();
In the third example, a stored procedure performing a data retrieval is called, but this time it returns multiple result sets.
type ResultCustomers {
string FIRSTNAME,
};
type ResultCustomers2 {
string FIRSTNAME,
string LASTNAME,
};
//call the stored procedure
table[] dts = check testDB->call("{call SelectPersonDataMultiple()}", [ResultCustomers, ResultCustomers2]);
string firstName1;
string firstName2;
string lastName;
//read the first result set
while (dts[0].hasNext()) {
ResultCustomers rs = check <ResultCustomers>dts[0].getNext();
firstName1 = rs.FIRSTNAME;
}
//read the second result set
while (dts[1].hasNext()) {
ResultCustomers2 rs = check <ResultCustomers2>dts[1].getNext();
firstName2 = rs.FIRSTNAME;
lastName = rs.LASTNAME;
}
testDB.stop();
Package Contents
Records Summary
Record | Description | ||
---|---|---|---|
ClientEndpointConfiguration | The Client endpoint configuration for SQL databases. |
Objects Summary
Object | Description | ||
---|---|---|---|
Client | Represents an JDBC SQL client endpoint. |
Functions Summary
Return Type | Function and Description | ||
---|---|---|---|
CallerActions | createClient(ClientEndpointConfiguration config) |
public type ClientEndpointConfiguration
The Client endpoint configuration for SQL databases.
Field Name | Data Type | Default Value | Description |
---|---|---|---|
url | string | URL of the database to connect |
|
username | string | Username for the database connection |
|
password | string | Password for the database connection |
|
poolOptions | PoolOptions | Properties for the connection pool configuration |
|
dbOptions | map | Data source specific properties which are used along with the dataSourceClassName |
public function createClient(ClientEndpointConfiguration config) returns (CallerActions)
Parameter Name | Data Type | Default Value | Description |
---|---|---|---|
config | ClientEndpointConfiguration |
Return Type | Description | ||
---|---|---|---|
CallerActions |
public type Client object
Represents an JDBC SQL client endpoint.
-
<Client> getCallerActions() returns (CallerActions)
Returns the connector that the client code uses.
Return Type Description CallerActions -
<Client> stop()
Stops the JDBC client.