org.jdbi.v3.core.Handle Java Examples

public List<MysqlTableSchema> queryByBinlogFilePos(BinlogFilePos pos) {
  Preconditions.checkNotNull(pos, "BinlogFilePos cannot be null");
  try (Handle handle = jdbi.open()) {
    return MysqlSchemaUtil.LIST_TABLE_SCHEMA_RETRYER.call(
        () ->
            handle
                .createQuery(
                    String.format(
                        "SELECT * FROM `%s`.`%s` WHERE binlog_file_position = :pos",
                        storeDBName, sourceName))
                .bind("pos", pos.toString())
                .map(MysqlTableSchemaMapper.INSTANCE)
                .list());
  } catch (Exception ex) {
    log.error(
        String.format("Failed to query table schema by binlog pos: %s. Exception: %s", pos, ex));
    throw new RuntimeException(ex);
  }
}
 

@Test
public void whenTransactionRollbackThenCommit_thenOnlyLastInserted() {
    Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");
    jdbi.useHandle(handle -> {
        handle.useTransaction((Handle h) -> {
            assertEquals(handle, h);

            handle.execute("create table PROJECT_13 (ID IDENTITY, NAME VARCHAR (50), URL VARCHAR (100))");
            handle.execute("INSERT INTO PROJECT_13 (NAME, URL) VALUES ('tutorials', 'https://github.com/eugenp/tutorials')");
            handle.rollback();
            handle.begin();
            handle.execute("INSERT INTO PROJECT_13 (NAME, URL) VALUES ('REST with Spring', 'https://github.com/eugenp/REST-With-Spring')");
            handle.rollback();
            handle.begin();
            handle.execute("INSERT INTO PROJECT_13 (NAME, URL) VALUES ('tutorials', 'https://github.com/eugenp/tutorials')");
            handle.execute("INSERT INTO PROJECT_13 (NAME, URL) VALUES ('REST with Spring', 'https://github.com/eugenp/REST-With-Spring')");
            handle.commit();
        });
        List<Map<String, Object>> list = handle.select("SELECT * FROM PROJECT_13").mapToMap().list();

        assertEquals(2, list.size());
    });
}
 

void applyDDL(@NonNull final String sql, final String database) {
  log.info(String.format("Applying DDL statement: %s (Database selected: %s)", sql, database));
  try (Handle handle = jdbi.open()) {
    handle.execute("SET foreign_key_checks=0");
    MysqlSchemaUtil.VOID_RETRYER.call(
        () -> {
          MysqlSchemaUtil.executeWithJdbc(
              handle, getSchemaDatabaseName(sourceName, database), addSourcePrefix(sql));
          return null;
        });
    metrics.schemaDatabaseApplyDDLSuccess(database);
  } catch (Exception ex) {
    log.error(
        String.format(
            "Failed to apply DDL Statement to source: %s database: %s. (SQL: %s. Exception: %s)",
            sourceName, database, sql, ex));
    metrics.schemaDatabaseApplyDDLFailure(database, ex);
    throw new RuntimeException(ex);
  }
}
 

List<String> listDatabases() {
  try (Handle handle = jdbi.open()) {
    return MysqlSchemaUtil.LIST_STRING_RETRYER.call(
        () ->
            handle
                .createQuery(
                    String.format(
                        "select SCHEMA_NAME from information_schema.SCHEMATA "
                            + "where SCHEMA_NAME LIKE '%s%s%%'",
                        sourceName, DELIMITER))
                .mapTo(String.class)
                .map(
                    database ->
                        database.replaceFirst(String.format("^%s%s", sourceName, DELIMITER), ""))
                .list());
  } catch (Exception ex) {
    log.error(
        String.format("Failed to list databases for source: %s (Exception: %s)", sourceName, ex));
    throw new RuntimeException(ex);
  }
}
 

