org.jdbi.v3.core.Jdbi Java Examples

private void createSampleResources(IronTestConfiguration configuration, Environment environment) {
    final JdbiFactory jdbiFactory = new JdbiFactory();
    final Jdbi jdbi = jdbiFactory.build(environment, configuration.getSampleDatabase(), "sampleDatabase");

    //  create DAO objects
    final ArticleDAO articleDAO = jdbi.onDemand(ArticleDAO.class);

    //  create database tables
    articleDAO.createTableIfNotExists();

    //  register APIs
    environment.jersey().register(new ArticleResource(articleDAO));

    //  register SOAP web services
    jaxWsBundle.publishEndpoint(new EndpointBuilder("/article", new ArticleSOAP(articleDAO)));
}
 

/**
 * Try to load a Trellis resource.
 * @param jdbi the Jdbi object
 * @param identifier the identifier
 * @param extensions a map of extensions
 * @param includeLdpType whether to include the LDP type in the RDF body
 * @param supportDirectContainment whether to support direct containment
 * @param supportIndirectContainment whether to support indirect containment
 * @return a Resource, if one exists
 */
public static CompletionStage<Resource> findResource(final Jdbi jdbi, final IRI identifier,
        final Map<String, IRI> extensions, final boolean includeLdpType, final boolean supportDirectContainment,
        final boolean supportIndirectContainment) {
    return supplyAsync(() -> {
        final DBResource res = new DBResource(jdbi, identifier, extensions, includeLdpType,
                supportDirectContainment, supportIndirectContainment);
        if (!res.fetchData()) {
            return MISSING_RESOURCE;
        }
        if (res.isDeleted()) {
            return DELETED_RESOURCE;
        }
        return res;
    });
}
 

public static ErrorReportController build(final AppConfig configuration, final Jdbi jdbi) {
  final boolean isTest = configuration.getGithubApiToken().equals("test");

  final GithubIssueClient githubIssueClient =
      GithubIssueClient.builder()
          .uri(AppConfig.GITHUB_WEB_SERVICE_API_URL)
          .authToken(configuration.getGithubApiToken())
          .githubOrg(AppConfig.GITHUB_ORG)
          .githubRepo(configuration.getGithubRepo())
          .isTest(isTest)
          .build();

  if (isTest) {
    Preconditions.checkState(!configuration.isProd());
  }

  return ErrorReportController.builder()
      .errorReportIngestion(CreateIssueStrategy.build(githubIssueClient, jdbi))
      .build();
}
 

@Override
protected Statement classBlock(RunNotifier notifier) {
    final Statement statement = super.classBlock(notifier);
    return new Statement() {
        @Override
        public void evaluate() throws Throwable {
            // Open a new handle for every test
            // It affords to avoid creating a static state which makes tests more independent
            JditProperties jditProperties = klass.getAnnotation(JditProperties.class);
            Jdbi dbi = jditProperties != null ? DBIContextFactory.getDBI(jditProperties.value()) : DBIContextFactory.getDBI();
            try (Handle handle = dbi.open()) {
                injector = new TestObjectsInjector(dbi, handle);
                databaseMaintenance = DatabaseMaintenanceFactory.create(handle);
                dataSetInjector = new DataSetInjector(new DataMigration(handle));
                statement.evaluate();
            }
        }
    };
}
 

public MysqlSchemaArchiver createArchiver(String sourceName) {
  MysqlSourceMetrics metrics = new MysqlSourceMetrics(sourceName, new TaggedMetricRegistry());
  Jdbi jdbi =
      Jdbi.create(
          MysqlClient.createMysqlDataSource(
              configuration.getHost(),
              configuration.getPort(),
              username,
              password,
              configuration.isMTlsEnabled(),
              tlsConfiguration));
  MysqlSchemaStore schemaStore =
      new MysqlSchemaStore(
          sourceName,
          configuration.getDatabase(),
          configuration.getArchiveDatabase(),
          jdbi,
          metrics);
  MysqlSchemaDatabase schemaDatabase = new MysqlSchemaDatabase(sourceName, jdbi, metrics);

  return new MysqlSchemaManager(sourceName, schemaStore, schemaDatabase, null, null, true);
}
 

