Java Code Examples for io.vertx.ext.sql.SQLConnection

The following examples show how to use io.vertx.ext.sql.SQLConnection. These examples are extracted from open source projects. 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 Project: prebid-server-java   Source File: BasicJdbcClient.java    License: 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 2
Source Project: prebid-server-java   Source File: BasicJdbcClientTest.java    License: 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 3
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 4
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 5
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 6
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 7
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 8
Source Project: vertx-auth   Source File: AuthJDBCExamples.java    License: 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 9
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 10
Source Project: microtrader   Source File: AuditVerticleTest.java    License: 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 11
Source Project: vertx-jdbc-client   Source File: JDBCBatchTest.java    License: 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 12
Source Project: vertx-jdbc-client   Source File: JDBCCustomTypesTest.java    License: 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 13
Source Project: vertx-jdbc-client   Source File: JDBCCustomTypesTest.java    License: 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 14
Source Project: vertx-jdbc-client   Source File: JDBCQueryTimeoutTest.java    License: 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 15
Source Project: vertx-jdbc-client   Source File: SQLExamples.java    License: 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 16
Source Project: vertx-jdbc-client   Source File: CloseTest.java    License: 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 17
Source Project: vertx-jdbc-client   Source File: SQLExamples.java    License: 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 18
Source Project: vertx-jdbc-client   Source File: SQLExamples.java    License: 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 19
Source Project: vertx-jdbc-client   Source File: SQLExamples.java    License: 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 20
Source Project: vertx-jdbc-client   Source File: JDBCQueryTimeoutTest.java    License: 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 21
Source Project: vertx-jdbc-client   Source File: JDBCStoredProcedureTest.java    License: 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 22
Source Project: vertx-jdbc-client   Source File: CloseTest.java    License: 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 23
Source Project: vertx-jdbc-client   Source File: JDBCExamples.java    License: 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 24
@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 25
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 Project: vertx-jdbc-client   Source File: JDBCQueryTimeoutTest.java    License: 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 27
Source Project: vertx-jdbc-client   Source File: JDBCTypesTestBase.java    License: 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 28
Source Project: vertx-jdbc-client   Source File: JDBCBatchTest.java    License: 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 Project: enode   Source File: JDBCEventStore.java    License: MIT License 5 votes vote down vote up
private SQLClient batchWithParams(String sql, List<JsonArray> params, Handler<AsyncResult<List<Integer>>> handler) {
    sqlClient.getConnection(getConnection -> {
        if (getConnection.failed()) {
            handler.handle(Future.failedFuture(getConnection.cause()));
            return;
        }
        final SQLConnection conn = getConnection.result();
        conn.setAutoCommit(false, autocommit -> {
            if (autocommit.failed()) {
                handler.handle(Future.failedFuture(autocommit.cause()));
                resetAutoCommitAndCloseConnection(conn);
                return;
            }
            conn.batchWithParams(sql, params, batch -> {
                if (batch.succeeded()) {
                    conn.commit(commit -> {
                        if (commit.succeeded()) {
                            handler.handle(Future.succeededFuture(batch.result()));
                        } else {
                            handler.handle(Future.failedFuture(commit.cause()));
                        }
                        resetAutoCommitAndCloseConnection(conn);
                    });
                } else {
                    conn.rollback(rollback -> {
                        if (rollback.succeeded()) {
                            handler.handle(Future.failedFuture(batch.cause()));
                        } else {
                            handler.handle(Future.failedFuture(rollback.cause()));
                        }
                        resetAutoCommitAndCloseConnection(conn);
                    });
                }
            });
        });
    });
    return sqlClient;
}
 
Example 30
Source Project: enode   Source File: JDBCEventStore.java    License: MIT License 5 votes vote down vote up
private void resetAutoCommitAndCloseConnection(SQLConnection conn) {
    conn.setAutoCommit(true, commit -> {
        if (commit.failed()) {
            logger.error("jdbc driver set autocommit true failed", commit.cause());
        }
        // close will put the connection into pool
        conn.close();
    });
}