io.vertx.ext.sql.ResultSet Java Examples

@Override
public Single<Optional<CartEvent>> retrieveOne(Long id) {
  return client.rxGetConnection()
    .flatMap(conn ->
      conn.rxQueryWithParams(RETRIEVE_STATEMENT, new JsonArray().add(id))
        .map(ResultSet::getRows)
        .map(list -> {
          if (list.isEmpty()) {
            return Optional.<CartEvent>empty();
          } else {
            return Optional.of(list.get(0))
              .map(this::wrapCartEvent);
          }
        })
        .doAfterTerminate(conn::close)
    );
}
 

@Before
public void before() {

  columnNames = Arrays.asList("foo", "bar", "wibble");
  results = new ArrayList<>();
  int numRows = 10;
  for (int i = 0; i < numRows; i++) {
    JsonArray result = new JsonArray();
    for (int j = 0; j < columnNames.size(); j++) {
      result.add("res" + j);
    }
    results.add(result);
  }

  rs = new ResultSet(columnNames, results, null);
}
 

public static StoredResponseDataResult map(ResultSet resultSet, Set<String> responseIds) {
    final Map<String, String> storedIdToResponse = new HashMap<>(responseIds.size());
    final List<String> errors = new ArrayList<>();

    if (resultSet == null || CollectionUtils.isEmpty(resultSet.getResults())) {
        handleEmptyResultError(responseIds, errors);
    } else {
        try {
            for (JsonArray result : resultSet.getResults()) {
                storedIdToResponse.put(result.getString(0), result.getString(1));
            }
        } catch (IndexOutOfBoundsException e) {
            errors.add("Result set column number is less than expected");
            return StoredResponseDataResult.of(Collections.emptyMap(), errors);
        }
        errors.addAll(responseIds.stream().filter(id -> !storedIdToResponse.containsKey(id))
                .map(id -> String.format("No stored response found for id: %s", id))
                .collect(Collectors.toList()));
    }

    return StoredResponseDataResult.of(storedIdToResponse, errors);
}
 

public void example4(JDBCClient client) {

    // Now do stuff with it:

    client.getConnection(res -> {
      if (res.succeeded()) {

        SQLConnection connection = res.result();

        connection.query("SELECT * FROM some_table", res2 -> {
          if (res2.succeeded()) {

            ResultSet rs = res2.result();
            // Do something with results
          }
        });
      } else {
        // Failed to get connection - deal with it
      }
    });

  }
 

@Test
public void executeQueryShouldReturnFailedFutureIfQueryFails() {
    // given
    given(vertx.setTimer(anyLong(), any())).willReturn(123L);

    final SQLConnection connection = mock(SQLConnection.class);
    givenGetConnectionReturning(Future.succeededFuture(connection));

    givenQueryReturning(connection, Future.failedFuture(new RuntimeException("Failed to execute query")));

    // when
    final Future<ResultSet> future = jdbcClient.executeQuery("query", emptyList(), identity(), timeout);

    // then
    verify(vertx).cancelTimer(eq(123L));

    assertThat(future.failed()).isTrue();
    assertThat(future.cause()).isInstanceOf(RuntimeException.class).hasMessage("Failed to execute query");
}
 

@Test
public void executeQueryShouldReturnFailedFutureIfConnectionAcquisitionFails() {
    // given
    given(vertx.setTimer(anyLong(), any())).willReturn(123L);

    givenGetConnectionReturning(Future.failedFuture(new RuntimeException("Failed to acquire connection")));

    // when
    final Future<ResultSet> future = jdbcClient.executeQuery("query", emptyList(), identity(), timeout);

    // then
    verify(vertx).cancelTimer(eq(123L));

    assertThat(future.failed()).isTrue();
    assertThat(future.cause()).isInstanceOf(RuntimeException.class).hasMessage("Failed to acquire connection");
}
 

