Java Code Examples for io.vertx.ext.sql.SQLConnection#updateWithParams()

private Future<Article> insert(SQLConnection connection, Article article, boolean closeConnection) {
    Future<Article> future = Future.future();
    String sql = "INSERT INTO Articles (title, url) VALUES (?, ?)";
    connection.updateWithParams(sql,
        new JsonArray().add(article.getTitle()).add(article.getUrl()),
        ar -> {
            if (closeConnection) {
                connection.close();
            }
            future.handle(
                ar.map(res -> new Article(res.getKeys().getLong(0), article.getTitle(), article.getUrl()))
            );
        }
    );
    return future;
}
 

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 Future<Void> delete(SQLConnection connection, String id) {
    Future<Void> future = Future.future();
    String sql = "DELETE FROM Articles WHERE id = ?";
    connection.updateWithParams(sql,
        new JsonArray().add(Integer.valueOf(id)),
        ar -> {
            connection.close();
            if (ar.failed()) {
                future.fail(ar.cause());
            } else {
                if (ar.result().getUpdated() == 0) {
                    future.fail(new NoSuchElementException("Unknown article " + id));
                } else {
                    future.complete();
                }
            }
        }
    );
    return future;
}
 

public void example9(JDBCAuthentication jdbcAuth, SQLConnection conn) {

    String hash = jdbcAuth.hash(
      "pbkdf2", // hashing algorithm
      VertxContextPRNG.current().nextString(32), // secure random salt
      "sausages" // password
    );
    // save to the database
    conn.updateWithParams(
      "INSERT INTO user (username, password) VALUES (?, ?)",
      new JsonArray().add("tim").add(hash), res -> {
      if (res.succeeded()) {
        // success!
      }
    });
  }
 

/**
 * Test that insert and update works in a table without an identity column.
 */
@Test
public void testInsertUpdateNoIdentity() {
  SQLConnection conn = connection();
  String insertsql = "INSERT INTO insert_tableNoIdentity (id, lname, fname, dob) VALUES (?, ?, ?, ?)";
  JsonArray insertparams = new JsonArray().add(1).add("LastName1").addNull().add("2002-02-02");
  conn.updateWithParams(insertsql, insertparams, onSuccess(insertResultSet -> {
    assertUpdate(insertResultSet, 1);
    int insertid = insertResultSet.getKeys().isEmpty() ? 1 : insertResultSet.getKeys().getInteger(0);
    conn.queryWithParams("SElECT lname FROM insert_tableNoIdentity WHERE id=?", new JsonArray().add(1), onSuccess(insertQueryResultSet -> {
      assertNotNull(insertQueryResultSet);
      assertEquals(1, insertQueryResultSet.getResults().size());
      assertEquals("LastName1", insertQueryResultSet.getResults().get(0).getValue(0));
      System.out.println("testInsertUpdateNoIdentity: insert: " + insertQueryResultSet.getResults().get(0).getValue(0));
      // Now test that update works
      String updSql = "UPDATE insert_tableNoIdentity SET lname=? WHERE id=?";
      JsonArray updParams = new JsonArray().add("LastName2").add(insertid);
      conn.updateWithParams(updSql, updParams, onSuccess(updateResultSet -> {
        assertUpdate(updateResultSet, 1);
        int updateid = updateResultSet.getKeys().isEmpty() ? 1 : updateResultSet.getKeys().getInteger(0);
        conn.queryWithParams("SElECT lname FROM insert_tableNoIdentity WHERE id=?", new JsonArray().add(updateid), onSuccess(updateQueryResultSet -> {
          assertNotNull(updateQueryResultSet);
          assertEquals(1, updateQueryResultSet.getResults().size());
          assertEquals("LastName2", updateQueryResultSet.getResults().get(0).getValue(0));
          System.out.println("testInsertUpdateNoIdentity: update: " + updateQueryResultSet.getResults().get(0).getValue(0));
          testComplete();
        }));
      }));
    }));
  }));

  await();
}
 

public void example5(SQLConnection connection) {

    String update = "UPDATE PEOPLE SET SHOE_SIZE = 10 WHERE LNAME=?";
    JsonArray params = new JsonArray().add("Fox");

    connection.updateWithParams(update, params, res -> {

      if (res.succeeded()) {

        UpdateResult updateResult = res.result();

        System.out.println("No. of rows updated: " + updateResult.getUpdated());

      } else {

        // Failed!

      }
    });

  }