ballerina/h2 module
Module overview
This module provides the functionality required to access and manipulate data stored in an H2 database.
Client
To access a database, you must first create a client
object. Create a client of the H2 Client type (i.e., h2:Client
) and provide the necessary connection parameters. This will create a pool of connections to the given H2 database. A sample for creating a H2 client can be found below.
Database operations
Once the client is created, 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, and batch updating data. For more details on the supported remote functions refer to the sql
module. Also the details of the SQL data types and query parameters relevant to these database operations can be found in the documentation of the sql
module.
Samples
Creating a client in H2 Embedded Mode
h2:Client testDB = new({
path: "/home/ballerina/test/",
name: "testdb",
username: "SA",
password: "",
poolOptions: { maximumPoolSize: 5 }
});
Creating a client in H2 Server Mode
h2:Client testDB = new({
host: "localhost",
port: 9092,
name: "testdb",
username: "SA",
password: "",
poolOptions: { maximumPoolSize: 5 }
});
Creating a client in H2 In-Memory Mode
h2:Client testDB = new({
name: "testdb",
username: "SA",
password: "",
poolOptions: { maximumPoolSize: 5 }
});
The full list of client properties can be found in 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|byte[][]|sql:Parameter parameters) returns (table<$anonType$0>[] | null | error<>)
The call operation 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|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|byte[][]|sql:Parameter parameters) returns (table<$anonType$1> | error<>)
The select operation 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|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|int|boolean|float|byte[][]|sql:Parameter parameters) returns (int | error<>)
The update operation 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
parameters string|int|boolean|float|byte[][]|sql:Parameter The parameters to be passed to the update query. The number of parameters is variable
Return Type Description int | error<> int
number of rows updated by the statement and elseerror
will be returned if there is any error -
<Client> batchUpdate(string sqlQuery, string|int|boolean|float|byte[][][]|sql:Parameter parameters) returns (int[] | error<>)
The batchUpdate operation 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|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 -
<Client> updateWithGeneratedKeys(string sqlQuery, string[]? keyColumns, string|int|boolean|float|byte[][]|sql:Parameter parameters) returns ((int,string[]) | error<>)
The updateWithGeneratedKeys operation implementation for H2 Client which returns the auto generated keys during the update remote function.
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|byte[][]|sql:Parameter The parameters to be passed to the update query. The number of parameters is variable
Return Type Description (int,string[]) | error<> A
Tuple
will be returned and would represent updated row count during the query exectuion, aray of auto generated key values during the query execution, in order. Elseerror
will be returned if there is any error.