io.vertx.ext.sql.UpdateResult Java Examples

private void storeInDatabase(JsonObject operation) {
    // 1. need to retrieve a connection
    // 2. execute the insertion statement
    // 3. close the connection


    // Step 1 get the connection
    Single<SQLConnection> connectionRetrieved = jdbc.rxGetConnection();

    // Step 2, when the connection is retrieved (this may have failed), do the insertion (upon success)
    Single<UpdateResult> update = connectionRetrieved
        .flatMap(connection ->
            connection.rxUpdateWithParams(INSERT_STATEMENT, new JsonArray().add(operation.encode()))

                // Step 3, when the insertion is done, close the connection.
                .doAfterTerminate(connection::close));

    update.subscribe(result -> {
        // Ok
    }, err -> {
        System.err.println("Failed to insert operation in database: " + err);
    });
}
 

private void storeInDatabase(JsonObject operation) {
  // Storing in the database is also a multi step process,
  // 1. need to retrieve a connection
  // 2. execute the insertion statement
  // 3. close the connection

  // Step 1 get the connection
  Single<SQLConnection> connectionRetrieved = jdbc.rxGetConnection();

  // Step 2, when the connection is retrieved (this may have failed), do the insertion (upon success)
  Single<UpdateResult> update = connectionRetrieved.flatMap(connection -> connection
      .rxUpdateWithParams(INSERT_STATEMENT, new JsonArray().add(operation.encode()))

      // Step 3, when the insertion is done, close the connection.
      .doAfterTerminate(connection::close));

  update.subscribe(result -> {
    // Ok
  }, err -> {
    System.err.println("Failed to insert operation in database: " + err);
  });
}
 

private void storeInDatabase(JsonObject operation) {
  // Storing in the database is also a multi step process,
  // 1. need to retrieve a connection
  // 2. execute the insertion statement
  // 3. close the connection

  // Step 1 get the connection
  Single<SQLConnection> connectionRetrieved = jdbc.rxGetConnection();

  // Step 2, when the connection is retrieved (this may have failed), do the insertion (upon success)
  Single<UpdateResult> update = connectionRetrieved.flatMap(connection -> connection
      .rxUpdateWithParams(INSERT_STATEMENT, new JsonArray().add(operation.encode()))

      // Step 3, when the insertion is done, close the connection.
      .doAfterTerminate(connection::close));

  update.subscribe(result -> {
    // Ok
  }, err -> {
    System.err.println("Failed to insert operation in database: " + err);
  });
}
 

protected Future<UpdateResult> updateJsonField(final DeviceKey key, final Statement statement, final String jsonValue, final Optional<String> resourceVersion,
        final Span span) {

    var expanded = statement.expand(map -> {
        map.put("tenant_id", key.getTenantId());
        map.put("device_id", key.getDeviceId());
        map.put("next_version", UUID.randomUUID().toString());
        map.put("data", jsonValue);
        resourceVersion.ifPresent(version -> map.put("expected_version", version));
    });

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

    // execute update
    var result = expanded.trace(this.tracer, span).update(this.client);

    // process result, check optimistic lock
    return checkOptimisticLock(
            result, span,
            resourceVersion,
            checkSpan -> readDevice(this.client, key, checkSpan));
}
 

public Future<UpdateResult> createDevice(final DeviceKey key, final Device device, final SpanContext spanContext) {

        final String json = Json.encode(device);

        final Span span = TracingHelper.buildChildSpan(this.tracer, spanContext, "create device", getClass().getSimpleName())
                .withTag("tenant_instance_id", key.getTenantId())
                .withTag("device_id", key.getDeviceId())
                .withTag("data", json)
                .start();

        var expanded = this.createStatement.expand(params -> {
            params.put("tenant_id", key.getTenantId());
            params.put("device_id", key.getDeviceId());
            params.put("version", UUID.randomUUID().toString());
            params.put("data", json);
        });

        log.debug("createDevice - statement: {}", expanded);
        var f = expanded.trace(this.tracer, span).update(this.client)
                .recover(SQL::translateException);

        return MoreFutures
                .whenComplete(f, span::finish);
    }
 

