The main features of Helidon DB Client are:

• Unified API for data access and query: The API was implemented as a layer above JDBC or MongoDB Reactive Streams Java Driver, so any relational databases with JDBC driver or MongoDB are supported.

• Reactive database access with non-reactive drivers: Most JDBC drivers are blocking. Using them in a reactive application is problematic. Helidon DB Client allows the use of blocking JDBC drivers in your reactive application by wrapping a blocking driver in an executor service.

• Observability: Support for health checks, metrics and tracing.

• Backpressure: Performs database operations only when it is requested by the consumer. This is propagated all the way to the TCP layer.

• Portability between relational database drivers: Works with native database statements that can be used inline in the code or defined as named statements in database configuration. By moving the native query code to configuration files, the Helidon DB Client allows you to switch to another database by changing the configuration files, not the code.

H2 is a Java SQL database that is lightweight and easy to use. If H2 is not installed on your machine, here are few steps to quickly download and set it up:

From Docker

FROM openjdk:11-jre-slim

ENV H2_VERSION "1.4.199"

ADD "https://repo1.maven.org/maven2/com/h2database/h2/${H2_VERSION}/h2-${H2_VERSION}.jar" /opt/h2.jar

COPY h2.server.properties /root/.h2.server.properties

EXPOSE 8082
EXPOSE 9092

CMD java \
       -cp /opt/h2.jar \
       org.h2.tools.Server \
       -web -webDaemon -webAllowOthers -webPort 8082 \
       -tcp -tcpAllowOthers -tcpPort 9092 \
       -ifNotExists

webSSL=false
webAllowOthers=true
webPort=8082
0=Generic H2 (Server)|org.h2.Driver|jdbc\:h2\:tcp\://localhost\:9092/~/test|sa

docker build -f Dockerfile.h2 . -t h2db

docker run --rm -p 8082:8082 -p 9092:9092 --name=h2 h2db

From the Command Line

java -jar h2-\{latest-version}.jar -webAllowOthers -tcpAllowOthers

Web Console server running at http://127.0.1.1:8082 (others can connect)
Opening in existing browser session.
TCP server running at tcp://127.0.1.1:9092 (others can connect)
PG server running at pg://127.0.1.1:5435 (only local connections)

Open the console at http://127.0.1.1:8082 in your favorite browser. It displays a login window. Select Generic H2 from Saved Settings. The following settings should be set by default:

• Driver Class: org.h2.Driver

• JDBC URL: jdbc:h2:tcp://localhost:9092/~/test

• User Name: sa

• Password:

Password must stay empty. Click Connect, the browser displays a web page. The database is correctly set and running.

Generate the project sources using the Helidon SE Maven archetype. The result is a simple project that can be used for the examples in this guide.

mvn -U archetype:generate -DinteractiveMode=false \
    -DarchetypeGroupId=io.helidon.archetypes \
    -DarchetypeArtifactId=helidon-quickstart-se \
    -DarchetypeVersion=3.2.16 \
    -DgroupId=io.helidon.examples \
    -DartifactId=helidon-quickstart-se \
    -Dpackage=io.helidon.examples.quickstart.se

A new directory named helidon-quickstart-se is created.

cd helidon-quickstart-se

Navigate to the helidon-quickstart-se directory and open the pom.xml file to add the following Helidon dependencies required to use the DB Client:

<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>io.helidon.integrations.db</groupId>   
        <artifactId>h2</artifactId>
    </dependency>
    <dependency>
        <groupId>io.helidon.dbclient</groupId>      
        <artifactId>helidon-dbclient-health</artifactId>
    </dependency>
    <dependency>
        <groupId>io.helidon.dbclient</groupId>      
        <artifactId>helidon-dbclient-metrics</artifactId>
    </dependency>
    <dependency>
        <groupId>io.helidon.dbclient</groupId>      
        <artifactId>helidon-dbclient-jsonp</artifactId>
    </dependency>
</dependencies>

• DB Client API dependency.
• Using JDBC driver for this example.
• H2 driver dependency.
• Support for health check.
• Support for metrics.
• Support for Jsonp.

To configure the application, Helidon uses the application.yaml. The DB Client configuration can be joined in the same file and is located here: src/main/resources.

