Accessing Database with Helidon DB Client

In my previous article of introducing Helidon, I have demonstrated how to create a simple CRUD application with RESTful APIs using the functional programming style provided Helidon SE. But unluckily there is no means to connect to a database using the Helidon SE stack at that moment.

Image for post
Image for post

The things will be changed soon in the upcoming Helidon 2.0. Helidon 2.0 brings a lot of significant changes to the existing stack, including the long-awaited database operating feature(so-called DB Client) in Helidon SE. More info about the changes of Helidon 2.0, check the Changelog.

The first milestone of Helidon 2.0 is on board, currently it supports Jdbc and MongoDB. The Jdbc support is not really reactive APIs, it wraps the blocking execution into an executor service, and Mongo implementation is truly reactive due to its underlying ReactiveStreams drivers.

In this post, we will add DB Client to our former sample application, and replace the dummy codes in our Repository with real world database operations.

Preparing the Project Skeleton

The newest Helidon reorganized the structure of its architypes. If you work on the existing codes, follow these steps to upgrade the existing codes to the new Helidon.

First of all, use the new helidon-se BOM as the parent in the project pom.xml.

Add the following dependencies. All of them are managed in helidon-se .

  • The helidon-bundles-webserver is the infrastructure of Helidon SE WebServer.
  • The helidon-config-yaml enable YAML format in the application configuration.
  • The helidon-health, helidon-health-checks and helidon-metrics add health check and metrics support for service observability at runtime.
  • The helidon-media-jsonp-server adds JSON processing support and HTTP message encode and decodes. Besides JSON-P, it also support JSON-B and Jackson by adding helidon-media-jsonb-server and helidon-media-jacksone-server.

Remove the old Helidon declared in dependencyManagement .

In the build element, replace the configuration of maven-dependency-plugin with the following .

In the properties , only needs to declare the mainClass properties.

Alternatively, you can follow the newest QuickStart Guide to generate a new project skeleton through helidon-quickstart-se maven architype.

Then copy the existing codes to this project folder.

Note: Helidon 2.0 has moved the codebase to Java 11, make sure a JDK 11 is installed for local development environment. AdoptOpenJDK is highly recommended for developers.

Configuring Db Client

Let’s start to contribute database operations using DbClient.

Add the Db Client related dependencies.

  • The helidon-dbclient-jdbc is required for DbClient Jdbc support.
  • The HikariCP is use for creating Jdbc connection pool.
  • Add the latest postgresql Jdbc driver, we will connect a PostgreSQL server in our application.

Configure db in the application.yaml. DB Client configuration consists of the following parts.

  • source: jdbc or mongo
  • connection: database connection configuration
  • statements: named statements.

DbClient uses a mapper class to map the data between database table rows and Java POJOs.

Registering a DbMapper

Let’s start with the Post POJO.

A custom DbMapper must implements DbMapper interface with a POJO class as parameterized type, there are 3 methods needed to be implemented.

  • The T read(DbRow dbRow) method is used for reading database table rows and copy the data to the mapped POJOs.
  • The toNamedParameters and toIndexedParameters are used for setting the values of a POJO class to the named params and indexed params in the SQL query string, esp. when performing inserting and updating operations there are a couple of params are to be bound before executing queries.

Create a DbMapperProvider to register a DbMapper.

Helidon uses Java built-in Service Locater to find these DbMapperProvider s as the application is starting.

Create a file named io.helidon.dbclient.spi.DbMapperProvider in the folder src/main/resources/META-INF/services/.

Add the FQN of the PostMapperProvider class into this file.

Let’s refactor the PostRepository class.

Refactoring PostRepository

Add a constructor to accept a DbClient argument.

No worries how to find a DbClient dependency, and we do not use any Dependency Injection frameworks at all. We will assemble the dependencies manually in the Main class.

Let’s start with the all method which is used for fetching all posts.

The execute method of DbClient accept Function which uses a DbExecute as source. DbExecute provides a collection of methods to simplify the CRUD operations on database.

  • The createQuery, createInsert, createUpdate and createDelete are used for perform generic queries(mainly SELECT), INSERT, UPDATE, DELETE SQL clauses .
  • The createNamedQuery, createNamedInsert, createNamedUpdate and createNamedDelete are similar with the above, but the SQL statements are defined in the application.yaml, use the name to refer them in the methods .
  • The query, insert, update and delete methods are a simple form, and they accept index based params as the second argument.

