Helidon DB Client

Tomáš Kraus
Feb 27 · 4 min read

It was always problematic to work with databases in reactive applications. It’s not anymore. In Helidon 2.0.0-M1 we introduced the pre-release version of Helidon DB Client — a unified, reactive API for working with databases in non-blocking way.

Note: Pre-release versions are experimental and not intended for production use. APIs and features are not yet fully tested and are subject to change.

Features Overview

The Helidon 2.0 DB Client includes the following key features:

  • 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 is being added to all Helidon SE APIs and the Helidon DB Client is no exception.
  • Backpressure
    Helidon DB Client performs database operations only when it’s requested by the consumer. This is propagated all the way to the TCP layer.
  • Improved portability between relational database drivers
    The DB Client 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.

The DB Client must be configured before you begin. Let’s take a look at how you can use Helidon Config to set up your JDBC-based client.

The Helidon Configuration node includes the following sections:

  • source: jdbc or mongodb
  • connection: database connection parameters
  • statements: named statements when used

Configuration for JDBC-based Database Client:

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

DB Client API

The Helidon DB Client API contains many methods to run various statements with parameters and to retrieve statement execution results. The following sections describe the options you can use to build and execute your statements.

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

  • execute(Function<DbExecute, T> executor)
  • inTransaction(Function<DbTransaction, CompletionStage<T>> executor)

Both methods require Function interface argument with statements executor.

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
  • common statements: createStatement, createNamedStatement

Methods with “Named” in their name expect statement name from statements config section.

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.

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

SELECT name FROM Pokemons WHERE id = ?

They 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 SQL statement:

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 CompletionStage<R> where R is the statement execution result.

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 CompletionStage<DbRows<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 CompletionStage<Long> with the number of modified records in the database.

Execution of DML statements will always return CompletionStage<Long> with the number of modified records in the database.

In following example, the number of modified records is being printed to standard output:

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

Execution of a query statement will always return CompletionStage<DbRows<DbRow>>. Class DbRows offers several methods to access this result:

  • Flow.Publisher<DbRow> publisher() to process individual result rows using Flow.Subscriber<DbRow>
  • CompletionStage<List<DbRow>> collect() to collect all rows and return them as List<DbRow>
  • <U> DbRows<U> map(…) to map returned result using provided mapper

Refer to the pokemon sample to see how to collect all rows and process them as List<DbRow>.

Samples

This sample project will help you get started. More samples demonstrating how to use Helidon DB Client with both relational databases and MongoDB are available here.

Summary

Helidon DB Client is still an experimental feature. Before making it final we need your feedback. Please try it and let us know how we can improve it by leaving a comment to this article, as an issue in our issues tracker or in our official Slack channel.

Helidon

The official project Helidon blog containing articles from…

Thanks to Dmitry Kornilov

Tomáš Kraus

Written by

Senior Software Developer at Oracle.

Helidon

Helidon

The official project Helidon blog containing articles from Helidon developers and the developers community. All articles are approved by the Helidon team. Contact @dkornilov to publish your story.

More From Medium

More on Helidon from Helidon

More on Helidon from Helidon

Introducing the Helidon CLI

More on Helidon from Helidon

More on Helidon from Helidon

Reactive Messaging with Helidon 2.0

Mar 25 · 4 min read

78

More on Reactive Programming from Helidon

More on Reactive Programming from Helidon

Helidon Web Client

Mar 23 · 3 min read

93

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade