io.vertx.sqlclient.Pool Java Examples

public void transaction03(Pool pool) {

    // Acquire a transaction and begin the transaction
    pool.withTransaction(client -> client
      .query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')")
      .execute()
      .flatMap(res -> client
        .query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')")
        .execute()
        // Map to a message result
        .map("Users inserted"))
    ).onComplete(ar -> {
      // The connection was automatically return to the pool
      if (ar.succeeded()) {
        // Transaction was committed
        String message = ar.result();
        System.out.println("Transaction succeeded: " + message);
      } else {
        // Transaction was rolled back
        System.out.println("Transaction failed " + ar.cause().getMessage());
      }
    });  }
 

public void usingConnections01(Vertx vertx, Pool pool) {

    pool.getConnection(ar1 -> {
      if (ar1.succeeded()) {
        SqlConnection connection = ar1.result();

        connection
          .query("SELECT * FROM users WHERE id='andy'")
          .execute(ar2 -> {
          if (ar1.succeeded()) {
            connection
              .query("SELECT * FROM users WHERE id='julien'")
              .execute(ar3 -> {
              // Do something with rows and return the connection to the pool
              connection.close();
            });
          } else {
            // Return the connection to the pool
            connection.close();
          }
        });
      }
    });
  }
 

private void registerVertxPool(String persistenceUnitName,
        RuntimeSettings runtimeSettings,
        PreconfiguredReactiveServiceRegistryBuilder serviceRegistry) {
    if (runtimeSettings.isConfigured(AvailableSettings.URL)) {
        // the pool has been defined in the persistence unit, we can bail out
        return;
    }

    // for now we only support one pool but this will change
    InstanceHandle<Pool> poolHandle = Arc.container().instance(Pool.class);
    if (!poolHandle.isAvailable()) {
        throw new IllegalStateException("No pool has been defined for persistence unit " + persistenceUnitName);
    }

    serviceRegistry.addInitiator(new QuarkusReactiveConnectionPoolInitiator(poolHandle.get()));
}
 

public void close() {
    final long lock = stampedLock.writeLock();
    try {
        isOpen = false;
        //While this synchronized block might take a while as we have to close all
        //pool instances, it shouldn't block the getPool method as contention is
        //prevented by the exclusive stamped lock.
        synchronized (threadLocalPools) {
            for (Pool pool : threadLocalPools) {
                log.debugf("Closing pool: %s", pool);
                pool.close();
            }
        }
    } finally {
        stampedLock.unlockWrite(lock);
    }
}
 

protected Pool configurePool(Map configurationValues, Vertx vertx) {
	URI uri = jdbcUrl(configurationValues);
	SqlConnectOptions connectOptions = sqlConnectOptions( uri );
	PoolOptions poolOptions = poolOptions( configurationValues );

	try {
		// First try to load the Pool using the standard ServiceLoader pattern
		// This only works if exactly 1 Driver is on the classpath.
		return Pool.pool( vertx, connectOptions, poolOptions );
	}
	catch (ServiceConfigurationError e) {
		// Backup option if multiple drivers are on the classpath.
		// We will be able to remove this once Vertx 3.9.2 is available
		return findDriver( uri, e ).createPool( vertx, connectOptions, poolOptions );
	}
}
 

public void usingConnections01(Vertx vertx, Pool pool) {

    pool.getConnection(ar1 -> {
      if (ar1.succeeded()) {
        SqlConnection connection = ar1.result();

        connection
          .query("SELECT * FROM users WHERE id='julien'")
          .execute(ar2 -> {
          if (ar1.succeeded()) {
            connection
              .query("SELECT * FROM users WHERE id='paulo'")
              .execute(ar3 -> {
              // Do something with rows and return the connection to the pool
              connection.close();
            });
          } else {
            // Return the connection to the pool
            connection.close();
          }
        });
      }
    });
  }
 

public void transaction03(Pool pool) {

    // Acquire a transaction and begin the transaction
    pool.withTransaction(client -> client
      .query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')")
      .execute()
      .flatMap(res -> client
        .query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')")
        .execute()
        // Map to a message result
        .map("Users inserted"))
    ).onComplete(ar -> {
      // The connection was automatically return to the pool
      if (ar.succeeded()) {
        // Transaction was committed
        String message = ar.result();
        System.out.println("Transaction succeeded: " + message);
      } else {
        // Transaction was rolled back
        System.out.println("Transaction failed " + ar.cause().getMessage());
      }
    });
  }
 