db:
  source: jdbc  
  connection:   
    url: "jdbc:h2:tcp://localhost:9092/~/test"
    username: "sa"
    password:
  statements:   
    create-table: "CREATE TABLE IF NOT EXISTS LIBRARY (NAME VARCHAR NOT NULL, INFO VARCHAR NOT NULL)"
    insert-book: "INSERT INTO LIBRARY (NAME, INFO) VALUES (:name, :info)"
    select-book: "SELECT INFO FROM LIBRARY WHERE NAME = ?"
    delete-book: "DELETE FROM LIBRARY WHERE NAME = ?"

• Source property support two values: jdbc and mongo.
• Connection detail we used to set up H2.
• SQL statements to manage the database.

In the application Main.class, an instance of DbClient is created based on the configuration from application.yaml.

import io.helidon.dbclient.metrics.DbClientMetrics; 
import io.helidon.dbclient.DbClient;

Config config = Config.create(); // Landmark to add DB client

DbClient dbClient = DbClient.builder()
                .config(config.get("db"))       
                .addService(DbClientMetrics.counter().statementNames("select-book"))   
                .build();

• Add import statements
• Configure the DB Client with the "db" section of application.yaml.
• Add a counter for metrics.

The DB Client metric counter will be executed only for the select-book statement and it will check how many times it was invoked. At this point, the database is empty, and needs to be initialized. To achieve that, the DB Client can be used to create a table in the database.

DbClient dbClient = DbClient.builder()
                .config(config.get("db"))
                .addService(DbClientMetrics.counter().statementNames("select-book"))
                .build();

createTable(dbClient);

private static void createTable(DbClient dbClient) {
    dbClient.execute(exec -> exec.namedDml("create-table"))     
            .await();
}

• Use the "create-table" script to build a table with book name and information.

The createTable is invoked only once and creates an empty table with two columns: name and info. The script is used to boostrap the server, so the await method is called in this particular case because the table must be created before the server starts. A new service can manage requests to interact with this table which represents our library.The services are registered in the createRouting method.

import io.helidon.dbclient.health.DbClientHealthCheck;

WebServer server = WebServer.builder(createRouting(config, dbClient))   
                .config(config.get("server"))
                .addMediaSupport(JsonpSupport.create())
                .build();

private static Routing createRouting(Config config, DbClient dbClient) {
    HealthSupport health = HealthSupport.builder()
            .addLiveness(DbClientHealthCheck.create(dbClient))  
            .build();

    return Routing.builder()
            .register(health)                   // Health at "/health"
            .register(MetricsSupport.create())                  // Metrics at "/metrics"
            .register("/greet", new GreetService(config))
            .register("/library", new LibraryService(dbClient))  
            .build();
}

• Add dbClient as a parameter of createRouting method.
• Add Health check to control the application behavior.
• Register the LibraryService to the Routing.

The library service does not yet exist, but you’ll creat it in the next step of the guide. It has a constructor with the DB Client as a parameter because it will manage the library. The DB Client health check uses the select-book statement from the configuration. As shown above, to create a DB Client health check, call the DbClientHealthCheck.create method and pass the concerned DbClient. Then add it to the health support builder and register it to the routing.

Create LibraryService class into io.helidon.examples.quickstart.se package.

package io.helidon.examples.quickstart.se;

import io.helidon.common.http.Http;     
import io.helidon.dbclient.DbClient;
import io.helidon.webserver.Routing;
import io.helidon.webserver.ServerRequest;
import io.helidon.webserver.ServerResponse;
import io.helidon.webserver.Service;

public class LibraryService implements Service {

    private final DbClient dbClient;    

    LibraryService(DbClient pDbClient){
        this.dbClient = pDbClient;      
    }
}

• Add new import statement
• Declare the Helidon DB Client
• A DB Client instance is provided when LibraryService is instantiated.

As the LibraryService implements io.helidon.webserver.Service, the update(Routing) method has to be implemented. It defines application endpoints and Http request which can be reached by clients.

@Override
public void update(Routing.Rules rules) {
    rules
        .get("/{name}", this::getBook)      
        .put("/{name}", this::addBook)      
        .delete("/{name}", this::deleteBook)   
        .get("/json/{name}", this::getJsonBook); 
}

