connection.execute("create table test(id int primary key, name varchar(255))", create -> { if (create.failed()) { System.err.println("Cannot create the table"); connection.execute("insert into test values (1, 'Hello'), (2, 'World')", insert -> { connection.queryWithParams("select * from test where id = ?", new JsonArray().add(2), rs -> { if (rs.failed()) { System.err.println("Cannot retrieve the data from the database"); connection.close(done -> { if (done.failed()) { throw new RuntimeException(done.cause());
private void handleAddProduct(RoutingContext routingContext) { HttpServerResponse response = routingContext.response(); SQLConnection conn = routingContext.get("conn"); JsonObject product = routingContext.getBodyAsJson(); conn.updateWithParams("INSERT INTO products (name, price, weight) VALUES (?, ?, ?)", new JsonArray().add(product.getString("name")).add(product.getFloat("price")).add(product.getInteger("weight")), query -> { if (query.failed()) { sendError(500, response); } else { response.end(); } }); }
connection.execute("create table test(id int primary key, name varchar(255))", res -> { if (res.failed()) { resultHandler.handle(Future.failedFuture(res.cause())); connection.execute("insert into test values(1, 'Hello')", insert -> { connection.query("select * from test", rs -> { results.addAll(rs.result().getResults().stream().map(JsonArray::encode).collect(Collectors.toList())); connection.close(done -> { if (done.failed()) { resultHandler.handle(Future.failedFuture(done.cause()));
connection.execute("create table test(id int primary key, name varchar(255))", res -> { if (res.failed()) { throw new RuntimeException(res.cause()); connection.execute("insert into test values (1, 'Hello'), (2, 'Goodbye'), (3, 'Cya Later')", insert -> { connection.queryStream("select * from test", stream -> { if (stream.succeeded()) { SQLRowStream sqlRowStream = stream.result(); .endHandler(v -> { connection.close(done -> { if (done.failed()) { throw new RuntimeException(done.cause());
protected <R> void execute(JsonArray params, String sql, R ret, Handler<AsyncResult<R>> resultHandler) { client.getConnection(connHandler(resultHandler, connection -> { connection.updateWithParams(sql, params, r -> { if (r.succeeded()) { resultHandler.handle(Future.succeededFuture(ret)); } else { resultHandler.handle(Future.failedFuture(r.cause())); } connection.close(); }); })); }
setupSimpleTable(conn, ar2 -> { ensureSuccess(context, ar2); conn.setAutoCommit(false, ar3 -> { ensureSuccess(context, ar3); conn.updateWithParams("UPDATE test_table SET name=? WHERE id=?", new JsonArray().add(name).add(id), ar4 -> { ensureSuccess(context, ar4); UpdateResult updateRes = ar4.result(); conn.rollback(ar5 -> { ensureSuccess(context, ar5); conn.query("SELECT name FROM test_table ORDER BY id", ar6 -> { ensureSuccess(context, ar6); ResultSet selectRes = ar6.result();
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()); } } }); } }
@Test public void testWorkerPerConnection() { int numConns = 4; ArrayList<SQLConnection> conns = new ArrayList<>(); for (int i = 0; i < numConns; i++) { conns.add(connection()); } AtomicInteger count = new AtomicInteger(); Context context = vertx.getOrCreateContext(); context.runOnContext(v -> { for (SQLConnection conn : conns) { conn.setAutoCommit(false, onSuccess(ar1 -> { conn.execute("LOCK TABLE insert_table WRITE", onSuccess(ar2 -> { String sql = "INSERT INTO insert_table VALUES (null, 'doe', 'john', '2001-01-01');"; conn.update(sql, onSuccess(res3 -> { conn.commit(onSuccess(committed -> { conn.close(onSuccess(closed -> { if (count.incrementAndGet() == numConns) { testComplete(); } })); })); })); })); })); } }); await(); }
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; }); }
@Test public void testRollingBackWhenNotInTransaction(TestContext context) { int id = 0; String name = "adele"; Async async = context.async(); client.getConnection(ar -> { ensureSuccess(context, ar); conn = ar.result(); setupSimpleTable(conn, ar2 -> { ensureSuccess(context, ar2); conn.setAutoCommit(false, ar3 -> { ensureSuccess(context, ar3); conn.updateWithParams("UPDATE test_table SET name=? WHERE id=?", new JsonArray().add(name).add(id), ar4 -> { ensureSuccess(context, ar4); conn.setAutoCommit(true, ar5 -> { ensureSuccess(context, ar5); conn.rollback(ar6 -> { context.assertTrue(ar6.failed()); async.complete(); }); }); }); }); }); }); }
@Test public void testCommitWhenNotInTransaction(TestContext context) { int id = 0; String name = "adele"; Async async = context.async(); client.getConnection(ar -> { ensureSuccess(context, ar); conn = ar.result(); setupSimpleTable(conn, ar2 -> { ensureSuccess(context, ar2); conn.setAutoCommit(false, ar3 -> { ensureSuccess(context, ar3); conn.updateWithParams("UPDATE test_table SET name=? WHERE id=?", new JsonArray().add(name).add(id), ar4 -> { ensureSuccess(context, ar4); conn.setAutoCommit(true, ar5 -> { ensureSuccess(context, ar5); conn.commit(ar6 -> { context.assertTrue(ar6.failed()); async.complete(); }); }); }); }); }); }); }
@Override @Suspendable 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); // Get a connection try (SQLConnection conn = awaitResult(jdbc::getConnection)) { // Create a table Void v = awaitResult(h -> conn.execute("CREATE TABLE test(col VARCHAR(20))", h)); // Insert some stuff for (int i = 0; i < 10; i++) { int ii = i; UpdateResult res = awaitResult(h -> conn.update("INSERT INTO test (col) VALUES ('val" + ii + "')", h)); System.out.println("Rows updated: " + res.getUpdated()); } // Select the results ResultSet res = awaitResult(h -> conn.query("SELECT * FROM test", h)); System.out.println("Selected " + res.getNumRows() + " results"); res.getResults().forEach(System.out::println); } }
@Test public void testSelectTx() { String sql = "INSERT INTO insert_table VALUES (?, ?, ?, ?);"; JsonArray params = new JsonArray().addNull().add("smith").add("john").add("2003-03-03"); client.getConnection(onSuccess(conn -> { assertNotNull(conn); conn.setAutoCommit(false, onSuccess(v -> { conn .setOptions(new SQLOptions().setAutoGeneratedKeys(true)) .updateWithParams(sql, params, onSuccess((UpdateResult updateResult) -> { assertUpdate(updateResult, 1); int id = updateResult.getKeys().getInteger(0); // Explicit typing of resultset is not really necessary but without it IntelliJ reports // syntax error :( conn.queryWithParams("SELECT LNAME FROM insert_table WHERE id = ?", new JsonArray().add(id), onSuccess((ResultSet resultSet) -> { assertFalse(resultSet.getResults().isEmpty()); assertEquals("smith", resultSet.getResults().get(0).getString(0)); testComplete(); })); })); })); })); await(); }
conn.result().close(done -> { if (done.failed()) { throw new RuntimeException(done.cause());
private void query(SQLConnection conn, String sql, Handler<ResultSet> done) { conn.query(sql, res -> { if (res.failed()) { throw new RuntimeException(res.cause()); } done.handle(res.result()); }); }
private void setupTestTable(SQLConnection conn, Supplier<String> idNameValuesSupplier, Handler<AsyncResult<Void>> handler) { conn.execute("BEGIN", ar -> conn.execute("DROP TABLE IF EXISTS test_table", ar2 -> conn.execute(CREATE_TABLE_STATEMENT, ar3 -> conn.update("INSERT INTO test_table (id, name) VALUES " + idNameValuesSupplier.get(), ar4 -> conn.execute("COMMIT", handler))))); }
protected void removeAll(String sql, Handler<AsyncResult<Void>> resultHandler) { client.getConnection(connHandler(resultHandler, connection -> { connection.update(sql, r -> { if (r.succeeded()) { resultHandler.handle(Future.succeededFuture()); } else { resultHandler.handle(Future.failedFuture(r.cause())); } connection.close(); }); })); }
/** * Executes the given SQL statement which may be an <code>INSERT</code>, <code>UPDATE</code>, or <code>DELETE</code> * statement. * @param sql the SQL to execute. For example <code>INSERT INTO table ...</code> * @param resultHandler the handler which is called once the operation completes. * @return */ public io.vertx.rxjava.ext.sql.SQLConnection update(String sql, Handler<AsyncResult<UpdateResult>> resultHandler) { delegate.update(sql, resultHandler); return this; }