# 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 `JDBCClient`.

## 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:

    JDBCClient client = JDBCClient.createShared(vertx, config);

The first call to `JDBCClient.createShared` 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

    JDBCClient client = JDBCClient.createShared(vertx, config, "MyDataSource");

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 `JDBCClient.createShared` 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 `JDBCClient.createNonShared`.

    JDBCClient client = JDBCClient.createNonShared(vertx, config);

This is equivalent to calling `JDBCClient.createShared` 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:

    JDBCClient client = JDBCClient.create(vertx, dataSource);

## 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.

## Getting a connection

Once you’ve created a client you use `getConnection` to get a connection.

This will return the connection in the handler when one is ready from the pool.

    client.getConnection(res -> {
      if (res.succeeded()) {

        SQLConnection connection = res.result();

        connection.query("SELECT * FROM some_table", res2 -> {
          if (res2.succeeded()) {

            ResultSet rs = res2.result();
            // Do something with results
          }
        });
      } else {
        // Failed to get connection - deal with it
      }
    });

The connection is an instance of `SQLConnection` which is a common interface used by more than Vert.x sql service.

You can learn how to use it in the common sql interface documentation.

## 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.

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).

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:

    JsonObject config = new JsonObject()
      .put("url", "jdbc:hsqldb:mem:test?shutdown=true")
      .put("driver_class", "org.hsqldb.jdbcDriver")
      .put("max_pool_size", 30);

    JDBCClient client = JDBCClient.createShared(vertx, config);