io.vertx.ext.jdbc.JDBCClient Java Examples

public void example2(ServiceDiscovery discovery) {
  // Get the record
  discovery.getRecord(
      new JsonObject().put("name", "some-data-source-service"),
      ar -> {
        if (ar.succeeded() && ar.result() != null) {
          // Retrieve the service reference
          ServiceReference reference = discovery.getReferenceWithConfiguration(
              ar.result(), // The record
              new JsonObject().put("username", "clement").put("password", "*****")); // Some additional metadata

          // Retrieve the service object
          JDBCClient client = reference.getAs(JDBCClient.class);

          // ...

          // when done
          reference.release();
        }
      });
}
 

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

public void example3(ServiceDiscovery discovery) {
  JDBCDataSource.<JsonObject>getJDBCClient(discovery,
      new JsonObject().put("name", "some-data-source-service"),
      new JsonObject().put("username", "clement").put("password", "*****"), // Some additional metadata
      ar -> {
        if (ar.succeeded()) {
          JDBCClient client = ar.result();

          // ...

          // Dont' forget to release the service
          ServiceDiscovery.releaseServiceObject(discovery, client);

        }
      });
}
 

@Test
public void testWithSugarWithoutConsumerConf() throws InterruptedException {
  Record record = JDBCDataSource.createRecord("some-hsql-db",
      new JsonObject().put("url", "jdbc:hsqldb:file:target/dumb-db;shutdown=true"),
      new JsonObject().put("database", "some-raw-data").put("driverclass", "org.hsqldb.jdbcDriver"));

  discovery.publish(record, (r) -> {
  });
  await().until(() -> record.getRegistration() != null);


  AtomicBoolean success = new AtomicBoolean();
  JDBCDataSource.getJDBCClient(discovery, new JsonObject().put("name", "some-hsql-db"),
      ar -> {
        JDBCClient client = ar.result();
        client.getConnection(conn -> {
          if (ar.succeeded()) {
            conn.result().close();
          }
          client.close();
          success.set(conn.succeeded());
        });
      });
  await().untilAtomic(success, is(true));
}
 

@Bean
JDBCClient vertxJdbcClient(Vertx vertx, StoredRequestsDatabaseProperties storedRequestsDatabaseProperties) {
    final String jdbcUrl = String.format("%s//%s:%d/%s?%s",
            storedRequestsDatabaseProperties.getType().jdbcUrlPrefix,
            storedRequestsDatabaseProperties.getHost(),
            storedRequestsDatabaseProperties.getPort(),
            storedRequestsDatabaseProperties.getDbname(),
            storedRequestsDatabaseProperties.getType().jdbcUrlSuffix);

    return JDBCClient.createShared(vertx, new JsonObject()
            .put("url", jdbcUrl)
            .put("user", storedRequestsDatabaseProperties.getUser())
            .put("password", storedRequestsDatabaseProperties.getPassword())
            .put("driver_class", storedRequestsDatabaseProperties.getType().jdbcDriver)
            .put("initial_pool_size", storedRequestsDatabaseProperties.getPoolSize())
            .put("min_pool_size", storedRequestsDatabaseProperties.getPoolSize())
            .put("max_pool_size", storedRequestsDatabaseProperties.getPoolSize()));
}
 

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

  }
 

private static BasicJdbcClient createBasicJdbcClient(
        Vertx vertx, JDBCClient vertxJdbcClient, Metrics metrics, Clock clock, ContextRunner contextRunner) {
    final BasicJdbcClient basicJdbcClient = new BasicJdbcClient(vertx, vertxJdbcClient, metrics, clock);

    contextRunner.<Void>runOnServiceContext(promise -> basicJdbcClient.initialize().setHandler(promise));

    return basicJdbcClient;
}
 

private void initJdbc(Vertx vertx) {
    populateData();
    jdbcClient = JDBCClient.createShared(vertx, new JsonObject()
        .put("url", URL)
        .put("driver_class", "org.postgresql.Driver")
        .put("user", USER)
        .put("password", PASSWORD)
        .put("max_pool_size", 30));
}
 

private SQLClient createClient(MysqlSideProperties mysqlProperties, Vertx vertx) {
    JsonObject mysqlClientConfig = new JsonObject();
    mysqlClientConfig.put("url", mysqlProperties.getUrl()).put("driver_class", MYSQL_DRIVER)
        .put("max_pool_size",
            mysqlProperties.getMaxPoolSize() == null ? DEFAULT_MAX_DB_CONN_POOL_SIZE
                : mysqlProperties.getMaxPoolSize())
        .put("user", mysqlProperties.getUsername()).put("password", mysqlProperties.getPassword());

    return JDBCClient.createNonShared(vertx, mysqlClientConfig);
}
 

