Accessing Database with Helidon DB Client

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.

<parent>
<groupId>io.helidon.applications</groupId>
<artifactId>helidon-se</artifactId>
<version>2.0.0-M1</version>
<relativePath/>
</parent>
<dependencies>
<dependency>
<groupId>io.helidon.bundles</groupId>
<artifactId>helidon-bundles-webserver</artifactId>
</dependency>
<dependency>
<groupId>io.helidon.config</groupId>
<artifactId>helidon-config-yaml</artifactId>
</dependency>
<dependency>
<groupId>io.helidon.health</groupId>
<artifactId>helidon-health</artifactId>
</dependency>
<dependency>
<groupId>io.helidon.health</groupId>
<artifactId>helidon-health-checks</artifactId>
</dependency>
<dependency>
<groupId>io.helidon.metrics</groupId>
<artifactId>helidon-metrics</artifactId>
</dependency>
<dependency>
<groupId>io.helidon.media.jsonp</groupId>
<artifactId>helidon-media-jsonp-server</artifactId>
</dependency>
<dependency>
<groupId>io.helidon.media.jsonb</groupId>
<artifactId>helidon-media-jsonb-server</artifactId>
</dependency>

...
</dependencies>
  • 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.
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-dependency-plugin</artifactId>
<executions>
<execution>
<id>copy-libs</id>
</execution>
</executions>
</plugin>
<mainClass>demo.Main</mainClass>
mvn archetype:generate -DinteractiveMode=false \
-DarchetypeGroupId=io.helidon.archetypes \
-DarchetypeArtifactId=helidon-quickstart-se \
-DarchetypeVersion=2.0.0-M1 \
-DgroupId=io.helidon.examples \
-DartifactId=helidon-quickstart-se \
-Dpackage=io.helidon.examples.quickstart.se

Configuring Db Client

Let’s start to contribute database operations using DbClient.

<dependency>
<groupId>io.helidon.dbclient</groupId>
<artifactId>helidon-dbclient-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.10</version>
</dependency>
  • 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.
  • source: jdbc or mongo
  • connection: database connection configuration
  • statements: named statements.
db:
source: jdbc
connection:
url: jdbc:postgresql://127.0.0.1:5432/test
username: user
password: password
poolName: hikariPool
initializationFailTimeout: -1
connectionTimeout: 2000

Registering a DbMapper

Let’s start with the Post POJO.

public class PostMapper implements DbMapper<Post> {
private final static Logger LOGGER = Logger.getLogger(PostMapper.class.getName());
@Override
public Post read(DbRow dbRow) {
var id = dbRow.column("id");
var title = dbRow.column("title");
var content = dbRow.column("content");
var createdAt = dbRow.column("created_at");
Post post = new Post();
post.setId(id.as(UUID.class));
post.setTitle(title.as(String.class));
post.setContent(content.as(String.class));
post.setCreatedAt(createdAt.as(Timestamp.class).toLocalDateTime());
return post;
}
@Override
public Map<String, ?> toNamedParameters(Post post) {
var map = Map.of(
"title", post.getTitle(),
"content", post.getContent()
);
return map;
}
@Override
public List<?> toIndexedParameters(Post post) {
return List.of(
post.getId(),
post.getTitle(),
post.getContent(),
post.getCreatedAt()
);
}
}
  • 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.
@Priority(1000)
public class PostMapperProvider implements DbMapperProvider {
private static final PostMapper MAPPER = new PostMapper();
@Override
public <T> Optional<DbMapper<T>> mapper(Class<T> type) {
if (type.equals(Post.class)) {
return Optional.of((DbMapper<T>) MAPPER);
}
return Optional.empty();
}
}
demo.PostMapperProvider

Refactoring PostRepository

Add a constructor to accept a DbClient argument.

public class PostRepository {    private DbClient dbClient;    public PostRepository(DbClient dbClient) {
this.dbClient = dbClient;
}
...
}
public CompletionStage<List<Post>> all() {
return this.dbClient
.execute(dbExecute -> dbExecute.createQuery("SELECT * FROM posts")
.execute()
)
.thenCompose(dbRowDbRows -> dbRowDbRows.map(Post.class).collect());
}
  • 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.
public CompletionStage<Post> getById(UUID id) {
return this.dbClient
.execute(
dbExecute -> dbExecute.createGet("SELECT * FROM posts WHERE id=?")
.addParam(id)
.execute()
)
.thenApply(
rowOptional -> rowOptional.map(dbRow -> dbRow.as(Post.class)).orElseThrow(() -> new PostNotFoundException(id))
);
}
  • 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.