/**
 * Registers all JDBI row mappers. These are classes that map result set values to corresponding
 * return objects.
 */
public static void registerRowMappers(final Jdbi jdbi) {
  jdbi.registerRowMapper(AccessLogRecord.class, AccessLogRecord.buildResultMapper());
  jdbi.registerRowMapper(BanLookupRecord.class, BanLookupRecord.buildResultMapper());
  jdbi.registerRowMapper(ChatHistoryRecord.class, ChatHistoryRecord.buildResultMapper());
  jdbi.registerRowMapper(
      PlayerIdentifiersByApiKeyLookup.class, PlayerIdentifiersByApiKeyLookup.buildResultMapper());
  jdbi.registerRowMapper(
      PlayerApiKeyLookupRecord.class, PlayerApiKeyLookupRecord.buildResultMapper());
  jdbi.registerRowMapper(PlayerAliasRecord.class, PlayerAliasRecord.buildResultMapper());
  jdbi.registerRowMapper(PlayerHistoryRecord.class, PlayerHistoryRecord.buildResultMapper());
  jdbi.registerRowMapper(PlayerBanRecord.class, PlayerBanRecord.buildResultMapper());
  jdbi.registerRowMapper(UserBanRecord.class, UserBanRecord.buildResultMapper());
  jdbi.registerRowMapper(UsernameBanRecord.class, UsernameBanRecord.buildResultMapper());
  jdbi.registerRowMapper(UserRoleLookup.class, UserRoleLookup.buildResultMapper());
  jdbi.registerRowMapper(
      ModeratorAuditHistoryRecord.class, ModeratorAuditHistoryRecord.buildResultMapper());
  jdbi.registerRowMapper(ModeratorUserDaoData.class, ModeratorUserDaoData.buildResultMapper());
}
 

@Test
public void whenIdentityColumn_thenInsertReturnsNewId() {
    Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");
    jdbi.useHandle(handle -> {
        handle.execute("create table PROJECT_2 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))");
        Update update = handle.createUpdate(
                "INSERT INTO PROJECT_2 (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials')");
        ResultBearing generatedKeys = update.executeAndReturnGeneratedKeys();

        assertEquals(0, generatedKeys.mapToMap().findOnly().get("id"));

        update = handle.createUpdate(
                "INSERT INTO PROJECT_2 (NAME, URL) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')");

        assertEquals(1, generatedKeys.mapToMap().findOnly().get("id"));
    });
}
 

@BeforeMethod
public void setupTest()
{
    queryRunner.getCoordinator().getSessionPropertyDefaults()
            .setConfigurationManager("db-test", ImmutableMap.<String, String>builder()
                    .put("session-property-manager.db.url", mysqlContainer.getJdbcUrl())
                    .put("session-property-manager.db.username", mysqlContainer.getUsername())
                    .put("session-property-manager.db.password", mysqlContainer.getPassword())
                    .build());

    MysqlDataSource dataSource = new MysqlDataSource();
    dataSource.setURL(mysqlContainer.getJdbcUrl());
    dataSource.setUser(mysqlContainer.getUsername());
    dataSource.setPassword(mysqlContainer.getPassword());
    dao = Jdbi.create(dataSource)
            .installPlugin(new SqlObjectPlugin())
            .onDemand(SessionPropertiesDao.class);
}
 

@Inject
public SessionPropertiesDaoProvider(DbSessionPropertyManagerConfig config)
{
    requireNonNull(config, "config is null");
    String url = requireNonNull(config.getConfigDbUrl(), "db url is null");

    MysqlDataSource dataSource = new MysqlDataSource();
    dataSource.setURL(url);

    Optional<String> username = Optional.ofNullable(config.getUsername());
    username.ifPresent(dataSource::setUser);

    Optional<String> password = Optional.ofNullable(config.getPassword());
    password.ifPresent(dataSource::setPassword);

    this.dao = Jdbi.create(dataSource)
            .installPlugin(new SqlObjectPlugin())
            .onDemand(SessionPropertiesDao.class);
}
 