@Bean
@ConditionalOnProperty(prefix = "settings.database.circuit-breaker", name = "enabled", havingValue = "false",
        matchIfMissing = true)
BasicJdbcClient basicJdbcClient(
        Vertx vertx, JDBCClient vertxJdbcClient, Metrics metrics, Clock clock, ContextRunner contextRunner) {

    return createBasicJdbcClient(vertx, vertxJdbcClient, metrics, clock, contextRunner);
}
 

@Bean
@ConditionalOnProperty(prefix = "settings.database.circuit-breaker", name = "enabled", havingValue = "true")
CircuitBreakerSecuredJdbcClient circuitBreakerSecuredJdbcClient(
        Vertx vertx, JDBCClient vertxJdbcClient, Metrics metrics, Clock clock, ContextRunner contextRunner,
        @Qualifier("databaseCircuitBreakerProperties") CircuitBreakerProperties circuitBreakerProperties) {

    final JdbcClient jdbcClient = createBasicJdbcClient(vertx, vertxJdbcClient, metrics, clock, contextRunner);
    return new CircuitBreakerSecuredJdbcClient(vertx, jdbcClient, metrics,
            circuitBreakerProperties.getOpeningThreshold(), circuitBreakerProperties.getOpeningIntervalMs(),
            circuitBreakerProperties.getClosingIntervalMs(), clock);
}
 

public void example51(ServiceDiscovery discovery, Record record, JsonObject conf) {
  ServiceReference reference = discovery.getReferenceWithConfiguration(record, conf);

  // Then, gets the service object, the returned type depends on the service type:
  // For http endpoint:
  JDBCClient client = reference.getAs(JDBCClient.class);

  // Do something with the client...

  // When done with the service
  reference.release();
}
 

@Bean
@ConditionalOnProperty(prefix = "health-check.database", name = "enabled", havingValue = "true")
HealthChecker databaseChecker(Vertx vertx,
                              JDBCClient jdbcClient,
                              @Value("${health-check.database.refresh-period-ms}") long refreshPeriod) {

    return new DatabaseHealthChecker(vertx, jdbcClient, refreshPeriod);
}
 

@Test
public void stopConnectionAttempts() {
  JsonObject config = config().put("acquire_retry_attempts", 1).put("break_after_acquire_failure", true);
  client = JDBCClient.create(vertx, config);
  vertx.setTimer(2000, res -> {
    fail("Should not get invoked");
  });
  client.getConnection(onFailure(t -> {
    assertThat(t, instanceOf(SQLException.class));
    testComplete();
  }));
  await();
}
 

/**
 * Starts the verticle asynchronously. The the initialization is completed, it calls
 * `complete()` on the given {@link Future} object. If something wrong happens,
 * `fail` is called.
 *
 * @param future the future to indicate the completion
 */
@Override
public void start(Future<Void> future) throws ClassNotFoundException {
    super.start();

    // Get configuration
    config = ConfigFactory.load();

    // creates the jdbc client.
    JsonObject jdbcConfig = new JsonObject(config.getObject("jdbc").render(ConfigRenderOptions.concise()));
    jdbc = JDBCClient.createNonShared(vertx, jdbcConfig);
    Class.forName(jdbcConfig.getString("driverclass"));

    // Start HTTP server and listen for portfolio events
    EventBus eventBus = vertx.eventBus();
    Future<HttpServer> httpEndpointReady = configureTheHTTPServer();
    httpEndpointReady.setHandler(ar -> {
       if (ar.succeeded()) {
           MessageConsumer<JsonObject> portfolioConsumer = eventBus.consumer(config.getString("portfolio.address"));
           portfolioConsumer.handler(message -> {
               storeInDatabase(message.body());
           });
           future.complete();
       } else {
           future.fail(ar.cause());
       }
    });

    publishHttpEndpoint("audit", config.getString("http.host"), config.getInt("http.public.port"), config.getString("http.root"), ar -> {
        if (ar.failed()) {
            ar.cause().printStackTrace();
        } else {
            System.out.println("Audit (Rest endpoint) service published : " + ar.succeeded());
        }
    });
}
 

