Search in sources :

Example 1 with SqlConnection

use of io.vertx.sqlclient.SqlConnection in project vertx-examples by vert-x3.

the class SqlClientExample method start.

@Override
public void start() {
    Pool pool = PgPool.pool(vertx, new PgConnectOptions().setPort(5432).setHost("the-host").setDatabase("the-db").setUser("user").setPassword("secret"), new PoolOptions().setMaxSize(4));
    // Uncomment for MySQL
    // Pool pool = MySQLPool.pool(vertx, new MySQLConnectOptions()
    // .setPort(5432)
    // .setHost("the-host")
    // .setDatabase("the-db")
    // .setUser("user")
    // .setPassword("secret"), new PoolOptions().setMaxSize(4));
    pool.getConnection(res1 -> {
        if (res1.failed()) {
            System.err.println(res1.cause().getMessage());
            return;
        }
        SqlConnection connection = res1.result();
        // create a test table
        connection.query("create table test(id int primary key, name varchar(255))").execute(res2 -> {
            if (res2.failed()) {
                connection.close();
                System.err.println("Cannot create the table");
                res2.cause().printStackTrace();
                return;
            }
            // insert some test data
            connection.query("insert into test values (1, 'Hello'), (2, 'World')").execute(res3 -> {
                // query some data with arguments
                connection.preparedQuery("select * from test where id = ?").execute(Tuple.of(2), rs -> {
                    if (rs.failed()) {
                        System.err.println("Cannot retrieve the data from the database");
                        rs.cause().printStackTrace();
                        return;
                    }
                    for (Row line : rs.result()) {
                        System.out.println("" + line);
                    }
                    // and close the connection
                    connection.close();
                });
            });
        });
    });
}
Also used : PgConnectOptions(io.vertx.pgclient.PgConnectOptions) PoolOptions(io.vertx.sqlclient.PoolOptions) PgPool(io.vertx.pgclient.PgPool) Pool(io.vertx.sqlclient.Pool) SqlConnection(io.vertx.sqlclient.SqlConnection) Row(io.vertx.sqlclient.Row)

Example 2 with SqlConnection

use of io.vertx.sqlclient.SqlConnection in project raml-module-builder by folio-org.

the class PostgresClientIT method saveBatchJsonConnection.