@Override
public <T> Future<T> executeQuery(String query, List<Object> params, Function<ResultSet, T> mapper,
                                  Timeout timeout) {
    final long remainingTimeout = timeout.remaining();
    if (remainingTimeout <= 0) {
        return Future.failedFuture(timeoutException());
    }
    final long startTime = clock.millis();
    final Promise<ResultSet> queryResultPromise = Promise.promise();

    // timeout implementation is inspired by this answer:
    // https://groups.google.com/d/msg/vertx/eSf3AQagGGU/K7pztnjLc_EJ
    final long timerId = vertx.setTimer(remainingTimeout, id -> timedOutResult(queryResultPromise, startTime));

    final Promise<SQLConnection> connectionPromise = Promise.promise();
    jdbcClient.getConnection(connectionPromise);
    connectionPromise.future()
            .recover(BasicJdbcClient::logConnectionError)
            .compose(connection -> makeQuery(connection, query, params))
            .setHandler(result -> handleResult(result, queryResultPromise, timerId, startTime));

    return queryResultPromise.future().map(mapper);
}
 

@Test
public void executeQueryShouldReturnSucceededFutureWithMappedQueryResult() {
    // given
    given(vertx.setTimer(anyLong(), any())).willReturn(123L);

    final SQLConnection connection = mock(SQLConnection.class);
    givenGetConnectionReturning(Future.succeededFuture(connection));

    givenQueryReturning(connection, Future.succeededFuture(
            new ResultSet().setResults(singletonList(new JsonArray().add("value")))));

    // when
    final Future<String> future = jdbcClient.executeQuery("query", emptyList(),
            resultSet -> resultSet.getResults().get(0).getString(0), timeout);

    // then
    verify(vertx).cancelTimer(eq(123L));

    assertThat(future.succeeded()).isTrue();
    assertThat(future.result()).isEqualTo("value");
}
 

public void example10(SQLConnection connection) {
  // Assume that there is a SQL procedure like this:
  //
  // create procedure customer_lastname(IN firstname varchar(50), OUT lastname varchar(50))
  //   modifies sql data
  //   select lastname into lastname from customers where firstname = firstname;

  String func = "{ call customer_lastname(?, ?) }";

  connection.callWithParams(func, new JsonArray().add("John"), new JsonArray().addNull().add("VARCHAR"), res -> {

    if (res.succeeded()) {
      ResultSet result = res.result();
    } else {
      // Failed!
    }
  });
}
 

public void example8(SQLConnection connection) {
  // Assume that there is a SQL function like this:
  //
  // create function one_hour_ago() returns timestamp
  //    return now() - 1 hour;

  // note that you do not need to declare the output for functions
  String func = "{ call one_hour_ago() }";

  connection.call(func, res -> {

    if (res.succeeded()) {
      ResultSet result = res.result();
    } else {
      // Failed!
    }
  });
}
 

public void example3_1(SQLConnection connection) {

    String query = "SELECT ID, FNAME, LNAME, SHOE_SIZE from PEOPLE WHERE LNAME=? AND SHOE_SIZE > ?";
    JsonArray params = new JsonArray().add("Fox").add(9);

    connection.queryWithParams(query, params, res -> {

      if (res.succeeded()) {
        // Get the result set
        ResultSet resultSet = res.result();
      } else {
        // Failed!
      }
    });

  }
 

static void query(String sql, JsonArray params, Handler<ResultSet> resultHandler) {
    log.info(sql);
    getConnection(connection -> {
        connection.queryWithParams(sql, params, res -> {
            try {
                if (res.succeeded()) {
                    ResultSet rs = res.result();
                    resultHandler.handle(rs);
                } else {
                    fail(res.cause());
                }
            } finally {
                connection.close();
            }
        });
    });
}
 

@GET
public Single<Template> index(){
	return fiber((con) -> {
		ResultSet res = await(con.rxQuery(SQL.SQL_ALL_PAGES));
		List<String> pages = res.getResults()
				.stream()
				.map(json -> json.getString(0))
				.sorted()
				.collect(Collectors.toList());
		boolean canCreatePage = await(user.rxIsAuthorised("create"));
		return new Template("templates/index.ftl")
				.set("title", "Wiki home")
				.set("pages", pages)
				.set("canCreatePage", canCreatePage)
				.set("username", getUserName())
				.set("backup_gist_url", flash.get("backup_gist_url"));
	});
}
 