@Override
public void start(Future<Void> fut) throws Exception {
	LOG.info("start DATA Verticle ...");
	thisVertxName = System.getProperty("thisVertxName", "VX-API");
	initShorthand();// 初始化简写后的常量数据
	JsonObject dbConfig = config();
	String url = dbConfig.getString("url");
	if (dbConfig.getString("url").contains("jdbc:sqlite:")) {
		String temp = url.replace("jdbc:sqlite:", "");
		if (temp.indexOf("/") < 0) {
			dbConfig.put("url", "jdbc:sqlite:" + PathUtil.getPathString(temp));
		}
	}
	jdbcClient = JDBCClient.createShared(vertx, dbConfig, VxApiGatewayAttribute.NAME);

	// application operation address
	vertx.eventBus().consumer(thisVertxName + VxApiEventBusAddressConstant.FIND_APP, this::findApplication);
	vertx.eventBus().consumer(thisVertxName + VxApiEventBusAddressConstant.GET_APP, this::getApplication);
	vertx.eventBus().consumer(thisVertxName + VxApiEventBusAddressConstant.ADD_APP, this::addApplication);
	vertx.eventBus().consumer(thisVertxName + VxApiEventBusAddressConstant.DEL_APP, this::delApplication);
	vertx.eventBus().consumer(thisVertxName + VxApiEventBusAddressConstant.UPDT_APP, this::updtApplication);
	// api operation address
	vertx.eventBus().consumer(thisVertxName + VxApiEventBusAddressConstant.FIND_API_ALL, this::findAPI);
	vertx.eventBus().consumer(thisVertxName + VxApiEventBusAddressConstant.FIND_API_BY_PAGE, this::findAPIByPage);
	vertx.eventBus().consumer(thisVertxName + VxApiEventBusAddressConstant.GET_API, this::getAPI);
	vertx.eventBus().consumer(thisVertxName + VxApiEventBusAddressConstant.ADD_API, this::addAPI);
	vertx.eventBus().consumer(thisVertxName + VxApiEventBusAddressConstant.DEL_API, this::delAPI);
	vertx.eventBus().consumer(thisVertxName + VxApiEventBusAddressConstant.UPDT_API, this::updtAPI);
	// blacklist
	vertx.eventBus().consumer(thisVertxName + VxApiEventBusAddressConstant.FIND_BLACKLIST, this::findBlacklist);
	vertx.eventBus().consumer(thisVertxName + VxApiEventBusAddressConstant.REPLACE_BLACKLIST, this::replaceBlacklist);
	LOG.info("start DATA Verticle successful");
	fut.complete();
}
 

private JdbcClient jdbcClient() {
    return new BasicJdbcClient(vertx, JDBCClient.createShared(vertx,
            new JsonObject()
                    .put("url", JDBC_URL)
                    .put("driver_class", "org.h2.Driver")
                    .put("max_pool_size", 10)), metrics, clock
    );
}
 

@Test
public void continuingConnectionAttempts() {
  client = JDBCClient.create(vertx, config());
  vertx.setTimer(2000, res -> {
    testComplete();
  });
  client.getConnection(ar -> {
    if (ar.succeeded()) {
      fail("Should not succeed");
    }
  });
  await();
}
 

@Override
public AuthProvider create(Vertx vertx, JsonObject config) {
  final JDBCAuthOptions options = new JDBCAuthOptions(config);
  final JDBCClient client;

  if (options.isShared()) {
    String datasourceName = options.getDatasourceName();
    if (datasourceName != null) {
      client = JDBCClient.createShared(vertx, options.getConfig(), datasourceName);
    } else {
      client = JDBCClient.createShared(vertx, options.getConfig());
    }
  } else {
    client = JDBCClient.create(vertx, options.getConfig());
  }

  final JDBCAuth auth = JDBCAuth.create(vertx, client);

  if (options.getAuthenticationQuery() != null) {
    auth.setAuthenticationQuery(options.getAuthenticationQuery());
  }
  if (options.getRolesQuery() != null) {
    auth.setRolesQuery(options.getRolesQuery());
  }
  if (options.getPermissionsQuery() != null) {
    auth.setPermissionsQuery(options.getPermissionsQuery());
  }
  if (options.getRolesPrefix() != null) {
    auth.setRolePrefix(options.getRolesPrefix());
  }
  return auth;
}
 

/**
 * Convenient method that looks for a JDBC datasource source and provides the configured {@link io.vertx.ext.jdbc.JDBCClient}. The
 * async result is marked as failed is there are no matching services, or if the lookup fails.
 *
 * @param discovery     The service discovery instance
 * @param filter        The filter, optional
 * @param resultHandler The result handler
 */
static void getJDBCClient(ServiceDiscovery discovery, JsonObject filter,
                          Handler<AsyncResult<JDBCClient>> resultHandler) {
  discovery.getRecord(filter, ar -> {
    if (ar.failed() || ar.result() == null) {
      resultHandler.handle(Future.failedFuture("No matching record"));
    } else {
      resultHandler.handle(Future.succeededFuture(discovery.<JDBCClient>getReference(ar.result()).get()));
    }
  });
}
 

