Using the asynchronous SQL client

Fi­nally, back… This post is the fifth post of the in­tro­duc­tion to vert.x blog se­ries, after a not-​that-small break. In this post we are going to see how we can use JDBC in a vert.x ap­pli­ca­tion, and this, using the asyn­chro­nous API pro­vided by the vertx-​jdbc-client.

Previously in the introduction to vert.x series

As it was quite some time since the last post, let’s start by re­fresh­ing our mind about the four pre­vi­ous posts:

  1. The first post has de­scribed how to build a vert.x ap­pli­ca­tion with Maven and ex­e­cute unit tests.
  2. The sec­ond post has de­scribed how this ap­pli­ca­tion can be­come con­fig­urable.
  3. The third post has in­tro­duced vertx-​web, and a small col­lec­tion man­age­ment ap­pli­ca­tion has been de­vel­oped. This ap­pli­ca­tion of­fers a REST API used by a HTML/JavaScript fron­tend.
  4. The pre­vi­ous post has pre­sented how you can run in­te­gra­tion tests to en­sure the be­hav­ior of your ap­pli­ca­tion.

In this post, back to code. The cur­rent ap­pli­ca­tion uses an in-​memory map to store the prod­ucts. It’s time to use a data­base. In this post we are going to use HSQL, but you can use any data­base pro­vid­ing a JDBC dri­ver. In­ter­ac­tions with the data­base will be asyn­chro­nous and made using the vertx-​jdbc-client.

The code of this post are avail­able on this Github project, in the post-5 branch branch.


One of the vert.x char­ac­ter­is­tics is being asyn­chro­nous. With an asyn­chro­nous API, you don’t wait for a re­sult, but you are no­ti­fied when this re­sult is ready. Just to il­lus­trate this, let’s take a very sim­ple ex­am­ple.

Let’s imag­ine an add method. Tra­di­tion­ally, you would use it like this: int r = add(1, 1). This is a syn­chro­nous API as you are wait­ing for the re­sult. An asyn­chro­nous ver­sion of this API would be: add(1, 1, r -> { /* do something with the result */ }). In this ver­sion, you pass a Handler called when the re­sult has been com­puted. The method does not re­turn any­thing, and could be im­ple­mented as:

public void add(int a, int b, Handler<Integer> resultHandler) {
    int r = a + b;

Just to avoid mis­con­cep­tions, asyn­chro­nous API are not about threads. As we can see in the add ex­am­ple, there are no threads in­volved.

JDBC yes, but asynchronous

So, now that we have seen some ba­sics about asyn­chro­nous API, let’s have a look to the vertx-​jdbc-client. This com­po­nent lets us in­ter­act with a data­base through a JDBC dri­ver. These in­ter­ac­tions are asyn­chro­nous, so when you were doing:

String sql = "SELECT * FROM Products";
ResultSet rs = stmt.executeQuery(sql);

it will be:

connection.query("SELECT * FROM Products", result -> {
        // do something with the result

This model is more ef­fi­cient as it avoids wait­ing for the re­sult. You are no­ti­fied when the re­sult is avail­able.

Let’s now mod­ify our ap­pli­ca­tion to use a data­base to store our prod­ucts.

Some maven dependencies

The first things we need to do it to de­clare two new Maven de­pen­den­cies in our pom.xml file:


The first de­pen­dency pro­vides the vertx-​jdbc-client, while the sec­ond one pro­vide the HSQL JDBC dri­ver. If you want to use an­other data­base, change this de­pen­dency. You will also need to change the JDBC url and JDBC dri­ver class name later.

Initializing the JDBC client

Now that we have added these de­pen­den­cies, it’s time to cre­ate our JDBC client:

In the MyFirstVerticle class, de­clare a new field JDBCClient jdbc;, and add the fol­low­ing line at the be­gin­ning of the start method:

jdbc = JDBCClient.createShared(vertx, config(), "My-Whisky-Collection");

This cre­ates an in­stance of JDBC client, con­fig­ured with the con­fig­u­ra­tion pro­vided to the ver­ti­cle. To work cor­rectly this con­fig­u­ra­tion needs to pro­vide:

  • url - the JDBC url such as jdbc:hsqldb:mem:db?shutdown=true
  • dri­ver_class - the JDBC dri­ver class such as org.hsqldb.jdbcDriver

Ok, we have the client, we need a con­nec­tion to the data­base. This is achieved using the jdbc.getConnection that take a Handler<AsyncResult<SQLConnection>> as pa­ra­me­ter. Let’s have a deeper look to this type. It’s a Handler, so it is called when the re­sult is ready. This re­sult is an in­stance of AsyncResult<SQLConnection>. AsyncResult is a struc­ture pro­vided by vert.x that lets us know if the op­er­a­tion was com­pleted suc­cess­fully or failed. In case of suc­cess, it pro­vides the re­sult, here an in­stance of SQLConnection.

When you re­ceive an in­stance of AsyncResult, your code gen­er­ally looks like:

if (ar.failed()) {
  System.err.println("The operation has failed...: "
      + ar.cause().getMessage());
} else {
  // Use the result:
  result = ar.result();

So, let’s go back to our SQLConnection. We need to re­trieve it, and then start the rest of the ap­pli­ca­tion. This changes how we start the ap­pli­ca­tion, as it will be­come asyn­chro­nous. So, if we di­vide our startup se­quence into small chunks it would be some­thing like:

 (connection) -> createSomeData(connection,
     (nothing) -> startWebApp(
         (http) -> completeStartup(http, fut)
     ), fut
 ), fut);


  1. startBackend - re­trieves a SQLConnection and then calls the next step
  2. createSomeData - ini­tial­izes the data­base and in­serts some data. When done, it calls the next step
  3. startWebApp - starts our web ap­pli­ca­tion
  4. completeStartup - fi­nal­izes our start se­quence

fut is the com­ple­tion fu­ture passed by vert.x that let us re­port when we are started, or if an issue has been en­coun­tered while start­ing.

Let’s have a look to startBackend:

private void startBackend(Handler<AsyncResult<SQLConnection>> next, Future<Void> fut) {
  jdbc.getConnection(ar -> {
    if (ar.failed()) {;
    } else {

This method re­trieves a SQLConnection, check whether this op­er­a­tion suc­ceeded. If so, it calls the next step. In case of fail­ure, it re­ports it.

The other meth­ods fol­low the same pat­tern: 1) check if the last op­er­a­tion has suc­ceeded, 2) do the task, 3) call the next step.

A bit of SQL…

Our client is ready, let’s now write some SQL state­ments. Let’s start by the createSomeData method that is part of the startup se­quence:

private void createSomeData(AsyncResult<SQLConnection> result,
    Handler<AsyncResult<Void>> next, Future<Void> fut) {
    if (result.failed()) {;
    } else {
      SQLConnection connection = result.result();
          "CREATE TABLE IF NOT EXISTS Whisky (id INTEGER IDENTITY, name varchar(100), " +
          "origin varchar(100))",
          ar -> {
            if (ar.failed()) {
            connection.query("SELECT * FROM Whisky", select -> {
              if (select.failed()) {
              if (select.result().getNumRows() == 0) {
                    new Whisky("Bowmore 15 Years Laimrig", "Scotland, Islay"),
                    (v) -> insert(new Whisky("Talisker 57° North", "Scotland, Island"),
                        (r) -> {
              } else {

This method checks that the SQLConnection is avail­able and then start ex­e­cut­ing some SQL state­ments. First, it cre­ates the ta­bles if there are not there yet. As you can see, the method called is struc­tured as fol­lows:

    SQL statement,
    handler called when the statement has been executed

The han­dler re­ceives an AsyncResult<Void>, i.e. a no­ti­fi­ca­tion of the com­ple­tion with­out an ac­tual re­sult.

Closing connection

Don’t for­get to close the SQL con­nec­tion when you are done. The con­nec­tion will be given back to the con­nec­tion pool and be reused.

In the code of this han­dler, we check whether or not the state­ment has been ex­e­cuted cor­rectly, and if so we check to see if the table al­ready con­tains some data, if not, it in­serts data using the insert method:

private void insert(Whisky whisky, SQLConnection connection, Handler<AsyncResult<Whisky>> next) {
  String sql = "INSERT INTO Whisky (name, origin) VALUES ?, ?";
      new JsonArray().add(whisky.getName()).add(whisky.getOrigin()),
      (ar) -> {
        if (ar.failed()) {
        UpdateResult result = ar.result();
        // Build a new whisky instance with the generated id.
        Whisky w = new Whisky(result.getKeys().getInteger(0), whisky.getName(), whisky.getOrigin());

This method uses the updateWithParams method with an IN­SERT state­ment, and pass val­ues. This ap­proach avoids SQL in­jec­tion. Once the the state­ment has been ex­e­cuted, we cre­ates a new Whisky ob­ject with the cre­ated (auto-​generated) id.

Some REST with a pinch of SQL

The method de­scribed above is part of our start se­quence. But what about the method in­voked by our REST API. Let’s have a look to the getAll method. This method is called by the web front-​end to re­trieve all stored prod­ucts:

 private void getAll(RoutingContext routingContext) {
    jdbc.getConnection(ar -> {
      SQLConnection connection = ar.result();
      connection.query("SELECT * FROM Whisky", result -> {
        List<Whisky> whiskies = result.result().getRows().stream().map(Whisky::new).collect(Collectors.toList());
            .putHeader("content-type", "application/json; charset=utf-8")
        connection.close(); // Close the connection     

This method gets a SQLConnection, and then issue a query. Once the re­sult has been re­trieved it writes the HTTP re­sponse as be­fore. The getOne, deleteOne, updateOne and addOne meth­ods fol­low the same pat­tern. No­tice that the con­nec­tion can be closed after the re­sponse has been writ­ten.

Let’s have a look to the re­sult pro­vided to the han­dler passed to the query method. It gets a ResultSet, which con­tains the query re­sult. Each row is a JsonObject, so if your data ob­ject has a con­struc­tor tak­ing a JsonObject as unique ar­gu­ment, cre­at­ing there ob­jects is straight­for­ward.

Test, test, and test again

We need to slightly up­date our tests to con­fig­ure the JDBCClient. In the MyFirstVertilceTest class, change the DeploymentOption ob­ject cre­ated in the setUp method to be:

DeploymentOptions options = new DeploymentOptions()
    .setConfig(new JsonObject()
        .put("http.port", port)
        .put("url", "jdbc:hsqldb:mem:test?shutdown=true")
        .put("driver_class", "org.hsqldb.jdbcDriver")

In ad­di­tion to the http.port, we also put the JDBC url and the class of the JDBC dri­ver. We use an in-​memory data­base for tests.

The same mod­i­fi­ca­tion needs to be done in the src/test/resources/my-it-config.json file:

  "http.port": ${http.port},
  "url": "jdbc:hsqldb:mem:it-test?shutdown=true",
  "driver_class": "org.hsqldb.jdbcDriver"

The src/main/conf/my-application-conf.json file also needs to be up­dated, not for the tests, but to run the ap­pli­ca­tion:

  "http.port" : 8082,
  "url": "jdbc:hsqldb:file:db/whiskies",
  "driver_class": "org.hsqldb.jdbcDriver"

The JDBC url is a bit dif­fer­ent in this last file, as we store the data­base on the file sys­tem.

Show time!

Let’s now build our ap­pli­ca­tion:

mvn clean package

As we didn’t change the API (nei­ther the pub­lic java one nor the REST), test should run smoothly.

Then launch the ap­pli­ca­tion with:

java -jar target/my-first-app-1.0-SNAPSHOT-fat.jar -conf src/main/conf/my-application-conf.json

Open your browser to http://localhost:8082/assets/index.html, and you should see the ap­pli­ca­tion using the data­base. This time the prod­ucts are stored in a data­base per­sisted on the file sys­tem. So, if we stop and restart the ap­pli­ca­tion, the data is re­stored.


In this post, we saw how you can use JDBC data­base with vert.x, and thus with­out too much bur­den. You may have been sur­prised by the asyn­chro­nous de­vel­op­ment model, but once you start using it, it’s hard to come back.

In the next post, we see how the same ap­pli­ca­tion can use Mon­goDB in­stead of HSQL.

Stay tuned, and happy cod­ing!

Next post

Vert.x ES6 back to the future

On October 21th, 2015 we all rejoiced with the return from the past of Marty McFly with his flying car and so on, however in the Vert.x world we were quite sad about our rather old JavaScript support.

Read more
Previous post

Vert.x 3.1.0 is released!

I'm pleased to announce the release of Vert.x 3.1!

Read more
Related posts

Unit and Integration Tests

Let’s refresh our mind about what we developed so far in the introduction to vert.x series. We forgot an important task. We didn’t test the API.

Read more

Combine vert.x and mongo to build a giant

This blog post is part of the introduction to Vert.x series. We are now going to replace this JDBC client by the vertx-mongo-client, and thus connect to a Mongo database.

Read more

My first Vert.x 3 Application

Let's say, you heard someone saying that Vert.x is awesome. Ok great, but you may want to try it by yourself. Well, the next natural question is “where do I start ?”

Read more