public Future<UpdateResult> dropTenant(final String tenantId, final SpanContext spanContext) {

        final Span span = TracingHelper.buildChildSpan(this.tracer, spanContext, "drop tenant", getClass().getSimpleName())
                .withTag("tenant_instance_id", tenantId)
                .start();

        var expanded = this.dropTenantStatement.expand(params -> {
            params.put("tenant_id", tenantId);
        });

        log.debug("dropTenant - statement: {}", expanded);
        var result = expanded.trace(this.tracer, span).update(this.client);

        return MoreFutures
                .whenComplete(result, span::finish);
    }
 

public Future<UpdateResult> processSetCommandHandlingAdapterInstance(final DeviceConnectionKey key, final String adapterInstanceId, final SpanContext spanContext) {

        final Span span = TracingHelper.buildChildSpan(this.tracer, spanContext, "Set Command Handling Adapter Instance", getClass().getSimpleName())
                .withTag("tenant_instance_id", key.getTenantId())
                .withTag("device_id", key.getDeviceId())
                .withTag("adapter_instance_id", adapterInstanceId)
                .start();

        var expanded = this.updateAdapterInstanceStatement.expand(params -> {
            params.put("tenant_id", key.getTenantId());
            params.put("device_id", key.getDeviceId());
            params.put("adapter_instance_id", adapterInstanceId);
        });

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

        var result = expanded.trace(this.tracer, span).update(this.client);

        return MoreFutures
                .whenComplete(result, span::finish);

    }
 

public Future<UpdateResult> setLastKnownGateway(final DeviceConnectionKey key, final String gatewayId, final SpanContext spanContext) {

        final Span span = TracingHelper.buildChildSpan(this.tracer, spanContext, "update device state", getClass().getSimpleName())
                .withTag("tenant_instance_id", key.getTenantId())
                .withTag("device_id", key.getDeviceId())
                .withTag("gateway_id", gatewayId)
                .start();

        var expanded = this.updateLastKnownGatewayStatement.expand(params -> {
            params.put("tenant_id", key.getTenantId());
            params.put("device_id", key.getDeviceId());
            params.put("gateway_id", gatewayId);
        });

        log.debug("setLastKnownGateway - statement: {}", expanded);
        var result = expanded.trace(this.tracer, span).update(this.client);

        return MoreFutures
                .whenComplete(result, span::finish);

    }
 

/**
 * Delete all devices belonging to the provided tenant.
 *
 * @param tenantId The tenant to clean up.
 * @param spanContext The span to contribute to.
 *
 * @return A future tracking the outcome of the operation.
 */
public Future<UpdateResult> dropTenant(final String tenantId, final SpanContext spanContext) {

    final Span span = TracingHelper.buildChildSpan(this.tracer, spanContext, "drop tenant", getClass().getSimpleName())
            .withTag("tenant_instance_id", tenantId)
            .start();

    final var expanded = this.dropTenantStatement.expand(params -> {
        params.put("tenant_id", tenantId);
    });

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

    return expanded
            .trace(this.tracer, span)
            .update(this.client)
            .onComplete(x -> span.finish());

}
 

@Override
public Future<Integer> execute(Function<DSLContext, ? extends Query> queryFunction) {
    return getConnection().compose(safeExecute(sqlConnection -> {
            Query query = createQuery(queryFunction);
            log(query);
            Promise<Integer> promise = Promise.<Integer>promise();
            sqlConnection.updateWithParams(
                    query.getSQL(),
                    getBindValues(query),
                    this.<UpdateResult,Integer>executeAndClose(UpdateResult::getUpdated,
                            sqlConnection,
                            promise)
            );
            return promise.future();
    }));
}
 

