Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance. Project price only 1 $
You can buy this project and download/modify it how often you want.
= Reactive MSSQL Client
:PREPARED_PARAMS: `@p1`, `@p2`, etc…
The Reactive MSSQL Client is a client for Microsoft SQL Server with a straightforward API focusing on
scalability and low overhead.
The client is reactive and non-blocking, allowing to handle many database connections with a single thread.
*Features*
* Event driven
* Lightweight
* Built-in connection pooling
* Direct memory to object without unnecessary copies
* Java 8 Date and Time
* RxJava API
* SSL/TLS
* Cursor
* Row streaming
*Not supported yet*
* Prepared queries caching
== Usage
To use the Reactive MSSQL Client add the following dependency to the _dependencies_ section of your build descriptor:
* Maven (in your `pom.xml`):
[source,xml]
----
${maven.groupId}${maven.artifactId}${maven.version}
----
* Gradle (in your `build.gradle` file):
[source,groovy]
----
dependencies {
compile '${maven.groupId}:${maven.artifactId}:${maven.version}'
}
----
== Getting started
Here is the simplest way to connect, query and disconnect
[source,$lang]
----
{@link examples.MSSQLClientExamples#gettingStarted()}
----
== Connecting to SQL Server
Most of the time you will use a pool to connect to MSSQL:
[source,$lang]
----
{@link examples.MSSQLClientExamples#connecting01}
----
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,$lang]
----
{@link examples.MSSQLClientExamples#connecting02}
----
You need to release the pool when you don't need it anymore:
[source,$lang]
----
{@link examples.MSSQLClientExamples#connecting03}
----
When you need to execute several operations on the same connection, you need to use a client
{@link io.vertx.mssqlclient.MSSQLConnection connection}.
You can easily get one from the pool:
[source,$lang]
----
{@link examples.MSSQLClientExamples#connecting04}
----
Once you are done with the connection you must close it to release it to the pool, so it can be reused.
== Configuration
=== Data Object
A simple way to configure the client is to specify a `MSSQLConnectOptions` data object.
[source,$lang]
----
{@link examples.MSSQLClientExamples#configureFromDataObject(io.vertx.core.Vertx)}
----
=== Connection URI
As an alternative to configuring the client with a {@link io.vertx.mssqlclient.MSSQLConnectOptions} data object, you can use a connection URI:
[source,$lang]
----
{@link examples.MSSQLClientExamples#configureFromUri(io.vertx.core.Vertx)}
----
The connection URI format is defined by the client in an idiomatic way:
----
sqlserver://[user[:[password]]@]host[:port][/database][?=[&=]]
----
Currently, the client supports the following parameter keys:
* `host`
* `port`
* `user`
* `password`
* `database`
NOTE: Configuring parameters in connection URI will override the default properties.
== Connect retries
You can configure the client to retry when a connection fails to be established.
[source,$lang]
----
{@link examples.MSSQLClientExamples#reconnectAttempts}
----
include::queries.adoc[leveloffset=1]
== Working with `identity` columns
You can retrieve the value of an `identity` column after inserting new data using the `OUTPUT` clause:
[source,$lang]
----
{@link examples.MSSQLClientExamples#identityColumn}
----
include::connections.adoc[]
include::transactions.adoc[]
include::cursor.adoc[]
== Tracing queries
include::tracing.adoc[]
== Data types supported
Currently, the client supports the following SQL Server types:
* TINYINT(`java.lang.Short`)
* SMALLINT(`java.lang.Short`)
* INT(`java.lang.Integer`)
* BIGINT(`java.lang.Long`)
* BIT(`java.lang.Boolean`)
* REAL(`java.lang.Float`)
* DOUBLE(`java.lang.Double`)
* NUMERIC/DECIMAL(`{@link java.math.BigDecimal}`)
* CHAR/VARCHAR(`java.lang.String`)
* NCHAR/NVARCHAR(`java.lang.String`)
* DATE(`java.time.LocalDate`)
* TIME(`java.time.LocalTime`)
* SMALLDATETIME(`java.time.LocalDateTime`)
* DATETIME(`java.time.LocalDateTime`)
* DATETIME2(`java.time.LocalDateTime`)
* DATETIMEOFFSET(`java.time.OffsetDateTime`)
* BINARY/VARBINARY(`io.vertx.core.buffer.Buffer`)
* MONEY (`{@link java.math.BigDecimal}`)
* SMALLMONEY (`{@link java.math.BigDecimal}`)
* GUID (`{@link java.util.UUID}`)
Tuple decoding uses the above types when storing values.
=== Using Java `enum` types
SQL Server does not have `ENUM` data type, but the client can map the retrieved string/numeric data type to enum.
You can encode Java enums like this:
[source,$lang]
----
{@link examples.MSSQLClientExamples#enumeratedType01Example}
----
You can decode a Java enum like this:
[source,$lang]
----
{@link examples.MSSQLClientExamples#enumeratedType02Example}
----
=== Handling `NULL`
If you modify a `Tuple` with one of the `addXXX` methods, `null` values are handled transparently.
The client can infer the right SQL type when executing a prepared query:
[source,$lang]
----
{@link examples.MSSQLClientExamples#transparentNullHandling}
----
Otherwise, you should declare the type explicitly using one of the {@link io.vertx.sqlclient.data.NullValue} constants or the {@link io.vertx.sqlclient.data.NullValue#of} method:
[source,$lang]
----
{@link examples.MSSQLClientExamples#explicitNullHandling}
----
== Collector queries
You can use Java collectors with the query API:
[source,$lang]
----
{@link examples.MSSQLClientExamples#collector01Example(io.vertx.sqlclient.SqlClient)}
----
The collector processing must not keep a reference on the {@link io.vertx.sqlclient.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,$lang]
----
{@link examples.MSSQLClientExamples#collector02Example(io.vertx.sqlclient.SqlClient)}
----
== Information messages
SQL Server can send information messages to the client as part of responses to queries.
By default, they are logged at `WARN` level.
You can set a handler on a connection to catch them and do something useful with them.
[source,$lang]
----
{@link examples.MSSQLClientExamples#infoHandler}
----
== Using SSL/TLS
=== Encryption level negotiation
When a connection is established, the client and the server negotiate the encryption level.
The negotiated level depends on the client config in {@link io.vertx.mssqlclient.MSSQLConnectOptions} and the server config:
* no encryption: if `ssl` is set to `false` in client options, and the server does not support encryption
* encrypt login packet only: if `ssl` is set to `false` in client options, and the server supports encryption
* encrypt entire connection: if `ssl` is set to `true` in client options, or the server requires encryption
[NOTE]
====
The negotiation fails if `ssl` is set to `true` in client options, and the server does not support encryption.
In this case, the client terminates the connection.
====
=== Configuration
To configure `ssl` in client options, use the {@link io.vertx.mssqlclient.MSSQLConnectOptions#setSsl} method.
By default, `ssl` is set to `false`.
[source,$lang]
----
{@link examples.MSSQLClientExamples#setSsl}
----
When `ssl` is set to `false`, the client trusts all server certificates.
Otherwise, it performs hostname validation.
If `ssl` is set to true in client options, and the server uses a self-signed certificate, hostname validation can be disabled:
[source,$lang]
----
{@link examples.MSSQLClientExamples#disableHostnameValidation}
----
Alternatively, the client can be configured to trust the server certificate with {@link io.vertx.core.net.TrustOptions}.
For example, {@link io.vertx.core.net.PemTrustOptions} can be used if a PEM file contains the server certificate:
[source,$lang]
----
{@link examples.MSSQLClientExamples#usingTrustOptions}
----
For further details about SSL support in Vert.x, please refer to the https://vertx.io/docs/vertx-core/java/#ssl[Vert.x Core documentation].
== Pool sharing
include::pool_sharing.adoc[]
== Advanced pool configuration
include::pool_config.adoc[]
