io.vertx.ext.sql.SQLConnection Java Examples

The following examples show how to use io.vertx.ext.sql.SQLConnection. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: JDBCCustomTypesTest.java    From vertx-jdbc-client with Apache License 2.0 6 votes vote down vote up
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();
}
 
Example #2
Source File: JDBCExamples.java    From vertx-jdbc-client with Apache License 2.0 6 votes vote down vote up
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
      }
    });

  }
 
Example #3
Source File: CloseTest.java    From vertx-jdbc-client with Apache License 2.0 6 votes vote down vote up
@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());
    }
  });
}
 
Example #4
Source File: JDBCStoredProcedureTest.java    From vertx-jdbc-client with Apache License 2.0 6 votes vote down vote up
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();
}
 
Example #5
Source File: JDBCQueryTimeoutTest.java    From vertx-jdbc-client with Apache License 2.0 6 votes vote down vote up
@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();
}
 
Example #6
Source File: BasicJdbcClientTest.java    From prebid-server-java with Apache License 2.0 6 votes vote down vote up
@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");
}
 
Example #7
Source File: JDBCCustomTXIsolationTest.java    From vertx-jdbc-client with Apache License 2.0 6 votes vote down vote up
@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();
}
 
Example #8
Source File: JDBCCustomTXIsolationTest.java    From vertx-jdbc-client with Apache License 2.0 6 votes vote down vote up
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();
}
 
Example #9
Source File: JDBCQueryTimeoutTest.java    From vertx-jdbc-client with Apache License 2.0 6 votes vote down vote up
@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();
  }
 
Example #10
Source File: JDBCTypesTestBase.java    From vertx-jdbc-client with Apache License 2.0 6 votes vote down vote up
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();
}
 
Example #11
Source File: BasicJdbcClient.java    From prebid-server-java with Apache License 2.0 6 votes vote down vote up
@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);
}
 
Example #12
Source File: SQLExamples.java    From vertx-jdbc-client with Apache License 2.0 6 votes vote down vote up
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!
    }
  });
}
 
Example #13
Source File: MyFirstVerticle.java    From introduction-to-eclipse-vertx with Apache License 2.0 6 votes vote down vote up
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;
}
 
Example #14
Source File: SQLExamples.java    From vertx-jdbc-client with Apache License 2.0 6 votes vote down vote up
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!
    }
  });
}
 
Example #15
Source File: MyFirstVerticle.java    From introduction-to-eclipse-vertx with Apache License 2.0 6 votes vote down vote up
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;
}
 
Example #16
Source File: MyFirstVerticle.java    From introduction-to-eclipse-vertx with Apache License 2.0 6 votes vote down vote up
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;
}
 
Example #17
Source File: MyFirstVerticle.java    From introduction-to-eclipse-vertx with Apache License 2.0 6 votes vote down vote up
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;
}
 
Example #18
Source File: SQLExamples.java    From vertx-jdbc-client with Apache License 2.0 6 votes vote down vote up
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!
    }
  });
}
 
Example #19
Source File: MyFirstVerticle.java    From introduction-to-eclipse-vertx with Apache License 2.0 6 votes vote down vote up
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;
}
 
Example #20
Source File: CloseTest.java    From vertx-jdbc-client with Apache License 2.0 6 votes vote down vote up
@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());
    }
  });
}
 
Example #21
Source File: AuthJDBCExamples.java    From vertx-auth with Apache License 2.0 6 votes vote down vote up
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!
      }
    });
  }
 
Example #22
Source File: SQLExamples.java    From vertx-jdbc-client with Apache License 2.0 6 votes vote down vote up
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!
      }
    });

  }
 
Example #23
Source File: RecommendationPersistenceVerticle.java    From istio-tutorial with Apache License 2.0 6 votes vote down vote up
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()));
        }
    });
}
 
Example #24
Source File: AuditVerticleTest.java    From microtrader with MIT License 6 votes vote down vote up
@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();
            });
        }
    });
}
 
Example #25
Source File: JDBCBatchTest.java    From vertx-jdbc-client with Apache License 2.0 6 votes vote down vote up
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();
}
 
Example #26
Source File: JDBCQueryTimeoutTest.java    From vertx-jdbc-client with Apache License 2.0 6 votes vote down vote up
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();
}
 
Example #27
Source File: JDBCCustomTypesTest.java    From vertx-jdbc-client with Apache License 2.0 6 votes vote down vote up
@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();
}
 
Example #28
Source File: JDBCBatchTest.java    From vertx-jdbc-client with Apache License 2.0 6 votes vote down vote up
@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();
}
 
Example #29
Source File: SQL.java    From enmasse with Apache License 2.0 5 votes vote down vote up
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);
}
 
Example #30
Source File: JDBCAuthorizationImpl.java    From vertx-auth with Apache License 2.0 5 votes vote down vote up
@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()));
    }
  });
}