private void handleListProducts(RoutingContext routingContext) { HttpServerResponse response = routingContext.response(); SQLConnection conn = routingContext.get("conn"); conn.query("SELECT id, name, price, weight FROM products", query -> { if (query.failed()) { sendError(500, response); } else { JsonArray arr = new JsonArray(); query.result().getRows().forEach(arr::add); routingContext.response().putHeader("content-type", "application/json").end(arr.encode()); } }); }
@Override public void start() throws Exception { JsonObject config = new JsonObject().put("url", "jdbc:hsqldb:mem:test?shutdown=true") .put("driver_class", "org.hsqldb.jdbcDriver"); JDBCClient jdbc = JDBCClient.createShared(vertx, config); // Connect to the database jdbc.rxGetConnection().flatMap(conn -> { // Now chain some statements using flatmap composition Single<ResultSet> resa = conn.rxUpdate("CREATE TABLE test(col VARCHAR(20))") .flatMap(result -> conn.rxUpdate("INSERT INTO test (col) VALUES ('val1')")) .flatMap(result -> conn.rxUpdate("INSERT INTO test (col) VALUES ('val2')")) .flatMap(result -> conn.rxQuery("SELECT * FROM test")); return resa.doAfterTerminate(conn::close); }).subscribe(resultSet -> { // Subscribe to the final result System.out.println("Results : " + resultSet.getRows()); }, err -> { System.out.println("Database problem"); err.printStackTrace(); }); } }
private void handleGetProduct(RoutingContext routingContext) { String productID = routingContext.request().getParam("productID"); HttpServerResponse response = routingContext.response(); if (productID == null) { sendError(400, response); } else { SQLConnection conn = routingContext.get("conn"); conn.queryWithParams("SELECT id, name, price, weight FROM products where id = ?", new JsonArray().add(Integer.parseInt(productID)), query -> { if (query.failed()) { sendError(500, response); } else { if (query.result().getNumRows() == 0) { sendError(404, response); } else { response.putHeader("content-type", "application/json").end(query.result().getRows().get(0).encode()); } } }); } }
@Override public void start() throws Exception { JsonObject config = new JsonObject().put("url", "jdbc:hsqldb:mem:test?shutdown=true") .put("driver_class", "org.hsqldb.jdbcDriver"); JDBCClient jdbc = JDBCClient.createShared(vertx, config); // Connect to the database jdbc.rxGetConnection().flatMap(conn -> { // Now chain some statements using flatmap composition Single<ResultSet> resa = conn.rxUpdate("CREATE TABLE test(col VARCHAR(20))") .flatMap(result -> conn.rxUpdate("INSERT INTO test (col) VALUES ('val1')")) .flatMap(result -> conn.rxUpdate("INSERT INTO test (col) VALUES ('val2')")) .flatMap(result -> conn.rxQuery("SELECT * FROM test")); return resa.doAfterTerminate(conn::close); }).subscribe(resultSet -> { // Subscribe to the final result System.out.println("Results : " + resultSet.getRows()); }, err -> { System.out.println("Database problem"); err.printStackTrace(); }); } }
).subscribe(resultSet -> { System.out.println("Results : " + resultSet.getRows()); }, Throwable::printStackTrace);
).subscribe(resultSet -> { System.out.println("Results : " + resultSet.getRows()); }, Throwable::printStackTrace);
private List<String> findRecommendation(final ResultSet resultSet) { final List<String> recommendations = new ArrayList<>(); for (JsonObject row : resultSet.getRows()) { recommendations.add(row.getString("name")); } return recommendations; }
private List<String> findRecommendationNew(final ResultSet resultSet) { final List<String> recommendations = new ArrayList<>(); for (JsonObject row : resultSet.getRows()) { recommendations.add(row.getString("movie_name")); } return recommendations; }
@Override public Single<List<Todo>> getAll() { return client.rxQuery(SQL_QUERY_ALL) .map(ar -> ar.getRows().stream() .map(Todo::new) .collect(Collectors.toList()) ); }
private Single<List<Article>> query(SQLConnection connection) { return connection.rxQuery("SELECT * FROM articles") .map(rs -> rs.getRows().stream().map(Article::new).collect(Collectors.toList())) .doFinally(connection::close); }
@Override public boolean getBoolean(final String columnLabel) { return orDefault(resultSet.getRows().get(position).getBoolean(columnLabel), false); }
@Override public double getDouble(final String columnLabel) { return orDefault(resultSet.getRows().get(position).getDouble(columnLabel), 0d); }
protected Future<List<JsonObject>> retrieveMany(JsonArray param, String sql) { return getConnection().compose(connection -> { Future<List<JsonObject>> future = Future.future(); connection.queryWithParams(sql, param, r -> { if (r.succeeded()) { future.complete(r.result().getRows()); } else { future.fail(r.cause()); } connection.close(); }); return future; }); }
protected Future<List<JsonObject>> retrieveAll(String sql) { return getConnection().compose(connection -> { Future<List<JsonObject>> future = Future.future(); connection.query(sql, r -> { if (r.succeeded()) { future.complete(r.result().getRows()); } else { future.fail(r.cause()); } connection.close(); }); return future; }); }
@Override public <P> CompletableFuture<P> fetchOne(Query query, Function<JsonObject, P> mapper){ return getConnection().thenCompose(sqlConnection -> { CompletableFuture<P> cf = new VertxCompletableFuture<P>(vertx); sqlConnection.queryWithParams(query.getSQL(), getBindValues(query), executeAndClose(rs -> { Optional<P> optional = rs.getRows().stream().findFirst().map(mapper); return optional.orElseGet(() -> null); }, sqlConnection, cf)); return cf; }); }
@Override public <P> Single<List<P>> fetch(Query query, java.util.function.Function<JsonObject, P> mapper){ return getConnection().flatMap(executeAndClose(sqlConnection -> sqlConnection.rxQueryWithParams(query.getSQL(), getBindValues(query)).map(rs -> rs.getRows().stream().map(mapper).collect(Collectors.toList()) ))); }
@Override public <P> Single<P> fetchOne(Query query, Function<JsonObject, P> mapper){ return getConnection().flatMap(executeAndClose(sqlConnection -> sqlConnection.rxQueryWithParams(query.getSQL(), getBindValues(query)).map(rs -> { Optional<P> optional = rs.getRows().stream().findFirst().map(mapper); return optional.orElseGet(() -> null); }))); }
@Override public <P> Single<P> fetchOne(Query query, Function<JsonObject, P> mapper){ return getConnection().flatMap(executeAndClose(sqlConnection -> sqlConnection.rxQueryWithParams(query.getSQL(), getBindValues(query)).map(rs -> { Optional<P> optional = rs.getRows().stream().findFirst().map(mapper); return optional.orElseGet(() -> null); }))); }
@Override public <P> Single<List<P>> fetch(Query query, java.util.function.Function<JsonObject, P> mapper){ return getConnection().flatMap(executeAndClose(sqlConnection -> sqlConnection.rxQueryWithParams(query.getSQL(), getBindValues(query)).map(rs -> rs.getRows().stream().map(mapper).collect(Collectors.toList()) ))); }
@Test public void testCaseInsensitiveRows() { JsonObject row = rs.getRows(true).get(0); assertEquals("res0", row.getString("foo")); assertEquals("res0", row.getString("FOO")); assertEquals("res0", row.getString("fOo")); }