Using PostgreSQL JSON with Vert.x3

PSQLVerticle.java

package io.vertx.blog;

import io.vertx.core.AbstractVerticle;
import io.vertx.core.http.HttpServerResponse;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.ext.jdbc.JDBCClient;
import io.vertx.ext.sql.SQLConnection;
import io.vertx.ext.web.Router;
import io.vertx.ext.web.handler.BodyHandler;

public class PSQLVerticle extends AbstractVerticle {

  @Override
  public void start() {

    // This client expects a running PostgreSQL instance with a table named sales
    // e.g.: CREATE TABLE sales (sale JSON)
    final JDBCClient jdbc = JDBCClient.createNonShared(vertx, new JsonObject()
        .put("url", "jdbc:postgresql://localhost:5432/postgres")
        .put("user", "postgres")
        .put("password", "mysecretpassword")
        .put("driver_class", "org.postgresql.Driver"));

    final Router router = Router.router(vertx);

    // enable the body parser so we can handle JSON input
    router.route().handler(BodyHandler.create());

    // this might look a bit strange but all it does is get a DB connection
    // to PostgreSQL and makes sure that the connection is closed when
    // the request is done or there is an error in between.
    router.route("/sales*").handler(ctx -> jdbc.getConnection(res -> {
      if (res.failed()) {
        ctx.fail(res.cause());
      } else {
        SQLConnection conn = res.result();

        // save the connection on the context
        ctx.put("conn", conn);

        // we register a handler to close the connection at
        // the end of the request
        ctx.addHeadersEndHandler(done -> conn.close(close -> {
          if (close.failed()) {
            done.fail(close.cause());
          } else {
            done.complete();
          }
        }));

        ctx.next();
      }
    })).failureHandler(routingContext -> {
      // if for some reason thare is an error in the route also
      // make sure it closes any one DB connections
      SQLConnection conn = routingContext.get("conn");
      if (conn != null) {
        conn.close(v -> {
        });
      }
    });

    // Here we handle a incoming request to get JSON
    // from PostgreSQL
    router.get("/sales/:saleID").handler(ctx -> {
      String saleID = ctx.request().getParam("saleID");
      HttpServerResponse response = ctx.response();
      if (saleID == null) {
        ctx.fail(400);
        return;
      }

      SQLConnection conn = ctx.get("conn");

      // Note that I am querying the JSON document itself for the property ID
      conn.queryWithParams("SELECT sale FROM sales WHERE sale->>'id' = ?",
              new JsonArray().add(saleID), query -> {
        if (query.failed()) {
          ctx.fail(query.cause());
          return;
        }

        if (query.result().getNumRows() == 0) {
          response.setStatusCode(404).end();
        }

        // here we return the document and note that i don't need to convert from JsonObject to String
        // PostgreSQL returns JSON as strings so less conversions, better performance!
        response
            .putHeader("content-type", "application/json")
            .end(query.result().getResults().get(0).getString(0));
      });
    });

    // Here handle the creation of new sales
    router.post("/sales").handler(ctx -> {
      HttpServerResponse response = ctx.response();
      SQLConnection conn = ctx.get("conn");

      // We need to instruct PostgreSQL that the String (again no conversion needed) is a JSON document
      conn.updateWithParams("INSERT INTO sales (sale) VALUES (?::JSON)",
              new JsonArray().add(ctx.getBodyAsString()), query-> {
        if (query.failed()) {
          ctx.fail(query.cause());
          return;
        }

        response.setStatusCode(201).end();
      });
    });

    vertx.createHttpServer().requestHandler(router::accept).listen(8080);
  }
}