Java Code Examples for org.jdbi.v3.core.Handle

The following examples show how to use org.jdbi.v3.core.Handle. These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source Project: SpinalTap   Source File: MysqlSchemaStore.java    License: Apache License 2.0 6 votes vote down vote up
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);
  }
}
 
Example 2
Source Project: SpinalTap   Source File: MysqlSchemaDatabase.java    License: Apache License 2.0 6 votes vote down vote up
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);
  }
}
 
Example 3
Source Project: SpinalTap   Source File: MysqlSchemaDatabase.java    License: Apache License 2.0 6 votes vote down vote up
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);
  }
}
 
Example 4
Source Project: SpinalTap   Source File: MysqlSchemaDatabase.java    License: Apache License 2.0 6 votes vote down vote up
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);
  }
}
 
Example 5
Source Project: SpinalTap   Source File: MysqlSchemaDatabase.java    License: Apache License 2.0 6 votes vote down vote up
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);
  }
}
 
Example 6
Source Project: SpinalTap   Source File: MysqlSchemaReader.java    License: Apache License 2.0 6 votes vote down vote up
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);
  }
}
 
Example 7
Source Project: trellis   Source File: DBResourceService.java    License: Apache License 2.0 6 votes vote down vote up
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();
        }
    }
}
 
Example 8
Source Project: trellis   Source File: DBResourceService.java    License: Apache License 2.0 6 votes vote down vote up
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();
            }
        }
    });
}
 
Example 9
Source Project: apicurio-studio   Source File: ApiDesignTypeUpgrader.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * @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).");
}
 
Example 10
Source Project: jdit   Source File: DBIRunner.java    License: MIT License 6 votes vote down vote up
@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();
            }
        }
    };
}
 
Example 11
@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;
	}
}
 
Example 12
Source Project: tutorials   Source File: JdbiIntegrationTest.java    License: MIT License 6 votes vote down vote up
@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());
    });
}
 
Example 13
Source Project: micronaut-sql   Source File: SpringTransactionHandler.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public void commit(Handle handle) {
    withLocalStuff(handle, (localStuff) -> {
        try {
            this.transactionManager.commit(localStuff.getTransactionStatus());
        } finally {
            restore(handle);
        }
    });
}
 
Example 14
Source Project: micronaut-sql   Source File: SpringTransactionHandler.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public void rollback(Handle handle) {
    didTxnRollback.set(true);
    withLocalStuff(handle, (localStuff) -> {
        try {
            this.transactionManager.rollback(localStuff.getTransactionStatus());
        } finally {
            restore(handle);
        }
    });
}
 
Example 15
Source Project: micronaut-sql   Source File: SpringTransactionHandler.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public void savepoint(Handle handle, String savepointName) {
    withLocalStuff(handle, (localStuff) -> {
        Object savePoint = localStuff.getTransactionStatus().createSavepoint();
        localStuff.getSavepoints().put(savepointName, savePoint);
    });
}
 
Example 16
Source Project: micronaut-sql   Source File: SpringTransactionHandler.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public void rollbackToSavepoint(Handle handle, String savepointName) {
    withLocalStuff(handle, (localStuff) -> {
        Object savePoint = localStuff.getSavepoints().get(savepointName);
        if (savePoint != null) {
            localStuff.getTransactionStatus().rollbackToSavepoint(savePoint);
        }
    });
}
 
Example 17
Source Project: micronaut-sql   Source File: SpringTransactionHandler.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public void releaseSavepoint(Handle handle, String savepointName) {
    withLocalStuff(handle, (localStuff) -> {
        Object savePoint = localStuff.getSavepoints().remove(savepointName);
        if (savePoint != null) {
            localStuff.getTransactionStatus().releaseSavepoint(savePoint);
        }
    });
}
 
Example 18
Source Project: micronaut-sql   Source File: SpringTransactionHandler.java    License: Apache License 2.0 5 votes vote down vote up
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);
    }
}
 
Example 19
@Override
public void commit(Handle handle) {
    withLocalStuff(handle, (localStuff) -> {
        try {
            this.transactionManager.commit(localStuff.getTransactionStatus());
        } finally {
            restore(handle);
        }
    });
}
 
Example 20
@Override
public void rollback(Handle handle) {
    didTxnRollback.set(true);
    withLocalStuff(handle, (localStuff) -> {
        try {
            this.transactionManager.rollback(localStuff.getTransactionStatus());
        } finally {
            restore(handle);
        }
    });
}
 
Example 21
@Override
public void savepoint(Handle handle, String savepointName) {
    withLocalStuff(handle, (localStuff) -> {
        Object savePoint = localStuff.getTransactionStatus().createSavepoint();
        localStuff.getSavepoints().put(savepointName, savePoint);
    });
}
 
