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

    Copyright (c) 2019, 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.

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

= Helidon MP Data Source Guide
:description: Helidon MP Data Source Guide
:keywords: helidon, guide, datasource, microprofile

This guide shows how to configure and use named
https://docs.oracle.com/javase/8/docs/api/javax/sql/DataSource.html[`DataSource`]s
in your Helidon MP application.

== What You Need

|===
|About 20 minutes
|<<about/03_prerequisites.adoc,Helidon Prerequisites>>
|`curl` (for testing)
|===

== What You'll Do

By following this guide, you'll enhance a bare-bones Helidon MP
application to access an in-memory
https://www.h2database.com/html/main.html[H2 database] database.
You'll see how to install the relevant dependencies, set up and
configure the datasource, and add datasource-related code to your
application.

== Use the Maven Archetype to Generate a Helidon MP Application

In a shell, `cd` into an empty directory and run this:

[source,bash,subs="attributes+"]
----
mvn archetype:generate \
    -DinteractiveMode=false \
    -DarchetypeGroupId=io.helidon.archetypes \
    -DarchetypeArtifactId=helidon-mp \
    -DarchetypeVersion={helidon-version} \
    -DgroupId=io.helidon.example \
    -DartifactId=helidon-ds \
    -Dpackage=io.helidon.example.ds \
    -DrestResourceName=ExampleResource \
    -DapplicationName=ExampleApplication
----

Now `cd` into `helidon-ds`.  The rest of this guide will assume all
relative paths are relative to this directory.

== Add the H2 Database Driver to the Runtime Classpath

Add the following dependency in your `pom.xml`:

[source,xml]
.`pom.xml`
----
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.199</version>
    <scope>runtime</scope>
</dependency>
----

In a production application, you may use a different database, so in
that case you may add a different database driver dependency here
instead.

== Add the Hikari Connection Pool Extension to the Runtime Classpath

Add the following dependency in your `pom.xml`:

[source,xml]
.`pom.xml`
----
<dependency>
    <groupId>io.helidon.integrations.cdi</groupId>
    <artifactId>helidon-integrations-cdi-datasource-hikaricp</artifactId>
    <scope>runtime</scope>
</dependency>
----

== Add an `application.yaml` File With Database Connectivity Information

Replace the contents of the following file under `src/main/resources`:

[source,yaml]
.`src/main/resources/application.yaml`
----
server:
    port: 8080
javax:
    sql:
        DataSource: <1>
            test: <2>
                dataSourceClassName: org.h2.jdbcx.JdbcDataSource <3>
                dataSource: <4>
                    url: jdbc:h2:mem:test <5>
                    user: sa
                    password: ""

----

<1> This `javax:`/`sql:`/`DataSource:` preamble is required.

<2> `test` is the name of the `DataSource` being configured here.

<3> `dataSourceClassName` is an
https://github.com/brettwooldridge/HikariCP/blob/dev/README.md#configuration-knobs-baby[essential
Hikari connection pool property].

<4> `dataSource` is a
https://github.com/brettwooldridge/HikariCP/blob/dev/README.md#initialization[Hikari
connection pool keyword].

<5> These are some of the Java Beans-compliant properties exposed by,
in this case, the
https://www.h2database.com/javadoc/org/h2/jdbcx/JdbcDataSource.html[`org.h2.jdbcx.JdbcDataSource`
class].

== Inject a https://docs.oracle.com/javase/8/docs/api/javax/sql/DataSource.html[`DataSource`] in Your Application Code

In the `src/main/java/io/helidon/example/ds/ExampleResource.java` file, add the following
imports:

[source,java]
.`src/main/java/io/helidon/example/ds/ExampleResource.java`
----
import javax.enterprise.context.Dependent;
import javax.inject.Inject;
import javax.inject.Named;
import javax.sql.DataSource;
----

Annotate the resource class declaration with `@Dependent`:

[source,java]
.`src/main/java/io/helidon/example/ds/ExampleResource.java`
----
@Dependent <1>
public class ExampleResource {
----

<1> This ensures that `io.helidon.example.jpa.ExampleResource` is a
discoverable CDI bean.

Then add the following annotated field declaration:

[source,java]
.`src/main/java/io/helidon/example/ds/ExampleResource.java`
----
@Inject <1>
@Named("test") <2>
private DataSource testDataSource;
----

<1> The
http://javax-inject.github.io/javax-inject/api/javax/inject/Inject.html[`@Inject`
annotation] is used to indicate that the CDI container should set the
annotated field automatically.

<2> The
http://javax-inject.github.io/javax-inject/api/javax/inject/Named.html[`@Named`
annotation] is used to select which data source should be injected.
Here, the `test` data source is requested.

== Use The Injected `DataSource`

Now that you have a `DataSource`, you'll use it to connect to the database.

First, ensure the `io.heldion.example.ds.ExampleResource` resource
class imports various `java.sql` classes:

[source,java]
.`src/main/java/io/helidon/example/ds/ExampleResource.java`
----
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
----

Add the following resource method to the `ExampleResource` class:

[source,java]
.`src/main/java/io/helidon/example/ds/ExampleResource.java`
----
@GET
@Path("tables")
@Produces("text/plain")
public String getTableNames() throws SQLException { <1>
    StringBuilder sb = new StringBuilder();
    try (Connection connection = this.testDataSource.getConnection(); <2>
         PreparedStatement ps =
           connection.prepareStatement(" SELECT TABLE_NAME" <3>
                                       + " FROM INFORMATION_SCHEMA.TABLES "
                                       + "ORDER BY TABLE_NAME ASC");
         ResultSet rs = ps.executeQuery()) {
      while (rs.next()) {
        sb.append(rs.getString(1)).append("\n");
      }
    }
    return sb.toString();
}
----

<1> Database interactions can throw `SQLException`.

<2> We acquire a `Connection`, a `PreparedStatement` and a `ResultSet`
in a try-with-resources block.

<3> This SQL statement returns a list of all table names in the database.

== Build the Application

Execute the following from the root directory of your application:

[source,bash]
----
mvn clean package
----

== Run the Application

Execute the following from the root directory of your application:

[source,bash]
----
java -jar target/helidon-ds.jar
----

== Test the Application

Execute the following:

[source,bash]
----
curl http://localhost:8080/example/tables
----

Observe that the result will be a list of database table names.

== Related Examples

Helidon features a few examples of projects that use data sources.

* https://github.com/oracle/helidon/tree/{helidon-version}/examples/integrations/cdi/datasource-hikaricp-h2[An
  example showing a Hikari connection pool data source connected to an
  H2 database]

* https://github.com/oracle/helidon/tree/{helidon-version}/examples/integrations/cdi/datasource-hikaricp-mysql[An
  example showing a Hikari connection pool data source connected to a
  MySQL database]

Some examples' configurations can be found in their
`META-INF/microprofile-config.properties` resources instead of in an
`application.yaml` file as described above.  Though the syntax is
different, the same principles as those described above still apply.
