io.vertx.ext.sql.SQLConnection Java Examples

private SQLConnection connection() {
  CountDownLatch latch = new CountDownLatch(1);
  AtomicReference<SQLConnection> ref = new AtomicReference<>();
  client.getConnection(onSuccess(conn -> {
    ref.set(conn);
    latch.countDown();
  }));

  try {
    latch.await();
  } catch (InterruptedException e) {
    throw new RuntimeException(e);
  }

  return ref.get();
}
 

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
      }
    });

  }
 

@Override
public void start(io.vertx.core.Promise<Void> f) throws Exception {
  SQLClient client = JDBCClient.createShared(vertx, theConfig);
  String sql = "SELECT ID, FNAME, LNAME FROM select_table ORDER BY ID";
  client.getConnection(ar1 -> {
    if (ar1.succeeded()) {
      SQLConnection conn = ar1.result();
      conn.query(sql, ar2 -> {
        if (ar2.succeeded()) {
          f.complete();
        } else {
          f.fail(ar2.cause());
        }
      });
    } else {
      f.fail(ar1.cause());
    }
  });
}
 

private SQLConnection connection() {
  CountDownLatch latch = new CountDownLatch(1);
  AtomicReference<SQLConnection> ref = new AtomicReference<>();
  client.getConnection(onSuccess(conn -> {
    ref.set(conn);
    latch.countDown();
  }));

  try {
    latch.await();
  } catch (InterruptedException e) {
    throw new RuntimeException(e);
  }

  return ref.get();
}
 

@Test
public void testMultiSelect() {
  String sql = "{ call MS() }";

  final SQLConnection conn = connection();

  conn.execute("CREATE PROCEDURE MS() PARAMETER STYLE JAVA LANGUAGE JAVA READS SQL DATA DYNAMIC RESULT SETS 2 EXTERNAL NAME 'io.vertx.ext.jdbc.Functions.multiSelect'", onSuccess(res -> {
    conn.call(sql, onSuccess(resultSet -> {
      assertNotNull(resultSet);
      assertNotNull(resultSet.getNext());
      testComplete();
    }));

  }));


  await();
}
 

@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");
}
 

@Test
public void testGetSet() {
  SQLConnection conn = connection();

  conn.getTransactionIsolation(txIsolation -> {
    if (txIsolation.failed()) {
      fail(txIsolation.cause());
      return;
    }

    conn.setTransactionIsolation(txIsolation.result(), res -> {
      if (res.failed()) {
        fail(res.cause());
      }

      testComplete();
    });
  });

  await();
}
 

private SQLConnection connection() {
  CountDownLatch latch = new CountDownLatch(1);
  AtomicReference<SQLConnection> ref = new AtomicReference<>();
  client.getConnection(onSuccess(conn -> {
    ref.set(conn);
    latch.countDown();
  }));

  try {
    latch.await();
  } catch (InterruptedException e) {
    throw new RuntimeException(e);
  }

  return ref.get();
}
 

@Test
  public void testQueryTimeout() {
    String sql = "{call NAP(1) + NAP(1) + NAP(1) + NAP(1) + NAP(1)}";

    final SQLConnection conn = connection();

    conn.execute("CREATE FUNCTION NAP() returns INT PARAMETER STYLE JAVA reads sql data language JAVA EXTERNAL NAME 'io.vertx.ext.jdbc.Functions.nap'", onSuccess(res -> {
      conn.setQueryTimeout(1).call(sql, onFailure(resultSet -> {
        assertNotNull(resultSet);
//        assertEquals(1, resultSet.getResults().size());
//        // we expect a String since UUID will be converted with the fallback mode
//        assertNotNull(resultSet.getResults().get(0).getString(0));
        testComplete();
      }));

    }));


    await();
  }
 

private SQLConnection connection() {
  CountDownLatch latch = new CountDownLatch(1);
  AtomicReference<SQLConnection> ref = new AtomicReference<>();
  client.getConnection(onSuccess(conn -> {
    ref.set(conn);
    latch.countDown();
  }));

  try {
    latch.await();
  } catch (InterruptedException e) {
    throw new RuntimeException(e);
  }

  return ref.get();
}
 

@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);
}
 

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!
    }
  });
}
 

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;
}
 

public void example9(SQLConnection connection) {
  // Assume that there is a SQL procedure like this:
  //
  // create procedure new_customer(firstname varchar(50), lastname varchar(50))
  //   modifies sql data
  //   insert into customers values (default, firstname, lastname, current_timestamp);

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

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

    if (res.succeeded()) {
      // Success!
    } else {
      // Failed!
    }
  });
}
 

private Future<Article> queryOne(SQLConnection connection, String id) {
    Future<Article> future = Future.future();
    String sql = "SELECT * FROM articles WHERE id = ?";
    connection.queryWithParams(sql, new JsonArray().add(Integer.valueOf(id)), result -> {
        connection.close();
        future.handle(
            result.map(rs -> {
                List<JsonObject> rows = rs.getRows();
                if (rows.size() == 0) {
                    throw new NoSuchElementException("No article with id " + id);
                } else {
                    JsonObject row = rows.get(0);
                    return new Article(row);
                }
            })
        );
    });
    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 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!
    }
  });
}
 

private Future<SQLConnection> createSomeDataIfNone(SQLConnection connection) {
    Future<SQLConnection> future = Future.future();
    connection.query("SELECT * FROM Articles", select -> {
        if (select.failed()) {
            future.fail(select.cause());
        } else {
            if (select.result().getResults().isEmpty()) {
                Article article1 = new Article("Fallacies of distributed computing",
                    "https://en.wikipedia.org/wiki/Fallacies_of_distributed_computing");
                Article article2 = new Article("Reactive Manifesto",
                    "https://www.reactivemanifesto.org/");
                Future<Article> insertion1 = insert(connection, article1, false);
                Future<Article> insertion2 = insert(connection, article2, false);
                CompositeFuture.all(insertion1, insertion2)
                    .setHandler(r -> future.handle(r.map(connection)));
            } else {
                future.complete(connection);
            }
        }
    });
    return future;
}
 

@Override
public void start(io.vertx.core.Promise<Void> f) throws Exception {
  SQLClient client = JDBCClient.create(vertx, ds);
  String sql = "SELECT ID, FNAME, LNAME FROM select_table ORDER BY ID";
  client.getConnection(ar1 -> {
    if (ar1.succeeded()) {
      SQLConnection conn = ar1.result();
      conn.query(sql, ar2 -> {
        if (ar2.succeeded()) {
          f.complete();
        } else {
          f.fail(ar2.cause());
        }
      });
    } else {
      f.fail(ar1.cause());
    }
  });
}
 

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!
      }
    });
  }
 

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!
      }
    });

  }
 

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()));
        }
    });
}
 

@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();
            });
        }
    });
}
 

private SQLConnection connection() {
  CountDownLatch latch = new CountDownLatch(1);
  AtomicReference<SQLConnection> ref = new AtomicReference<>();
  client.getConnection(onSuccess(conn -> {
    ref.set(conn);
    latch.countDown();
  }));

  try {
    latch.await();
  } catch (InterruptedException e) {
    throw new RuntimeException(e);
  }

  return ref.get();
}
 

private SQLConnection connection() {
  CountDownLatch latch = new CountDownLatch(1);
  AtomicReference<SQLConnection> ref = new AtomicReference<>();
  client.getConnection(onSuccess(conn -> {
    ref.set(conn);
    latch.countDown();
  }));

  try {
    latch.await();
  } catch (InterruptedException e) {
    throw new RuntimeException(e);
  }

  return ref.get();
}
 

@Test
public void testCustomInsert() {
  String sql = "INSERT INTO t (u, t, d, ts) VALUES (?, ?, ?, ?)";
  final String uuid = UUID.randomUUID().toString();

  final SQLConnection conn = connection();

  conn.setAutoCommit(false, tx -> {
    if (tx.succeeded()) {
      conn.updateWithParams(sql, new JsonArray().add(uuid).add("09:00:00").add("2015-03-16").add(Instant.now()), onSuccess(resultSet -> {
        testComplete();
      }));
    }
  });

  await();
}
 

@Test
public void testBatchCallableStatement() {
  final SQLConnection conn = connection();

  conn.batch(Arrays.asList("CREATE ALIAS println FOR \"io.vertx.ext.jdbc.JDBCBatchTest.proc\""), onSuccess(batchResult -> {
    assertNotNull(batchResult);
    assertEquals(1, batchResult.size());

    conn.batchCallableWithParams("{ call println() }", Arrays.asList(new JsonArray(), new JsonArray(), new JsonArray()), Arrays.asList(new JsonArray(), new JsonArray(), new JsonArray()), onSuccess(batchResult2 -> {
      assertNotNull(batchResult2);
      assertEquals(3, batchResult2.size());
      testComplete();
    }));
  }));

  await();
}
 

public static Future<SQLConnection> setAutoCommit(final Tracer tracer, final SpanContext context, final SQLConnection connection, boolean state) {
    final Span span = startSqlSpan(tracer, context, "set autocommit", builder -> {
        builder.withTag("db.autocommit", state);
    });
    final Promise<Void> promise = Promise.promise();
    connection.setAutoCommit(state, promise);
    return finishSpan(promise.future().map(connection), span, null);
}
 

@Override
public void getAuthorizations(User user, Handler<AsyncResult<Void>> resultHandler) {
  client.getConnection(connectionResponse -> {
    if (connectionResponse.succeeded()) {
      String username = user.principal().getString(usernameKey);
      if (username != null) {
        JsonArray params = new JsonArray().add(username);
        SQLConnection connection = connectionResponse.result();
        getRoles(connection, params, roleResponse -> {
          if (roleResponse.succeeded()) {
            Set<Authorization> authorizations = new HashSet<>(roleResponse.result());
            getPermissions(connection, params, permissionResponse -> {
              if (permissionResponse.succeeded()) {
                authorizations.addAll(permissionResponse.result());
                user.authorizations().add(getId(), authorizations);
                resultHandler.handle(Future.succeededFuture());
              } else {
                resultHandler.handle(Future.failedFuture(permissionResponse.cause()));
              }
              connection.close();
            });
          } else {
            resultHandler.handle(Future.failedFuture(roleResponse.cause()));
            connection.close();
          }
        });
      } else {
        resultHandler.handle(Future.failedFuture("Couldn't get the username"));
        connectionResponse.result().close();
      }
    } else {
      resultHandler.handle(Future.failedFuture(connectionResponse.cause()));
    }
  });
}