/**
 * Inject a DBI instance to a field with {@link DBIInstance} annotation.
 *
 * @param test  current test
 * @param field current field
 * @throws IllegalAccessException reflection error
 */
private void handleDbiInstance(Object test, Field field) throws IllegalAccessException {
    if (!field.getType().equals(Jdbi.class)) {
        throw new IllegalArgumentException("Unable inject a DBI instance to " +
                "a field with type " + field.getType());
    }
    if (Modifier.isStatic(field.getModifiers())) {
        throw new IllegalArgumentException("Unable inject a DBI instance to a static field");
    }
    field.setAccessible(true);
    field.set(test, dbi);
}
 

@Override
public void run(IronTestConfiguration configuration, Environment environment) throws IOException {
    final JdbiFactory jdbiFactory = new JdbiFactory();
    final Jdbi systemDBJdbi = jdbiFactory.build(environment, configuration.getSystemDatabase(), "systemDatabase");

    if (!checkVersion(systemDBJdbi)) {
        System.out.println("Press Enter to exit.");
        System.in.read();
        System.exit(0);
    }

    //  Override Java's trusted cacerts with our own trust store if available.
    //  Notice that setting the properties without the trust store being existing could cause unexpected result
    //  at runtime with Java 10 (Java 1.8 does not have the issue), such as failure of SOAP test step run (caused
    //  by 'new SSLContextBuilder().loadTrustMaterial').
    if (new File(configuration.getSslTrustStorePath()).exists()) {
        System.setProperty("javax.net.ssl.trustStore", configuration.getSslTrustStorePath());
        System.setProperty("javax.net.ssl.trustStorePassword", configuration.getSslTrustStorePassword());
    }

    //  start WireMock server (in the same JVM)
    WireMockServer wireMockServer = new WireMockServer(options()
            .extensions(new ResponseTemplateTransformer(true))
            .port(Integer.parseInt(configuration.getWireMock().get("port")))
            .maxRequestJournalEntries(Integer.parseInt(configuration.getWireMock().get("maxRequestJournalEntries")))
            .notifier(new WireMockFileNotifier())
    );
    wireMockServer.start();

    createSystemResources(configuration, environment, systemDBJdbi, wireMockServer);
    createSampleResources(configuration, environment);
}
 

public static PlayerApiKeyDaoWrapper build(final Jdbi jdbi) {
  return PlayerApiKeyDaoWrapper.builder()
      .lobbyApiKeyDao(jdbi.onDemand(PlayerApiKeyDao.class))
      .userJdbiDao(jdbi.onDemand(UserJdbiDao.class))
      .userRoleDao(jdbi.onDemand(UserRoleDao.class))
      .keyMaker(ApiKey::newKey)
      .keyHashingFunction(new ApiKeyHasher())
      .build();
}
 

private static void enableAuthentication(
    final Environment environment, final MetricRegistry metrics, final Jdbi jdbi) {
  environment
      .jersey()
      .register(
          new AuthDynamicFeature(
              new OAuthCredentialAuthFilter.Builder<AuthenticatedUser>()
                  .setAuthenticator(buildAuthenticator(metrics, jdbi))
                  .setAuthorizer(new RoleAuthorizer())
                  .setPrefix(AuthenticationHeaders.KEY_BEARER_PREFIX)
                  .buildAuthFilter()));
  environment.jersey().register(new AuthValueFactoryProvider.Binder<>(AuthenticatedUser.class));
}
 

public TestService(Jdbi db) {
    this.db = db;
    db.useHandle(handle -> {
        handle.execute("CREATE TABLE IF NOT EXISTS foo(id INTEGER);");
        handle.execute("INSERT INTO foo(id) VALUES (0);");
    });

}
 

