Databases

You are currently viewing the documentation for the unreleased version 5.0.0.CR2 of Vert.x. Visit the latest stable version of this page.

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.CR2</version>
</dependency>

Gradle (in your build.gradle file):

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

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:

Managed connection mode
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()
  .compose(conn -> conn
    .query("SELECT * FROM user")
    .execute()
    // very important! don't forget to return the connection
    .eventually(conn::close))
  .onSuccess(rows -> {
    for (Row row : rows) {
      System.out.println(row.getString("FIRST_NAME"));
    }
  })
  .onFailure(e -> {
    // handle the failure
  });

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

pool
  .getConnection()
  .compose(conn -> conn
    .preparedQuery("SELECT * FROM user WHERE emp_id > ?")
    .execute(Tuple.of(1000))
    // very important! don't forget to return the connection
    .eventually(conn::close))
  .onSuccess(rows -> {
    for (Row row : rows) {
      System.out.println(row.getString("FIRST_NAME"));
    }
  })
  .onFailure(e -> {
    // handle the failure
  });

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

Cassandra

Kafka