static void query(String sql, Handler<ResultSet> resultHandler) {
    log.info(sql);
    getConnection(connection -> {
        connection.query(sql, res -> {
            try {
                if (res.succeeded()) {
                    ResultSet rs = res.result();
                    resultHandler.handle(rs);
                } else {
                    fail(res.cause());
                }
            } finally {
                connection.close();
            }
        });
    });
}
 

@Override
public <Q extends Record> Future<JsonObject> findOneJson(Function<DSLContext, ? extends ResultQuery<Q>> queryFunction) {
    return getConnection().compose(safeExecute(sqlConnection -> {
        Query query = createQuery(queryFunction);
        log(query);
        Promise<JsonObject> promise = Promise.<JsonObject>promise();
        sqlConnection.queryWithParams(
                query.getSQL(),
                getBindValues(query),
                this.<ResultSet,JsonObject>executeAndClose(rs -> {
                            List<JsonObject> rows = rs.getRows();
                            switch (rows.size()) {
                                case 0: return null;
                                case 1: return rows.get(0);
                                default: throw new TooManyRowsException(String.format("Found more than one row: %d", rows.size()));
                            }
                        },
                        sqlConnection,
                        promise)
        );
        return promise.future();
    }));
}
 

@Override
public <Q extends Record> CompletableFuture<List<JsonObject>> findManyJson(Function<DSLContext, ? extends ResultQuery<Q>> queryFunction) {
    return getConnection().thenCompose(safeExecute(sqlConnection -> {
        Query query = createQuery(queryFunction);
        log(query);
        CompletableFuture<List<JsonObject>> cf = new VertxCompletableFuture<>(vertx);
        sqlConnection.queryWithParams(
                query.getSQL(),
                getBindValues(query),
                executeAndClose(ResultSet::getRows,
                        sqlConnection,
                        cf)
        );
        return cf;
    }));
}
 

@Test
public void testStockTradesPersisted(TestContext context) throws ClassNotFoundException {
    Async async = context.async();
    JsonObject jdbcConfig = new JsonObject(config.getObject("jdbc").render(ConfigRenderOptions.concise()));
    JDBCClient jdbc = JDBCClient.createNonShared(vertx, jdbcConfig);
    Class.forName(jdbcConfig.getString("driverclass"));

    jdbc.getConnection(ar -> {
        SQLConnection connection = ar.result();
        if (ar.failed()) {
            context.fail(ar.cause());
        } else {
            connection.query(SELECT_STATEMENT, result -> {
                ResultSet set = result.result();
                List<JsonObject> operations = set.getRows().stream()
                        .map(json -> new JsonObject(json.getString("OPERATION")))
                        .collect(Collectors.toList());
                context.assertTrue(operations.size() >= 3);
                connection.close();
                async.complete();
            });
        }
    });
}
 

@Override
public <Q extends Record> Future<List<JsonObject>> findManyJson(Function<DSLContext, ? extends ResultQuery<Q>> queryFunction) {
    return getConnection().compose(safeExecute(sqlConnection -> {
        Query query = createQuery(queryFunction);
        log(query);
        Promise<List<JsonObject>> promise = Promise.<List<JsonObject>>promise();
        sqlConnection.queryWithParams(
                query.getSQL(),
                getBindValues(query),
                this.<ResultSet,List<JsonObject>>executeAndClose(ResultSet::getRows, sqlConnection, promise)
        );
        return promise.future();
    }));
}
 

public void example2(SQLConnection connection) {
  connection.query("SELECT ID, FNAME, LNAME, SHOE_SIZE from PEOPLE", res -> {
    if (res.succeeded()) {
      // Get the result set
      ResultSet resultSet = res.result();
    } else {
      // Failed!
    }
  });
}
 