@Test
public void saveBatchJsonConnection(TestContext context) {
    String id = randomUuid();
    JsonArray update = new JsonArray().add(new JsonObject().put("id", id).put("key", "y").encode());
    createFoo(context).save(FOO, id, new StringPojo("x"), context.asyncAssertSuccess(x -> {
        postgresClient.getSQLConnection(sqlConnection -> {
            postgresClient.saveBatch(sqlConnection, FOO, update, context.asyncAssertFailure(e -> {
                postgresClient.upsertBatch(sqlConnection, FOO, update, context.asyncAssertSuccess(rowSet -> {
                    postgresClient.getById(FOO, id, context.asyncAssertSuccess(json -> {
                        assertThat(rowSet.iterator().next().getUUID(0).toString(), is(id));
                        assertThat(json.getString("key"), is("y"));
                    }));
                }));
            }));
        });
    }));
}
Also used : JsonArray(io.vertx.core.json.JsonArray) CoreMatchers.is(org.hamcrest.CoreMatchers.is) TestContext(io.vertx.ext.unit.TestContext) RowStream(io.vertx.sqlclient.RowStream) Arrays(java.util.Arrays) PgNotification(io.vertx.pgclient.PgNotification) TransactionRollbackException(io.vertx.sqlclient.TransactionRollbackException) VertxUtils(org.folio.rest.tools.utils.VertxUtils) Tuple(io.vertx.sqlclient.Tuple) UpdateSection(org.folio.rest.persist.Criteria.UpdateSection) CoreMatchers.instanceOf(org.hamcrest.CoreMatchers.instanceOf) RowIterator(io.vertx.sqlclient.RowIterator) AtomicInteger(java.util.concurrent.atomic.AtomicInteger) SqlResult(io.vertx.sqlclient.SqlResult) After(org.junit.After) JsonObject(io.vertx.core.json.JsonObject) Offset(org.folio.rest.persist.Criteria.Offset) Collector(java.util.stream.Collector) Transaction(io.vertx.sqlclient.Transaction) AfterClass(org.junit.AfterClass) CoreMatchers.containsString(org.hamcrest.CoreMatchers.containsString) RowImpl(io.vertx.pgclient.impl.RowImpl) Set(java.util.Set) UUID(java.util.UUID) FieldException(org.folio.cql2pgjson.exception.FieldException) Future(io.vertx.core.Future) StandardCharsets(java.nio.charset.StandardCharsets) UncheckedIOException(java.io.UncheckedIOException) IOUtils(org.apache.commons.io.IOUtils) CQL2PgJSON(org.folio.cql2pgjson.CQL2PgJSON) Base64(java.util.Base64) List(java.util.List) Stream(java.util.stream.Stream) Criterion(org.folio.rest.persist.Criteria.Criterion) Results(org.folio.rest.persist.interfaces.Results) Facet(org.folio.rest.jaxrs.model.Facet) RowDesc(io.vertx.sqlclient.impl.RowDesc) Async(io.vertx.ext.unit.Async) CQLWrapper(org.folio.rest.persist.cql.CQLWrapper) BeforeClass(org.junit.BeforeClass) FacetField(org.folio.rest.persist.facets.FacetField) PostgresTesterContainer(org.folio.postgres.testing.PostgresTesterContainer) Criteria(org.folio.rest.persist.Criteria.Criteria) CoreMatchers.not(org.hamcrest.CoreMatchers.not) RunWith(org.junit.runner.RunWith) Timeout(io.vertx.ext.unit.junit.Timeout) ResultInfo(org.folio.rest.jaxrs.model.ResultInfo) Function(java.util.function.Function) TotaledResults(org.folio.rest.persist.PostgresClient.TotaledResults) ArrayList(java.util.ArrayList) PreparedStatement(io.vertx.sqlclient.PreparedStatement) HashSet(java.util.HashSet) CompositeFuture(io.vertx.core.CompositeFuture) Poline(org.folio.rest.persist.helpers.Poline) PrepareOptions(io.vertx.sqlclient.PrepareOptions) SqlConnection(io.vertx.sqlclient.SqlConnection) Limit(org.folio.rest.persist.Criteria.Limit) QueryHelper(org.folio.rest.persist.PostgresClient.QueryHelper) RowSet(io.vertx.sqlclient.RowSet) MatcherAssert.assertThat(org.hamcrest.MatcherAssert.assertThat) AsyncResult(io.vertx.core.AsyncResult) LinkedList(java.util.LinkedList) DatabaseMetadata(io.vertx.sqlclient.spi.DatabaseMetadata) CoreMatchers.nullValue(org.hamcrest.CoreMatchers.nullValue) Before(org.junit.Before) Files(java.nio.file.Files) Query(io.vertx.sqlclient.Query) Promise(io.vertx.core.Promise) ObjectMapper(com.fasterxml.jackson.databind.ObjectMapper) Vertx(io.vertx.core.Vertx) PgPool(io.vertx.pgclient.PgPool) JsonProcessingException(com.fasterxml.jackson.core.JsonProcessingException) IOException(java.io.IOException) Test(org.junit.Test) VertxUnitRunner(io.vertx.ext.unit.junit.VertxUnitRunner) PreparedQuery(io.vertx.sqlclient.PreparedQuery) JsonArray(io.vertx.core.json.JsonArray) PgConnection(io.vertx.pgclient.PgConnection) Rule(org.junit.Rule) Paths(java.nio.file.Paths) Row(io.vertx.sqlclient.Row) LocalRowSet(org.folio.rest.persist.helpers.LocalRowSet) Handler(io.vertx.core.Handler) SimplePojo(org.folio.rest.persist.helpers.SimplePojo) Collections(java.util.Collections) InputStream(java.io.InputStream) JsonObject(io.vertx.core.json.JsonObject) CoreMatchers.containsString(org.hamcrest.CoreMatchers.containsString) Test(org.junit.Test)

Example 3 with SqlConnection

use of io.vertx.sqlclient.SqlConnection in project raml-module-builder by folio-org.

the class PostgresClientIT method postgresClientConnectionThrowsException.

/**
 * @return a PostgresClient where invoking SQLConnection::update(...) or SQLConnection::updateWithParams
 * throws an RuntimeException.
 */