void createDatabase(@NonNull final String database) {
  log.info("Creating database: {}", database);
  try (Handle handle = jdbi.open()) {
    MysqlSchemaUtil.VOID_RETRYER.call(
        () -> {
          MysqlSchemaUtil.executeWithJdbc(
              handle,
              null,
              String.format(
                  "CREATE DATABASE `%s`",
                  getSchemaDatabaseName(sourceName, MysqlSchemaUtil.escapeBackQuote(database))));
          return null;
        });
  } catch (Exception ex) {
    log.error(
        String.format(
            "Failed to create database %s (Exception: %s)",
            getSchemaDatabaseName(sourceName, database), ex));
    throw new RuntimeException(ex);
  }
}
 

void dropDatabase(@NonNull final String database) {
  log.info("Dropping database: {}", database);
  try (Handle handle = jdbi.open()) {
    handle.execute("SET foreign_key_checks=0");
    MysqlSchemaUtil.VOID_RETRYER.call(
        () -> {
          MysqlSchemaUtil.executeWithJdbc(
              handle,
              null,
              String.format(
                  "DROP DATABASE IF EXISTS `%s`",
                  MysqlSchemaUtil.escapeBackQuote(getSchemaDatabaseName(sourceName, database))));
          return null;
        });
  } catch (Exception ex) {
    log.error(String.format("Failed to drop database %s. (Exception: %s)", database, ex));
    throw new RuntimeException(ex);
  }
}
 

List<String> getAllTablesIn(String database) {
  try (Handle handle = jdbi.open()) {
    return MysqlSchemaUtil.LIST_STRING_RETRYER.call(
        () ->
            handle
                .createQuery(
                    "select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = :db and TABLE_TYPE = 'BASE TABLE'")
                .bind("db", database)
                .mapTo(String.class)
                .list());
  } catch (Exception ex) {
    log.error(
        String.format(
            "Failed to get all tables in database %s on %s, exception: %s",
            database, sourceName, ex));
    throw new RuntimeException(ex);
  }
}
 

private static void batchUpdateTriples(final Handle handle, final int resourceId, final String table,
        final Graph graph, final int batchSize) {
    final String query
        = "INSERT INTO " + table + " (resource_id, subject, predicate, object, lang, datatype) "
        + "VALUES (?, ?, ?, ?, ?, ?)";
    try (final PreparedBatch batch = handle.prepareBatch(query)) {
        graph.stream().sequential().forEach(triple -> {
            batch.bind(0, resourceId)
                 .bind(1, ((IRI) triple.getSubject()).getIRIString())
                 .bind(2, triple.getPredicate().getIRIString())
                 .bind(3, getObjectValue(triple.getObject()))
                 .bind(4, getObjectLang(triple.getObject()))
                 .bind(5, getObjectDatatype(triple.getObject())).add();
            if (batch.size() >= batchSize) {
                batch.execute();
            }
        });
        if (batch.size() > 0) {
            batch.execute();
        }
    }
}
 

private static void updateExtra(final Handle handle, final int resourceId, final IRI identifier,
        final Dataset dataset) {
    dataset.getGraph(PreferUserManaged).ifPresent(graph -> {
        final String query = "INSERT INTO extra (resource_id, predicate, object) VALUES (?, ?, ?)";
        try (final PreparedBatch batch = handle.prepareBatch(query)) {
            graph.stream(identifier, LDP.inbox, null).map(Triple::getObject).filter(t -> t instanceof IRI)
                .map(t -> ((IRI) t).getIRIString()).findFirst().ifPresent(iri ->
                        batch.bind(0, resourceId)
                             .bind(1, LDP.inbox.getIRIString())
                             .bind(2, iri)
                             .add());

            graph.stream(identifier, OA.annotationService, null).map(Triple::getObject)
                 .filter(t -> t instanceof IRI).map(t -> ((IRI) t).getIRIString()).findFirst().ifPresent(iri ->
                        batch.bind(0, resourceId)
                             .bind(1, OA.annotationService.getIRIString())
                             .bind(2, iri).add());

            if (batch.size() > 0) {
                batch.execute();
            }
        }
    });
}
 

/**
 * @see io.apicurio.hub.core.storage.jdbc.IDbUpgrader#upgrade(org.jdbi.v3.core.Handle)
 */
