= Reactive Oracle Client
:PREPARED_PARAMS: `?`

The Reactive Oracle Client is a client for Oracle with a straightforward API focusing on scalability and low overhead.

*Features*

* Event driven
* Built-in connection pooling
* Java 8 Date and Time
* SSL/TLS
* RxJava API
* Cursor
* Row streaming

*Not supported yet*

* Prepared queries caching
* Stored Procedures

== Usage

To use the Reactive Oracle Client add the following dependency to the _dependencies_ section of your build descriptor:

* Maven (in your `pom.xml`):

[source,xml]
----
<dependency>
  <groupId>io.vertx</groupId>
  <artifactId>vertx-oracle-client</artifactId>
  <version>5.0.2</version>
</dependency>
----
* Gradle (in your `build.gradle` file):

[source,groovy]
----
dependencies {
  compile 'io.vertx:vertx-oracle-client:5.0.2'
}
----

== Getting started

Here is the simplest way to connect, query and disconnect

[source,java]
----
OracleConnectOptions connectOptions = new OracleConnectOptions()
  .setPort(1521)
  .setHost("the-host")
  .setDatabase("the-db")
  .setUser("user")
  .setPassword("secret");

// Pool options
PoolOptions poolOptions = new PoolOptions()
  .setMaxSize(5);

// Create the client pool
Pool client = OracleBuilder.pool()
  .with(poolOptions)
  .connectingTo(connectOptions)
  .build();

// A simple query
client
  .query("SELECT * FROM users WHERE id='julien'")
  .execute()
  .onComplete(ar -> {
    if (ar.succeeded()) {
      RowSet<Row> result = ar.result();
      System.out.println("Got " + result.size() + " rows ");
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }

    // Now close the pool
    client.close();
  });
----

== Connecting to Oracle

Most of the time you will use a pool to connect to Oracle:

[source,java]
----
OracleConnectOptions connectOptions = new OracleConnectOptions()
  .setPort(1521)
  .setHost("the-host")
  .setDatabase("the-db")
  .setUser("user")
  .setPassword("secret");

// Pool options
PoolOptions poolOptions = new PoolOptions()
  .setMaxSize(5);

// Create the pooled client
Pool client = OracleBuilder.pool()
  .with(poolOptions)
  .connectingTo(connectOptions)
  .using(vertx)
  .build();
----

The pooled client uses a connection pool and any operation will borrow a connection from the pool
to execute the operation and release it to the pool.

If you are running with Vert.x you can pass it your Vertx instance:

[source,java]
----
OracleConnectOptions connectOptions = new OracleConnectOptions()
  .setPort(1521)
  .setHost("the-host")
  .setDatabase("the-db")
  .setUser("user")
  .setPassword("secret");

// Pool options
PoolOptions poolOptions = new PoolOptions()
  .setMaxSize(5);
// Create the pooled client
Pool client = OracleBuilder.pool()
  .with(poolOptions)
  .connectingTo(connectOptions)
  .using(vertx)
  .build();
----

You need to release the pool when you don't need it anymore:

[source,java]
----
pool.close();
----

When you need to execute several operations on the same connection, you need to use a client
`link:../../apidocs/io/vertx/sqlclient/SqlConnection.html[connection]`.

You can easily get one from the pool:

[source,java]
----
OracleConnectOptions connectOptions = new OracleConnectOptions()
  .setPort(1521)
  .setHost("the-host")
  .setDatabase("the-db")
  .setUser("user")
  .setPassword("secret");

// Pool options
PoolOptions poolOptions = new PoolOptions()
  .setMaxSize(5);

// Create the pooled client
Pool client = OracleBuilder.pool()
  .with(poolOptions)
  .connectingTo(connectOptions)
  .using(vertx)
  .build();

// Get a connection from the pool
client.getConnection().compose(conn -> {
  System.out.println("Got a connection from the pool");

  // All operations execute on the same connection
  return conn
    .query("SELECT * FROM users WHERE id='julien'")
    .execute()
    .compose(res -> conn
      .query("SELECT * FROM users WHERE id='emad'")
      .execute())
    .onComplete(ar -> {
      // Release the connection to the pool
      conn.close();
    });
}).onComplete(ar -> {
  if (ar.succeeded()) {

    System.out.println("Done");
  } else {
    System.out.println("Something went wrong " + ar.cause().getMessage());
  }
});
----

