///////////////////////////////////////////////////////////////////////////////

    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 & MP 



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,java]
----

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:

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

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

=== 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:

----
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 `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:

----
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:

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