@Override
public Future<T> insertReturning(Function<DSLContext, ? extends InsertResultStep<R>> queryFunction, Function<Object, T> keyMapper) {
    return getConnection().compose(safeExecute(sqlConnection->{
        Query query = createQuery(queryFunction);
        log(query);
        Promise<Object> promise = Promise.promise();
        if(isMysql){
            sqlConnection.updateWithParams(
                    query.getSQL(),
                    getBindValues(query),
                    this.executeAndClose(UpdateResult::getKeys,
                            sqlConnection,
                            promise)
            );
        }else{
            sqlConnection.queryWithParams(
                    query.getSQL(),
                    getBindValues(query),
                    this.executeAndClose(res -> res.getResults().get(0),
                            sqlConnection,
                            promise)
            );
        }
        return promise.future().map(keyMapper);
    }));
}
 

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

/**
 * Drop all entries for a tenant.
 *
 * @param tenantId The tenant to drop.
 * @param spanContext The span to contribute to.
 *
 * @return The future, tracking the outcome of the operation.
 */
public Future<UpdateResult> dropTenant(final String tenantId, final SpanContext spanContext) {

    final Span span = TracingHelper.buildChildSpan(this.tracer, spanContext, "drop tenant", getClass().getSimpleName())
            .withTag("tenant_instance_id", tenantId)
            .start();

    final var expanded = this.dropTenantStatement.expand(params -> {
        params.put("tenant_id", tenantId);
    });

    log.debug("dropTenant - statement: {}", expanded);
    final var result = expanded.trace(this.tracer, span).update(this.client);

    return result.onComplete(x -> span.finish());

}
 

/**
 * Set the last known gateway information of the device state.
 * <p>
 * This will execute the {@code update} statement to update the entry.
 *
 * @param key The key to the device entry.
 * @param spanContext The span to contribute to.
 * @param gatewayId The value to set.
 *
 * @return The future, tracking the outcome of the operation.
 */
public Future<UpdateResult> setLastKnownGateway(final DeviceConnectionKey key, final String gatewayId, final SpanContext spanContext) {

    final Span span = TracingHelper.buildChildSpan(this.tracer, spanContext, "update device state", getClass().getSimpleName())
            .withTag("tenant_instance_id", key.getTenantId())
            .withTag("device_id", key.getDeviceId())
            .withTag("gateway_id", gatewayId)
            .start();

    final var expanded = this.updateStatement.expand(params -> {
        params.put("tenant_id", key.getTenantId());
        params.put("device_id", key.getDeviceId());
        params.put("gateway_id", gatewayId);
    });

    log.debug("setLastKnownGateway - statement: {}", expanded);
    final var result = expanded.trace(this.tracer, span).update(this.client);

    return result.onComplete(x -> span.finish());

}
 

private Future<Void> update(SQLConnection connection, String id, Article article) {
    Future<Void> future = Future.future();
    String sql = "UPDATE articles SET title = ?, url = ? WHERE id = ?";
    connection.updateWithParams(sql, new JsonArray().add(article.getTitle()).add(article.getUrl())
            .add(Integer.valueOf(id)),
        ar -> {
            connection.close();
            if (ar.failed()) {
                future.fail(ar.cause());
            } else {
                UpdateResult ur = ar.result();
                if (ur.getUpdated() == 0) {
                    future.fail(new NoSuchElementException("No article with id " + id));
                } else {
                    future.complete();
                }
            }
        });
    return future;
}
 

private void insert(RoutingContext ctx, String sql, JsonArray attributes) {
    jdbcClient.getConnection(res -> {
        if (res.succeeded()) {
            try (final SQLConnection connection = res.result()) {
                connection.updateWithParams(sql, attributes, insert -> {
                    final UpdateResult ur = insert.result();
                    if (ur != null) {
                        ctx.response()
                            .setStatusCode(201)
                            .end(Integer.toString(ur.getKeys().getInteger(0)));
                    } else {
                        ctx.response()
                            .setStatusCode(500)
                            .end(String.format("Connection to database couldn't be established: %s", res.cause()));
                    }
                });
            }
        } else {
            ctx.response()
                .setStatusCode(500)
                .end(String.format("Connection to database couldn't be established: %s", res.cause()));
        }
    });
}
 

