x-jdbc-client.4.5.4.source-code.index.adoc Maven / Gradle / Ivy
= Vert.x JDBC client
This client allows you to interact with any JDBC compliant database using an asynchronous API from your Vert.x
application.
The client API is represented with the interface {@link io.vertx.ext.jdbc.JDBCClient}.
To use this project, add the following dependency to the _dependencies_ section of your build descriptor:
* Maven (in your `pom.xml`):
[source,xml,subs="+attributes"]
----
io.vertx
vertx-jdbc-client
${maven.version}
----
* Gradle (in your `build.gradle` file):
[source,groovy,subs="+attributes"]
----
compile 'io.vertx:vertx-jdbc-client:${maven.version}'
----
== Using the Sql Client API
Sql Client is the vert.x reactive API to communicate with SQL databases. There are already several implementations of the
API for popular engines such as `MySQL`, `PostgreSQL`, `MSSQL` and `IBM DB2`. However as there are still many other
engines that have no asynchronous drivers, the JDBC Client also implements the same API to allow the usage of JDBC
drivers until such asynchronous support is implemented (eventually).
=== Creating a DB Pool
It all starts with a pool. Creating a pool is simple. The shortest example would be:
[source,$lang]
----
{@link examples.JDBCSqlClientExamples#exampleCreateDefault}
----
In this case we are reusing the JDBC client API to create the pool. The configuration is free form and users need to
look up the properties required for the underlying connection pool of choice.
For a type safe alternative, a second factory method is present. This second factory method ensures that the config is
correct (as it's properties and type is validated by the compiler) but currently only works with the `Agroal` connection
pool.
[source,$lang]
----
{@link examples.JDBCSqlClientExamples#exampleCreateTypeSafe}
----
==== Using the Pool
Once you have a pool, you can start using your database, the pool operates in 2 modes:
1. Managed connection mode
2. Manual connection mode
When working in managed connection mode, as a user, you don't need to worry to fetch and return connections to the pool.
Queries can be run directly against the pool and the pool ensures that a connection is acquired and returned after the
query is terminated.
[source,$lang]
----
{@link examples.JDBCSqlClientExamples#exampleQueryManaged}
----
This is also applicable for prepared statements:
[source,$lang]
----
{@link examples.JDBCSqlClientExamples#examplePreparedQueryManaged}
----
Using this mode is quite convenient as it allows you to focus on the business logic, not on the connection management.
There are times when order and causality may need to be preserved. In this case we need to execute queries on a manual
connection mode:
[source,$lang]
----
{@link examples.JDBCSqlClientExamples#exampleQueryManual}
----
Of course same is to say that prepared statements are also applicable to this mode:
[source,$lang]
----
{@link examples.JDBCSqlClientExamples#examplePreparedQueryManual}
----
=== Retrieving the generated keys
Generated keys is a common feature of JDBC drivers. The pool allows you to retrieve the keys using a special property:
`JDBCPool.GENERATED_KEYS`. For example:
[source,$lang]
----
{@link examples.JDBCSqlClientExamples#exampleGeneratedKeys}
----
=== Working with Procedures and Functions
Users that have used JDBC before are aware that in order to call either a function or a stored procedure, one must use
the interface `CallableStatement`. This abstraction is, for most database engines, synthetic as popular engines like
`PostgreSQL` or `MySQL` do not really have any special command to differentiate from callable statements from any other
regular statement.
The existing SQL client api was designed to be closed to what the reality of wire protocols shows, rather than adapt to
the JDBC spec, therefore you will not find any specific way to deal with callable statements. This design choice
introduces some complexities on the JDBC SQL client, as we need to adapt a regular call to respect the JDBC spec
requirements and be compatible with clients out there.
==== Simple IN mapping
Mapping simple `IN` parameters is trivial. Consider the following procedure:
[source,"sql"]
----
create procedure new_customer(firstname varchar(50), lastname varchar(50))
modifies sql data
insert into customers values (default, firstname, lastname, current_timestamp)
----
In order to call this procedure from the JDBC client, you would write some code as:
[source,$lang]
----
{@link examples.JDBCSqlClientExamples#exampleCallableIN}
----
==== Complex IN / OUT mapping
The previous example, showed how to create simple queries. However, there are some limitations with it. It will assume
that arguments are always of type `IN` and that the argument type is the corresponding java type of the argument, which
will not always work, for example, when dealing with `null`.
In this case, the client has a helper {@link io.vertx.jdbcclient.SqlOutParam} that allows you to explicitly tag the
desired type of the argument. Not only the data type but also if it is a `IN` or a `OUT`.
Consider the following procedure:
[source,"sql"]
----
create procedure customer_lastname(IN firstname varchar(50), OUT lastname varchar(50))
modifies sql data
select lastname into lastname from customers where firstname = firstname
----
This procedure will return all last names, from all customers, when looking for a first name. So we will need to map
both `IN` parameters as well as `OUT` parameters.
[source,$lang]
----
{@link examples.JDBCSqlClientExamples#exampleCallableINOUT}
----
Sometimes, you will need to map both `IN` and `OUT` parameters to the same variable. Again, it is all handled by the
{@link io.vertx.jdbcclient.SqlOutParam} helper.
[source,$lang]
----
{@link examples.JDBCSqlClientExamples#exampleOutParam}
----
== Legacy JDBC Client API
The JDBC Client API created in Vert.x 3 is now deprecated and instead the new SQL Client API should be used.
It will remain supported for the life time of Vert.x 4 to allow applications to be migrated to the new SQL
Client API.
=== Getting a connection
Once you've created a client you use {@link io.vertx.ext.jdbc.JDBCClient#getConnection(io.vertx.core.Handler)} to get
a connection.
This will return the connection in the handler when one is ready from the pool.
[source,$lang]
----
{@link examples.JDBCExamples#example4}
----
The connection is an instance of {@link io.vertx.ext.sql.SQLConnection} which is a common interface not only used by
the Vert.x JDBC Client.
You can learn how to use it in the http://vertx.io/docs/vertx-sql-common/$lang/[common sql interface] documentation.
=== Simple SQL Operations
There are times when you will want to run a single SQL operation, e.g.: a single select of a row, or a update to a
set of rows which do not require to be part of a transaction or have dependencies on the previous or next operation.
For these cases, clients provide a boilerplate-less API {@link io.vertx.ext.sql.SQLOperations}. This interface will
perform the following steps for you:
1. acquire a connection from the connection pool
2. perform your action
3. close and return the connection to the connection pool
An example where users get loaded from the `USERS` table could be:
[source,$lang]
----
{@link examples.SQLExamples#example16}
----
You can perform the following operations as a simple one "shot" method call:
* {@link io.vertx.ext.sql.SQLClient#query(java.lang.String, io.vertx.core.Handler)}
* {@link io.vertx.ext.sql.SQLClient#queryWithParams(java.lang.String, io.vertx.core.json.JsonArray, io.vertx.core.Handler)}
* {@link io.vertx.ext.sql.SQLClient#querySingle(java.lang.String, io.vertx.core.Handler)}
* {@link io.vertx.ext.sql.SQLClient#querySingleWithParams(java.lang.String, io.vertx.core.json.JsonArray, io.vertx.core.Handler)}
* {@link io.vertx.ext.sql.SQLClient#update(java.lang.String, io.vertx.core.Handler)}
* {@link io.vertx.ext.sql.SQLClient#updateWithParams(java.lang.String, io.vertx.core.json.JsonArray, io.vertx.core.Handler)}
* {@link io.vertx.ext.sql.SQLClient#call(java.lang.String, io.vertx.core.Handler)}
* {@link io.vertx.ext.sql.SQLClient#callWithParams(java.lang.String, io.vertx.core.json.JsonArray, io.vertx.core.json.JsonArray, io.vertx.core.Handler)}
For further details on these API please refer to the {@link io.vertx.ext.sql.SQLOperations} interface.
=== Creating a the client
There are several ways to create a client. Let's go through them all.
==== Using default shared data source
In most cases you will want to share a data source between different client instances.
E.g. you scale your application by deploying multiple instances of your verticle and you want each verticle instance
to share the same datasource so you don't end up with multiple pools
You do this as follows:
[source,java]
----
{@link examples.JDBCExamples#exampleCreateDefault}
----
The first call to {@link io.vertx.ext.jdbc.JDBCClient#createShared(io.vertx.core.Vertx, io.vertx.core.json.JsonObject)}
will actually create the data source, and the specified config will be used.
Subsequent calls will return a new client instance that uses the same data source, so the configuration won't be used.
==== Specifying a data source name
You can create a client specifying a data source name as follows
[source,java]
----
{@link examples.JDBCExamples#exampleCreateDataSourceName}
----
If different clients are created using the same Vert.x instance and specifying the same data source name, they will
share the same data source.
The first call to {@link io.vertx.ext.jdbc.JDBCClient#createShared(io.vertx.core.Vertx, io.vertx.core.json.JsonObject)}
will actually create the data source, and the specified config will be used.
Subsequent calls will return a new client instance that uses the same data source, so the configuration won't be used.
Use this way of creating if you wish different groups of clients to have different data sources, e.g. they're
interacting with different databases.
==== Creating a client with a non shared data source
In most cases you will want to share a data source between different client instances.
However, it's possible you want to create a client instance that doesn't share its data source with any other client.
In that case you can use {@link io.vertx.ext.jdbc.JDBCClient#create(io.vertx.core.Vertx, io.vertx.core.json.JsonObject)}.
[source,java]
----
{@link examples.JDBCExamples#exampleCreateNonShared}
----
This is equivalent to calling {@link io.vertx.ext.jdbc.JDBCClient#createShared(io.vertx.core.Vertx, io.vertx.core.json.JsonObject, java.lang.String)}
with a unique data source name each time.
==== Specifying a data source
If you already have a pre-existing data source, you can also create the client directly specifying that:
[source,java]
----
{@link examples.JDBCExamples#exampleCreateWithDataSource}
----
=== Closing the client
It's fine to keep hold of the client for a long time (e.g. the lifetime of your verticle), but once you're
done with it you should close it.
Clients that share a data source with other client instances are reference counted. Once the last one that references
the same data source is closed, the data source will be closed.
==== Automatic clean-up in verticles
If you're creating clients from inside verticles, the clients will be automatically closed when the verticle is undeployed.
=== The SQL Connection
A connection to the database is represented by {@link io.vertx.ext.sql.SQLConnection}.
==== Auto-commit
When you obtain a connection auto commit is set to `true`. This means that each operation you perform will effectively
execute in its own transaction.
If you wish to perform multiple operations in a single transaction you should set auto commit to false with
{@link io.vertx.ext.sql.SQLConnection#setAutoCommit(boolean, io.vertx.core.Handler)}.
When the operation is complete, the handler will be called:
[source,$lang]
----
{@link examples.SQLExamples#example1}
----
==== Executing queries
To execute a query use {@link io.vertx.ext.sql.SQLConnection#query(java.lang.String, io.vertx.core.Handler)}
The query string is raw SQL that is passed through without changes to the actual database.
The handler will be called with the results, represented by {@link io.vertx.ext.sql.ResultSet} when the query has
been run.
[source,$lang]
----
{@link examples.SQLExamples#example2}
----
The {@link io.vertx.ext.sql.ResultSet} instance represents the results of a query.
The list of column names are available with {@link io.vertx.ext.sql.ResultSet#getColumnNames()}, and the actual results
available with {@link io.vertx.ext.sql.ResultSet#getResults()}
The results are a list of {@link io.vertx.core.json.JsonArray} instances, one for each row of the results.
[source,$lang]
----
{@link examples.SQLExamples#example3}
----
You can also retrieve the rows as a list of Json object instances with {@link io.vertx.ext.sql.ResultSet#getRows()} -
this can give you a somewhat simpler API to work with, but please be aware that SQL results can contain duplicate
column names - if that's the case you should use {@link io.vertx.ext.sql.ResultSet#getResults()} instead.
Here's an example of iterating through the results as Json object instances:
[source,$lang]
----
{@link examples.SQLExamples#example3__1}
----
==== Prepared statement queries
To execute a prepared statement query you can use
{@link io.vertx.ext.sql.SQLConnection#queryWithParams(java.lang.String, io.vertx.core.json.JsonArray, io.vertx.core.Handler)}.
This takes the query, containing the parameter place holders, and a {@link io.vertx.core.json.JsonArray} or parameter
values.
[source,$lang]
----
{@link examples.SQLExamples#example3_1}
----
==== Executing INSERT, UPDATE or DELETE
To execute an operation which updates the database use {@link io.vertx.ext.sql.SQLConnection#update(java.lang.String, io.vertx.core.Handler)}.
The update string is raw SQL that is passed through without changes to the actual database.
The handler will be called with the results, represented by {@link io.vertx.ext.sql.UpdateResult} when the update has
been run.
The update result holds the number of rows updated with {@link io.vertx.ext.sql.UpdateResult#getUpdated()}, and
if the update generated keys, they are available with {@link io.vertx.ext.sql.UpdateResult#getKeys()}.
[source,$lang]
----
{@link examples.SQLExamples#example4}
----
==== Prepared statement updates
To execute a prepared statement update you can use
{@link io.vertx.ext.sql.SQLConnection#updateWithParams(java.lang.String, io.vertx.core.json.JsonArray, io.vertx.core.Handler)}.
This takes the update, containing the parameter place holders, and a {@link io.vertx.core.json.JsonArray} or parameter
values.
[source,$lang]
----
{@link examples.SQLExamples#example5}
----
==== Callable statements
To execute a callable statement (either SQL functions or SQL procedures) you can use
{@link io.vertx.ext.sql.SQLConnection#callWithParams(java.lang.String, io.vertx.core.json.JsonArray, io.vertx.core.json.JsonArray, io.vertx.core.Handler)}.
This takes the callable statement using the standard JDBC format `{ call func_proc_name() }`, optionally including
parameter place holders e.g.: `{ call func_proc_name(?, ?) }`, a {@link io.vertx.core.json.JsonArray} containing the
parameter values and finally a {@link io.vertx.core.json.JsonArray} containing the
output types e.g.: `[null, 'VARCHAR']`.
Note that the index of the output type is as important as the params array. If the return value is the second
argument then the output array must contain a null value as the first element.
A SQL function returns some output using the `return` keyword, and in this case one can call it like this:
[source,$lang]
----
{@link examples.SQLExamples#example8}
----
When working with Procedures you and still return values from your procedures via its arguments, in the case you do
not return anything the usage is as follows:
[source,$lang]
----
{@link examples.SQLExamples#example9}
----
However you can also return values like this:
[source,$lang]
----
{@link examples.SQLExamples#example10}
----
Note that the index of the arguments matches the index of the `?` and that the output parameters expect to be a
String describing the type you want to receive.
To avoid ambiguation the implementations are expected to follow the following rules:
* When a place holder in the `IN` array is `NOT NULL` it will be taken
* When the `IN` value is NULL a check is performed on the OUT
When the `OUT` value is not null it will be registered as a output parameter
When the `OUT` is also null it is expected that the IN value is the `NULL` value.
The registered `OUT` parameters will be available as an array in the result set under the output property.
==== Batch operations
The SQL common interface also defines how to execute batch operations. There are 3 types of batch operations:
* Batched statements {@link io.vertx.ext.sql.SQLConnection#batch(java.util.List, io.vertx.core.Handler)}
* Batched prepared statements {@link io.vertx.ext.sql.SQLConnection#batchWithParams(java.lang.String, java.util.List, io.vertx.core.Handler)}
* Batched callable statements {@link io.vertx.ext.sql.SQLConnection#batchCallableWithParams(java.lang.String, java.util.List, java.util.List, io.vertx.core.Handler)}
A batches statement will exeucte a list of sql statements as for example:
[source,$lang]
----
{@link examples.SQLExamples#example12}
----
While a prepared or callable statement batch will reuse the sql statement and take an list of arguments as for example:
[source,$lang]
----
{@link examples.SQLExamples#example11}
----
==== Executing other operations
To execute any other database operation, e.g. a `CREATE TABLE` you can use
{@link io.vertx.ext.sql.SQLConnection#execute(java.lang.String, io.vertx.core.Handler)}.
The string is passed through without changes to the actual database. The handler is called when the operation
is complete
[source,$lang]
----
{@link examples.SQLExamples#example6}
----
==== Multiple ResultSet responses
In some cases your query might return more than one result set, in this case and to preserve the compatibility when
the returned result set object is converted to pure json, the next result sets are chained to the current result set
under the property `next`. A simple walk of all result sets can be achieved like this:
[source,$lang]
----
{@link examples.SQLExamples#example13}
----
==== Streaming
When dealing with large data sets, it is not advised to use API just described but to stream data since it avoids
inflating the whole response into memory and JSON and data is just processed on a row by row basis, for example:
[source,$lang]
----
{@link examples.SQLExamples#example14}
----
You still have full control on when the stream is pauses, resumed and ended. For cases where your query returns
multiple result sets you should use the result set ended event to fetch the next one if available. If there is more
data the stream handler will receive the new data, otherwise the end handler is invoked.
[source,$lang]
----
{@link examples.SQLExamples#example15}
----
==== Using transactions
To use transactions first set auto-commit to false with {@link io.vertx.ext.sql.SQLConnection#setAutoCommit(boolean, io.vertx.core.Handler)}.
You then do your transactional operations and when you want to commit or rollback use
{@link io.vertx.ext.sql.SQLConnection#commit(io.vertx.core.Handler)} or
{@link io.vertx.ext.sql.SQLConnection#rollback(io.vertx.core.Handler)}.
Once the commit/rollback is complete the handler will be called and the next transaction will be automatically started.
[source,$lang]
----
{@link examples.SQLExamples#example7}
----
==== Closing connections
When you've done with the connection you should return it to the pool with {@link io.vertx.ext.sql.SQLConnection#close(io.vertx.core.Handler)}.
ifeval::["$lang" == "java"]
include::override/rxjava2.adoc[]
endif::[]
=== Configuration
Configuration is passed to the client when creating or deploying it.
The following configuration properties generally apply:
`provider_class`:: The class name of the class actually used to manage the database connections. By default this is
`io.vertx.ext.jdbc.spi.impl.C3P0DataSourceProvider` but if you want to use a different provider you can override
this property and provide your implementation.
`row_stream_fetch_size`:: The size of `SQLRowStream` internal cache which used to better performance. By default
it equals to `128`
`datasourceName`:: Name of the data source. Pool metrics will be reported under this name. By default, random
UUID is generated.
Assuming the C3P0 implementation is being used (the default), the following extra configuration properties apply:
`url`:: the JDBC connection URL for the database
`driver_class`:: the class of the JDBC driver
`user`:: the username for the database
`password`:: the password for the database
`max_pool_size`:: the maximum number of connections to pool - default is `15`
`initial_pool_size`:: the number of connections to initialise the pool with - default is `3`
`min_pool_size`:: the minimum number of connections to pool
`max_statements`:: the maximum number of prepared statements to cache - default is `0`.
`max_statements_per_connection`:: the maximum number of prepared statements to cache per connection - default is `0`.
`max_idle_time`:: number of seconds after which an idle connection will be closed - default is `0` (never expire).
Other Connection Pool providers are:
* Hikari
* Agroal
Similar to C3P0 they can be configured by passing the configuration values on the JSON config object. For the special
case where you do not want to deploy your app as a fat jar but run with a vert.x distribution, then it is recommended
to use Agroal if you have no write permissions to add the JDBC driver to the vert.x lib directory and are passing it
using the `-cp` command line flag.
If you want to configure any other C3P0 properties, you can add a file `c3p0.properties` to the classpath.
Here's an example of configuring a service:
[source,java]
----
{@link examples.JDBCExamples#example5}
----
Hikari uses a different set of properties:
* `jdbcUrl` for the JDBC URL
* `driverClassName` for the JDBC driven class name
* `maximumPoolSize` for the pool size
* `username` for the login (`password` for the password)
Refer to the https://github.com/brettwooldridge/HikariCP#configuration-knobs-baby[Hikari documentation]
for further details. Also refer to the https://agroal.github.io/[Agroal documentation]
to configure Agroal.
=== JDBC Drivers
If you are using the default `DataSourceProvider` (relying on c3p0), you would need to copy the JDBC driver class
in your _classpath_.
If your application is packaged as a _fat jar_, be sure to embed the jdbc driver. If your application is launched
with the `vertx` command line, copy the JDBC driver to `${VERTX_HOME}/lib`.
The behavior may be different when using a different connection pool.
=== Data types
Since Vert.x supports many vendor SQL databases, we confide in https://docs.oracle.com/javase/8/docs/technotes/guides/jdbc/jdbc_42.html[JDBC 4.2 specification] to provide the best efforts in converting `Java type` to `SQL type` and vice versa
If any limitations to the data types accepted in the specific SQL database, you will get out of the box the standard by overriding your owns:
* The `encoder` {@link io.vertx.ext.jdbc.spi.JDBCEncoder} to convert `Java input type` to `SQL type`. By default implementation, it is `io.vertx.ext.jdbc.spi.impl.JDBCEncoderImpl`
* The `decoder` {@link io.vertx.ext.jdbc.spi.JDBCDecoder} to convert `SQL result type` to `Java type`. By default implementation, it is `io.vertx.ext.jdbc.spi.impl.JDBCDecoderImpl`
You can include the overridden classes in the SPI file
`META-INF/services/io.vertx.ext.jdbc.spi.JDBCEncoder` and `META-INF/services/io.vertx.ext.jdbc.spi.JDBCDecoder` to load in runtime. Or you can configure directly in the configuration with 2 properties:
* encoderCls
* decoderCls
[source,java]
----
{@link examples.JDBCTypeExamples#createJDBCClient}
----
[source,java]
----
{@link examples.JDBCTypeExamples#createJDBCPool}
----
© 2015 - 2025 Weber Informatics LLC | Privacy Policy