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()); } }); }
results.addAll(rs.result().getResults().stream().map(JsonArray::encode).collect(Collectors.toList()));
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 @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 testSelectWithParameters() { String sql = "SELECT ID, FNAME, LNAME FROM select_table WHERE fname = ?"; connection().queryWithParams(sql, new JsonArray().add("john"), onSuccess(resultSet -> { assertNotNull(resultSet); assertEquals(1, resultSet.getResults().size()); assertEquals("ID", resultSet.getColumnNames().get(0)); assertEquals("FNAME", resultSet.getColumnNames().get(1)); assertEquals("LNAME", resultSet.getColumnNames().get(2)); JsonArray result0 = resultSet.getResults().get(0); assertEquals(1, (int) result0.getInteger(0)); assertEquals("john", result0.getString(1)); assertEquals("doe", result0.getString(2)); testComplete(); })); await(); }
public static void toJson(ResultSet obj, java.util.Map<String, Object> json) { if (obj.getColumnNames() != null) { JsonArray array = new JsonArray(); obj.getColumnNames().forEach(item -> array.add(item)); json.put("columnNames", array); } if (obj.getNext() != null) { json.put("next", obj.getNext().toJson()); } json.put("numColumns", obj.getNumColumns()); json.put("numRows", obj.getNumRows()); if (obj.getOutput() != null) { json.put("output", obj.getOutput()); } if (obj.getResults() != null) { JsonArray array = new JsonArray(); obj.getResults().forEach(item -> array.add(item)); json.put("results", array); } if (obj.getRows() != null) { JsonArray array = new JsonArray(); obj.getRows().forEach(item -> array.add(item)); json.put("rows", array); } } }
@Test public void testUsingUUIDsInTables(TestContext context) { Async async = context.async(); final UUID uuid = UUID.randomUUID(); final String name = "xyz"; client.getConnection(ar -> { ensureSuccess(context, ar); conn = ar.result(); setupTableWithUUIDs(conn, ar2 -> { ensureSuccess(context, ar2); conn.queryWithParams("INSERT INTO test_table (some_uuid, name) VALUES (?, ?)", new JsonArray().add(uuid.toString()).add(name), ar3 -> { ensureSuccess(context, ar3); conn.queryWithParams("SELECT some_uuid FROM test_table WHERE name = ?", new JsonArray().add(name), ar4 -> { ensureSuccess(context, ar4); ResultSet resultSet = ar4.result(); context.assertEquals(1, resultSet.getNumRows()); context.assertEquals("some_uuid", resultSet.getColumnNames().get(0)); context.assertEquals(new JsonObject().put("some_uuid", uuid.toString()), resultSet.getRows().get(0)); async.complete(); }); }); }); }); }
@Test public void testSelectWithLabels() { String sql = "SELECT ID as \"IdLabel\", FNAME as \"first_name\", LNAME as \"LAST.NAME\" FROM select_table WHERE fname = ?"; connection().queryWithParams(sql, new JsonArray().add("john"), onSuccess(resultSet -> { assertNotNull(resultSet); assertEquals(1, resultSet.getResults().size()); assertEquals("IdLabel", resultSet.getColumnNames().get(0)); assertEquals("first_name", resultSet.getColumnNames().get(1)); assertEquals("LAST.NAME", resultSet.getColumnNames().get(2)); JsonArray result0 = resultSet.getResults().get(0); assertEquals(1, (int) result0.getInteger(0)); assertEquals("john", result0.getString(1)); assertEquals("doe", result0.getString(2)); JsonObject row0 = resultSet.getRows().get(0); assertEquals(1, (int) row0.getInteger("IdLabel")); assertEquals("john", row0.getString("first_name")); assertEquals("doe", row0.getString("LAST.NAME")); testComplete(); })); await(); }
@Test public void testResultSet() { assertEquals(numRows, rs.getNumRows()); assertEquals(columnNames.size(), rs.getNumColumns()); assertEquals(columnNames.size(), rs.getColumnNames().size()); assertEquals(columnNames, rs.getColumnNames()); assertEquals(results, rs.getResults()); List<JsonObject> rows = rs.getRows(); assertEquals(numRows, rs.getRows().size()); int index = 0; for (JsonObject row: rows) { JsonArray result = results.get(index); assertEquals(columnNames.size(), row.size()); assertEquals(row.size(), result.size()); for (int i = 0; i < columnNames.size(); i++) { String columnName = columnNames.get(i); String columnValue = result.getString(i); assertEquals(columnValue, row.getString(columnName)); } index++; } }
@Override public boolean next() { return ++position < resultSet.getNumRows(); }
@Test public void testStoredProcedure4() { client.callWithParams("{call times2(?)}", new JsonArray().add(2), new JsonArray().add("INTEGER"), onSuccess(resultSet -> { assertNotNull(resultSet); assertEquals(0, resultSet.getResults().size()); assertEquals(new Integer(4), resultSet.getOutput().getInteger(0)); testComplete(); })); await(); }
class App { public <T extends Msg> ResultSet<T> getResult(Class<T> cls) { return new ResultSet(); } }
ref = resultSet; } else { ref.setNext(helper.asList(rs)); ref = ref.getNext(); ref.setOutput(convertOutputs(statement)); if (outResult) { resultSet = new io.vertx.ext.sql.ResultSet(Collections.emptyList(), Collections.emptyList(), null).setOutput(convertOutputs(statement));
list.add((String)item); }); obj.setColumnNames(list); obj.setNext(new io.vertx.ext.sql.ResultSet((JsonObject)member.getValue())); case "output": if (member.getValue() instanceof JsonArray) { obj.setOutput(((JsonArray)member.getValue()).copy()); list.add(((JsonArray)item).copy()); }); obj.setResults(list);
/** * @see io.apiman.gateway.engine.components.jdbc.IJdbcResultSet#getColumnNames() */ @Override public List<String> getColumnNames() { return resultSet.getColumnNames(); }
@Test public void testSimpleConnection(TestContext context) { Async async = context.async(); client.getConnection(ar -> { if (ar.failed()) { context.fail(ar.cause()); return; } conn = ar.result(); conn.query("SELECT 1 AS something", ar2 -> { if (ar2.failed()) { context.fail(ar2.cause()); } else { ResultSet result = ar2.result(); context.assertNotNull(result); JsonObject expected = new JsonObject() .put("columnNames", new JsonArray().add("something")) .put("numColumns", 1) .put("numRows", 1) .put("rows", new JsonArray().add(new JsonObject().put("something", 1))) .put("results", new JsonArray().add(new JsonArray().add(1))); context.assertEquals(expected, result.toJson()); async.complete(); } }); }); }
@Test public void testSelectWithParameters() { String sql = "SELECT ID, FNAME, LNAME FROM select_table WHERE fname = ?"; connection().queryWithParams(sql, new JsonArray().add("john"), onSuccess(resultSet -> { assertNotNull(resultSet); assertEquals(1, resultSet.getResults().size()); assertEquals("ID", resultSet.getColumnNames().get(0)); assertEquals("FNAME", resultSet.getColumnNames().get(1)); assertEquals("LNAME", resultSet.getColumnNames().get(2)); JsonArray result0 = resultSet.getResults().get(0); assertEquals(1, (int) result0.getInteger(0)); assertEquals("john", result0.getString(1)); assertEquals("doe", result0.getString(2)); testComplete(); })); await(); }
@Test public void testUsingUUIDsInTables(TestContext context) { Async async = context.async(); final UUID uuid = UUID.randomUUID(); final String name = "xyz"; client.getConnection(ar -> { ensureSuccess(context, ar); conn = ar.result(); setupTableWithUUIDs(conn, ar2 -> { ensureSuccess(context, ar2); conn.queryWithParams("INSERT INTO test_table (some_uuid, name) VALUES (?, ?)", new JsonArray().add(uuid.toString()).add(name), ar3 -> { ensureSuccess(context, ar3); conn.queryWithParams("SELECT some_uuid FROM test_table WHERE name = ?", new JsonArray().add(name), ar4 -> { ensureSuccess(context, ar4); ResultSet resultSet = ar4.result(); context.assertEquals(1, resultSet.getNumRows()); context.assertEquals("some_uuid", resultSet.getColumnNames().get(0)); context.assertEquals(new JsonObject().put("some_uuid", uuid.toString()), resultSet.getRows().get(0)); async.complete(); }); }); }); }); }
@Test public void testSelectWithLabels() { String sql = "SELECT ID as \"IdLabel\", FNAME as \"first_name\", LNAME as \"LAST.NAME\" FROM select_table WHERE fname = ?"; connection().queryWithParams(sql, new JsonArray().add("john"), onSuccess(resultSet -> { assertNotNull(resultSet); assertEquals(1, resultSet.getResults().size()); assertEquals("IdLabel", resultSet.getColumnNames().get(0)); assertEquals("first_name", resultSet.getColumnNames().get(1)); assertEquals("LAST.NAME", resultSet.getColumnNames().get(2)); JsonArray result0 = resultSet.getResults().get(0); assertEquals(1, (int) result0.getInteger(0)); assertEquals("john", result0.getString(1)); assertEquals("doe", result0.getString(2)); JsonObject row0 = resultSet.getRows().get(0); assertEquals(1, (int) row0.getInteger("IdLabel")); assertEquals("john", row0.getString("first_name")); assertEquals("doe", row0.getString("LAST.NAME")); testComplete(); })); await(); }