@Override
public <Q extends Record> Single<List<JsonObject>> findManyJson(Function<DSLContext, ? extends ResultQuery<Q>> queryFunction) {
    return getConnection().flatMap(
            safeExecute(
                    executeAndClose(
                            sqlConnection -> {
                                Query query = createQuery(queryFunction);
                                log(query);
                                return sqlConnection.rxQueryWithParams(query.getSQL(), getBindValues(query));
                            })))
            .map(ResultSet::getRows);
}
 

public void example16(SQLClient client) {
  client.query("SELECT * FROM USERS", ar -> {
    if (ar.succeeded()) {
      if (ar.succeeded()) {
        ResultSet result = ar.result();
      } else {
        // Failed!
      }
      // NOTE that you don't need to worry about
      // the connection management (e.g.: close)
    }
  });
}
 

public void example13(ResultSet rs) {
  while (rs != null) {
    // do something with the result set...

    // next step
    rs = rs.getNext();
  }
}
 

@Test
public void testJson() {

  JsonObject json = rs.toJson();
  ResultSet rs2 = new ResultSet(json);
  assertEquals(rs, rs2);

}
 

public void inTransactionSingle(SQLClient sqlClient) {
  SQLClientHelper.inTransactionSingle(sqlClient, conn -> {
    return conn.rxExecute("... insert into album ...")
      .andThen(conn.rxExecute("... insert into tracks ..."))
      .andThen(conn.rxQuery("... select from album, tracks ...").map(ResultSet::getResults)); // <1>
  }).subscribe(rows -> {
    // send to client
  }, throwable -> {
    // handle error
  });
}
 

void executeQuery(String query, JsonArray params, Handler<AsyncResult<ResultSet>> resultHandler) {
  client.getConnection(res -> {
    if (res.succeeded()) {
      SQLConnection connection = res.result();
      connection.queryWithParams(query, params, queryResponse -> {
        resultHandler.handle(queryResponse);
        // close the connection right away
        connection.close();
      });
    } else {
      resultHandler.handle(Future.failedFuture(res.cause()));
    }
  });
}
 

protected Future<ResultSet> read(final SQLOperations operations, final DeviceKey key, final Optional<String> resourceVersion, final Statement statement, final Span span) {

        var expanded = statement.expand(params -> {
            params.put("tenant_id", key.getTenantId());
            params.put("device_id", key.getDeviceId());
            resourceVersion.ifPresent(version -> params.put("expected_version", version));
        });

        log.debug("read - statement: {}", expanded);

        return expanded.trace(this.tracer, span).query(this.client);
    }
 

@Override
protected Future<ResultSet> read(final SQLOperations operations, final DeviceKey key, final Optional<String> resourceVersion, final Statement statement, final Span span) {

    var expanded = statement.expand(params -> {
        params.put("tenant_id", key.getTenantId());
        params.put("device_id", key.getDeviceId());
        resourceVersion.ifPresent(version -> params.put("expected_version", version));
    });

    log.debug("read - statement: {}", expanded);

    return expanded.trace(this.tracer, span).query(this.client);
}
 

private static Future<String> extractVersionForUpdate(final ResultSet device) {
    final Optional<String> version = device.getRows(true).stream().map(o -> o.getString("version")).findAny();

    if (version.isEmpty()) {
        log.debug("No version or no row found -> entity not found");
        return Future.failedFuture(new EntityNotFoundException());
    }

    return Future.succeededFuture(version.get());
}
 

@Override
public Observable<CartEvent> streamByUser(String userId) {
  JsonArray params = new JsonArray().add(userId).add(userId);
  return client.rxGetConnection()
    .flatMapObservable(conn ->
      conn.rxQueryWithParams(STREAM_STATEMENT, params)
        .map(ResultSet::getRows)
        .flatMapObservable(Observable::from)
        .map(this::wrapCartEvent)
        .doOnTerminate(conn::close)
    );
}
 

/**
 * Execute this statement as a query.
 * @param connection The connection to work on.
 * @return A future tracking the query result.
 */
public Future<ResultSet> query(final SQLOperations connection) {
    final Span sqlSpan = startSqlSpan();
    return SQL.finishSpan(run(connection::queryWithParams), sqlSpan, (r, log) -> {
        log.put("rows", r.getNumRows());
    });
}