public static void main(final String[] args) {
  Driver.load();
  final Jdbi dbi = Jdbi.create("jdbc:h2:mem:dbi", "sa", "");
  try (final Handle handle = dbi.open()) {
    handle.execute("CREATE TABLE employer (id INTEGER)");
  }

  TestUtil.checkSpan(new ComponentSpanCount("java-jdbc", 2));
}
 

@PostConstruct
public void postConstruct() {
    logger.debug("JDBC Storage constructed successfully.");

    jdbi = Jdbi.create(dataSource);
    
    this.shareForEveryone = config.isShareForEveryone();
    
    if (config.isJdbcInit()) {
        synchronized (dbMutex) {
            if (!isDatabaseInitialized()) {
                logger.debug("Database not initialized.");
                initializeDatabase();
            } else {
                logger.debug("Database was already initialized, skipping.");
            }
            
            if (!isDatabaseCurrent()) {
                logger.debug("Old database version detected, upgrading.");
                upgradeDatabase();
            }
        }
    } else {
        if (!isDatabaseInitialized()) {
            logger.error("Database not initialized.  Please use the DDL scripts to initialize the database before starting the application.");
            throw new RuntimeException("Database not initialized.");
        }
        
        if (!isDatabaseCurrent()) {
            logger.error("Detected an old version of the database.  Please use the DDL upgrade scripts to bring your database up to date.");
            throw new RuntimeException("Database not upgraded.");
        }
    }
}
 

private Jdbi createJdbi(final AppConfig configuration, final Environment environment) {
  final JdbiFactory factory = new JdbiFactory();
  final Jdbi jdbi =
      factory.build(environment, configuration.getDatabase(), "postgresql-connection-pool");
  JdbiDatabase.registerRowMappers(jdbi);

  if (configuration.isLogSqlStatements()) {
    JdbiDatabase.registerSqlLogger(jdbi);
  }
  return jdbi;
}
 

@Test
public void whenMultipleResults_thenFindFirstReturnsFirst() {
    Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");
    jdbi.useHandle(handle -> {
        handle.execute("create table PROJECT_7 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))");
        handle.execute("INSERT INTO PROJECT_7 (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials')");
        handle.execute("INSERT INTO PROJECT_7 (NAME, URL) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')");

        Query query = handle.createQuery("select * from project_7 order by id");
        Optional<Map<String, Object>> first = query.mapToMap().findFirst();

        assertTrue(first.isPresent());
        assertEquals("tutorials", first.get().get("name"));
    });
}
 

public static UserBanController build(
    final Jdbi jdbi,
    final Chatters chatters,
    final WebSocketMessagingBus chatMessagingBus,
    final WebSocketMessagingBus gameMessagingBus) {
  return UserBanController.builder()
      .bannedUsersService(
          UserBanService.builder()
              .jdbi(jdbi)
              .chatters(chatters)
              .chatMessagingBus(chatMessagingBus)
              .gameMessagingBus(gameMessagingBus)
              .build())
      .build();
}
 

public static GameListing build(final Jdbi jdbi, final WebSocketMessagingBus playerMessagingBus) {
  return GameListing.builder()
      .lobbyGameDao(jdbi.onDemand(LobbyGameDao.class))
      .auditHistoryDao(jdbi.onDemand(ModeratorAuditHistoryDao.class))
      .playerMessagingBus(playerMessagingBus)
      .games(
          new ExpiringAfterWriteCache<>(
              GameListingClient.KEEP_ALIVE_SECONDS,
              TimeUnit.SECONDS,
              new GameTtlExpiredListener(playerMessagingBus)))
      .build();
}
 

/**
 * Create a Database-backed resource service.
 * @param jdbi the jdbi object
 */
