mirror of
https://github.com/jlengrand/helidon.git
synced 2026-03-10 08:21:17 +00:00
272 lines
8.9 KiB
Plaintext
272 lines
8.9 KiB
Plaintext
///////////////////////////////////////////////////////////////////////////////
|
||
|
||
Copyright (c) 2020 Oracle and/or its affiliates.
|
||
|
||
Licensed under the Apache License, Version 2.0 (the "License");
|
||
you may not use this file except in compliance with the License.
|
||
You may obtain a copy of the License at
|
||
|
||
http://www.apache.org/licenses/LICENSE-2.0
|
||
|
||
Unless required by applicable law or agreed to in writing, software
|
||
distributed under the License is distributed on an "AS IS" BASIS,
|
||
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
|
||
See the License for the specific language governing permissions and
|
||
limitations under the License.
|
||
|
||
///////////////////////////////////////////////////////////////////////////////
|
||
|
||
= About Helidon DB Client
|
||
:description: Helidon DB Client
|
||
:keywords: helidon, se, database, dbclient
|
||
:h1Prefix: SE
|
||
|
||
The Helidon SE DB Client provides a unified, reactive API for working with databases in non-blocking way.
|
||
|
||
== Helidon DB Client Features Overview
|
||
|
||
The DB Client simplifies how you work with databases by abstracting the type of the database.
|
||
The API can be used both for relational and non-relational databases.
|
||
|
||
The API provides:
|
||
|
||
* Database configuration abstraction
|
||
+
|
||
Using Helidon configuration allows database implementation specific configuration options
|
||
without the need to use database implementation specific APIs. This allows for seamless switching between databases
|
||
based on configuration.
|
||
|
||
* Statement configuration abstraction
|
||
+
|
||
Using Helidon configuration allows use of database specific statements. This allows usage of
|
||
different databases on different environments without changing code.
|
||
|
||
* Unified API for data access and query
|
||
+
|
||
Thanks to the statement configuration abstraction, we can invoke a statement against a relational
|
||
or non-relations databases (such as MySQL and MongoDB) withough modifying source code
|
||
|
||
* Reactive database access with backpressure
|
||
+
|
||
Current we support natively reactive driver for MongoDB, and an executor service wrapped
|
||
support for any JDBC driver.
|
||
This allows for seamless use of JDBC drivers in a reactive non-blocking environment, including support
|
||
for backpressure (result set is processed as requested by the query subscriber)
|
||
|
||
* Observability +
|
||
+
|
||
The API offers support for health checks, metrics and tracing.
|
||
|
||
|
||
== Getting Started
|
||
|
||
Before you begin you must add the DB Client dependencies and configure the client.
|
||
|
||
. Add the DB Client dependencies to the Maven `pom.xml` file.
|
||
+
|
||
The <<about/04_managing-dependencies.adoc, Managing Dependencies>> page describes how you
|
||
should declare dependency management for Helidon applications.
|
||
|
||
For the DB Client using JDBC implementation and H2 database, you must include the following dependencies in your project:
|
||
|
||
[source,java]
|
||
----
|
||
<dependencies>
|
||
<dependency>
|
||
<groupId>io.helidon.dbclient</groupId> // <1>
|
||
<artifactId>helidon-dbclient</artifactId>
|
||
</dependency>
|
||
<dependency>
|
||
<groupId>io.helidon.dbclient</groupId> // <2>
|
||
<artifactId>helidon-dbclient-jdbc</artifactId>
|
||
</dependency>
|
||
<dependency>
|
||
<groupId>com.h2.database</groupId> // <3>
|
||
<artifactId>h2</artifactId>
|
||
<version>1.4.200</version>
|
||
</dependency>
|
||
</dependencies>
|
||
----
|
||
<1> Add the Helidon DB Client
|
||
<2> Specify JDBC or MongoDB
|
||
<3> Add the database JDBC driver (only for JDBC)
|
||
|
||
. Use Helidon Config to configure the client.
|
||
|
||
The DB Client must be configured before you begin. In the example below we'll use Helidon Config to set up JDBC-based client:
|
||
|
||
[source,yaml]
|
||
----
|
||
db:
|
||
source: "jdbc" // <1>
|
||
connection:
|
||
url: "jdbc:mysql://127.0.0.1:3306/pokemon?useSSL=false" // <2>
|
||
username: "user"
|
||
password: "password"
|
||
statements: // <3>
|
||
ping: "DO 0" // <4>
|
||
select-all-pokemons: "SELECT id, name FROM Pokemons"
|
||
|
||
----
|
||
<1> Source: `jdbc` or `mongoDb`
|
||
<2> Connection: database connection parameters
|
||
<3> Statements: named statements to be used in application
|
||
<4> A ping statement used by health check
|
||
|
||
== Using DB Client API Methods
|
||
|
||
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.
|
||
|
||
=== Executor Selection
|
||
|
||
`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.
|
||
|
||
=== Statement Building and Execution
|
||
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 (`create**Named**DmlStatement`) 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
|
||
|
||
Ordered parameters are written down as `?` in the statement text:
|
||
|
||
[source,sql]
|
||
----
|
||
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
|
||
|
||
Named parameters are written down as `:<name>` in the JDBC statements
|
||
|
||
[source,sql]
|
||
----
|
||
SELECT name FROM Pokemons WHERE id = :id
|
||
----
|
||
|
||
or as `$<name>` in the MongoDB statement:
|
||
|
||
[source,json]
|
||
----
|
||
{
|
||
"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
|
||
|
||
=== Statement Execution
|
||
|
||
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:
|
||
|
||
[source,java]
|
||
----
|
||
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:
|
||
|
||
[source,java]
|
||
----
|
||
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:
|
||
|
||
[source,java]
|
||
----
|
||
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
|
||
|
||
Execution of DML statements will always return `Single<Long>` with the number of modified records in the database.
|
||
|
||
In following example, the number of modified records is printed to standard output:
|
||
|
||
[source,java]
|
||
----
|
||
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
|
||
|
||
Execution of a query statement will always return `Multi<DbRow>>`. `Multi` has several useful properties:
|
||
|
||
* It is an implementation of `Flow.Publisher` to process individual result rows using `Flow.Subscriber<DbRow>`
|
||
* `Single<List<DbRow>> collectList()` to collect all rows and return them as a promise of `List<DbRow>`
|
||
* `<U> Multi<U> map(…)` to map returned result using provided mapper
|
||
|
||
== Next Steps
|
||
|
||
Now that you understand how to build and execute statements, try it for yourself.
|
||
https://github.com/oracle/helidon/tree/master/examples/dbclient[DB Client Examples].
|
||
|
||
|
||
|
||
|