se.dbclient.adoc Maven / Gradle / Ivy
///////////////////////////////////////////////////////////////////////////////
Copyright (c) 2020, 2024 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
:feature-name: DB Client
:rootdir: {docdir}/..
include::{rootdir}/includes/se.adoc[]
== Contents
- <>
- <>
- <>
- <>
- <>
- <>
== Overview
The Helidon SE DB Client provides a unified API for working with databases.
include::{rootdir}/includes/dependencies.adoc[]
[source,xml]
----
io.helidon.dbclient
helidon-dbclient
----
To use with a JDBC client also add the following dependency:
[source,xml]
----
io.helidon.dbclient
helidon-dbclient-jdbc
----
Or to use with MongoDB client add the following dependency:
[source,xml]
----
io.helidon.dbclient
helidon-dbclient-mongodb
----
== Usage
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.
== API
* 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) without modifying source code
* Observability +
+
The API offers support for health checks, metrics and tracing.
== Configuration
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.
For the DB Client using JDBC implementation and H2 database, you must include the following dependencies in your project:
[source,xml]
----
io.helidon.dbclient
helidon-dbclient
io.helidon.dbclient
helidon-dbclient-jdbc
com.h2database
h2
----
<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()`
* `transaction()`
Both methods provide an executor: either `DbExecute` or `DbTransaction`.
=== 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 `:` in the JDBC statements
[source,sql]
----
SELECT name FROM Pokemons WHERE id = :id
----
or as `$` 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 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]
----
include::{sourcedir}/se/DbClientSnippets.java[tag=snippet_1, indent=0]
----
JDBC query with named parameters and the query runs in transaction:
[source,java]
----
include::{sourcedir}/se/DbClientSnippets.java[tag=snippet_2, indent=0]
----
Both examples will return `Multi` 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]
----
include::{sourcedir}/se/DbClientSnippets.java[tag=snippet_3, indent=0]
----
This update statement will return a `long` with the number of modified records in the database.
==== DML Statement Result
Execution of DML statements will always return a `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]
----
include::{sourcedir}/se/DbClientSnippets.java[tag=snippet_4, indent=0]
----
==== Query Statement Result
Execution of a query statement will always return `Stream>`.
* The stream is populated lazily, result rows can be processed individually
* Use `.map(…)` to map returned result
* Use `.toList()` on the stream to collect all rows
== Additional Information
Now that you understand how to build and execute statements, try it for yourself.
link:{helidon-github-tree-url}/examples/dbclient[DB Client Examples].
© 2015 - 2025 Weber Informatics LLC | Privacy Policy