/**
 * Convenient method that looks for a JDBC datasource source and provides the configured {@link io.vertx.ext.jdbc.JDBCClient}. The
 * async result is marked as failed is there are no matching services, or if the lookup fails.
 *
 * @param discovery     The service discovery instance
 * @param filter        The filter (must not be {@code null})
 * @param resultHandler The result handler
 */
static void getJDBCClient(ServiceDiscovery discovery, Function<Record, Boolean> filter,
                          Handler<AsyncResult<JDBCClient>> resultHandler) {
  discovery.getRecord(filter, ar -> {
    if (ar.failed() || ar.result() == null) {
      resultHandler.handle(Future.failedFuture("No matching record"));
    } else {
      resultHandler.handle(Future.succeededFuture(discovery.<JDBCClient>getReference(ar.result()).get()));
    }
  });
}
 

/**
 * Convenient method that looks for a JDBC datasource source and provides the configured {@link io.vertx.ext.jdbc.JDBCClient}. The
 * async result is marked as failed is there are no matching services, or if the lookup fails.
 *
 * @param discovery             The service discovery instance
 * @param filter                The filter, optional
 * @param consumerConfiguration the consumer configuration
 * @param resultHandler         the result handler
 */
static void getJDBCClient(ServiceDiscovery discovery, JsonObject filter, JsonObject consumerConfiguration,
                          Handler<AsyncResult<JDBCClient>> resultHandler) {
  discovery.getRecord(filter, ar -> {
    if (ar.failed() || ar.result() == null) {
      resultHandler.handle(Future.failedFuture("No matching record"));
    } else {
      resultHandler.handle(Future.succeededFuture(
          discovery.<JDBCClient>getReferenceWithConfiguration(ar.result(), consumerConfiguration).get()));
    }
  });
}
 

/**
 * Convenient method that looks for a JDBC datasource source and provides the configured {@link io.vertx.ext.jdbc.JDBCClient}. The
 * async result is marked as failed is there are no matching services, or if the lookup fails.
 *
 * @param discovery             The service discovery instance
 * @param filter                The filter, must not be {@code null}
 * @param consumerConfiguration the consumer configuration
 * @param resultHandler         the result handler
 */
static void getJDBCClient(ServiceDiscovery discovery, Function<Record, Boolean> filter, JsonObject consumerConfiguration,
                          Handler<AsyncResult<JDBCClient>> resultHandler) {
  discovery.getRecord(filter, ar -> {
    if (ar.failed() || ar.result() == null) {
      resultHandler.handle(Future.failedFuture("No matching record"));
    } else {
      resultHandler.handle(Future.succeededFuture(
        discovery.<JDBCClient>getReferenceWithConfiguration(ar.result(), consumerConfiguration).get()));
    }
  });
}
 

@Override
public JDBCClient retrieve() {
  JsonObject result = record().getMetadata().copy();
  result.mergeIn(record().getLocation());

  if (config != null) {
    result.mergeIn(config);
  }

  if (result.getBoolean("shared", false)) {
    return JDBCClient.createShared(vertx, result);
  } else {
    return JDBCClient.create(vertx, result);
  }
}
 

@Test
public void test() throws InterruptedException {
  JsonObject conf = new JsonObject()
      .put("driverclass", "org.hsqldb.jdbcDriver");

  Record record = JDBCDataSource.createRecord("some-hsql-db",
      new JsonObject().put("url", "jdbc:hsqldb:file:target/dumb-db;shutdown=true"),
      new JsonObject().put("database", "some-raw-data"));

  discovery.publish(record, (r) -> {
  });
  await().until(() -> record.getRegistration() != null);

  AtomicReference<Record> found = new AtomicReference<>();
  discovery.getRecord(new JsonObject().put("name", "some-hsql-db"), ar -> {
    found.set(ar.result());
  });

  await().until(() -> found.get() != null);

  ServiceReference service = discovery.getReferenceWithConfiguration(found.get(), conf);
  JDBCClient client = service.get();
  AtomicBoolean success = new AtomicBoolean();
  client.getConnection(ar -> {
    if (ar.succeeded()) {
      ar.result().close();
    }
    success.set(ar.succeeded());
  });

  await().untilAtomic(success, is(true));
  service.release();
  // Just there to be sure we can call it twice
  service.release();
}
 