private void storeInDatabase(JsonObject operation) {
    // 1. need to retrieve a connection
    // 2. execute the insertion statement
    // 3. close the connection


    // Step 1 get the connection
    Single<SQLConnection> connectionRetrieved = jdbc.rxGetConnection();

    // Step 2, when the connection is retrieved (this may have failed), do the insertion (upon success)
    Single<UpdateResult> update = connectionRetrieved
        .flatMap(connection ->
            connection.rxUpdateWithParams(INSERT_STATEMENT, new JsonArray().add(operation.encode()))

                // Step 3, when the insertion is done, close the connection.
                .doAfterTerminate(connection::close));

    update.subscribe(result -> {
        // Ok
    }, err -> {
        System.err.println("Failed to insert operation in database: " + err);
    });
}
 

private void assertUpdate(UpdateResult result, int updated, boolean generatedKeys) {
  assertNotNull(result);
  assertEquals(updated, result.getUpdated());
  if (generatedKeys) {
    JsonArray keys = result.getKeys();
    assertNotNull(keys);
    assertEquals(updated, keys.size());
    Set<Integer> numbers = new HashSet<>();
    for (int i = 0; i < updated; i++) {
      assertTrue(keys.getValue(i) instanceof Integer);
      assertTrue(numbers.add(i));
    }
  }
}
 

@Override
public Single<Integer> execute(Function<DSLContext, ? extends Query> queryFunction) {
    return getConnection()
            .flatMap(
                    safeExecute(
                            executeAndClose(sqlConnection -> {
                                        Query query = createQuery(queryFunction);
                                        log(query);
                                        return sqlConnection
                                                .rxUpdateWithParams(query.getSQL(), getBindValues(query))
                                                ;
                                    }
                            ))).map(UpdateResult::getUpdated);
}
 

@Override
@SuppressWarnings("unchecked")
public CompletableFuture<T> insertReturning(Function<DSLContext, ? extends InsertResultStep<R>> queryFunction, Function<Object, T> keyMapper) {
    return getConnection().thenCompose(safeExecute(sqlConnection -> {
        Query query = createQuery(queryFunction);
        log(query);
        CompletableFuture<Object> cf = new VertxCompletableFuture<>(vertx);
        if(isMysql){
            sqlConnection.updateWithParams(
                    query.getSQL(),
                    getBindValues(query),
                    this.<UpdateResult, Object>executeAndClose(UpdateResult::getKeys,
                            sqlConnection,
                            cf)
            );
        }else{
            sqlConnection.queryWithParams(
                    query.getSQL(),
                    getBindValues(query),
                    this.<ResultSet, Object>executeAndClose(res -> res.getResults().get(0),
                            sqlConnection,
                            cf)
            );
        }
        return cf.thenApply(keyMapper);
    }));
}
 

protected void assertUpdate(UpdateResult result, int updated, boolean generatedKeys) {
  assertNotNull(result);
  assertEquals(updated, result.getUpdated());
  if (generatedKeys) {
    JsonArray keys = result.getKeys();
    assertNotNull(keys);
    assertEquals(updated, keys.size());
    Set<Integer> numbers = new HashSet<>();
    for (int i = 0; i < updated; i++) {
      assertTrue(keys.getValue(i) instanceof Integer);
      assertTrue(numbers.add(i));
    }
  }
}
 

@Override
public CompletableFuture<Integer> execute(Function<DSLContext, ? extends Query> queryFunction) {
    return getConnection().thenCompose(safeExecute(sqlConnection -> {
        Query query = createQuery(queryFunction);
        log(query);
        CompletableFuture<Integer> cf = new VertxCompletableFuture<>(vertx);
        JsonArray bindValues = getBindValues(query);
        sqlConnection.updateWithParams(query.getSQL(), bindValues, executeAndClose(UpdateResult::getUpdated,sqlConnection,cf));
        return cf;
    }));
}
 

@Test
public void testJson() {

  JsonObject json = ur.toJson();
  UpdateResult ur2 = new UpdateResult(json);
  assertEquals(ur, ur2);

}
 