public CompletionStage<UUID> save(Post post) {
return this.dbClient
.execute(
dbExecute -> dbExecute
.query("INSERT INTO posts(title, content) VALUES (?, ?) RETURNING id", post.getTitle(), post.getContent())
)
.thenCompose(DbRows::collect)
.thenApply(data -> data.isEmpty() ? null : data.get(0).column("id").as(UUID.class));
}
public CompletionStage<Long> update(UUID id, Post post) {
return this.dbClient
.inTransaction(
tx -> tx.createGet("SELECT * FROM posts WHERE id=? FOR UPDATE")
.addParam(id)
.execute()
.thenApply(
rowOptional -> rowOptional.map(dbRow -> dbRow.as(Post.class)).orElseThrow(() -> new PostNotFoundException(id))
)
.thenApply(p ->
Map.of("title", post.getTitle(), "content", post.getContent(), "id", id)
)
.thenCompose(
map -> tx.createUpdate("UPDATE posts SET title=:title, content=:content WHERE id=:id")
.params(map)
.execute()
)
);
}
public CompletionStage<Long> deleteById(UUID id) {
return this.dbClient.execute(
dbExecute -> dbExecute.createDelete("DELETE FROM posts WHERE id = :id")
.addParam("id", id)
.execute()
);
}
public class PostService implements Service {
private final static Logger LOGGER = Logger.getLogger(PostService.class.getName());
private final PostRepository posts;
private final CommentRepository comments;
public PostService(PostRepository posts, CommentRepository comments) {
this.posts = posts;
this.comments = comments;
}
@Override
public void update(Routing.Rules rules) {
rules.get("/", this::getAllPosts)
.post("/", this::savePost)
.get("/{id}", this::getPostById)
.put("/{id}", this::updatePost)
.delete("/{id}", this::deletePostById)
.register("/{id}/comments", new CommentService(comments));
}
private void deletePostById(ServerRequest serverRequest, ServerResponse serverResponse) {
var id = extractIdFromPathParams(serverRequest);
this.posts.deleteById(id)
.thenCompose(
count -> {
LOGGER.log(Level.INFO, "{0} posts deleted.", count);
return serverResponse.status(204).send();
}
);
} private UUID extractIdFromPathParams(ServerRequest serverRequest) {
try {
return UUID.fromString(serverRequest.path().param("id"));
} catch (Exception e) {
LOGGER.log(Level.WARNING, "Failed to parse `id` from request param...", e);
serverRequest.next(e);
}
return null;
}
private void updatePost(ServerRequest serverRequest, ServerResponse serverResponse) {
var id = extractIdFromPathParams(serverRequest);
serverRequest.content().as(JsonObject.class)
.thenApply(EntityUtils::fromJsonObject)
.thenCompose(data -> this.posts.update(id, data))
.thenCompose(
p -> serverResponse.status(204).send()
)
.exceptionally(throwable -> {
LOGGER.log(Level.WARNING, "Failed to updatePost", throwable);
serverRequest.next(throwable);
return null;
});
}
private void savePost(ServerRequest serverRequest, ServerResponse serverResponse) { serverRequest.content().as(JsonObject.class)
.thenApply(EntityUtils::fromJsonObject)
.thenApply(p ->
Post.of(p.getTitle(), p.getContent())
)
.thenCompose(this.posts::save)
.thenCompose(
p -> {
serverResponse.status(201)
.headers()
.location(URI.create("/posts/" + p));
return serverResponse.send();
}
)
.exceptionally(throwable -> {
LOGGER.log(Level.WARNING, "Failed to savePost", throwable);
serverRequest.next(throwable);
return null;
});
}
private void getPostById(ServerRequest serverRequest, ServerResponse serverResponse) {
var id = extractIdFromPathParams(serverRequest);
this.posts.getById(id)
.thenCompose(post -> serverResponse.status(200).send(EntityUtils.toJsonObject(post)))
.exceptionally(throwable -> {
LOGGER.log(Level.WARNING, "Failed to getPostById", throwable);
serverRequest.next(throwable);
return null;
});
}
private void getAllPosts(ServerRequest serverRequest, ServerResponse serverResponse) {
this.posts.all()
.thenApply(EntityUtils::toJsonArray)
.thenCompose(data -> serverResponse.send(data))
.exceptionally(throwable -> {
LOGGER.log(Level.WARNING, "Failed to getAllPosts", throwable);
serverRequest.next(throwable);
return null;
});
}
}
private static ErrorHandler<Throwable> handleErrors() {
return (req, res, t) -> {
Throwable root = t;
while (!(root instanceof PostNotFoundException) && root.getCause() != null) {
root = root.getCause();
}
if (root instanceof PostNotFoundException) {
res.status(404).send(root.getMessage());
} else {
req.next(t);
}
};
}
private static Routing createRouting(Config config) {
Config dbConfig = config.get("db");
// Interceptors added through a service loader
DbClient dbClient = DbClient.builder(dbConfig)
.build();
HealthSupport health = HealthSupport.builder()
.addLiveness(DbClientHealthCheck.create(dbClient))
.addLiveness(HealthChecks.healthChecks())
.build();
MetricsSupport metrics = MetricsSupport.create();
var greetService = new GreetService(config);
var posts = new PostRepository(dbClient);
var comments = new CommentRepository(dbClient);
var postService = new PostService(posts, comments);
// initializing data...
DataInitializer.init(dbClient);
return Routing.builder()
.register(JsonSupport.create())
.register(health) // Health at "/health"
.register(metrics) // Metrics at "/metrics"
.register("/greet", greetService)
.register("/posts", postService)
.error(Throwable.class, handleErrors())
.build();
}
public class DataInitializer {
private static final Logger LOGGER = Logger.getLogger(DataInitializer.class.getName());
public static void init(DbClient dbClient) {
dbClient.inTransaction(
tx -> tx.createDelete("DELETE FROM comments").execute()
.thenAccept(
count -> LOGGER.log(Level.INFO, "{0} comments deleted.", count)
)
.thenCompose(
v -> tx.createDelete("DELETE FROM posts").execute()
.thenAccept(count2 -> LOGGER.log(Level.INFO, "{0} posts deleted.", count2))
)
.thenCompose(
v2 -> tx.createInsert("INSERT INTO posts(title, content) VALUES(?, ?), (?, ?)")
.params(List.of("My first post of Helidon", "The content of my first post", "My second post of Helidon", "The content of my second post"))
.execute()
.thenAccept(count3 -> LOGGER.log(Level.INFO, "{0} posts inserted.", count3))
)
.thenCompose(
v2 -> tx.createQuery("SELECT * FROM posts")
.execute()
.thenCompose(dbRowDbRows -> dbRowDbRows.collect())
.thenAccept(rows -> LOGGER.log(Level.INFO, "found posts: {0}.", rows))
)
.exceptionally(throwable -> {
LOGGER.log(Level.WARNING, "Failed to initialize data", throwable);
return null;
})
);
}
private DataInitializer() {
}
}
  • Delete all comments
  • Delete all posts
  • Insert two sample posts
  • Query and print all posts