@Test
public void testWithSugar() throws InterruptedException {
  JsonObject conf = new JsonObject()
      .put("driverclass", "org.hsqldb.jdbcDriver");

  Record record = JDBCDataSource.createRecord("some-hsql-db",
      new JsonObject().put("url", "jdbc:hsqldb:file:target/dumb-db;shutdown=true"),
      new JsonObject().put("database", "some-raw-data"));

  discovery.publish(record, (r) -> {
  });
  await().until(() -> record.getRegistration() != null);


  AtomicBoolean success = new AtomicBoolean();
  JDBCDataSource.getJDBCClient(discovery, new JsonObject().put("name", "some-hsql-db"), conf,
      ar -> {
        JDBCClient client = ar.result();
        client.getConnection(conn -> {
          if (ar.succeeded()) {
            conn.result().close();
          }
          client.close();
          success.set(conn.succeeded());
        });
      });
  await().untilAtomic(success, is(true));

}
 

@Override
public JDBCAuth createProvider(Vertx vertx) {
  JDBCClient client;
  if (shared) {
    if (datasourceName != null) {
      client = JDBCClient.createShared(vertx, config, datasourceName);
    } else {
      client = JDBCClient.createShared(vertx, config);
    }
  } else {
    client = JDBCClient.create(vertx, config);
  }
  JDBCAuth auth = JDBCAuth.create(vertx, client);
  if (authenticationQuery != null) {
    auth.setAuthenticationQuery(authenticationQuery);
  }
  if (rolesQuery != null) {
    auth.setRolesQuery(rolesQuery);
  }
  if (permissionsQuery != null) {
    auth.setPermissionsQuery(permissionsQuery);
  }
  if (rolesPrefix != null) {
    auth.setRolePrefix(rolesPrefix);
  }
  return auth;
}
 

public JDBCAuthImpl(Vertx vertx, JDBCClient client) {
  this.client = client;
  this.hashStrategy = JDBCHashStrategy.createSHA512(vertx);
  this.authenticationOptions = new JDBCAuthenticationOptions();
  this.authorizationOptions = new JDBCAuthorizationOptions();
  this.authenticationProvider = JDBCAuthentication.create(client, hashStrategy, authenticationOptions);
  this.authorizationProvider = JDBCAuthorization.create("jdbc-auth", client, authorizationOptions);
}
 

private void createAuthHandlers() {
  String auth = json.getString("auth-type");
  JsonObject authProperties = json.getJsonObject("auth-properties");

  // TODO : discuss it. I'm really not convinced about all the boilerplate needed in config (dbName only for JDBC, etc.)
  if (authProperties != null) {
    // For now, only JWT,Shiro and JDBC supported (same as for Vert.x web)
    switch (auth) {
      case "JWT":// For now only allow properties realm
        this.authProvider = JWTAuth.create(vertx, authProperties);
        break;
      case "Shiro":
        ShiroAuth.create(vertx, new ShiroAuthOptions(authProperties));
        break;
      case "JDBC":
        String dbName = json.getString("db-name");
        Objects.requireNonNull(dbName);
        JDBCClient client = JDBCClient.createShared(vertx, authProperties, dbName);
        this.authProvider = JDBCAuth.create(vertx, client);
        break;
      default:
        LOG.warn("Unknown type of auth : " + auth + " . Ignoring.");
    }
  } else if (auth != null) {
    LOG.warn("You have defined " + auth + " as auth type, but didn't provide any configuration, can't create authProvider");
  }

}
 

public static SQLClient dataSource(final Vertx vertx, final JdbcProperties dataSourceProperties) {

        /*
         * In the following lines we explicitly set the "provider_class" and **must not** use
         * the existing constant for that.
         *
         * The reason for this is, that the downstream version changes the value of the constant to
         * use Agroal as the connection pool. As C3P0 is the upstream default for Vert.x and Agroal
         * isn't even mentioned upstream, we explicitly set C3P0 here. Without using the (not so) constant.
         */

        final JsonObject config = new JsonObject()
                // set default explicitly: see comment above
                .put("provider_class", "io.vertx.ext.jdbc.spi.impl.C3P0DataSourceProvider")
                .put("url", dataSourceProperties.getUrl())
                .put("user", dataSourceProperties.getUsername())
                .put("password", dataSourceProperties.getPassword());

        if (dataSourceProperties.getDriverClass() != null) {
            config.put("driver_class", dataSourceProperties.getDriverClass());
        }
        if (dataSourceProperties.getMaximumPoolSize() != null) {
            config.put("max_pool_size", dataSourceProperties.getMaximumPoolSize());
        }

        log.info("Creating new SQL client: {} - table: {}", config, dataSourceProperties.getTableName());

        return JDBCClient.createShared(vertx, config);

    }