@Override
public void upgrade(Handle dbHandle) throws Exception {
    logger.debug("Setting the value of 'api_type' for all rows in 'api_designs'.");

    // Explanation of query:
    //   - Select all rows from api_designs (we want to update the "api_type" column for each such row)
    //   - Include in the result set the raw content (any "Document" row from the api_content table)
    String query = "SELECT d.* FROM api_designs d WHERE d.api_type IS NULL";

    Connection connection = dbHandle.getConnection();
    Statement statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    ResultSet resultSet = statement.executeQuery(query);
    
    long rowCount = 0;
    while (resultSet.next()) {
        String designId = resultSet.getString("id");
        ApiDesignType apiType = getApiType(dbHandle, designId);
        resultSet.updateString("api_type", apiType.name());
        resultSet.updateRow();
        rowCount++;
    }
    
    logger.debug("Updated " + rowCount + " rows (added api_type column to every row).");
}
 

@Override
public World[] updatesQueries(int totalQueries) {
	try (Handle handle = jdbi.open()) {
		WorldJDBIImpl dao = handle.attach(WorldJDBIImpl.class);

		final World updates[] = new World[totalQueries];

		for (int i = 0; i < totalQueries; i++) {
			final World world = dao.findById(Helper.randomWorld());
			world.setRandomNumber(Helper.randomWorld());
			updates[i] = world;
		}
		// Reason for sorting : https://github.com/TechEmpower/FrameworkBenchmarks/pull/2684
		Arrays.sort(updates, Comparator.comparingInt(World::getId));
		dao.update(updates);
		handle.commit();
		
		return updates;
	}
}
 

@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 List<String> getLastNames() {
    try (Handle handle = dbi().open()) {
        return handle.createQuery("select last_name from players")
                .mapTo(String.class)
                .list();
    }
}
 

private void restore(final Handle handle) {
    try {
        final LocalStuff stuff = this.localStuff.remove(handle);
        if (stuff != null) {
            stuff.getSavepoints().clear();
        }
    } finally {
        // prevent memory leak if rollback throws an exception
        this.localStuff.remove(handle);
    }
}
 

@Override
public void rollback(Handle handle) {
    didTxnRollback.set(true);
    withLocalStuff(handle, (localStuff) -> {
        try {
            this.transactionManager.rollback(localStuff.getTransactionStatus());
        } finally {
            restore(handle);
        }
    });
}
 

List<String> getAllDatabases() {
  try (Handle handle = jdbi.open()) {
    return MysqlSchemaUtil.LIST_STRING_RETRYER.call(
        () ->
            handle
                .createQuery("select SCHEMA_NAME from information_schema.SCHEMATA")
                .mapTo(String.class)
                .list());
  } catch (Exception ex) {
    log.error(String.format("Failed to get all databases on %s, exception: %s", sourceName, ex));
    throw new RuntimeException(ex);
  }
}
 

@Test
public void whenHandle_thenBoh() {
    Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");
    final Handle[] handleRef = new Handle[1];
    boolean closed = jdbi.withHandle(handle -> {
        handleRef[0] = handle;
        return handle.isClosed();
    });

    assertFalse(closed);
    assertTrue(handleRef[0].isClosed());
}
 

@BeforeAll
public void setup() throws Exception {
  JerseyGuiceUtils.reset();
  singularityTestModule = new SingularityTestModule(useDBTests, customConfigSetup);

  singularityTestModule.getInjector().injectMembers(this);
  singularityTestModule.start();
  leaderCacheCoordinator.activateLeaderCache();
  configuration.setThreadpoolShutdownDelayInSeconds(0);
  if (useDBTests) {
    Handle handle = dbiProvider.get().open();
    handle.getConnection().setAutoCommit(true);

    Database database = DatabaseFactory
      .getInstance()
      .findCorrectDatabaseImplementation(new JdbcConnection(handle.getConnection()));

    Liquibase liquibase = new Liquibase(
      "singularity_test.sql",
      new FileSystemResourceAccessor(),
      database
    );
    liquibase.update((String) null);

    try {
      database.close();
      handle.close();
    } catch (Throwable t) {}
  }
}
 

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

