For the DB Client using JDBC implementation and H2 database, you must include the following dependencies in your project:

<dependencies>
     <dependency>
         <groupId>io.helidon.dbclient</groupId> 
         <artifactId>helidon-dbclient</artifactId>
     </dependency>
     <dependency>
         <groupId>io.helidon.dbclient</groupId> 
         <artifactId>helidon-dbclient-jdbc</artifactId>
     </dependency>
     <dependency>
         <groupId>com.h2database</groupId> 
         <artifactId>h2</artifactId>
    </dependency>
</dependencies>

• Add the Helidon DB Client
• Specify JDBC or MongoDB
• Add the database JDBC driver (only for JDBC)

The DB Client must be configured before you begin. In the example below we’ll use Helidon Config to set up JDBC-based client:

db:
  source: "jdbc" 
  connection:
    url: "jdbc:mysql://127.0.0.1:3306/pokemon?useSSL=false" 
    username: "user"
    password: "password"
  statements: 
    ping: "DO 0" 
    select-all-pokemons: "SELECT id, name FROM Pokemons"

• Source: jdbc or mongoDb
• Connection: database connection parameters
• Statements: named statements to be used in application
• A ping statement used by health check

DBClient class has two methods to select whether statements will be executed in transaction or not:

• execute(Function<DbExecute, T> executor)

• inTransaction(Function<DbTransaction, T> executor)

Both methods provide an executor (either DbExecute or DbTransaction) and expect either Single or a Multi result, usually returned by one of their methods.

DbExecute class offers many methods for various statements builders:

• DML statements: createDmlStatement, createNamedDmlStatement

• insert statements: createInsert, createNamedInsert

• update statements: createUpdate, createNamedUpdate

• delete statements: createDelete, createNamedDelete

• query statements: createQuery, createNamedQuery

• get statements: createGet, createNamedGet

Methods with "Named" in their name (createNamedDmlStatement) expect statement name from statements section of Config, or a named statement configured when the DbClient was created using a Builder.

All statement builders offer methods to set statement parameters. Those parameters can be ordered parameters or named parameters. Ordered and named parameters can’t be mixed in a single statement.

Note that get statements are query statements that allow zero to one results.

Ordered parameters are written down as ? in the statement text:

SELECT name FROM Pokemons WHERE id = ?

The ordered parameters are equivalent to JDBC PreparedStatement parameters.

Methods to set ordered parameters are:

• params(List<?> parameters) with all parameters as List

• params(Object… parameters) with all parameters as array

• indexedParam(Object parameters) POJO used with registered mapper

• addParam(Object parameter) with single parameter, can be called repeatedly

Named parameters are written down as :<name> in the JDBC statements

SELECT name FROM Pokemons WHERE id = :id

or as $<name> in the MongoDB statement:

{
    "collection": "pokemons",
    "operation": "update",
    "value": {
      "$set": {
        "name": "$name"
      }
    },
    "query": { "id": "$id" }
}

Methods to set named parameters are:

• params(Map<String, ?> parameters) with all parameters as Map

• namedParam(Object parameters) POJO used with registered mapper

• addParam(String name, Object parameter) with single parameter, can be called repeatedly

Statements are executed by calling execute() method after statement parameters are set. This method returns either a Single or Multi depending on statement type. The type returned also depends on statement type.

JDBC query with ordered parameters and query that does not run in the transaction:

dbClient.execute(exec -> exec
    .createQuery("SELECT name FROM Pokemons WHERE id = ?")
    .params(1)
    .execute()
);

JDBC query with named parameters and the query runs in transaction:

dbClient.inTransaction(tx -> tx
    .createQuery("SELECT name FROM Pokemons WHERE id = :id")
    .addParam("id", 1)
    .execute()
);

Both examples will return Multi<DbRow> with rows returned by the query.

This example shows a MongoDB update statement with named parameters and the query does not run in transaction:

dbClient.execute(exec -> exec
    .createUpdate("{\"collection\": \"pokemons\","
        + "\"value\":{$set:{\"name\":$name}},"
        + "\"query\":{id:$id}}")
    .addParam("id", 1)
    .addParam("name", "Pikachu")
    .execute()
);

This update statement will return Single<Long> with the number of modified records in the database.

DML Statement Result

dbClient.execute(exec -> exec
    .insert("INSERT INTO Pokemons (id, name) VALUES(?, ?)",
        1, "Pikachu"))
    .thenAccept(count ->
        System.out.printf("Inserted %d records\n", count));

Query Statement Result