Java Handle類代碼示例

import org.skife.jdbi.v2.Handle; //導入依賴的package包/類
private void doWriteDerby(Handle h, String id, InputStream file) {
    doDelete(h, id);
    try {
        Blob blob = h.getConnection().createBlob();
        try (OutputStream out = blob.setBinaryStream(1)) {
            IOUtils.copy(file, out);
        }

        h.insert("INSERT INTO icon_filestore(id, data) values (?,?)", id, blob);
    } catch (IOException | SQLException ex) {
        throw IconDataAccessException.launderThrowable(ex);
    }
}
 

import org.skife.jdbi.v2.Handle; //導入依賴的package包/類
private InputStream doReadStandard(Handle h, String id) {
    List<Map<String, Object>> res = h.select("SELECT data FROM icon_filestore WHERE id=?", id);

    Optional<Blob> blob = res.stream()
        .map(row -> row.get("data"))
        .map(Blob.class::cast)
        .findFirst();

    if (blob.isPresent()) {
        try {
            return blob.get().getBinaryStream();
        } catch (SQLException ex) {
            throw new IconDataAccessException("Unable to read from BLOB", ex);
        }
    }

    return null;
}
 

import org.skife.jdbi.v2.Handle; //導入依賴的package包/類
private boolean tableExists(Handle h, String tableName) {
    try {
        String tableToCheck = tableName;
        boolean caseSensitive = this.databaseKind == DatabaseKind.PostgreSQL;
        if (!caseSensitive) {
            tableToCheck = tableName.toUpperCase(Locale.ROOT);
        }
        DatabaseMetaData metaData = h.getConnection().getMetaData();

        try (ResultSet rs = metaData.getTables(null, null, tableToCheck, null)) {
            while (rs.next()) {
                String foundTable = rs.getString("TABLE_NAME");
                if (tableToCheck.equalsIgnoreCase(foundTable)) {
                    return true;
                }
            }
        }
        return false;
    } catch (SQLException ex) {
        throw IconDataAccessException.launderThrowable("Cannot check if the table " + tableName + " already exists", ex);
    }
}
 

import org.skife.jdbi.v2.Handle; //導入依賴的package包/類
private void doWriteDerby(Handle h, String path, InputStream file) {
    doDelete(h, path);
    try {
        Blob blob = h.getConnection().createBlob();
        try (OutputStream out = blob.setBinaryStream(1)) {
            IOUtils.copy(file, out);
        }

        h.insert("INSERT INTO filestore(path, data) values (?,?)", path, blob);
    } catch (IOException | SQLException ex) {
        throw ExtensionDataAccessException.launderThrowable(ex);
    }
}
 

import org.skife.jdbi.v2.Handle; //導入依賴的package包/類
private InputStream doReadStandard(Handle h, String path) {
    List<Map<String, Object>> res = h.select("SELECT data FROM filestore WHERE path=?", path);

    Optional<Blob> blob = res.stream()
        .map(row -> row.get("data"))
        .map(Blob.class::cast)
        .findFirst();

    if (blob.isPresent()) {
        try {
            return blob.get().getBinaryStream();
        } catch (SQLException ex) {
            throw new ExtensionDataAccessException("Unable to read from BLOB", ex);
        }
    }

    return null;
}
 

import org.skife.jdbi.v2.Handle; //導入依賴的package包/類
private boolean tableExists(Handle h, String tableName) {
    try {
        String tableToCheck = tableName;
        boolean caseSensitive = this.databaseKind == DatabaseKind.PostgreSQL;
        if (!caseSensitive) {
            tableToCheck = tableName.toUpperCase(Locale.ROOT);
        }
        DatabaseMetaData metaData = h.getConnection().getMetaData();

        try (ResultSet rs = metaData.getTables(null, null, tableToCheck, null)) {
            while (rs.next()) {
                String foundTable = rs.getString("TABLE_NAME");
                if (tableToCheck.equalsIgnoreCase(foundTable)) {
                    return true;
                }
            }
        }
        return false;
    } catch (SQLException ex) {
        throw ExtensionDataAccessException.launderThrowable("Cannot check if the table " + tableName + " already exists", ex);
    }
}
 

