ballerina/h2 module
Module overview
This module provides the functionality that is required to access and manipulate the data stored in an H2 database.
Creating the client
You need to create a client
object to access a database. You can create a sample H2 client as follows.
Handling the connection pool
There are 3 possible usage scenarios for a connection pool.
1. The globally shareable connection pool
If you do not provide the poolOptions
field, a globally shareable pool will be created for your database, unless
a connection pool matching the properties you provided already exists.
Info: This is the connection pool that is used by default.
h2:Client testDB = new({
path: "/home/ballerina/test/",
name: "testdb",
username: "SA",
password: ""
});
2. An unshareable connection pool owned by the client
If you define the poolOptions
field inline, an unshareable connection pool will be created.
h2:Client testDB = new({
path: "/home/ballerina/test/",
name: "testdb",
username: "SA",
password: "",
poolOptions: { maximumPoolSize: 5 }
});
3. A locally shareable connection pool
If you create a record of the sql:PoolOptions
type and reuse that in the configuration of multiple clients, a shared
connection pool will be created, for each set of clients that connect to the same database instance with the same set
of properties.
h2:Client testDB1;
h2:Client testDB2;
h2:Client testDB3;
sql:PoolOptions poolOptions1 = { maximumPoolSize: 5 };
testDB1 = new({
path: "/home/ballerina/test",
name: "testdb1",
username: "SA",
password: "",
poolOptions: poolOptions1
});
testDB2 = new({
path: "/home/ballerina/test",
name: "testdb2",
username: "SA",
password: "",
poolOptions: poolOptions1
});
testDB3 = new({
path: "/home/ballerina/test",
name: "testdb1",
username: "SA",
password: "",
poolOptions: poolOptions1
});
For the default values of the connection pool properties, see the documentation of the sql:PoolOptions
type.
Handling database operations
Once the client is created, the database operations can be executed through that client. This module provides support for creating tables and executing stored procedures. It also supports selecting, inserting, deleting, updating a single data record, and updating data in batches.
For more details on the supported remote functions, and of the SQL data types and query parameters relevant to these database operations, see the documentation of the sql
module.
Samples
Creating a client in the embedded mode of H2
h2:Client testDB = new({
path: "/home/ballerina/test/",
name: "testdb",
username: "SA",
password: ""
});
Creating a client in the server mode of H2
h2:Client testDB = new({
host: "localhost",
port: 9092,
name: "testdb",
username: "SA",
password: ""
});
Creating a client in the in-memory mode of H2
h2:Client testDB = new({
name: "testdb",
username: "SA",
password: ""
});
For a complete list of client properties, see the documentation of the sql:PoolOptions
type.
Records Summary
Record | Description | ||
---|---|---|---|
EmbeddedModeConfig | The Client endpoint configuration for the embedded mode of h2 databases. | ||
InMemoryConfig | The Client endpoint configuration for the in-memory mode of h2 databases. | ||
ServerModeConfig | The Client endpoint configuration for the server mode of h2 databases. |
Endpoints Summary
Endpoint | Description | ||
---|---|---|---|
Client | Represents an H2 client endpoint. |
public type EmbeddedModeConfig record
The Client endpoint configuration for the embedded mode of h2 databases.
Field Name | Data Type | Default Value | Description |
---|---|---|---|
path | string | The path of the database connection (in case of file based DB) |
public type InMemoryConfig record
The Client endpoint configuration for the in-memory mode of h2 databases.
Field Name | Data Type | Default Value | Description |
---|---|---|---|
name | string | The name of the database to connect |
|
username | string | Username for the database connection |
|
password | string | Password for the database connection |
|
poolOptions | sql:PoolOptions | Properties for the connection pool configuration. Refer |
|
dbOptions | map | {} | A map of DB specific properties |
public type ServerModeConfig record
The Client endpoint configuration for the server mode of h2 databases.
Field Name | Data Type | Default Value | Description |
---|---|---|---|
host | string | The host name of the database to connect (in case of server based DB) |
|
port | int | 9092 | The port of the database to connect (in case of server based DB) |
Endpoint Client
Represents an H2 client endpoint.
-
<Client> call(string sqlQuery, typedesc[]? recordType, string|int|boolean|float|decimal|byte[][]|sql:Parameter? parameters) returns (table<$anonType$0>[]|null|error<>)
The call remote function implementation for H2 Client to invoke stored procedures/functions.
Parameter Name Data Type Default Value Description sqlQuery string The SQL stored procedure to execute
recordType typedesc[]? Array of record types of the returned tables if there is any
parameters string|int|boolean|float|decimal|byte[][]|sql:Parameter? The parameters to be passed to the procedure/function call. The number of parameters is variable
Return Type Description table<$anonType$0>[]|null|error<> A
table[]
if there are tables returned by the call remote function and else nil,error
will be returned if there is any error -
<Client> select(string sqlQuery, typedesc? recordType, boolean loadToMemory, string|int|boolean|float|decimal|byte[][]|sql:Parameter? parameters) returns (table<$anonType$1>|error<>)
The select remote function implementation for H2 Client to select data from tables.
Parameter Name Data Type Default Value Description sqlQuery string SQL query to execute
recordType typedesc? Type of the returned table
loadToMemory boolean false Indicates whether to load the retrieved data to memory or not
parameters string|int|boolean|float|decimal|byte[][]|sql:Parameter? The parameters to be passed to the select query. The number of parameters is variable
Return Type Description table<$anonType$1>|error<> A
table
returned by the sql query statement elseerror
will be returned if there is any error -
<Client> update(string sqlQuery, string[]? keyColumns, string|int|boolean|float|decimal|byte[][]|sql:Parameter? parameters) returns (sql:UpdateResult|error<>)
The update remote function implementation for H2 Client to update data and schema of the database.
Parameter Name Data Type Default Value Description sqlQuery string SQL statement to execute
keyColumns string[]? () Names of auto generated columns for which the auto generated key values are returned
parameters string|int|boolean|float|decimal|byte[][]|sql:Parameter? The parameters to be passed to the update query. The number of parameters is variable
Return Type Description sql:UpdateResult|error<> sql:UpdateResult
with the updated row count and key column values, elseerror
will be returned if there is any error -
<Client> batchUpdate(string sqlQuery, string|int|boolean|float|decimal|byte[][][]|sql:Parameter? parameters) returns (int[]|error<>)
The batchUpdate remote function implementation for H2 Client to batch data insert.
Parameter Name Data Type Default Value Description sqlQuery string SQL statement to execute
parameters string|int|boolean|float|decimal|byte[][][]|sql:Parameter? Variable number of parameter arrays each representing the set of parameters of belonging to each individual update
Return Type Description int[]|error<> An
int[]
- The elements in the array returned by the operation may be one of the following or else anerror
will be returned if there is any error. A number greater than or equal to zero - indicates that the command was processed successfully and is an update count giving the number of rows A value of -2 - Indicates that the command was processed successfully but that the number of rows affected is unknown A value of -3 - Indicates that the command failed to execute successfully and occurs only if a driver continues to process commands after a command fails