public void usingConnections01(Vertx vertx, Pool pool) {

    pool.getConnection(ar1 -> {
      if (ar1.succeeded()) {
        SqlConnection connection = ar1.result();

        connection
          .query("SELECT * FROM users WHERE id='julien'")
          .execute(ar2 -> {
          if (ar1.succeeded()) {
            connection
              .query("SELECT * FROM users WHERE id='paulo'")
              .execute(ar3 -> {
              // Do something with rows and return the connection to the pool
              connection.close();
            });
          } else {
            // Return the connection to the pool
            connection.close();
          }
        });
      }
    });
  }
 

public void transaction03(Pool pool) {

    // Acquire a transaction and begin the transaction
    pool.withTransaction(client -> client
      .query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')")
      .execute()
      .flatMap(res -> client
        .query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')")
        .execute()
        // Map to a message result
        .map("Users inserted"))
    ).onComplete(ar -> {
      // The connection was automatically return to the pool
      if (ar.succeeded()) {
        // Transaction was committed
        String message = ar.result();
        System.out.println("Transaction succeeded: " + message);
      } else {
        // Transaction was rolled back
        System.out.println("Transaction failed " + ar.cause().getMessage());
      }
    });
  }
 

protected void initConnector() {
  connector = handler -> {
    Pool pool = getPool();
    pool.getConnection(ar1 -> {
      if (ar1.succeeded()) {
        SqlConnection conn = ar1.result();
        conn.begin(ar2 -> {
          if (ar2.succeeded()) {
            Transaction tx = ar2.result();
            tx.completion().onComplete(ar3 -> {
              conn.close();
            });
            handler.handle(Future.succeededFuture(new Result(conn, tx)));
          } else {
            conn.close();
          }
        });
      } else {
        handler.handle(ar1.mapEmpty());
      }
    });
  };
}
 

@Test
public void testWithTransactionCommit(TestContext ctx) {
  Async async = ctx.async();
  Pool pool = createPool();
  pool.withTransaction(client -> client
    .query("INSERT INTO mutable (id, val) VALUES (1, 'hello-1')")
    .execute()
    .mapEmpty()
    .flatMap(v -> client
      .query("INSERT INTO mutable (id, val) VALUES (2, 'hello-2')")
      .execute()
      .mapEmpty()))
    .onComplete(ctx.asyncAssertSuccess(v -> {
      pool
        .query("SELECT id, val FROM mutable")
        .execute(ctx.asyncAssertSuccess(rows -> {
          ctx.assertEquals(2, rows.size());
          async.complete();
      }));
    }));
}
 

@Test
public void testWithTransactionRollback(TestContext ctx) {
  Async async = ctx.async();
  Throwable failure = new Throwable();
  Pool pool = createPool();
  pool.withTransaction(client -> client
    .query("INSERT INTO mutable (id, val) VALUES (1, 'hello-1')")
    .execute()
    .mapEmpty()
    .flatMap(v -> Future.failedFuture(failure))
    .onComplete(ctx.asyncAssertFailure(err -> {
      ctx.assertEquals(failure, err);
      pool
        .query("SELECT id, val FROM mutable")
        .execute(ctx.asyncAssertSuccess(rows -> {
          ctx.assertEquals(0, rows.size());
          async.complete();
        }));
    })));
}
 

@Test
public void testWithTransactionImplicitRollback(TestContext ctx) {
  Async async = ctx.async();
  Pool pool = createPool();
  pool.withTransaction(client -> client
    .query("INSERT INTO mutable (id, val) VALUES (1, 'hello-1')")
    .execute()
    .mapEmpty()
    .flatMap(v -> client
      .query("INVALID")
      .execute())
    .onComplete(ctx.asyncAssertFailure(err-> {
      pool
        .query("SELECT id, val FROM mutable")
        .execute(ctx.asyncAssertSuccess(rows -> {
          ctx.assertEquals(0, rows.size());
          async.complete();
        }));
    })));
}
 

public void usingConnections01(Vertx vertx, Pool pool) {

    pool.getConnection(ar1 -> {
      if (ar1.succeeded()) {
        SqlConnection connection = ar1.result();

        connection
          .query("SELECT * FROM users WHERE id='julien'")
          .execute(ar2 -> {
          if (ar1.succeeded()) {
            connection
              .query("SELECT * FROM users WHERE id='paulo'")
              .execute(ar3 -> {
              // Do something with rows and return the connection to the pool
              connection.close();
            });
          } else {
            // Return the connection to the pool
            connection.close();
          }
        });
      }
    });
  }
 