import org.skife.jdbi.v2.Handle; //導入依賴的package包/類
@Before
public void setupTests() throws IOException {
    final DataSourceFactory dataSourceFactory = new DataSourceFactory();
    dataSourceFactory.setDriverClass("org.h2.Driver");
    dataSourceFactory.setUrl("jdbc:h2:mem:test-" + System.currentTimeMillis() + "?user=sa");
    dataSourceFactory.setInitialSize(1);
    final DBI dbi = new VavrDBIFactory().build(env, dataSourceFactory, "test");
    try (Handle h = dbi.open()) {
        h.execute("CREATE TABLE tasks (" +
                "id INT PRIMARY KEY, " +
                "assignee VARCHAR(255) NOT NULL, " +
                "start_date TIMESTAMP, " +
                "end_date TIMESTAMP, " +
                "comments VARCHAR(1024) " +
                ")");
    }
    dao = dbi.onDemand(TaskDao.class);

    dao.insert(100, Option.some("Name 1"), LocalDate.parse("2017-08-24"), Option.none(), Option.none());
    dao.insert(200, Option.some("Name 2"), LocalDate.parse("2017-08-25"), Option.none(), Option.some("To be done"));
}
 

import org.skife.jdbi.v2.Handle; //導入依賴的package包/類
public List<DBPixelPlacement> getUndoPixels(User who) {
    Handle h = dbi.open();
    List<Map<String, Object>> output = h
            .createQuery("SELECT DISTINCT secondary_id FROM pixels WHERE rollback_action AND who = :who AND secondary_id IS NOT NULL")
            .bind("who", who.getId())
            .list(); // this selects all pixels that we previously have rolled back.
    List<DBPixelPlacement> pixels = new ArrayList<>();
    for (Map<String, Object> entry : output) {
        int fromId = toIntExact((long) entry.get("secondary_id"));
        DBPixelPlacement fromPixel = getHandle().getPixel(fromId); // get the original pixel, the one that we previously rolled back
        
        boolean can_undo = getHandle().getCanUndo(fromPixel.x, fromPixel.y, fromPixel.id);
        if (can_undo) { // this basically checks if there are pixels that are more recent
            pixels.add(fromPixel); // add and later return
        }
    }
    h.close();
    return pixels;
}
 

import org.skife.jdbi.v2.Handle; //導入依賴的package包/類
@Before
public void createTestData() throws Exception {
  Handle handle = dbiProvider.get().open();

  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();
  } catch (Throwable t) {
  }

  handle.close();
}
 

import org.skife.jdbi.v2.Handle; //導入依賴的package包/類
public Integer deletePosition(final Position p) {
	return dbHandle.inTransaction(new TransactionCallback<Integer>() {
		public Integer inTransaction(Handle conn, TransactionStatus status) throws Exception {
			//if this position has any history, we'll just delete it
			dbHandle.execute("DELETE FROM peoplePositions WHERE positionId = ?", p.getId());
			
			//if this position is in an approval chain, we just delete it
			dbHandle.execute("DELETE FROM approvers WHERE positionId = ?", p.getId());
			
			//if this position is in an organization, it'll be automatically removed. 
			
			//if this position has any associated positions, just remove them.
			dbHandle.execute("DELETE FROM positionRelationships WHERE positionId_a = ? OR positionId_b= ?", p.getId(), p.getId());
			
			return dbHandle.createStatement("DELETE FROM positions WHERE id = :positionId")
				.bind("positionId", p.getId())
				.execute();
		}
	});
}
 

