pmlopes · December 25, 2019 02:38 · Jul 2, 2015 · Jul 2, 2015 · Jul 2, 2015
diff --git a/PSQLVerticle.java b/PSQLVerticle.java
@@ -74,7 +74,8 @@ public void start() {
       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 -> {
+      conn.queryWithParams("SELECT sale FROM sales WHERE sale->>'id' = ?",
+              new JsonArray().add(saleID), query -> {
         if (query.failed()) {
           ctx.fail(query.cause());
           return;
@@ -86,7 +87,9 @@ public void start() {
 
         // 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));
+        response
+            .putHeader("content-type", "application/json")
+            .end(query.result().getResults().get(0).getString(0));
       });
     });
 
@@ -96,7 +99,8 @@ public void start() {
       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-> {
+      conn.updateWithParams("INSERT INTO sales (sale) VALUES (?::JSON)",
+              new JsonArray().add(ctx.getBodyAsString()), query-> {
         if (query.failed()) {
           ctx.fail(query.cause());
           return;

diff --git a/PSQLVerticle.java b/PSQLVerticle.java
@@ -24,8 +24,12 @@ public void start() {
 
     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());
@@ -35,6 +39,8 @@ public void start() {
         // 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());
@@ -46,13 +52,17 @@ public void start() {
         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();
@@ -63,6 +73,7 @@ public void start() {
 
       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());
@@ -73,14 +84,18 @@ public void start() {
           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());

diff --git a/PSQLVerticle.java b/PSQLVerticle.java
@@ -0,0 +1,96 @@
+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);
+
+    router.route().handler(BodyHandler.create());
+
+    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);
+
+        ctx.addHeadersEndHandler(done -> conn.close(close -> {
+          if (close.failed()) {
+            done.fail(close.cause());
+          } else {
+            done.complete();
+          }
+        }));
+
+        ctx.next();
+      }
+    })).failureHandler(routingContext -> {
+      SQLConnection conn = routingContext.get("conn");
+      if (conn != null) {
+        conn.close(v -> {
+        });
+      }
+    });
+
+    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");
+
+      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();
+        }
+
+        response.putHeader("content-type", "application/json").end(query.result().getResults().get(0).getString(0));
+      });
+    });
+
+    router.post("/sales").handler(ctx -> {
+      HttpServerResponse response = ctx.response();
+      SQLConnection conn = ctx.get("conn");
+
+      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);
+  }
+}