Once you are done with the connection you must close it to release it to the pool, so it can be reused.

== Pool sharing

include::pool_sharing.adoc[]

== Configuration

There are several alternatives for you to configure the client.

=== Data Object

A simple way to configure the client is to specify a `link:../../apidocs/io/vertx/oracleclient/OracleConnectOptions.html[OracleConnectOptions]` data object.

[source,java]
----
OracleConnectOptions connectOptions = new OracleConnectOptions()
  .setPort(1521)
  .setHost("the-host")
  .setDatabase("the-db")
  .setUser("user")
  .setPassword("secret");

// Pool Options
PoolOptions poolOptions = new PoolOptions().setMaxSize(5);

// Create the pool from the data object
Pool pool = OracleBuilder.pool()
  .with(poolOptions)
  .connectingTo(connectOptions)
  .using(vertx)
  .build();

pool
  .getConnection()
  .onComplete(ar -> {
    // Handling your connection
  });
----

=== Connection URI

Apart from configuring with a `link:../../apidocs/io/vertx/oracleclient/OracleConnectOptions.html[OracleConnectOptions]` data object, we also provide you with an alternative way to connect when you want to configure with a connection URI.

Using the EZConnect format:

[source,java]
----
String connectionUri = "oracle:thin:@mydbhost1:5521/mydbservice?connect_timeout=10sec";

// Connect options
OracleConnectOptions connectOptions = OracleConnectOptions.fromUri(connectionUri)
  .setUser("user")
  .setPassword("secret");

// Pool Options
PoolOptions poolOptions = new PoolOptions().setMaxSize(5);

// Create the pool from the connection URI
Pool pool = OracleBuilder.pool()
  .with(poolOptions)
  .connectingTo(connectOptions)
  .using(vertx)
  .build();
----

Or, using the TNS Alias format:

[source,java]
----
String connectionUri = "oracle:thin:@prod_db?TNS_ADMIN=/work/tns/";

// Connect options
OracleConnectOptions connectOptions = OracleConnectOptions.fromUri(connectionUri)
  .setUser("user")
  .setPassword("secret");

// Pool Options
PoolOptions poolOptions = new PoolOptions().setMaxSize(5);

// Create the pool from the connection URI
Pool pool = OracleBuilder.pool()
  .with(poolOptions)
  .connectingTo(connectOptions)
  .using(vertx)
  .build();
----

== Connect retries

You can configure the client to retry when a connection fails to be established.

[source,java]
----
options
  .setReconnectAttempts(2)
  .setReconnectInterval(1000);
----

include::queries.adoc[leveloffset=1]

== Retrieving generated key values

When executing `INSERT` queries, you can retrieve the generated key values.

The values are returned as a `link:../../apidocs/io/vertx/sqlclient/Row.html[Row]` instance.
This instance is accessible by invoking `link:../../apidocs/io/vertx/sqlclient/SqlResult.html#property-io.vertx.sqlclient.PropertyKind-[SqlResult.property(kind)]` using the `link:../../apidocs/io/vertx/oracleclient/OracleClient.html#GENERATED_KEYS[OracleClient.GENERATED_KEYS]` property kind.

The key values can be retrieved by column name:

[source,java]
----
String sql = "INSERT INTO EntityWithIdentity (name, position) VALUES (?, ?)";

// Retrieve generated key column value by name
OraclePrepareOptions options = new OraclePrepareOptions()
  .setAutoGeneratedKeysIndexes(new JsonArray().add("ID"));

client.preparedQuery(sql, options)
  .execute(Tuple.of("john", 3))
  .onComplete(ar -> {
    if (ar.succeeded()) {
      RowSet<Row> result = ar.result();

      Row generated = result.property(OracleClient.GENERATED_KEYS);
      Long id = generated.getLong("ID");
    }
  });
----

Or, they can be retrieved by column index:

[source,java]
----
String sql = "INSERT INTO EntityWithIdentity (name, position) VALUES (?, ?)";