Example 22
@Override
public void rollbackToSavepoint(Handle handle, String savepointName) {
    withLocalStuff(handle, (localStuff) -> {
        Object savePoint = localStuff.getSavepoints().get(savepointName);
        if (savePoint != null) {
            localStuff.getTransactionStatus().rollbackToSavepoint(savePoint);
        }
    });
}
 
Example 23
@Override
public void releaseSavepoint(Handle handle, String savepointName) {
    withLocalStuff(handle, (localStuff) -> {
        Object savePoint = localStuff.getSavepoints().remove(savepointName);
        if (savePoint != null) {
            localStuff.getTransactionStatus().releaseSavepoint(savePoint);
        }
    });
}
 
Example 24
private void restore(final Handle handle) {
    try {
        final LocalStuff stuff = this.localTransactions.remove(handle);
        if (stuff != null) {
            stuff.getSavepoints().clear();
        }
    } finally {
        // prevent memory leak if rollback throws an exception
        this.localTransactions.remove(handle);
    }
}
 
Example 25
Source Project: micronaut-sql   Source File: AbstractTransactionHandler.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public <R, X extends Exception> R inTransaction(Handle handle,
                                                TransactionIsolationLevel level,
                                                HandleCallback<R, X> callback) throws X {
    final TransactionIsolationLevel initial = handle.getTransactionIsolationLevel();
    try {
        handle.setTransactionIsolation(level);
        return inTransaction(handle, callback);
    } finally {
        handle.setTransactionIsolation(initial);
    }
}
 
Example 26
Source Project: java-specialagent   Source File: Jdbi3ITest.java    License: Apache License 2.0 5 votes vote down vote up
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));
}
 
Example 27
Source Project: SpinalTap   Source File: MysqlSchemaStore.java    License: Apache License 2.0 5 votes vote down vote up
public void bootstrap(List<MysqlTableSchema> schemas) {
  try (Handle handle = jdbi.open()) {
    MysqlSchemaUtil.VOID_RETRYER.call(
        () -> {
          handle.execute(String.format(CREATE_SCHEMA_STORE_TABLE_QUERY, storeDBName, sourceName));
          PreparedBatch batch =
              handle.prepareBatch(String.format(PUT_SCHEMA_QUERY, storeDBName, sourceName));
          for (MysqlTableSchema schema : schemas) {
            GtidSet gtidSet = schema.getBinlogFilePos().getGtidSet();
            batch
                .bind("database", schema.getDatabase())
                .bind("table", schema.getTable())
                .bind("binlog_file_position", schema.getBinlogFilePos().toString())
                .bind("server_uuid", schema.getBinlogFilePos().getServerUUID())
                .bind("gtid_set", gtidSet == null ? null : gtidSet.toString())
                .bind("gtid", schema.getGtid())
                .bind("columns", OBJECT_MAPPER.writeValueAsString(schema.getColumns()))
                .bind("sql", schema.getSql())
                .bind("meta_data", OBJECT_MAPPER.writeValueAsString(schema.getMetadata()))
                .bind("timestamp", new Timestamp(schema.getTimestamp()))
                .add();
          }
          batch.execute();
          getAllSchemas().forEach(this::updateSchemaCache);
          return null;
        });
  } catch (Exception ex) {
    log.error(
        String.format("Failed to bootstrap schema store for %s. exception: %s", sourceName, ex));
    throw new RuntimeException(ex);
  }
}
 
Example 28
Source Project: SpinalTap   Source File: MysqlSchemaStore.java    License: Apache License 2.0 5 votes vote down vote up
public List<MysqlTableSchema> queryByGTID(String gtid) {
  Preconditions.checkArgument(Strings.isNotBlank(gtid), "GTID cannot be empty");
  try (Handle handle = jdbi.open()) {
    return handle
        .createQuery(
            String.format("SELECT * FROM `%s`.`%s` WHERE gtid = :gtid", storeDBName, sourceName))
        .bind("gtid", gtid)
        .map(MysqlTableSchemaMapper.INSTANCE)
        .list();
  } catch (Exception ex) {
    log.error(String.format("Failed to query table schema by GTID: %s. Exception: %s", gtid, ex));
    throw new RuntimeException(ex);
  }
}
 
Example 29
Source Project: SpinalTap   Source File: MysqlSchemaDatabase.java    License: Apache License 2.0 5 votes vote down vote up
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);
  }
}
 
Example 30
Source Project: SpinalTap   Source File: MysqlSchemaUtil.java    License: Apache License 2.0 5 votes vote down vote up
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);
}