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.

Asynchronous?

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;
    resultHandler.handle(r);
}

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:

<dependency>
  <groupId>io.vertx</groupId>
  <artifactId>vertx-jdbc-client</artifactId>
  <version>3.1.0</version>
</dependency>
<dependency>
  <groupId>org.hsqldb</groupId>
  <artifactId>hsqldb</artifactId>
  <version>2.3.3</version>
</dependency>

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:

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

with:

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()) {
      fut.fail(ar.cause());
    } else {
      next.handle(Future.succeededFuture(ar.result()));
    }
  });
}

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()) {
      fut.fail(result.cause());
    } else {
      SQLConnection connection = result.result();
      connection.execute(
          "CREATE TABLE IF NOT EXISTS Whisky (id INTEGER IDENTITY, name varchar(100), " +
          "origin varchar(100))",
          ar -> {
            if (ar.failed()) {
              fut.fail(ar.cause());
              connection.close();
              return;
            }
            connection.query("SELECT * FROM Whisky", select -> {
              if (select.failed()) {
                fut.fail(ar.cause());
                connection.close();
                return;
              }
              if (select.result().getNumRows() == 0) {
                insert(
                    new Whisky("Bowmore 15 Years Laimrig", "Scotland, Islay"),
                    connection,
                    (v) -> insert(new Whisky("Talisker 57° North", "Scotland, Island"),
                        connection,
                        (r) -> {
                          next.handle(Future.<Void>succeededFuture());
                          connection.close();
                        }));                                                    
              } else {
                next.handle(Future.<Void>succeededFuture());
                connection.close();
              }
            });
          });
    }
  }

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:

connection.execute(
    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.

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 ?, ?";
  connection.updateWithParams(sql,
      new JsonArray().add(whisky.getName()).add(whisky.getOrigin()),
      (ar) -> {
        if (ar.failed()) {
          next.handle(Future.failedFuture(ar.cause()));
          return;
        }
        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());
        next.handle(Future.succeededFuture(w));
      });
}

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());
        routingContext.response()
            .putHeader("content-type", "application/json; charset=utf-8")
            .end(Json.encodePrettily(whiskies));
        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.

Conclusion

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!