private String getDeleteAllStatement(Range range) { StringBuilder statement = new StringBuilder("DELETE FROM ").append(tableSchema.getTableId().getName()); if (!range.getBegin().isEmpty() || !range.getEnd().isEmpty()) { statement.append(" WHERE "); appendRange(statement, range); } return statement.toString(); }
private void appendRange(StringBuilder statement, Range range) { appendScanBound(statement, range.getBegin(), range.getBeginBound().equals(Range.Bound.INCLUSIVE) ? ">=" : ">"); if (!range.getBegin().isEmpty() && !range.getEnd().isEmpty()) { statement.append(" AND "); } appendScanBound(statement, range.getEnd(), range.getEndBound().equals(Range.Bound.INCLUSIVE) ? "<=" : "<"); }
private Scanner getScanner(Range keyRange) { // the method will always prepend the table name as prefix byte[] begin = convertKeyToBytes(keyRange.getBegin(), true); byte[] end = convertKeyToBytes(keyRange.getEnd(), true); // Table.scan() start key is inclusive by default, and if it is EXCLUSTIVE, we want to ensure the start keys are // not empty so that we do not scan from the start of some other table if (!keyRange.getBegin().isEmpty() && keyRange.getBeginBound() == Range.Bound.EXCLUSIVE) { begin = Bytes.stopKeyForPrefix(begin); } // Table.scan() stop key is exclusive by default, so when the end keys are not specifies, we will need to scan to // the end of table, which will be the default table prefix + 1. if (keyRange.getEnd().isEmpty() || keyRange.getEndBound() == Range.Bound.INCLUSIVE) { end = Bytes.stopKeyForPrefix(end); } return table.scan(begin, end); }
/** * Get the scan query for the range given. For example, if the range provides key1, key2 as the begin and end to * scan, both rows are inclusive, it will generate the following query: * SELECT * FROM simpletable WHERE (key1,key2)>=(?,?) AND (key1,key2)<=(?,?) LIMIT 10; * * @param range the range to scan. * @param limit limit number of row * @return the scan query */ private String getScanQuery(Range range, int limit) { StringBuilder queryString = new StringBuilder("SELECT * FROM ").append(tableSchema.getTableId().getName()); if (range.getBegin().isEmpty() && range.getEnd().isEmpty()) { return queryString.append(" LIMIT ").append(limit).append(";").toString(); } queryString.append(" WHERE "); appendRange(queryString, range); queryString.append(" LIMIT ").append(limit).append(";"); return queryString.toString(); }
@Override public void deleteAll(Range keyRange) throws InvalidFieldException, IOException { LOG.trace("Table {}: DeleteAll with range {}", tableSchema.getTableId(), keyRange); fieldValidator.validatePrimaryKeys(keyRange.getBegin(), true); fieldValidator.validatePrimaryKeys(keyRange.getEnd(), true); String sql = getDeleteAllStatement(keyRange); try (PreparedStatement statement = connection.prepareStatement(sql)) { int index = 1; if (keyRange.getBegin() != null) { for (Field<?> key : keyRange.getBegin()) { setField(statement, key, index); index++; } } if (keyRange.getEnd() != null) { for (Field<?> key : keyRange.getEnd()) { setField(statement, key, index); index++; } } LOG.trace("SQL statement: {}", statement); statement.executeUpdate(); } catch (SQLException e) { throw new IOException(String.format("Failed to delete the rows from table %s with range %s", tableSchema.getTableId().getName(), keyRange), e); } }
@Override public CloseableIterator<StructuredRow> scan(Range keyRange, int limit) throws InvalidFieldException, IOException { LOG.trace("Table {}: Scan range {} with limit {}", tableSchema.getTableId(), keyRange, limit); fieldValidator.validatePrimaryKeys(keyRange.getBegin(), true); fieldValidator.validatePrimaryKeys(keyRange.getEnd(), true); String scanQuery = getScanQuery(keyRange, limit); // We don't close the statement here because once it is closed, the result set is also closed. try { PreparedStatement statement = connection.prepareStatement(scanQuery); int index = 1; if (keyRange.getBegin() != null) { for (Field<?> key : keyRange.getBegin()) { setField(statement, key, index); index++; } } if (keyRange.getEnd() != null) { for (Field<?> key : keyRange.getEnd()) { setField(statement, key, index); index++; } } LOG.trace("SQL statement: {}", statement); ResultSet resultSet = statement.executeQuery(); return new ResultSetIterator(statement, resultSet, tableSchema); } catch (SQLException e) { throw new IOException(String.format("Failed to scan from table %s with range %s", tableSchema.getTableId().getName(), keyRange), e); } }