Map<String, List<MysqlColumn>> getColumnsForAllTables(@NonNull String database) {
  try (Handle handle = jdbi.open()) {
    Map<String, List<MysqlColumn>> tableColumnsMap = new HashMap<>();
    MysqlSchemaUtil.VOID_RETRYER.call(
        () -> {
          handle
              .createQuery(
                  "select TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, COLUMN_KEY from information_schema.COLUMNS "
                      + "where TABLE_SCHEMA = :db "
                      + "order by ORDINAL_POSITION")
              .bind("db", getSchemaDatabaseName(sourceName, database))
              .mapToMap(String.class)
              .forEach(
                  row -> {
                    String table = row.get("table_name");
                    tableColumnsMap.putIfAbsent(table, new LinkedList<>());
                    tableColumnsMap
                        .get(table)
                        .add(
                            new MysqlColumn(
                                row.get("column_name"),
                                row.get("data_type"),
                                row.get("column_type"),
                                "PRI".equals(row.get("column_key"))));
                  });
          return null;
        });
    return tableColumnsMap;
  } catch (Exception ex) {
    log.error(String.format("Failed to fetch table columns for database: %s", database), ex);
    throw new RuntimeException(ex);
  }
}
 

public void executeWithJdbc(
    @NonNull final Handle handle, final String database, @NonNull final String sql)
    throws SQLException {
  // Use JDBC API to excute raw SQL without any return value and no binding in SQL statement, so
  // we don't need to escape colon(:)
  // SQL statement with colon(:) inside needs to be escaped if using JDBI Handle.execute(sql)
  Connection connection = handle.getConnection();
  if (database != null) {
    connection.setCatalog(database);
  }
  Statement statement = connection.createStatement();
  statement.execute(sql);
}
 

public List<MysqlColumn> getTableColumns(@NonNull String database, @NonNull String table) {
  try (Handle handle = jdbi.open()) {
    List<MysqlColumn> columns =
        MysqlSchemaUtil.LIST_COLUMN_RETRYER.call(
            () ->
                handle
                    .createQuery(
                        "select COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, COLUMN_KEY from information_schema.COLUMNS "
                            + "where TABLE_SCHEMA = :db and TABLE_NAME = :table "
                            + "order by ORDINAL_POSITION")
                    .bind("db", database)
                    .bind("table", table)
                    .map(
                        (rs, ctx) ->
                            new MysqlColumn(
                                rs.getString("COLUMN_NAME"),
                                rs.getString("DATA_TYPE"),
                                rs.getString("COLUMN_TYPE"),
                                "PRI".equals(rs.getString("COLUMN_KEY"))))
                    .list());
    metrics.schemaStoreGetSuccess(database, table);
    return columns;
  } catch (Exception ex) {
    log.error(String.format("Failed to fetch schema for table %s, db %s", table, database), ex);
    metrics.schemaStoreGetFailure(database, table, ex);
    throw new RuntimeException(ex);
  }
}
 

private static int updateResource(final Handle handle, final Metadata metadata, final Dataset dataset,
        final Instant time, final boolean isDelete) {

    handle.execute("DELETE FROM resource WHERE subject = ?", metadata.getIdentifier().getIRIString());
    final String query
        = "INSERT INTO resource (subject, interaction_model, modified, deleted, is_part_of, acl, "
        + "ldp_member, ldp_membership_resource, ldp_has_member_relation, ldp_is_member_of_relation, "
        + "ldp_inserted_content_relation, binary_location, binary_format) "
        + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    // Set ldp:insertedContentRelation only for LDP-IC and LDP-DC resources
    final String icr = asList(LDP.DirectContainer, LDP.IndirectContainer).contains(metadata.getInteractionModel())
        ? metadata.getInsertedContentRelation().orElse(LDP.MemberSubject).getIRIString() : null;

    try (final Update update = handle.createUpdate(query)
            .bind(0, metadata.getIdentifier().getIRIString())
            .bind(1, metadata.getInteractionModel().getIRIString())
            .bind(2, time.toEpochMilli())
            .bind(3, isDelete)
            .bind(4, metadata.getContainer().map(IRI::getIRIString).orElse(null))
            .bind(5, dataset.contains(of(PreferAccessControl), null, null, null))
            .bind(6, metadata.getMembershipResource().map(TrellisUtils::normalizeIdentifier)
                .map(IRI::getIRIString).orElse(null))
            .bind(7, metadata.getMembershipResource().map(IRI::getIRIString).orElse(null))
            .bind(8, metadata.getMemberRelation().map(IRI::getIRIString).orElse(null))
            .bind(9, metadata.getMemberOfRelation().map(IRI::getIRIString).orElse(null))
            .bind(10, icr)
            .bind(11, metadata.getBinary().map(BinaryMetadata::getIdentifier).map(IRI::getIRIString).orElse(null))
            .bind(12, metadata.getBinary().flatMap(BinaryMetadata::getMimeType).orElse(null))) {
        return update.executeAndReturnGeneratedKeys("id").mapTo(Integer.class).one();
    }
}
 