public DBResourceService(final Jdbi jdbi) {
    this(jdbi, getConfig().getOptionalValue(CONFIG_JDBC_BATCH_SIZE, Integer.class).orElse(DEFAULT_BATCH_SIZE),
            getConfig().getOptionalValue(CONFIG_JDBC_LDP_TYPE, Boolean.class).orElse(Boolean.TRUE),
            getConfig().getOptionalValue(CONFIG_JDBC_DIRECT_CONTAINMENT, Boolean.class).orElse(Boolean.TRUE),
            getConfig().getOptionalValue(CONFIG_JDBC_INDIRECT_CONTAINMENT, Boolean.class).orElse(Boolean.TRUE),
            of(load(IdentifierService.class)).map(ServiceLoader::iterator).filter(Iterator::hasNext)
                .map(Iterator::next).orElseGet(DefaultIdentifierService::new));
}
 

public static GameHostingApiKeyDaoWrapper build(final Jdbi jdbi) {
  return GameHostingApiKeyDaoWrapper.builder()
      .gameHostApiKeyDao(jdbi.onDemand(GameHostingApiKeyDao.class))
      .keyMaker(ApiKey::newKey)
      .keyHashingFunction(new ApiKeyHasher())
      .build();
}
 

/**
 * Create and configure a {@link Jdbi} instance from the properties
 * The DB is created during the first connection.
 *
 * @param properties configuration of DB
 * @return a new {@link Jdbi} instance for performing database access
 */
private Jdbi createDBI(Properties properties) {
    try {
        DBIFactory dbiFactory = (DBIFactory) Class.forName(properties.getProperty("dbi.factory"))
                .newInstance();
        return dbiFactory.createDBI(properties);
    } catch (Exception e) {
        throw new IllegalStateException("Unable instantiate DBI Factory", e);
    }
}
 

@Test
public void testMapToDbObject() throws Exception {
    Jdbi dbi = Jdbi.create(DbHelper.getHsqlDataSource());
    dbi.installPlugins();
    Handle handle = dbi.open();
    try {
        DbObject dbObject = handle.createQuery(DbHelper.TEST_DB_OBJECT_QUERY).mapTo(DbObject.class).findFirst().get();
        DbHelper.assertDbObjectMapping(dbObject);
    } finally {
        handle.close();
    }
}
 

@Test
void getMembershipQuads() {
    assertAll(
        () -> DBResource.findResource(Jdbi.create(pg.getPostgresDatabase()), root, extensions, false, true, true)
            .thenAccept(res ->
                assertEquals(0L, res.stream(LDP.PreferMembership).count())).toCompletableFuture().join(),
        () -> DBResource.findResource(Jdbi.create(pg.getPostgresDatabase()), root, extensions, false, false, false)
            .thenAccept(res ->
                assertEquals(0L, res.stream(LDP.PreferMembership).count())).toCompletableFuture().join());
}
 

@Builder
public UserBanService(
    final Jdbi jdbi,
    final Chatters chatters,
    final WebSocketMessagingBus chatMessagingBus,
    final WebSocketMessagingBus gameMessagingBus) {
  moderatorAuditHistoryDao = jdbi.onDemand(ModeratorAuditHistoryDao.class);
  userBanDao = jdbi.onDemand(UserBanDao.class);
  publicIdSupplier = () -> UUID.randomUUID().toString();
  this.chatters = chatters;
  this.apiKeyDaoWrapper = PlayerApiKeyDaoWrapper.build(jdbi);
  this.chatMessagingBus = chatMessagingBus;
  this.gameMessagingBus = gameMessagingBus;
}
 

@Test
public void whenSelectMapToString_thenResultIsString() {
    Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");
    jdbi.useHandle(handle -> {
        handle.execute("create table PROJECT_4 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))");
        handle.execute("INSERT INTO PROJECT_4 (NAME, URL) VALUES ('tutorials', 'github.com/eugenp/tutorials')");
        handle.execute("INSERT INTO PROJECT_4 (NAME, URL) VALUES ('REST with Spring', 'github.com/eugenp/REST-With-Spring')");
        Query query = handle.createQuery("select name, url from PROJECT_4 order by id");
        List<String> list = query.mapTo(String.class).list();

        assertEquals("tutorials", list.get(0));
        assertEquals("REST with Spring", list.get(1));
    });
}
 

