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); } }
@Override public boolean next() { return ++position < resultSet.getNumRows(); }
@Override public boolean hasResults() { return this.resultSet.getNumRows()>0; }
protected int getNumRows() { return resultSet.getNumRows(); }
@Override public boolean hasResults() { return this.resultSet.getNumRows()>0; }
private SqlTableInfo createTableInfo(IMapper mapper, ResultSet resultSet) { if (resultSet.getNumRows() == 0) return null; return new SqlTableInfo(mapper); }
@Override public List<QueryResult> asList() { return IntStream .range(0, resultSet.getNumRows()) .mapToObj(i -> new AsyncQueryResult(resultSet, i)) .collect(Collectors.toList()); }
@Override public List<QueryResult> asList() { return IntStream .range(0, resultSet.getNumRows()) .mapToObj(i -> new AsyncQueryResult(resultSet, i)) .collect(Collectors.toList()); }
/** * Creates a lazy loading instance * * @param resultSet * The {@link ResultSet} of an executed query * @param store * the store which was used to execute the query * @param mapper * the underlaying mapper * @param query * the {@link SqlQueryRambler} */ public SqlQueryResult(ResultSet resultSet, MySqlDataStore store, IMapper mapper, SqlQueryRambler query) { super(store, mapper, resultSet.getNumRows(), query.getQueryExpression()); this.resultSet = resultSet; }
} else { ResultSet rs = result.result(); if (rs.getNumRows() == 0) { String message = String.format("No column definitions found for '%s'", tInfo.getName()); resultHandler.handle(Future.failedFuture(new MappingException(message)));
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); } } }
@Override public void updateModules(String id, SortedMap<String, Boolean> enabled, Handler<ExtendedAsyncResult<Void>> fut) { logger.debug("updateModules " + Json.encode(enabled.keySet())); PostgresQuery q = pg.getQuery(); String sql = "SELECT " + JSON_COLUMN + " FROM " + TABLE + " WHERE " + ID_SELECT; JsonArray jsa = new JsonArray(); jsa.add(id); q.queryWithParams(sql, jsa, res -> { if (res.failed()) { logger.fatal("updateModule failed: " + res.cause().getMessage()); fut.handle(new Failure<>(INTERNAL, res.cause())); } else { ResultSet rs = res.result(); if (rs.getNumRows() == 0) { fut.handle(new Failure<>(NOT_FOUND, messages.getMessage("1100", id))); q.close(); } else { logger.debug("update: replace"); updateModuleR(q, id, enabled, rs.getRows().iterator(), fut); } } }); } }
@Test public void testQuerySchema(TestContext context) { Async async = context.async(); String queryExpression = "SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='test' AND TABLE_NAME='SimpleMapper'; "; SqlUtil.query((MySqlDataStore) getDataStore(context), queryExpression, ur -> { if (ur.failed()) { context.fail(ur.cause().toString()); async.complete(); } else { ResultSet res = ur.result(); LOGGER.info("found records: " + res.getNumRows()); async.complete(); } }); }
@Test public void testQueryIN(TestContext context) { Async async = context.async(); JsonArray array = new JsonArray().add("1").add("2").add("3"); String insertExpression = "SELECT * from MiniMapper where id IN ( ?, ?, ?); "; SqlUtil.queryWithParams((MySqlDataStore) getDataStore(context), insertExpression, array, ur -> { if (ur.failed()) { LOGGER.error("ERror searching", ur.cause()); context.fail(ur.cause()); async.complete(); } else { ResultSet res = ur.result(); LOGGER.info("found records: " + res.getNumRows()); async.complete(); } }); }
@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 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 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 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++; } }
@Test public void testGeoDirect(TestContext context) { final Async async1 = context.async(); String insertExpression = "insert into GeoPointRecord set id=10, point = GeomFromText('POINT(18 -63)')"; SqlUtil.update((MySqlDataStore) getDataStore(context), insertExpression, ur -> { if (ur.failed()) { LOGGER.error("Error deleting", ur.cause()); async1.complete(); } else { UpdateResult res = ur.result(); LOGGER.info("deleted: " + res.getUpdated()); async1.complete(); } }); async1.await(); final Async async2 = context.async(); String queryExpression = "SELECT id, AsText(point) from GeoPointRecord; "; SqlUtil.query((MySqlDataStore) getDataStore(context), queryExpression, ur -> { if (ur.failed()) { LOGGER.error("ERror searching", ur.cause()); context.fail(ur.cause()); async2.complete(); } else { ResultSet res = ur.result(); LOGGER.info("found records: " + res.getNumRows()); async2.complete(); } }); async2.await(); }