public void transaction03(Pool pool) {

    // Acquire a transaction and begin the transaction
    pool.withTransaction(client -> client
      .query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')")
      .execute()
      .flatMap(res -> client
        .query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')")
        .execute()
        // Map to a message result
        .map("Users inserted"))
    ).onComplete(ar -> {
      // The connection was automatically return to the pool
      if (ar.succeeded()) {
        // Transaction was committed
        String message = ar.result();
        System.out.println("Transaction succeeded: " + message);
      } else {
        // Transaction was rolled back
        System.out.println("Transaction failed " + ar.cause().getMessage());
      }
    });
  }
 

@Test
public void testCreatePoolFromDriver01(TestContext ctx) {
  Pool p = getDriver().createPool(defaultOptions());
  p.getConnection(ctx.asyncAssertSuccess(ar -> {
    ar.close();
  }));
}
 

public void typeMapping01(Pool pool) {
  pool
    .query("SELECT 1::BIGINT \"VAL\"")
    .execute(ar -> {
    RowSet<Row> rowSet = ar.result();
    Row row = rowSet.iterator().next();

    // Stored as java.lang.Long
    Object value = row.getValue(0);

    // Convert to java.lang.Integer
    Integer intValue = row.getInteger(0);
  });
}
 

public void typeMapping02(Pool pool) {
  pool
    .query("SELECT 1::BIGINT \"VAL\"")
    .execute(ar -> {
    RowSet<Row> rowSet = ar.result();
    Row row = rowSet.iterator().next();

    // Stored as java.lang.Long
    Object value = row.getValue(0);

    // Convert to java.lang.Integer
    Integer intValue = row.getInteger(0);
  });
}
 

@BuildStep
@Record(ExecutionTime.RUNTIME_INIT)
ServiceStartBuildItem build(BuildProducer<FeatureBuildItem> feature,
        BuildProducer<DB2PoolBuildItem> db2Pool,
        BuildProducer<VertxPoolBuildItem> vertxPool,
        DB2PoolRecorder recorder,
        VertxBuildItem vertx,
        BuildProducer<SyntheticBeanBuildItem> syntheticBeans, ShutdownContextBuildItem shutdown,
        DataSourcesBuildTimeConfig dataSourcesBuildTimeConfig, DataSourcesRuntimeConfig dataSourcesRuntimeConfig,
        DataSourceReactiveBuildTimeConfig dataSourceReactiveBuildTimeConfig,
        DataSourceReactiveRuntimeConfig dataSourceReactiveRuntimeConfig,
        DataSourceReactiveDB2Config dataSourceReactiveDB2Config) {

    feature.produce(new FeatureBuildItem(Feature.REACTIVE_DB2_CLIENT));
    // Make sure the DB2PoolProducer is initialized before the StartupEvent is fired
    ServiceStartBuildItem serviceStart = new ServiceStartBuildItem("reactive-db2-client");

    // Note: we had to tweak that logic to support the legacy configuration
    if (dataSourcesBuildTimeConfig.defaultDataSource.dbKind.isPresent()
            && (!DatabaseKind.isDB2(dataSourcesBuildTimeConfig.defaultDataSource.dbKind.get())
                    || !dataSourceReactiveBuildTimeConfig.enabled)) {
        return serviceStart;
    }

    RuntimeValue<DB2Pool> db2PoolValue = recorder.configureDB2Pool(vertx.getVertx(),
            dataSourcesRuntimeConfig, dataSourceReactiveRuntimeConfig, dataSourceReactiveDB2Config,
            shutdown);
    db2Pool.produce(new DB2PoolBuildItem(db2PoolValue));

    // Synthetic bean for DB2Pool
    syntheticBeans.produce(SyntheticBeanBuildItem.configure(DB2Pool.class).addType(Pool.class).scope(Singleton.class)
            .runtimeValue(db2PoolValue)
            .setRuntimeInit().done());

    boolean isDefault = true; // assume always the default pool for now
    vertxPool.produce(new VertxPoolBuildItem(db2PoolValue, DatabaseKind.DB2, isDefault));

    return serviceStart;
}
 

public void typeMapping01(Pool pool) {
  pool
    .query("SELECT an_int_column FROM exampleTable")
    .execute(ar -> {
    RowSet<Row> rowSet = ar.result();
    Row row = rowSet.iterator().next();

    // Stored as INTEGER column type and represented as java.lang.Integer
    Object value = row.getValue(0);

    // Convert to java.lang.Long
    Long longValue = row.getLong(0);
  });
}
 

