Skip to content

Instantly share code, notes, and snippets.

@pmlopes
Last active December 25, 2019 02:38
Show Gist options
  • Save pmlopes/47f7f02b0b102b5e68d8 to your computer and use it in GitHub Desktop.
Save pmlopes/47f7f02b0b102b5e68d8 to your computer and use it in GitHub Desktop.
Using PostgreSQL JSON with Vert.x3
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);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment