se.guides.dbclient.adoc Maven / Gradle / Ivy
///////////////////////////////////////////////////////////////////////////////
Copyright (c) 2022, 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.
///////////////////////////////////////////////////////////////////////////////
= Helidon SE DB Client Guide
:description: Helidon db-client
:keywords: helidon, db, client
:rootdir: {docdir}/../..
include::{rootdir}/includes/se.adoc[]
This guide describes the features of Helidon's DB Client and how to create a sample Helidon SE project
that can be used to run some basic examples using the Helidon DB Client.
== What You Need
For this 15 minute tutorial, you will need the following:
include::{rootdir}/includes/prerequisites.adoc[tag=prerequisites]
== Introduction
The Helidon DB Client provides a unified API for working with databases.
=== Main Features
The main features of Helidon DB Client are:
* *Unified API for data access and query*:
The API was implemented as a layer above JDBC or MongoDB Java Driver, so any relational databases
with JDBC driver or MongoDB are supported.
* *Observability*:
Support for health checks, metrics and tracing.
* *Portability between relational database drivers*:
Works with native database statements that can be used inline in the code or defined as named statements
in database configuration. By moving the native query code to configuration files, the Helidon DB Client allows you to
switch to another database by changing the configuration files, not the code.
== Getting Started with Helidon DB Client
This section describes how to configure and use the key features of the Helidon DB Client.
=== Set Up the H2 Database
==== From Docker
Create a new file in `helidon-quickstart-se` named `Dockerfile.h2`. It will be used to create the H2 docker image
to run H2 in a container.
[source,dockerfile]
.Write the following content into the new file created
----
FROM openjdk:11-jre-slim
ENV H2_VERSION "1.4.199"
ADD "https://repo1.maven.org/maven2/com/h2database/h2/${H2_VERSION}/h2-${H2_VERSION}.jar" /opt/h2.jar
COPY h2.server.properties /root/.h2.server.properties
EXPOSE 8082
EXPOSE 9092
CMD java \
-cp /opt/h2.jar \
org.h2.tools.Server \
-web -webDaemon -webAllowOthers -webPort 8082 \
-tcp -tcpAllowOthers -tcpPort 9092 \
-ifNotExists
----
Create a new file `h2.server.properties` in the current directory.
[source,properties]
.Copy the properties into the properties file.
----
webSSL=false
webAllowOthers=true
webPort=8082
0=Generic H2 (Server)|org.h2.Driver|jdbc\:h2\:tcp\://localhost\:9092/~/test|sa
----
[source,bash]
.Build the H2 docker image
----
docker build -f Dockerfile.h2 . -t h2db
----
[source,bash]
.Run the H2 docker image
----
docker run --rm -p 8082:8082 -p 9092:9092 --name=h2 -it h2db
----
==== From the Command Line
A database stores the books from the library. H2 is a java SQL database that is easy to use and lightweight.
If H2 is not installed on your machine, here are few steps to quickly download and set it up:
1. Download the latest H2 version from the official website: https://www.h2database.com/html/main.html
* Note: Windows operating system users can download the Windows Installer.
2. Unzip the downloaded file into your directory.
* Only the h2-\{latest-version}.jar, located in the h2/bin folder, will be needed.
3. Open a terminal window and run the following command to start H2:.
[source, bash]
.Replace `\{latest-version}` with your current H2 version:
----
java -cp h2-{latest-version}.jar org.h2.tools.Shell -url dbc:h2:~/test -user sa -password "" -sql "" # <1>
java -jar h2-{latest-version}.jar -webAllowOthers -tcpAllowOthers -web -tcp # <2>
----
<1> Pre-create the database (optional if the file `~/test` already exists)
<2> Start the database
=== Connect to the Database
Open the console at http://127.0.0.1:8082 in your favorite browser. It displays a login window.
Select `Generic H2` from `Saved Settings`. The following settings should be set by default:
* Driver Class: org.h2.Driver
* JDBC URL: jdbc:h2:tcp://localhost:9092/~/test
* User Name: sa
* Password:
Password must stay empty. Click **Connect**, the browser displays a web page. The database is correctly set and running.
=== Create a Sample SE Project Using Maven Archetype
Generate the project sources using the Helidon SE Maven archetype.
The result is a simple project that can be used for the examples in this guide.
[source,bash,subs="attributes+"]
.Run the Maven archetype:
----
mvn -U archetype:generate -DinteractiveMode=false \
-DarchetypeGroupId=io.helidon.archetypes \
-DarchetypeArtifactId=helidon-quickstart-se \
-DarchetypeVersion={helidon-version} \
-DgroupId=io.helidon.examples \
-DartifactId=helidon-quickstart-se \
-Dpackage=io.helidon.examples.quickstart.se
----
A new directory named `helidon-quickstart-se` is created.
[source,bash]
.Enter into this directory:
----
cd helidon-quickstart-se
----
=== Add Dependencies
Navigate to the `helidon-quickstart-se` directory and open the `pom.xml` file to add the following Helidon dependencies required to use the DB Client:
[source,xml]
.Copy these dependencies to pom.xml:
----
io.helidon.dbclient
helidon-dbclient
io.helidon.dbclient
helidon-dbclient-jdbc
io.helidon.dbclient
helidon-dbclient-hikari
io.helidon.integrations.db
h2
org.slf4j
slf4j-jdk14
io.helidon.dbclient
helidon-dbclient-health
io.helidon.dbclient
helidon-dbclient-metrics
io.helidon.dbclient
helidon-dbclient-metrics-hikari
io.helidon.dbclient
helidon-dbclient-jsonp
----
<1> DB Client API dependency.
<2> Using JDBC driver for this example.
<3> Using HikariCP as a connection pool.
<4> H2 driver dependency.
<5> Support for health check.
<6> Support for metrics.
<7> Support for Jsonp.
=== Configure the DB Client
To configure the application, Helidon uses the `application.yaml`. The DB Client configuration can be joined in the same
file and is located here: `src/main/resources`.
[source,yaml]
.Copy these properties into application.yaml
----
db:
source: jdbc # <1>
connection: # <2>
url: "jdbc:h2:tcp://localhost:9092/~/test"
username: "sa"
password:
statements: # <3>
health-check: "SELECT 0"
create-table: "CREATE TABLE IF NOT EXISTS LIBRARY (NAME VARCHAR NOT NULL, INFO VARCHAR NOT NULL)"
insert-book: "INSERT INTO LIBRARY (NAME, INFO) VALUES (:name, :info)"
select-book: "SELECT INFO FROM LIBRARY WHERE NAME = ?"
delete-book: "DELETE FROM LIBRARY WHERE NAME = ?"
health-check:
type: "query"
statementName: "health-check"
services:
metrics:
- type: COUNTER # <4>
statement-names: [ "select-book" ]
----
<1> Source property support two values: jdbc and mongo.
<2> Connection detail we used to set up H2.
<3> SQL statements to manage the database.
<4> Add a counter for metrics only for the `select-book` statement.
[source,yaml]
.Copy these properties into application-test.yaml
----
db:
connection:
url: "jdbc:h2:mem:test" # <1>
----
<1> Override the JDBC URL to use an in-memory database for the tests
=== Set Up Helidon DB Client
[source,java]
.Update `Main#main`:
----
include::{sourcedir}/se/guides/DbclientSnippets.java[tag=snippet_1, indent=0]
----
<1> Create the DbClient instance
<2> Register it in the global context
<3> Create an instance of HealthObserver to register a DbClientHealthCheck
<4> Add the `HealthObserver` to the `ObserveFeature`
<5> Register the ObserveFeature on the server
=== Create the Library service
Create LibraryService class into `io.helidon.examples.quickstart.se` package.
[source,java]
.LibraryService class looks like this:
----
include::{sourcedir}/se/guides/DbclientSnippets.java[tag=snippet_2, indent=0]
----
<1> Declare the DB Client instance
<2> Initialize the DB Client instance using global config
<3> Initialize the database schema
As the LibraryService implements `io.helidon.webserver.HttpService`, the `routing(HttpRules)` method has to be implemented.
It defines application endpoints and Http request which can be reached by clients.
[source,java]
.Add update method to LibraryService
----
include::{sourcedir}/se/guides/DbclientSnippets.java[tag=snippet_3, indent=0]
----
<1> Return information about the required book from the database.
<2> Add a book to the library.
<3> Remove a book from the library.
<4> Return the book information in Json format.
To summarize, there is one endpoint that can manipulate books.
The number of endpoints and application
features can be changed from these rules by creating or modifying methods. `\{name}` is a path parameter for the book
name. The architecture of the application is defined, so the next step is to create these features.
[source,java]
.Add getBook to the LibraryService:
----
include::{sourcedir}/se/guides/DbclientSnippets.java[tag=snippet_4, indent=0]
----
<1> Get the book name from the path in the URL.
<2> Helidon DB Client executes the `select-book` SQL script from application.yaml.
<3> Sends 404 HTTP status if no book was found for the given name.
<4> Sends book information to the client.
The `getBook` method reach the book from the database and send the information to the client. The name of the book is
located into the url path. If the book is not present in the database, an HTTP 404 is sent back.
The `execute()` method is called on the dbClient instance to execute one statement.
Nevertheless, it is possible to execute a set of tasks into a single execution unit by using the
`transaction()` method.
DbExecute class provides many builders to create statements such as, DML, insert, update, delete, query and get
statements. For each statement there are two builders which can be regrouped in 2 categories. Builders with methods
containing `Named` keyword, they use a statement defined in the configuration file.
And builders without `Named` keyword, they use a statement passed as an argument. More information on the Helidon DB
Client xref:../dbclient.adoc[here].
[source,java]
.Add getJsonBook to the LibraryService:
----
include::{sourcedir}/se/guides/DbclientSnippets.java[tag=snippet_5, indent=0]
----
Instead of sending the `INFO` content of the targeted book, the `getJsonBook` method send the whole row of the
database as a `JsonObject`.
[source,java]
.Add addBook to the LibraryService:
----
include::{sourcedir}/se/guides/DbclientSnippets.java[tag=snippet_6, indent=0]
----
<1> The SQL statement requires the book name and its information. They are provided with `addParam` method.
<2> A new book was added to library, so an HTTP 201 code is returned.
When a user adds a new book, it uses HTTP PUT method where the book name is in the URL and the information in the
request content. To catch this content, the information is retrieved as a string and then the DB Client execute the
`insert-book` script to add the book to the library. It requires two parameters, the book name and information which are
passed to the dbClient thanks to `addParam` method. An HTTP 201 is sent back as a confirmation.
[source,java]
.Add deleteBook to LibraryService:
----
include::{sourcedir}/se/guides/DbclientSnippets.java[tag=snippet_7, indent=0]
----
<1> Execute SQL script from application.yaml to remove a book from the library by its name.
<2> The required book was removed, so an HTTP 204 is sent.
To remove a book from the library, use the "delete-book" script in the way than previously. If the book is removed
successfully, an HTTP 204 is sent back.
=== Set Up Routing
[source,java]
.Modify the `routing` method in `Main.java`:
----
include::{sourcedir}/se/guides/DbclientSnippets.java[tag=snippet_8, indent=0]
----
<1> Register the LibraryService to the Routing.
The library service does not yet exist, but you'll create it in the next step of the guide.
== Build and Run the Library Application
The application is ready to be built and run.
[source,bash]
.Run the following to build the application:
----
mvn package
----
Note that the tests are passing as the `GreetService` process was not modified. For the purposes of this demonstration,
we only added independent new content to the existing application.
Make sure H2 is running and start the Helidon quickstart with this command:
[source,bash]
.Run the application
----
java -jar target/helidon-quickstart-se.jar
----
Once the application starts, check the table LIBRARY is created in the H2 database. To do so, go to the
H2 Server console and LIBRARY table should be present in the left column under `jdbc:h2:tcp://localhost:9092/~/test`.
If it is not, try to refresh the page, and it should appear.
Use `curl` to send request to the application:
[source,bash]
.Get a book from the library
----
curl -i http://localhost:8080/library/SomeBook
----
[source, listing]
.HTTP response
----
HTTP/1.1 404 Not Found
Date: Tue, 12 Jan 2021 14:00:48 +0100
transfer-encoding: chunked
connection: keep-alive
----
There is currently no book inside the library, so the application returns a 404. Yet the application created an empty
library table. Try to add a new book.
[source,bash]
.Add a book from the library
----
curl -i -X PUT -d "Fantasy" http://localhost:8080/library/HarryPotter
----
[source, listing]
.HTTP response
----
HTTP/1.1 201 Created
Date: Tue, 12 Jan 2021 14:01:08 +0100
transfer-encoding: chunked
connection: keep-alive
----
This command creates an HTTP PUT request with the genre `Fantasy` content at the address
http://localhost:8080/library/\{book-name}. The 201 code means that Harry Potter book was successfully added to
the library. You can now try to get it !
[source,bash]
.Get Harry Potter from the library
----
curl -i http://localhost:8080/library/HarryPotter
----
[source, listing]
.HTTP response
----
HTTP/1.1 200 OK
Content-Type: text/plain
Date: Tue, 12 Jan 2021 14:01:14 +0100
connection: keep-alive
content-length: 6
Fantasy
----
The application accepted the request and returned an HTTP 200 OK with the book genre that was added earlier.
[source,bash]
.Get Harry Potter from the library in Json
----
curl -i http://localhost:8080/library/json/HarryPotter
----
[source, listing]
.HTTP response
----
HTTP/1.1 200 OK
Content-Type: text/plain
Date: Tue, 12 Jan 2021 14:01:14 +0100
connection: keep-alive
content-length: 6
{"INFO":"Fantasy"}
----
It returns the database row in a Json format for the Harry Potter book.
Harry Potter can be removed from the library with the following:
[source,bash]
.Remove Harry Potter from the library
----
curl -i -X DELETE http://localhost:8080/library/HarryPotter
----
[source, listing]
.HTTP response
----
HTTP/1.1 204 No Content
Date: Tue, 12 Jan 2021 14:01:22 +0100
connection: keep-alive
----
The book had been removed from the library and confirmed by the 204 HTTP status. To check that the book was correctly
deleted, try to get it again.
[source,bash]
.Get Harry Potter from the library
----
curl -i http://localhost:8080/library/HarryPotter
----
[source, listing]
.HTTP response
----
HTTP/1.1 404 Not Found
Date: Tue, 12 Jan 2021 14:00:48 +0100
transfer-encoding: chunked
connection: keep-alive
----
The book is not found. We quickly checked, thanks to this suite of command, the application behavior.
[source,bash]
.Check the health of your application:
----
curl http://localhost:8080/observe/health
----
[source,json]
.Response body
----
{
"status": "UP",
"checks": [
{
"name": "jdbc:h2",
"status": "UP"
}
]
}
----
It confirms that the database is UP.
[source,bash]
.Check the metrics of your application:
----
curl -H "Accept: application/json" http://localhost:8080/observe/metrics/application
----
[source,json]
.Response body
----
{
"db.counter.select-book" : 4
}
----
The select-book statement was invoked four times.
=== Summary
This guide provided an introduction to the Helidon DB Client's key features. If you want to learn more, see the
Helidon DB Client samples in link:{helidon-github-tree-url}/examples/dbclient[GitHub].
© 2015 - 2025 Weber Informatics LLC | Privacy Policy