• Return information about the required book from the database.
• Add a book to the library.
• Remove a book from the library.
• Return the book information in Json format.

To summarize, there is one endpoint that can manipulate books. The number of endpoints and application features can be changed from these rules by creating or modifying methods. {name} is a path parameter for the book name. The architecture of the application is defined, so the next step is to create these features.

private void getBook(ServerRequest serverRequest, ServerResponse serverResponse) {
    String bookName = serverRequest.path().param("name");   

    dbClient.execute(exec -> exec.namedGet("select-book", bookName))    
            .thenAccept(row -> {
                if (row.isPresent()) {
                    serverResponse.send(row.get().column("INFO").as(String.class)); 
                } else {
                    serverResponse.status(Http.Status.NOT_FOUND_404)    
                            .send();
                }
            })
            .exceptionally(serverResponse::send);   
}

• Get the book name from the path in the URL.
• Helidon DB Client executes the select-book SQL script from application.yaml.
• Sends book information to the client.
• Sends 404 HTTP status if no book was found for the given name.
• If an exception occurred during the process, it is sent to the client.

The getBook method reach the book from the database and send the information to the client. The name of the book is located into the url path. If the book is not present in the database, a HTTP 404 is sent back. The execute(Function<DbExecute, T> executor) method is called on the dbClient instance to execute one statement. Nevertheless, it is possible to execute a set of tasks into a single execution unit by using inTransaction (Function<DbTransaction, T> executor) method.

DbExecute class provides many builders to create statements such as, DML, insert, update, delete, query and get statements. For each statement there are two builders which can be regrouped in 2 categories. Builders with methods containing Named keyword, they use a statement defined in the configuration file.

And builders without Named keyword, they use a statement passed as an argument. More information on the Helidon DB Client here.

private void getJsonBook(ServerRequest serverRequest, ServerResponse serverResponse) {
    String bookName = serverRequest.path().param("name");

    dbClient.execute(exec -> exec.namedGet("select-book", bookName))
            .thenAccept(row -> {
                if (row.isPresent()) {
                    serverResponse.send(row.get().as(JsonObject.class));
                } else {
                    serverResponse.status(Http.Status.NOT_FOUND_404)
                            .send();
                }
            })
            .exceptionally(serverResponse::send);
}

Instead of sending the INFO content of the targeted book, the getJsonBook method send the whole row of the database as a JsonObject.

private void addBook(ServerRequest serverRequest, ServerResponse serverResponse) {
    String bookName = serverRequest.path().param("name");

    serverRequest.content()
            .as(String.class)
            .thenAccept(newValue -> {
                dbClient.execute(exec -> exec.createNamedInsert("insert-book")
                        .addParam("name", bookName)     
                        .addParam("info", newValue)
                        .execute())
                        .thenAccept(count -> serverResponse.status(Http.Status.CREATED_201).send())     
                        .exceptionally(serverResponse::send);
            });
}

• The SQL statement requires the book name and its information. They are provided with addParam method.
• A new book was added to library, so a HTTP 201 code is returned.

When a user adds a new book, it uses HTTP PUT method where the book name is in the URL and the information in the request content. To catch this content, the information is retrieved as a string and then the DB Client execute the insert-book script to add the book to the library. It requires two parameters, the book name and information which are passed to the dbClient thanks to addParam method. A HTTP 201 is sent back as a confirmation if no exception is thrown.

private void deleteBook(ServerRequest serverRequest, ServerResponse serverResponse) {
    String bookName = serverRequest.path().param("name");

    dbClient.execute(exec ->  exec.namedDelete("delete-book", bookName))     
                        .thenAccept(count -> serverResponse.status(Http.Status.NO_CONTENT_204).send())   
                        .exceptionally(serverResponse::send);
}

• Execute SQL script from application.yaml to remove a book from the library by its name.
• The required book was removed, so a HTTP 204 is sent.

To remove a book from the library, use the "delete-book" script in the way than previously. If the book is removed successfully, a HTTP 204 is sent back.

This guide provided an introduction to the Helidon DB Client’s key features. If you want to learn more, see the Helidon DB Client samples in https://medium.com/helidon/helidon-db-client-e12bbdc85b7.