import org.skife.jdbi.v2.Handle; //導入依賴的package包/類
@Override
public LocationList runSearch(LocationSearchQuery query, Handle dbHandle) {
	LocationList result = new LocationList();
	result.setPageNum(query.getPageNum());
	result.setPageSize(query.getPageSize());
	if (query.getText() == null || query.getText().trim().length() == 0) {
		return result;
	}
	
	Query<Location> sqlQuery = dbHandle.createQuery("/* MssqlLocationSearch */ SELECT *, count(*) over() as totalCount "
			+ "FROM locations WHERE CONTAINS (name, :name) "
			+ "ORDER BY name ASC OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY")
		.bind("name", Utils.getSqlServerFullTextQuery(query.getText()))
		.bind("offset", query.getPageSize() * query.getPageNum())
		.bind("limit", query.getPageSize())
		.map(new LocationMapper());
	
	result.setList(sqlQuery.list());
	if (result.getList().size() > 0) { 
		result.setTotalCount((Integer) sqlQuery.getContext().getAttribute("totalCount"));
	} else { 
		result.setTotalCount(0);
	}
	return result;
}
 

import org.skife.jdbi.v2.Handle; //導入依賴的package包/類
@Override
public Optional<RepairSchedule> deleteRepairSchedule(UUID id) {
  RepairSchedule result = null;
  try (Handle h = jdbi.open()) {
    IStoragePostgreSql pg = getPostgresStorage(h);
    RepairSchedule scheduleToDel = pg.getRepairSchedule(UuidUtil.toSequenceId(id));
    if (scheduleToDel != null) {
      int rowsDeleted = pg.deleteRepairSchedule(UuidUtil.toSequenceId(scheduleToDel.getId()));
      if (rowsDeleted > 0) {
        result = scheduleToDel.with().state(RepairSchedule.State.DELETED).build(id);
      }
    }
  }
  if (result != null) {
    tryDeletingRepairUnit(result.getRepairUnitId());
  }
  return Optional.fromNullable(result);
}
 

import org.skife.jdbi.v2.Handle; //導入依賴的package包/類
@Override
public LocationList runSearch(LocationSearchQuery query, Handle dbHandle) {
	LocationList result = new LocationList();
	result.setPageNum(query.getPageNum());
	result.setPageSize(query.getPageSize());
	if (query.getText() == null || query.getText().trim().length() == 0) {
		return result;
	}
	
	result.setList(dbHandle.createQuery("/* SqliteLocationSearch */ SELECT * FROM locations "
			+ "WHERE name LIKE '%' || :name || '%' "
			+ "ORDER BY name ASC LIMIT :limit OFFSET :offset")
		.bind("name", Utils.getSqliteFullTextQuery(query.getText()))
		.bind("offset", query.getPageSize() * query.getPageNum())
		.bind("limit", query.getPageSize())
		.map(new LocationMapper())
		.list());
	result.setTotalCount(result.getList().size()); // Sqlite cannot do true total counts, so this is a crutch. 
	return result;
}
 

import org.skife.jdbi.v2.Handle; //導入依賴的package包/類
@Override
public Session getSession(@Nonnull final String sessionId) {
    if (sessionId == null) {
        throw new IllegalArgumentException("sessionId cannot be null");
    }

    Handle handle = null;
    try {
        handle = dbi.open();
        return handle.createQuery(sqlGetSession)
                .bind("sessionId", sessionId)
                .map(sessionWithDataMapper)
                .first();
    } finally {
        IOUtils.closeQuietly(handle);
    }
}
 

import org.skife.jdbi.v2.Handle; //導入依賴的package包/類
@Override
public Optional<RepairUnit> getRepairUnit(
    String clusterName,
    String keyspaceName,
    Set<String> columnFamilies,
    Set<String> nodes,
    Set<String> datacenters,
    Set<String> blacklistedTables) {
  RepairUnit result;
  try (Handle h = jdbi.open()) {
    IStoragePostgreSql storage = getPostgresStorage(h);
    result =
        storage.getRepairUnitByClusterAndTables(
            clusterName, keyspaceName, columnFamilies, nodes, datacenters, blacklistedTables);
  }
  return Optional.fromNullable(result);
}