// Retrieve generated key column value by index
OraclePrepareOptions options = new OraclePrepareOptions()
  .setAutoGeneratedKeysIndexes(new JsonArray().add("1"));

client.preparedQuery(sql, options)
  .execute(Tuple.of("john", 3))
  .onComplete(ar -> {
    if (ar.succeeded()) {
      RowSet<Row> result = ar.result();

      Row generated = result.property(OracleClient.GENERATED_KEYS);
      Long id = generated.getLong("ID");
    }
  });
----

include::connections.adoc[]

include::transactions.adoc[]

include::cursor.adoc[]

== Data types supported

Currently, the client supports the following Oracle data types:

* CHAR/VARCHAR2(`java.lang.String`)
* NCHAR/NVARCHAR2(`java.lang.String`)
* NUMBER(``link:../../apidocs/java/math/BigDecimal.html[BigDecimal]``)
* FLOAT(`java.lang.Double`)
* DATE(`java.time.LocalDate`)
* TIMESTAMP(`java.time.LocalDateTime`)
* RAW(`io.vertx.core.buffer.Buffer`)

Tuple decoding uses the above types when storing values.

`BLOB` data type is also supported with one caveat: it must be represented by an instance of `link:../../apidocs/io/vertx/oracleclient/data/Blob.html[Blob]` when writing or filtering.
However, when reading `BLOB` data, the client returns a `link:../../apidocs/io/vertx/core/buffer/Buffer.html[Buffer]`.

[source,java]
----
client.preparedQuery("INSERT INTO images (name, data) VALUES (?, ?)")
  // Use io.vertx.oracleclient.data.Blob when inserting
  .execute(Tuple.of("beautiful-sunset.jpg", Blob.copy(imageBuffer)))
  .onComplete(ar -> {
    // Do something
  });

client.preparedQuery("SELECT data FROM images WHERE id = ?")
  .execute(Tuple.of(id))
  .onComplete(ar -> {
    if (ar.succeeded()) {
      Row row = ar.result().iterator().next();

      // Use io.vertx.core.buffer.Buffer when reading
      Buffer data = row.getBuffer("data");
    }
  });
----

== Tracing queries

include::tracing.adoc[]

== Collector queries

You can use Java collectors with the query API:

[source,java]
----
Collector<Row, ?, Map<Long, String>> collector = Collectors.toMap(
  row -> row.getLong("id"),
  row -> row.getString("last_name"));

// Run the query with the collector
client.query("SELECT * FROM users")
  .collecting(collector)
  .execute()
  .onComplete(ar -> {
    if (ar.succeeded()) {
      SqlResult<Map<Long, String>> result = ar.result();

      // Get the map created by the collector
      Map<Long, String> map = result.value();
      System.out.println("Got " + map);
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
----

The collector processing must not keep a reference on the `link:../../apidocs/io/vertx/sqlclient/Row.html[Row]` as there is a single row used for processing the entire set.

The Java `Collectors` provides many interesting predefined collectors, for example you can create easily create a string directly from the row set:

[source,java]
----
Collector<Row, ?, String> collector = Collectors.mapping(
  row -> row.getString("last_name"),
  Collectors.joining(",", "(", ")")
);

// Run the query with the collector
client.query("SELECT * FROM users")
  .collecting(collector)
  .execute()
  .onComplete(ar -> {
    if (ar.succeeded()) {
      SqlResult<String> result = ar.result();

      // Get the string created by the collector
      String list = result.value();
      System.out.println("Got " + list);
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
----

== Using SSL/TLS

To enable encryption in client options, use the `link:../../apidocs/io/vertx/oracleclient/OracleConnectOptions.html#setSsl-boolean-[setSsl]` method.
By default, `ssl` is set to `false`.

[source,java]
----
oracleConnectOptions.setSsl(true);
----

Encryption can be customized using properties.
For example, to set a trust store:

[source,java]
----
oracleConnectOptions
  .setSsl(true)
  .addProperty("javax.net.ssl.trustStore", pathToTrustStore)
  .addProperty("javax.net.ssl.trustStoreType", "JKS")
  .addProperty("javax.net.ssl.trustStorePassword", trustStorePassword);
----