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

To use this project, add the following dependency to the dependencies section of your build descriptor:

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

    <dependency>
      <groupId>io.vertx</groupId>
      <artifactId>vertx-jdbc-client</artifactId>
      <version>5.0.0.CR1</version>
    </dependency>

- Gradle (in your `build.gradle` file):

    compile 'io.vertx:vertx-jdbc-client:5.0.0.CR1'

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

    JDBCConnectOptions connectOptions = new JDBCConnectOptions()
      .setJdbcUrl("jdbc:h2:~/test")
      .setUser("sa")
      .setPassword("");
    PoolOptions poolOptions = new PoolOptions()
      .setMaxSize(16);
    Pool pool = JDBCPool.pool(vertx, connectOptions, poolOptions);

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.

    Pool pool = JDBCPool.pool(
      vertx,
      // configure the connection
      new JDBCConnectOptions()
        // H2 connection string
        .setJdbcUrl("jdbc:h2:~/test")
        // username
        .setUser("sa")
        // password
        .setPassword(""),
      // configure the pool
      new PoolOptions()
        .setMaxSize(16)
        .setName("pool-name")
    );

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

    pool
      .query("SELECT * FROM user")
      .execute()
      .onFailure(e -> {
        // handle the failure
      })
      .onSuccess(rows -> {
        for (Row row : rows) {
          System.out.println(row.getString("FIRST_NAME"));
        }
      });

This is also applicable for prepared statements:

    pool
      .preparedQuery("SELECT * FROM user WHERE emp_id > ?")
      // the emp id to look up
      .execute(Tuple.of(1000))
      .onFailure(e -> {
        // handle the failure
      })
      .onSuccess(rows -> {
        for (Row row : rows) {
          System.out.println(row.getString("FIRST_NAME"));
        }
      });

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:

    pool
      .getConnection()
      .onFailure(e -> {
        // failed to get a connection
      })
      .onSuccess(conn -> {
        conn
          .query("SELECT * FROM user")
          .execute()
          .onFailure(e -> {
            // handle the failure

            // very important! don't forget to return the connection
            conn.close();
          })
          .onSuccess(rows -> {
            for (Row row : rows) {
              System.out.println(row.getString("FIRST_NAME"));
            }

            // very important! don't forget to return the connection
            conn.close();
          });
      });

Of course same is to say that prepared statements are also applicable to this mode:

    pool
      .getConnection()
      .onFailure(e -> {
        // failed to get a connection
      })
      .onSuccess(conn -> {
        conn
          .preparedQuery("SELECT * FROM user WHERE emp_id > ?")
          // the emp_id to look up
          .execute(Tuple.of(1000))
          .onFailure(e -> {
            // handle the failure

            // very important! don't forget to return the connection
            conn.close();
          })
          .onSuccess(rows -> {
            for (Row row : rows) {
              System.out.println(row.getString("FIRST_NAME"));
            }

            // very important! don't forget to return the connection
            conn.close();
          });
      });

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

    String sql = "INSERT INTO insert_table (FNAME, LNAME) VALUES (?, ?)";

    pool
      .preparedQuery(sql)
      .execute(Tuple.of("Paulo", "Lopes"))
      .onSuccess(rows -> {
        // the generated keys are returned as an extra row
        Row lastInsertId = rows.property(JDBCPool.GENERATED_KEYS);
        // just refer to the position as usual:
        long newId = lastInsertId.getLong(0);
      });

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

    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:

    String sql = "{call new_customer(?, ?)}";

    pool
      .preparedQuery(sql)
      // by default the "IN" argument types are extracted from the
      // type of the data in the tuple, as well as the order
      .execute(Tuple.of("Paulo", "Lopes"))
      .onFailure(e -> {
        // handle the failure
      })
      .onSuccess(rows -> {
        // ... success handler
      });

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

    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.

    String sql = "{call customer_lastname(?, ?)}";

    pool
      .preparedQuery(sql)
      // by default the "IN" argument types are extracted from the
      // type of the data in the tuple, as well as the order
      //
      // Note that we now also declare the output parameter and it's
      // type. The type can be a "String", "int" or "JDBCType" constant
      .execute(Tuple.of("John", SqlOutParam.OUT(JDBCType.VARCHAR)))
      .onFailure(e -> {
        // handle the failure
      })
      .onSuccess(rows -> {
        // we can verify if there was a output received from the callable statement
        if (rows.property(JDBCPool.OUTPUT)) {
          // and then iterate the results
          for (Row row : rows) {
            System.out.println(row.getString(0));
          }
        }
      });

Sometimes, you will need to map both `IN` and `OUT` parameters to the same variable. Again, it is all handled by the `SqlOutParam` helper.

    SqlOutParam param;

    // map IN as "int" as well as "OUT" as VARCHAR
    param = SqlOutParam.INOUT(123456, JDBCType.VARCHAR);
    // or
    param = SqlOutParam.INOUT(123456, "VARCHAR");

    // and then just add to the tuple as usual:
    Tuple.of(param);