Besides these, createGet(and createdNamedGet, get) is used for a query result contains one or zero result, createDml(and createdNamedDml, dml) is use for DML operations, and createStatement(and createdNamedStatement, statement) is for executing general purpose DML or DQL clauses.

The createQuery.execture wraps the query result(DbRows<DbRow>) into a stream(CompletionStage), and DbRows.map method will apply the rule we defined in PostMapper.read and convert a DbRow to a Post, the collect to transform the stream into a new stream(CompletionStage<List<T>>).

Let’s move to getById method.

The createGet will return an Optional<DbRow> in the stream, finally we convert it to CompletionStage<Post>. And if is not present, throw an predefined PostNotFoundException instead, the existing error handler can handle this exception.

You have to notice .addParam of a DbStatement, there are several options to bind data to the params in query clauses.

  • The params(List<?>), addParam(Object)and indexedParam(Object) are use for binding position-based params.
  • The params(Map<?>), addParam(String, Object)and namedParam(Object) are use for binding name-based params.

Note, indexedParam(Object) and namedParam(Object) will apply the rules through toIndexedParameters and toNamedParameters methods defined in our DbMapper.

Let’s have a look at save method.

Generally, the insert, update, delete queries will return the number of affected records.

Here, we want to return the id of the inserted record, and we use a generic query instead, and unwrap the returning id manually.

OK, let’s go to the update method.

Instead of using dbClient.execute, we use dbClient.inTransaction here to execute a series of SQL in a transaction.

For updating an existing record, using SELECT FOR UPDATE mode is reasonable, query it firstly for fetching a update lock and then perform a update it as expected.

Lastly, let’s look at the deleteById method.

All methods of the PostRepository are updated to using Java 8' s CompletionStage as return type, so we have to refresh our former PostService.

In the former codes, we used Routing.builder()...error() in the Main class to handle global exceptions. Here we have to improve it slightly. When throwing an exception in a CompletionStage stream, it wraps it into a CompletionException.

Let’s assemble the dependencies in the Main class.

The DataInitializer is use for initializing sample data into database.

It performs the following tasks in sequence.

  • Delete all comments
  • Delete all posts
  • Insert two sample posts
  • Query and print all posts

BTW, the Comment related changes are similar, check out the source codes from my Github and explore them yourself.

Running the Application

To run this application, there is a docker-compose.yaml available to bootstrap a PostgreSQL in docker quickly.

And the table schema is defined a script file in the pg-initdb.d folder.

Run the following command to start a PostgreSQL server.

Now you can build and run the application by command.

Or run the Main as java application in your favorite IDEs.

When it is started, you will see the following info in the console.

Open a terminal, try to test the /posts endpoint by curl command.

Summary

As a developer, for my opinion, I think it is good experience, but it still need further improvements.

  • The DbMapper registration via ServiceLoader is a little tedious, if we need such a service registration and discovery mechanism, why not introduce JSR330 or Weld SE directly?
  • The toIndexedParameters and toNamedParameters are not so useful(but you have to implement them when creating a DbMapper), it is difficult to satisfy all cases when binding params.
  • Personally, I would like use a RowMapper as an extra parameter when building the query statement, like the one provided in Spring Jdbc. It is more flexible and easier to work with Java 8 Lambda. Of course DbRows can do such work as expected.
  • I used TIMESTAMP SQL type in DDL scripts, but it can not be read as Java 8 LocalDateTime in DbMapper. From PostgreSQL document, Java 8 DateTime is supported in PostgreSQL Jdbc Driver, see issue oracle/helidon#1454. Obviously, reading data from DbRow is not exact as from ResultSet.
  • Hope there is a next generation of Jdbc or async Jdbc or reactive Jdbc to embrace ReactiveStreams in driver level , there are some existing work, such as R2dbc.io, Asynchronous Database Access API (ADBA) .

Written by

Self-employed technical consultant, solution architect and full-stack developer

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store