public Long createPlayer(String firstName, String lastName, Date birthDate, int height, int weight) {
    try (Handle handle = dbi().open()) {
        return handle.createUpdate("insert into players(first_name, last_name, birth_date, weight, height) values" +
                "(:first_name, :last_name, :birth_date, :weight, :height)")
                .bind("first_name", firstName)
                .bind("last_name", lastName)
                .bind("birth_date", birthDate)
                .bind("height", height)
                .bind("weight", weight)
                .executeAndReturnGeneratedKeys()
                .mapTo(Long.class)
                .findOnly();
    }
}
 

private static void updateExtension(final Handle handle, final int resourceId, final String ext,
        final Graph graph) {
    final String query = "INSERT INTO extension (resource_id, ext, data) VALUES (?, ?, ?)";
    final String serialized = serializeGraph(graph);
    try (final Update update = handle.createUpdate(query).bind(0, resourceId).bind(1, ext).bind(2, serialized)) {
        update.execute();
    }
}
 

private static String getDatabaseVendor(Handle handle) {
    try {
        return handle.getConnection().getMetaData().getDatabaseProductName();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}
 

/**
 * Instantiates an instance of the given upgrader class and then invokes it.  Used to perform
 * advanced upgrade logic when upgrading the DB (logic that cannot be handled in simple SQL 
 * statements).
 * @param handle
 * @param cname
 */
private void applyUpgrader(Handle handle, String cname) {
    try {
        @SuppressWarnings("unchecked")
        Class<IDbUpgrader> upgraderClass = (Class<IDbUpgrader>) Class.forName(cname);
        IDbUpgrader upgrader = upgraderClass.newInstance();
        upgrader.upgrade(handle);
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}
 

public static DatabaseMaintenance create(Handle handle) {
    String databaseVendor = getDatabaseVendor(handle);
    switch (databaseVendor) {
        case POSTGRESQL:
            return new PostgresDatabaseMaintenance(handle);
        case HSQLDB:
            return new HsqlDatabaseMaintenance(handle);
        case H2:
            return new H2DatabaseMaintenance(handle);
        case MYSQL:
            return new MySqlDatabaseMaintenance(handle);
        default:
            throw new UnsupportedOperationException(databaseVendor + " is not supported");
    }
}
 

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

/**
 * Migrate the DB schema
 *
 * @param properties configuration of schema migration
 */
private void migrateSchema(Properties properties) {
    String property = properties.getProperty("schema.migration.enabled");
    if (property != null && !Boolean.parseBoolean(property)) {
        return;
    }
    try (Handle handle = dbi.open()) {
        String schemaLocation = properties.getProperty("schema.migration.location");
        if (schemaLocation == null) {
            schemaLocation = DEFAULT_SCHEMA_LOCATION;
        }
        DatabaseMaintenance databaseMaintenance = DatabaseMaintenanceFactory.create(handle);
        databaseMaintenance.dropTablesAndSequences();

        DataMigration dataMigration = new DataMigration(handle);
        URL resource = getClass().getClassLoader().getResource(schemaLocation);
        if (resource == null) {
            throw new IllegalArgumentException("File '" + schemaLocation + " is not exist in resources");
        }
        File file = new File(resource.getFile());
        if (file.isFile()) {
            dataMigration.executeScript(schemaLocation);
        } else {
            migrateDirectory(dataMigration, file, schemaLocation);
        }
    }
}