Running the Application

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

version: '3.7' # specify docker-compose versionservices:
postgres:
image: postgres
restart: always
ports:
- "5432:5432"
environment:
POSTGRES_PASSWORD: password
POSTGRES_DB: test
POSTGRES_USER: user
volumes:
- ./data/postgresql:/var/lib/postgresql
- ./pg-initdb.d:/docker-entrypoint-initdb.d
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";CREATE TABLE IF NOT EXISTS posts(
id UUID DEFAULT uuid_generate_v4() ,
title VARCHAR(255) NOT NULL,
content VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT LOCALTIMESTAMP ,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS comments(
id UUID DEFAULT uuid_generate_v4() ,
post_id UUID NOT NULL,
content VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT LOCALTIMESTAMP ,
PRIMARY KEY (id),
FOREIGN KEY (post_id) REFERENCES posts(id)
);
docker-compose up postgres
mvn clean package
java -jar ./target/se-dbclient.jar
2020.02.29 00:15:02 INFO demo.DataInitializer Thread[helidon-3,5,helidon-thread-pool-1]: 0 comments deleted.
2020.02.29 00:15:02 INFO io.helidon.common.HelidonFeatures Thread[main,5,main]: Helidon SE 2.0.0-M1 features: [Config, DbClient, Health, Metrics, WebServer]
2020.02.29 00:15:02 INFO io.helidon.common.HelidonFeatures Thread[main,5,main]: Detailed feature tree:
Config
YAML
DbClient
HealthCheck
JDBC
Metrics
Tracing
Health
Built-ins
Metrics
WebServer
JSON-P
2020.02.29 00:15:02 INFO demo.DataInitializer Thread[helidon-3,5,helidon-thread-pool-1]: 2 posts deleted.
2020.02.29 00:15:02 INFO demo.DataInitializer Thread[helidon-3,5,helidon-thread-pool-1]: 2 posts inserted.
2020.02.29 00:15:02 INFO demo.DataInitializer Thread[helidon-9,5,helidon-thread-pool-1]: found posts: [{created_at:2020-02-29 00:15:02.059223,id:a6201629-bd3b-4c0f-a8df-a94a192f5a02,title:My first post of Helidon,content:The content of my first post}, {created_at:2020-02-29 00:15:02.059223,id:52cdba76-0e18-47e8-8722-453f23bed35a,title:My second post of Helidon,content:The content of my second post}].
2020.02.29 00:15:06 INFO io.helidon.webserver.NettyWebServer Thread[nioEventLoopGroup-2-1,10,main]: Channel '@default' started: [id: 0x9521b1ed, L:/0:0:0:0:0:0:0:0:8080]
WEB server is up! http://localhost:8080/greet
# curl http://localhost:8080/posts/
[{"id":"52cdba76-0e18-47e8-8722-453f23bed35a","title":"My second post of Helidon","content":"The content of my second post","createdAt":"2020-02-29T00:15:02.059223"},{"id":"a6201629-bd3b-4c0f-a8df-a94a192f5a02","title":"my test title","content":"my content","createdAt":"2020-02-29T00:15:02.059223"}]

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) .

Grab the source codes from my Github.

--

--

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
Hantsy

Hantsy

Self-employed technical consultant, solution architect and full-stack developer, open source contributor, freelancer and remote worker