@Test
public void testCreatePool05(TestContext ctx) {
  // The default options will be an instanceof the driver-specific class, so manually copy
  // each option over onto a fresh generic options object to force the generic constructor path
  SqlConnectOptions defaults = defaultOptions();
  SqlConnectOptions opts = new SqlConnectOptions()
        .setHost(defaults.getHost())
        .setPort(defaults.getPort())
        .setDatabase(defaults.getDatabase())
        .setUser(defaults.getUser())
        .setPassword(defaults.getPassword());
  Pool.pool(opts).getConnection(ctx.asyncAssertSuccess(ar -> {
    ar.close();
  }));
}
 

@Test
public void testCreatePoolFromDriver04(TestContext ctx) {
  Pool p = getDriver().createPool(Vertx.vertx(), defaultOptions(), new PoolOptions().setMaxSize(1));
  p.getConnection(ctx.asyncAssertSuccess(ar -> {
    ar.close();
  }));
}
 

@Test
public void testCreatePoolFromDriver03(TestContext ctx) {
  Pool p = getDriver().createPool(defaultOptions(), new PoolOptions().setMaxSize(1));
  p.getConnection(ctx.asyncAssertSuccess(ar -> {
    ar.close();
  }));
}
 

@Test
public void testCreatePoolFromDriver02(TestContext ctx) {
  SqlConnectOptions genericOptions = new SqlConnectOptions(defaultOptions());
  Pool p = getDriver().createPool(genericOptions);
  p.getConnection(ctx.asyncAssertSuccess(ar -> {
    ar.close();
  }));
}
 

@Test
public void testClosePool(TestContext ctx) {
  AtomicInteger closeCount = new AtomicInteger();
  metrics = new ClientMetrics() {
    @Override
    public void close() {
      closeCount.incrementAndGet();
    }
  };
  Pool pool = createPool(vertx);
  ctx.assertEquals(0, closeCount.get());
  pool.close(ctx.asyncAssertSuccess(v -> {
    ctx.assertEquals(1, closeCount.get());
  }));
}
 

@Test
public void testDelayedCommit(TestContext ctx) {
  Pool nonTxPool = nonTxPool();
  Async async = ctx.async();
  connector.accept(ctx.asyncAssertSuccess(res -> {
    res.client.query("INSERT INTO mutable (id, val) VALUES (15, 'wait for it...')")
      .execute(ctx.asyncAssertSuccess(result -> {
      ctx.assertEquals(1, result.rowCount());
      // Should find the data within the same transaction
        res.client.query("SELECT id, val from mutable WHERE id = 15")
        .execute(ctx.asyncAssertSuccess(txRows -> {
        ctx.assertEquals(1, txRows.size());
        Row r = txRows.iterator().next();
        ctx.assertEquals(15, r.getInteger("id"));
        ctx.assertEquals("wait for it...", r.getString("val"));
        // Should NOT find the data from outside of the transaction
        nonTxPool.query("SELECT id, val from mutable WHERE id = 15")
          .execute(ctx.asyncAssertSuccess(notFound -> {
          ctx.assertEquals(0, notFound.size());
            res.tx.commit(ctx.asyncAssertSuccess(nonTxRows -> {
            nonTxPool.query("SELECT id, val from mutable WHERE id = 15")
              .execute(ctx.asyncAssertSuccess(nonTxFound -> {
              // After commiting the transaction, the data should be visible from other connections
              ctx.assertEquals(1, nonTxFound.size());
              Row nonTxRow = nonTxFound.iterator().next();
              ctx.assertEquals(15, nonTxRow.getInteger("id"));
              ctx.assertEquals("wait for it...", nonTxRow.getString("val"));
              async.complete();
            }));
          }));
        }));
      }));
    }));
  }));
}
 

@Test
public void testCreatePool03(TestContext ctx) {
  Pool.pool(defaultOptions(), new PoolOptions().setMaxSize(1)).getConnection(ctx.asyncAssertSuccess(ar -> {
    ar.close();
  }));
}
 

@Test
public void testCreatePool02(TestContext ctx) {
  Pool.pool(new SqlConnectOptions(defaultOptions()), new PoolOptions()).getConnection(ctx.asyncAssertSuccess(ar -> {
    ar.close();
  }));
}
 

@Test
public void testCreatePool04(TestContext ctx) {
  Pool.pool(Vertx.vertx(), defaultOptions(), new PoolOptions()).getConnection(ctx.asyncAssertSuccess(ar -> {
    ar.close();
  }));
}
 

@Override
public Pool createPool(Vertx vertx, SqlConnectOptions options, PoolOptions poolOptions) {
  return MySQLPool.pool(vertx, wrap(options), poolOptions);
}