Configurable SQL queries

    While the verticle will turn the previously hard-coded values to configuration parameters, we will also go a step further by loading the SQL queries from a properties file.

    The queries will be loaded from a file passed as a configuration parameter or from a default resource if none is being provided. The advantage of this approach is that the verticle can adapt both to different JDBC drivers and SQL dialects.

    The verticle class preamble consists mainly of configuration key definitions:

    SQL queries are being stored in a properties file, with the default ones for HSQLDB being located in :

    1. create-pages-table=create table if not exists Pages (Id integer identity primary key, Name varchar(255) unique, Content clob)
    2. get-page=select Id, Content from Pages where Name = ?
    3. create-page=insert into Pages values (NULL, ?, ?)
    4. save-page=update Pages set Content = ? where Id = ?
    5. all-pages=select Name from Pages
    6. delete-page=delete from Pages where Id = ?

    The following code from the WikiDatabaseVerticle class loads the SQL queries from a file, and make them available from a map:

    1. private JDBCClient dbClient;
    3. @Override
    4. public void start(Promise<Void> promise) throws Exception {
    6.   /*
    7.    * Note: this uses blocking APIs, but data is small...
    8.    */
    9.   loadSqlQueries();  (1)
    11.   dbClient = JDBCClient.createShared(vertx, new JsonObject()
    12.     .put("url", config().getString(CONFIG_WIKIDB_JDBC_URL, "jdbc:hsqldb:file:db/wiki"))
    13.     .put("driver_class", config().getString(CONFIG_WIKIDB_JDBC_DRIVER_CLASS, "org.hsqldb.jdbcDriver"))
    14.     .put("max_pool_size", config().getInteger(CONFIG_WIKIDB_JDBC_MAX_POOL_SIZE, 30)));
    16.   dbClient.getConnection(ar -> {
    17.     if (ar.failed()) {
    18.       LOGGER.error("Could not open a database connection", ar.cause());
    19.       promise.fail(ar.cause());
    20.     } else {
    21.       SQLConnection connection = ar.result();
    22.       connection.execute(sqlQueries.get(SqlQuery.CREATE_PAGES_TABLE), create -> {   (2)
    23.         connection.close();
    24.         if (create.failed()) {
    25.           LOGGER.error("Database preparation error", create.cause());
    26.           promise.fail(create.cause());
    27.         } else {
    28.           vertx.eventBus().consumer(config().getString(CONFIG_WIKIDB_QUEUE, "wikidb.queue"), this::onMessage);  (3)
    29.           promise.complete();
    30.         }
    31.       });
    32.     }
    33.   });
    34. }

    1. Interestingly we break an important principle in Vert.x which is to avoid blocking APIs, but since there are no asynchronous APIs for accessing resources on the classpath our options are limited. We could use the Vert.x executeBlocking method to offload the blocking I/O operations from the event loop to a worker thread, but since the data is very small there is no obvious benefit in doing so.

    2. Here is an example of using SQL queries.

    3. The consumer method registers an event bus destination handler.

    Dispatching requests

    The event bus message handler is the onMessage method:

    We defined a ErrorCodes enumeration for errors, which we use to report back to the message sender. To do so, the fail method of the Message class provides a convenient shortcut to reply with an error, and the original message sender gets a failed AsyncResult.

    Reducing the JDBC client boilerplate

    1. retrieve a connection,

    2. perform requests,

    3. release the connection.

    This leads to code where lots of error processing needs to happen for each asynchronous operation, as in:

    1. dbClient.getConnection(car -> {
    2.   if (car.succeeded()) {
    3.     connection.query(sqlQueries.get(SqlQuery.ALL_PAGES), res -> {
    4.       connection.close();
    6.         List<String> pages = res.result()
    7.           .getResults()
    8.           .stream()
    9.           .map(json -> json.getString(0))
    10.           .sorted()
    11.           .collect(Collectors.toList());
    12.         message.reply(new JsonObject().put("pages", new JsonArray(pages)));
    13.       } else {
    14.         reportQueryError(message, res.cause());
    15.       }
    16.     });
    17.   } else {
    18.     reportQueryError(message, car.cause());
    19.   }
    20. });

    Starting from Vert.x 3.5.0, the JDBC client now supports one-shot operations where a connection is being acquired to do a SQL operation, then released internally. The same code as above now reduces to:

    The rest of the class consists of private methods called when onMessage dispatches incoming messages:

    1. private void fetchAllPages(Message<JsonObject> message) {
    2.   dbClient.query(sqlQueries.get(SqlQuery.ALL_PAGES), res -> {
    3.     if (res.succeeded()) {
    4.       List<String> pages = res.result()
    5.         .getResults()
    6.         .stream()
    7.         .map(json -> json.getString(0))
    8.         .sorted()
    9.         .collect(Collectors.toList());
    10.       message.reply(new JsonObject().put("pages", new JsonArray(pages)));
    11.     } else {
    12.       reportQueryError(message, res.cause());
    13.     }
    14.   });
    15. }
    17. private void fetchPage(Message<JsonObject> message) {
    18.   String requestedPage = message.body().getString("page");
    19.   JsonArray params = new JsonArray().add(requestedPage);
    21.   dbClient.queryWithParams(sqlQueries.get(SqlQuery.GET_PAGE), params, fetch -> {
    22.     if (fetch.succeeded()) {
    23.       JsonObject response = new JsonObject();
    24.       ResultSet resultSet = fetch.result();
    25.       if (resultSet.getNumRows() == 0) {
    26.         response.put("found", false);
    27.       } else {
    28.         response.put("found", true);
    29.         JsonArray row = resultSet.getResults().get(0);
    30.         response.put("id", row.getInteger(0));
    31.         response.put("rawContent", row.getString(1));
    32.       }
    33.       message.reply(response);
    34.       reportQueryError(message, fetch.cause());
    35.     }
    36.   });
    37. }
    39. private void createPage(Message<JsonObject> message) {
    40.   JsonObject request = message.body();
    41.   JsonArray data = new JsonArray()
    42.     .add(request.getString("title"))
    43.     .add(request.getString("markdown"));
    45.   dbClient.updateWithParams(sqlQueries.get(SqlQuery.CREATE_PAGE), data, res -> {
    46.     if (res.succeeded()) {
    47.       message.reply("ok");
    48.     } else {
    49.       reportQueryError(message, res.cause());
    50.     }
    51.   });
    52. }
    54. private void savePage(Message<JsonObject> message) {
    55.   JsonObject request = message.body();
    56.   JsonArray data = new JsonArray()
    57.     .add(request.getString("markdown"))
    58.     .add(request.getString("id"));
    60.   dbClient.updateWithParams(sqlQueries.get(SqlQuery.SAVE_PAGE), data, res -> {
    61.     if (res.succeeded()) {
    62.       message.reply("ok");
    63.     } else {
    64.       reportQueryError(message, res.cause());
    65.     }
    66.   });
    67. }
    69. private void deletePage(Message<JsonObject> message) {
    70.   JsonArray data = new JsonArray().add(message.body().getString("id"));
    72.   dbClient.updateWithParams(sqlQueries.get(SqlQuery.DELETE_PAGE), data, res -> {
    73.     if (res.succeeded()) {
    74.       message.reply("ok");
    75.     } else {
    76.       reportQueryError(message, res.cause());
    77.     }
    78.   });
    79. }
    81. private void reportQueryError(Message<JsonObject> message, Throwable cause) {
    82.   LOGGER.error("Database query error", cause);
    84. }