@Test
public void whenNoResults_thenFindOnlyThrows() {
    Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");
    jdbi.useHandle(handle -> {
        handle.execute("create table PROJECT_8 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))");

        try {
            handle.createQuery("select * from project_8").mapToMap().findOnly();

            fail("Exception expected");
        } catch (Exception e) {
            e.printStackTrace();
        }
    });
}
 

private void upgradeSystemDB(String ironTestHome, String fullyQualifiedSystemDBURL, String user, String password,
                             List<ResourceFile> applicableSystemDBUpgrades, Path oldDir, Path newDir,
                             DefaultArtifactVersion jarFileVersion)
        throws IOException {
    Path oldDatabaseFolder = Files.createDirectory(Paths.get(oldDir.toString(), "database"));
    Path newDatabaseFolder = Files.createDirectory(Paths.get(newDir.toString(), "database"));
    String systemDBFileName = getSystemDBFileName(fullyQualifiedSystemDBURL);

    Path sourceFile = Paths.get(ironTestHome, "database", systemDBFileName);
    Path targetOldFile = Paths.get(oldDatabaseFolder.toString(), systemDBFileName);
    Path targetNewFile = Paths.get(newDatabaseFolder.toString(), systemDBFileName);
    Files.copy(sourceFile, targetOldFile);
    LOGGER.info("Copied current system database to " + oldDatabaseFolder.toString());
    Files.copy(sourceFile, targetNewFile);
    LOGGER.info("Copied current system database to " + newDatabaseFolder.toString());

    String newSystemDBURL = "jdbc:h2:" + targetNewFile.toString().replace(".mv.db", "") + ";IFEXISTS=TRUE";
    Jdbi jdbi = Jdbi.create(newSystemDBURL, user, password);

    //  run SQL scripts against the system database in the 'new' folder
    for (ResourceFile sqlFile: applicableSystemDBUpgrades) {
        try (InputStream is = getClass().getClassLoader().getResourceAsStream(sqlFile.getResourcePath())) {
            String sqlScript = IOUtils.toString(is, StandardCharsets.UTF_8.name());
            jdbi.withHandle(handle -> handle.createScript(sqlScript).execute());
        }
        LOGGER.info("Executed SQL script " + sqlFile.getResourcePath() + " in " + newSystemDBURL + ".");
    }

    //  update Version table
    jdbi.withHandle(handle -> handle
            .createUpdate("update version set version = ?, updated = CURRENT_TIMESTAMP")
            .bind(0, jarFileVersion.toString())
            .execute());
    LOGGER.info("Updated Version to " + jarFileVersion + " in " + newSystemDBURL + ".");
}
 

@Override
public void run(final AppConfig configuration, final Environment environment) {
  if (configuration.isLogRequestAndResponses()) {
    enableRequestResponseLogging(environment);
  }

  final MetricRegistry metrics = new MetricRegistry();
  final Jdbi jdbi = createJdbi(configuration, environment);

  environment.jersey().register(BannedPlayerFilter.newBannedPlayerFilter(jdbi));
  environment.jersey().register(new RolesAllowedDynamicFeature());
  enableAuthentication(environment, metrics, jdbi);

  exceptionMappers().forEach(mapper -> environment.jersey().register(mapper));

  final var sessionIsBannedCheck = SessionBannedCheck.build(jdbi);
  final var gameConnectionMessagingBus = new WebSocketMessagingBus();
  setupWebSocket(gameConnectionWebsocket, gameConnectionMessagingBus, sessionIsBannedCheck);

  final var playerConnectionMessagingBus = new WebSocketMessagingBus();
  setupWebSocket(playerConnectionWebsocket, playerConnectionMessagingBus, sessionIsBannedCheck);

  final var chatters = Chatters.build();
  ChatMessagingService.build(chatters, jdbi).configure(playerConnectionMessagingBus);

  endPointControllers(
          configuration, jdbi, chatters, playerConnectionMessagingBus, gameConnectionMessagingBus)
      .forEach(controller -> environment.jersey().register(controller));
}