protected void logResult(final String operation, final UpdateResult result, final Throwable error) {
    if (error == null) {
        log.info("{}: Cleaned up, deleted records: {}", operation, result.getUpdated());
    } else {
        log.warn("{}: Failed to clean up", operation, error);
    }
}
 

private void storeInDatabase(JsonObject operation) {
    // Storing in the database is also a multi step process,
    // 1. need to retrieve a connection
    // 2. execute the insertion statement
    // 3. close the connection
    Future<SQLConnection> connectionRetrieved = Future.future();
    Future<UpdateResult> insertionDone = Future.future();

    // Step 1 get the connection
    jdbc.getConnection(connectionRetrieved.completer());

    // Step 2, when the connection is retrieved (this may have failed), do the insertion (upon success)
    connectionRetrieved.setHandler(
            ar -> {
                if (ar.failed()) {
                    System.err.println("Failed to connect to database: " + ar.cause());
                } else {
                    SQLConnection connection = ar.result();
                    connection.updateWithParams(INSERT_STATEMENT,
                            new JsonArray().add(operation.encode()),
                            insertionDone.completer());
                }
            }
    );

    // Step 3, when the insertion is done, close the connection.
    insertionDone.setHandler(
            ar -> {
                if (ar.failed()) {
                    System.err.println("Failed to insert operation in database: " + ar.cause());
                } else {
                    connectionRetrieved.result().close();
                }
            }
    );
}
 

public Future<UpdateResult> deleteDevice(final DeviceKey key, final Optional<String> resourceVersion, final SpanContext spanContext) {

        final Span span = TracingHelper.buildChildSpan(this.tracer, spanContext, "delete device", getClass().getSimpleName())
                .withTag("tenant_instance_id", key.getTenantId())
                .withTag("device_id", key.getDeviceId())
                .start();

        resourceVersion.ifPresent(version -> span.setTag("version", version));

        final Statement statement;
        if (resourceVersion.isPresent()) {
            statement = this.deleteVersionedStatement;
        } else {
            statement = this.deleteStatement;
        }

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

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

        var result = expanded.trace(this.tracer, span).update(this.client);

        return MoreFutures
                .whenComplete(result, span::finish);

    }
 

private static Future<Object> checkUpdateOutcome(final UpdateResult updateResult) {
    if (updateResult.getUpdated() < 0) {
        // conflict
        log.debug("Optimistic lock broke");
        return Future.failedFuture(new OptimisticLockingException());
    }

    return Future.succeededFuture();
}
 

private static Future<Object> checkUpdateOutcome(final UpdateResult updateResult) {
    if (updateResult.getUpdated() < 0) {
        // conflict
        log.debug("Optimistic lock broke");
        return Future.failedFuture(new OptimisticLockingException());
    }

    return Future.succeededFuture();
}
 

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

/**
 * Delete a single device.
 * <p>
 * This will execute the {@code delete} or {@code deleteVersioned} SQL statement and provide
 * the named parameters {@code tenant_id}, {@code device_id}, and {@code expected_version} (if set).
 * It will return the plain update result of the operation.
 *
 * @param key The key of the device to delete.
 * @param resourceVersion An optional resource version.
 * @param spanContext The span to contribute to.
 *
 * @return A future, tracking the outcome of the operation.
 */
public Future<UpdateResult> deleteDevice(final DeviceKey key, final Optional<String> resourceVersion, final SpanContext spanContext) {

    final Span span = TracingHelper.buildChildSpan(this.tracer, spanContext, "delete device", getClass().getSimpleName())
            .withTag("tenant_instance_id", key.getTenantId())
            .withTag("device_id", key.getDeviceId())
            .start();

    resourceVersion.ifPresent(version -> span.setTag("version", version));

    final Statement statement;
    if (resourceVersion.isPresent()) {
        statement = this.deleteVersionedStatement;
    } else {
        statement = this.deleteStatement;
    }

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

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

    return expanded
            .trace(this.tracer, span)
            .update(this.client)
            .onComplete(x -> span.finish());

}