private PostgresClient postgresClientConnectionThrowsException() {
    PgConnection pgConnection = new PgConnection() {

        @Override
        public PgConnection notificationHandler(Handler<PgNotification> handler) {
            throw new RuntimeException();
        }

        @Override
        public PgConnection cancelRequest(Handler<AsyncResult<Void>> handler) {
            throw new RuntimeException();
        }

        @Override
        public int processId() {
            throw new RuntimeException();
        }

        @Override
        public int secretKey() {
            throw new RuntimeException();
        }

        @Override
        public PgConnection prepare(String s, Handler<AsyncResult<PreparedStatement>> handler) {
            throw new RuntimeException();
        }

        @Override
        public Future<PreparedStatement> prepare(String s) {
            return null;
        }

        @Override
        public SqlConnection prepare(String sql, PrepareOptions options, Handler<AsyncResult<PreparedStatement>> handler) {
            return null;
        }

        @Override
        public Future<PreparedStatement> prepare(String s, PrepareOptions prepareOptions) {
            return null;
        }

        @Override
        public PgConnection exceptionHandler(Handler<Throwable> handler) {
            return null;
        }

        @Override
        public PgConnection closeHandler(Handler<Void> handler) {
            return null;
        }

        @Override
        public void begin(Handler<AsyncResult<Transaction>> handler) {
        }

        @Override
        public Future<Transaction> begin() {
            return null;
        }

        @Override
        public boolean isSSL() {
            return false;
        }

        @Override
        public void close(Handler<AsyncResult<Void>> handler) {
            close().onComplete(handler);
        }

        @Override
        public Future<Void> close() {
            return Future.succeededFuture();
        }

        @Override
        public Query<RowSet<Row>> query(String s) {
            throw new RuntimeException();
        }

        @Override
        public PreparedQuery<RowSet<Row>> preparedQuery(String s, PrepareOptions options) {
            throw new RuntimeException();
        }

        @Override
        public PreparedQuery<RowSet<Row>> preparedQuery(String s) {
            throw new RuntimeException();
        }

        @Override
        public DatabaseMetadata databaseMetadata() {
            throw new RuntimeException();
        }
    };
    PgPool client = new PgPoolBase() {

        @Override
        public Future<SqlConnection> getConnection() {
            return Future.succeededFuture(pgConnection);
        }
    };
    try {
        PostgresClient postgresClient = new PostgresClient(vertx, TENANT);
        postgresClient.setClient(client);
        return postgresClient;
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}
Also used : PgPool(io.vertx.pgclient.PgPool) PgConnection(io.vertx.pgclient.PgConnection) RowSet(io.vertx.sqlclient.RowSet) LocalRowSet(org.folio.rest.persist.helpers.LocalRowSet) Handler(io.vertx.core.Handler) PreparedStatement(io.vertx.sqlclient.PreparedStatement) CoreMatchers.containsString(org.hamcrest.CoreMatchers.containsString) TransactionRollbackException(io.vertx.sqlclient.TransactionRollbackException) FieldException(org.folio.cql2pgjson.exception.FieldException) UncheckedIOException(java.io.UncheckedIOException) JsonProcessingException(com.fasterxml.jackson.core.JsonProcessingException) IOException(java.io.IOException) Transaction(io.vertx.sqlclient.Transaction) PrepareOptions(io.vertx.sqlclient.PrepareOptions) SqlConnection(io.vertx.sqlclient.SqlConnection)

Example 4 with SqlConnection

use of io.vertx.sqlclient.SqlConnection in project raml-module-builder by folio-org.

the class PostgresClientIT method postgresClientGetConnectionFails.

/**
 * @return a PostgresClient where getConnection(handler) invokes the handler with a failure.
 */
private PostgresClient postgresClientGetConnectionFails() {
    PgPool client = new PgPoolBase() {

        @Override
        public Future<SqlConnection> getConnection() {
            return Future.failedFuture("postgresClientGetConnectionFails");
        }
    };
    try {
        PostgresClient postgresClient = new PostgresClient(vertx, TENANT);
        postgresClient.setClient(client);
        return postgresClient;
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}
Also used : PgPool(io.vertx.pgclient.PgPool) SqlConnection(io.vertx.sqlclient.SqlConnection) TransactionRollbackException(io.vertx.sqlclient.TransactionRollbackException) FieldException(org.folio.cql2pgjson.exception.FieldException) UncheckedIOException(java.io.UncheckedIOException) JsonProcessingException(com.fasterxml.jackson.core.JsonProcessingException) IOException(java.io.IOException)

Example 5 with SqlConnection

use of io.vertx.sqlclient.SqlConnection in project raml-module-builder by folio-org.

the class PostgresClientIT method postgresClientQueryReturnBadResults.

/**
 * @return a PostgresClient where invoking SQLConnection::queryWithParams will return null ResultSet
 */
private PostgresClient postgresClientQueryReturnBadResults() {
    PgConnection pgConnection = new PgConnection() {

        @Override
        public PgConnection notificationHandler(Handler<PgNotification> handler) {
            return null;
        }

        @Override
        public PgConnection cancelRequest(Handler<AsyncResult<Void>> handler) {
            return this;
        }

        @Override
        public int processId() {
            return 0;
        }

        @Override
        public int secretKey() {
            return 0;
        }

        @Override
        public PgConnection prepare(String s, Handler<AsyncResult<PreparedStatement>> handler) {
            return null;
        }

        @Override
        public Future<PreparedStatement> prepare(String s) {
            return null;
        }

        @Override
        public SqlConnection prepare(String sql, PrepareOptions options, Handler<AsyncResult<PreparedStatement>> handler) {
            return null;
        }

        @Override
        public Future<PreparedStatement> prepare(String sql, PrepareOptions options) {
            return null;
        }

        @Override
        public PgConnection exceptionHandler(Handler<Throwable> handler) {
            return null;
        }

        @Override
        public PgConnection closeHandler(Handler<Void> handler) {
            return null;
        }

        @Override
        public void begin(Handler<AsyncResult<Transaction>> handler) {
        }

        @Override
        public Future<Transaction> begin() {
            return null;
        }

        @Override
        public boolean isSSL() {
            return false;
        }

        @Override
        public void close(Handler<AsyncResult<Void>> handler) {
        }

        @Override
        public Query<RowSet<Row>> query(String s) {
            return null;
        }

        @Override
        public PreparedQuery<RowSet<Row>> preparedQuery(String s) {
            return null;
        }

        @Override
        public PreparedQuery<RowSet<Row>> preparedQuery(String sql, PrepareOptions options) {
            return preparedQuery(sql);
        }

        @Override
        public Future<Void> close() {
            return null;
        }

        @Override
        public DatabaseMetadata databaseMetadata() {
            return null;
        }
    };
    PgPool client = new PgPoolBase() {

        @Override
        public Future<SqlConnection> getConnection() {
            return Future.succeededFuture(pgConnection);
        }
    };
    try {
        PostgresClient postgresClient = new PostgresClient(vertx, TENANT);
        postgresClient.setClient(client);
        return postgresClient;
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}
Also used : PgPool(io.vertx.pgclient.PgPool) PgConnection(io.vertx.pgclient.PgConnection) RowSet(io.vertx.sqlclient.RowSet) LocalRowSet(org.folio.rest.persist.helpers.LocalRowSet) Handler(io.vertx.core.Handler) PreparedStatement(io.vertx.sqlclient.PreparedStatement) CoreMatchers.containsString(org.hamcrest.CoreMatchers.containsString) TransactionRollbackException(io.vertx.sqlclient.TransactionRollbackException) FieldException(org.folio.cql2pgjson.exception.FieldException) UncheckedIOException(java.io.UncheckedIOException) JsonProcessingException(com.fasterxml.jackson.core.JsonProcessingException) IOException(java.io.IOException) Transaction(io.vertx.sqlclient.Transaction) PrepareOptions(io.vertx.sqlclient.PrepareOptions) SqlConnection(io.vertx.sqlclient.SqlConnection)

Aggregations

PgPool (io.vertx.pgclient.PgPool)10 SqlConnection (io.vertx.sqlclient.SqlConnection)10 JsonProcessingException (com.fasterxml.jackson.core.JsonProcessingException)7 PgConnection (io.vertx.pgclient.PgConnection)7 RowSet (io.vertx.sqlclient.RowSet)7 IOException (java.io.IOException)7 FieldException (org.folio.cql2pgjson.exception.FieldException)7 Handler (io.vertx.core.Handler)6 PreparedStatement (io.vertx.sqlclient.PreparedStatement)6 Row (io.vertx.sqlclient.Row)6 Transaction (io.vertx.sqlclient.Transaction)6 TransactionRollbackException (io.vertx.sqlclient.TransactionRollbackException)6 UncheckedIOException (java.io.UncheckedIOException)6 PrepareOptions (io.vertx.sqlclient.PrepareOptions)5 ObjectMapper (com.fasterxml.jackson.databind.ObjectMapper)3 AsyncResult (io.vertx.core.AsyncResult)3 Future (io.vertx.core.Future)3 Promise (io.vertx.core.Promise)3 Vertx (io.vertx.core.Vertx)3 JsonArray (io.vertx.core.json.JsonArray)3