Ballerina JDBC Client — Performing DB Operations

Published in

Ballerina Swan Lake Tech Blog

6 min readDec 2, 2017

In the previous post [1], we have discussed about the JDBC Client endpoint definition and initialization process. In this post we will look into the details on how to perform data definition, manipulation and access functions using the ballerina JDBC client. All these operations are also supported by the ballerina MySQL[2] and H2[3] clients.

Any Ballerina connector is designed with set of operations which represent the interactions that one can have with the remote service. JDBC Client endpoint comes with six operations as follows which are used to interact with the database.

select
update
updateWithGeneratedKeys
call
batchUpdate
getProxyTable

The syntax used in the samples in this post is based on Ballerina 0.970.0 release [4].

The Parameter type

Before looking into the details about each operation, let’s look at the record type named “Parameter” in ballerina/sql package [5]. All the common record types for any ballerina data client is defined in ballerina/sql package. So to use Parameter type we need to import balerina/sql package. This is used as an argument for SQL connector operations other than the getProxyTable operation.

The “Parameter” type represents the parameter passed into a SQL statement which is specified in the operation. SQL statement should be written with “?” for all the places where parameter is expected and set of parameters are passed into the operation, where there is a “Parameter” record for each question mark in the SQL statement.

NOTE: The parameter type is not required for primitive types like int, float, string, boolean and blob and those values can be passed directly to the operation.

Ex:

SELECT name, id FROM employee WHERE age = ? AND city = ?

Above query has two variable place holders for age and city. So the above statement should be passed into the select action with two“sql:Parameter” values.

The details of the Parameter type fields are as follows.

sqlType — This is the actual SQL data type of the given parameter value. When using a parameter, use the same SQL type as the actual database table column type. Otherwise data loss can occur. Following types are supported for sqlType.

sql:TYPE_VARCHAR
sql:TYPE_CHAR
sql:TYPE_LONGVARCHAR
sql:TYPE_NCHAR
sql:TYPE_LONGNVARCHAR
sql:TYPE_NVARCHARR
sql:TYPE_BIT
sql:TYPE_BOOLEAN
sql:TYPE_TINYINT
sql:TYPE_SMALLINT
sql:TYPE_INTEGER
sql:TYPE_BIGINT
sql:TYPE_NUMERIC
sql:TYPE_DECIMAL
sql:TYPE_REAL
sql:TYPE_FLOAT
sql:TYPE_DOUBLE
sql:TYPE_BINARY
sql:TYPE_BLOB
sql:TYPE_LONGVARBINARY
sql:TYPE_VARBINARY
sql:TYPE_CLOB
sql:TYPE_NCLOB
sql:TYPE_DATE
sql:TYPE_TIME
sql:TYPE_DATETIME
sql:TYPE_TIMESTAMP
sql:TYPE_ARRAY
sql:TYPE_STRUCT
sql:TYPE_REFCURSOR

value — This is the value of the parameter which needs to pass in the sql statement. This is “any” type variable and based on the SQL type and this can be integer, string, boolean, blob, float etc.

direction — indicates whether the parameter is input-only, output-only or bidirectional when using with stored procedures. Following are the direction types and the default value is sql:DIRECTION_IN.

recordType — In the case of OUT direction, if the sqlType is REFCURSOR, the out parameter’s return value will be a table. This typedesc represents the record type of the returned table in the out parameter value.

sql:DIRECTION_IN
sql:DIRECTION_OUT
sql:DIRECTION_INOUT

Select Operation

The select operation [6] is used to retrieve zero or more rows from given tables/views which matches the criteria given in the where clause.

It takes a query string , optionally record type and Parameters as the input and returns a table which represent the output tabular data or an error if error occurred.

Ex :

Without parameters:

With parameters:

Since the above parameter is an int type, instead of creating a sql:Parameter we can directly pass the value. The sql:Parameter is required for complex parameters where exact data type is need to specified or complex types are passed in as values.

By default the returned table contains only a pointer to the actual data in the database and the actual data is loaded to the memory only when that table is accessed. With this behavior it is possible to query tables with large data and stream that data in the response without growing the memory in the service. Since the table is only a pointer, the returned table can be accessed only once and for the second iteration of the same table no data is shown. If there is a situation where the returned table needs to iterate multiple times, the data should be loaded to the memory by specifying the loadToMemory flag to true. See below sample where the returned table is printing twice.

Update Operation

The update operation [7] executes the given SQL statement, which may be an

Data Manipulation statement — which can be NSERT, UPDATE, or DELETE statement or
Data Definition Statement — define the different structures in a database. It can be used to create, modify, and remove database objects such as tables, indexes, procedures, and users.

It takes a sql string and Parameters as the input and returns an integer or an error if error occurred. If the operation is for data manipulation, it returns the number of updated rows as the output. If it is a data definition statement it returns 0 to indicate that the update is succeeded.

Ex :

UpdateWithGeneratedKeys Operation

The types of SQL statements which can execute via the this operation is similar to the update operation [8]. The difference is this action allows to retrieve the auto generated keys as part of the statement execution. The first and third parameters are similar to the update action. The second parameter of string array allows to specify the names of the columns in the target table that contain the auto-generated keys that should be returned. This is useful when the table is having multiple auto generated keys. If key columns are not specified only the auto incremented column value (if there is auto-incremented column) will be returned.

This action returns two values.

int — The number of updated rows.
string [] — The auto generated column values

Ex:

Call Operation

The call operation [9] is used to execute SQL stored procedure and input parameters are similar to the above operations. Additionally it has optional typedesc array, to specify the types of the returned tables if there are any. It will return a table array if the procedure call is returning result sets/ref cursors. If the operation occured an error, error will be returned and else nil value is returned. The Direction of sql:Parameter is used to specify the parameter direction for the procedure call.

Ex : Execute a procedure with one out parameter and one inout parameter

BatchUpdate Operation

The batchUpdate action[10] submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts. The int elements of the array that is returned are ordered to correspond to the commands in the batch. A parameter array of array is used as input argument where each array represents the parameters for a single batch.

Ex:

GetProxyTable Operation

The tables returned by select or call operations are read only and they don’t allow add/remove values to them. The getProxyTable operation [11] returns a table to which we can perform select/add/remove operations and those operations will be directly reflected in the actual database table. To get a proxy table, the name of the actual database table and the mapping record type should be given.

For the remove operation we need to pass a function pointer as a parameter. All the records which satisfies the given criteria will get deleted by this operation.

Ex:

Shutdown the endpoint

When initializing a Ballerina JDBC client endpoint , it will create a connection pool to the database which is a cache of open database connections. So normally in a Ballerina service the JDBC client endpoint is created in the service level rather than the resource level. So it will be available throughout the lifetime of the service. But there may be some cases where we need the SQL connector only for limited time such as rarely used resource , or function which is called rarely etc. In such situations we need to properly shutdown the connection pool after we finish the work with the endpoint as follows.

testDB.stop();

This post has provided detailed information on each JDBC client operations and a complete example on the usage of the operation can be found in the Ballerina JDBC client sample [12].