void partitionRead() { // [START partition_read] final BatchReadOnlyTransaction txn = batchClient.batchReadOnlyTransaction(TimestampBound.strong()); List<Partition> partitions = txn.partitionRead( PartitionOptions.getDefaultInstance(), "Singers", KeySet.all(), Arrays.asList("SingerId", "FirstName", "LastName")); for (final Partition p : partitions) { try (ResultSet results = txn.execute(p)) { while (results.next()) { long singerId = results.getLong(0); String firstName = results.getString(1); String lastName = results.getString(2); System.out.println("[" + singerId + "] " + firstName + " " + lastName); } } } // [END partition_read] }
assertThat(rs.getColumnType(2)).isEqualTo(Type.bool()); assertThat(rs.getCurrentRowAsStruct()).isEqualTo(struct1); assertThat(rs.getString(0)).isEqualTo("x"); assertThat(rs.getLong(1)).isEqualTo(2L); assertThat(rs.getBoolean(2)).isTrue(); assertThat(rs.getDouble("doubleVal")).isWithin(0.0).of(doubleVal); assertThat(rs.getDouble(3)).isWithin(0.0).of(doubleVal); assertThat(rs.getString(4)).isEqualTo(stringVal); assertThat(rs.getString("stringVal")).isEqualTo(stringVal); assertThat(rs.getBytes(5)).isEqualTo(ByteArray.copyFrom(byteVal)); assertThat(rs.getBytes("byteVal")).isEqualTo(ByteArray.copyFrom(byteVal)); assertThat(rs.getString(0)).isEqualTo("y"); assertThat(rs.getLong(1)).isEqualTo(3L); assertThat(rs.isNull(2)).isTrue();
void partitionReadUsingIndex() { // [START partition_read_using_index] final BatchReadOnlyTransaction txn = batchClient.batchReadOnlyTransaction(TimestampBound.strong()); List<Partition> partitions = txn.partitionReadUsingIndex( PartitionOptions.getDefaultInstance(), "Singers", "SingerId", KeySet.all(), Arrays.asList("SingerId", "FirstName", "LastName")); for (Partition p : partitions) { try (ResultSet results = txn.execute(p)) { while (results.next()) { long singerId = results.getLong(0); String firstName = results.getString(1); String lastName = results.getString(2); System.out.println("[" + singerId + "] " + firstName + " " + lastName); } } } // [END partition_read_using_index] } }
void partitionQuery() { // [START partition_query] final BatchReadOnlyTransaction txn = batchClient.batchReadOnlyTransaction(TimestampBound.strong()); List<Partition> partitions = txn.partitionQuery( PartitionOptions.getDefaultInstance(), Statement.of("SELECT SingerId, FirstName, LastName FROM Singers")); for (final Partition p : partitions) { try (ResultSet results = txn.execute(p)) { while (results.next()) { long singerId = results.getLong(0); String firstName = results.getString(1); String lastName = results.getString(2); System.out.println("[" + singerId + "] " + firstName + " " + lastName); } } } // [END partition_query] }
.read("T", keys.build(), Arrays.asList("K", "BytesValue")); while (resultSet.next()) { String key = resultSet.getString(0); ByteArray value = resultSet.getBytes(1); assertThat(expected).containsKey(key);
.isEqualTo( Type.struct(StructField.of("K", Type.string()), StructField.of("V", Type.string()))); assertThat(resultSet.getString(0)).isEqualTo("k2"); assertThat(resultSet.getString(1)).isEqualTo("v2"); assertThat(resultSet.next()).isTrue(); assertThat(resultSet.getString("K")).isEqualTo("k3"); assertThat(resultSet.getString("V")).isEqualTo("v3"); assertThat(resultSet.next()).isFalse();
static void querySingersTable(DatabaseClient dbClient) { ResultSet resultSet = dbClient .singleUse() .executeQuery( Statement.of( "SELECT SingerId, FirstName, LastName FROM Singers")); while (resultSet.next()) { System.out.printf( "%s %s %s\n", resultSet.getLong("SingerId"), resultSet.getString("FirstName"), resultSet.getString("LastName")); } }
@Override public Void run(TransactionContext transaction) throws Exception { // Insert record. String sql = "INSERT INTO Singers (SingerId, FirstName, LastName) " + " VALUES (11, 'Timothy', 'Campbell')"; long rowCount = transaction.executeUpdate(Statement.of(sql)); System.out.printf("%d record inserted.\n", rowCount); // Read newly inserted record. sql = "SELECT FirstName, LastName FROM Singers WHERE SingerId = 11"; ResultSet resultSet = transaction.executeQuery(Statement.of(sql)); while (resultSet.next()) { System.out.printf( "%s %s\n", resultSet.getString("FirstName"), resultSet.getString("LastName")); } return null; } });
static void readUsingIndex(DatabaseClient dbClient) { ResultSet resultSet = dbClient .singleUse() .readUsingIndex( "Albums", "AlbumsByAlbumTitle", KeySet.all(), Arrays.asList("AlbumId", "AlbumTitle")); while (resultSet.next()) { System.out.printf("%d %s\n", resultSet.getLong(0), resultSet.getString(1)); } } // [END spanner_read_data_with_index]
static void read(DatabaseClient dbClient) { ResultSet resultSet = dbClient .singleUse() .read( "Albums", // KeySet.all() can be used to read all rows in a table. KeySet exposes other // methods to read only a subset of the table. KeySet.all(), Arrays.asList("SingerId", "AlbumId", "AlbumTitle")); while (resultSet.next()) { System.out.printf( "%d %d %s\n", resultSet.getLong(0), resultSet.getLong(1), resultSet.getString(2)); } } // [END spanner_read_data]
static void query(DatabaseClient dbClient) { // singleUse() can be used to execute a single read or query against Cloud Spanner. ResultSet resultSet = dbClient .singleUse() .executeQuery(Statement.of("SELECT SingerId, AlbumId, AlbumTitle FROM Albums")); while (resultSet.next()) { System.out.printf( "%d %d %s\n", resultSet.getLong(0), resultSet.getLong(1), resultSet.getString(2)); } } // [END spanner_query_data]
static void readOnlyTransaction(DatabaseClient dbClient) { // ReadOnlyTransaction must be closed by calling close() on it to release resources held by it. // We use a try-with-resource block to automatically do so. try (ReadOnlyTransaction transaction = dbClient.readOnlyTransaction()) { ResultSet queryResultSet = transaction.executeQuery( Statement.of("SELECT SingerId, AlbumId, AlbumTitle FROM Albums")); while (queryResultSet.next()) { System.out.printf( "%d %d %s\n", queryResultSet.getLong(0), queryResultSet.getLong(1), queryResultSet.getString(2)); } ResultSet readResultSet = transaction.read( "Albums", KeySet.all(), Arrays.asList("SingerId", "AlbumId", "AlbumTitle")); while (readResultSet.next()) { System.out.printf( "%d %d %s\n", readResultSet.getLong(0), readResultSet.getLong(1), readResultSet.getString(2)); } } } // [END spanner_read_only_transaction]
static void readStoringIndex(DatabaseClient dbClient) { // We can read MarketingBudget also from the index since it stores a copy of MarketingBudget. ResultSet resultSet = dbClient .singleUse() .readUsingIndex( "Albums", "AlbumsByAlbumTitle2", KeySet.all(), Arrays.asList("AlbumId", "AlbumTitle", "MarketingBudget")); while (resultSet.next()) { System.out.printf( "%d %s %s\n", resultSet.getLong(0), resultSet.getString(1), resultSet.isNull("MarketingBudget") ? "NULL" : resultSet.getLong("MarketingBudget")); } } // [END spanner_read_data_with_storing_index]
static void queryUsingIndex(DatabaseClient dbClient) { Statement statement = Statement // We use FORCE_INDEX hint to specify which index to use. For more details see // https://cloud.google.com/spanner/docs/query-syntax#from-clause .newBuilder( "SELECT AlbumId, AlbumTitle, MarketingBudget\n" + "FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}\n" + "WHERE AlbumTitle >= @StartTitle AND AlbumTitle < @EndTitle") // We use @BoundParameters to help speed up frequently executed queries. // For more details see https://cloud.google.com/spanner/docs/sql-best-practices .bind("StartTitle") .to("Aardvark") .bind("EndTitle") .to("Goo") .build(); ResultSet resultSet = dbClient.singleUse().executeQuery(statement); while (resultSet.next()) { System.out.printf( "%d %s %s\n", resultSet.getLong("AlbumId"), resultSet.getString("AlbumTitle"), resultSet.isNull("MarketingBudget") ? "NULL" : resultSet.getLong("MarketingBudget")); } } // [END spanner_query_data_with_index]
System.out.printf("%d %s\n", resultSet.getLong("SingerId"), resultSet.getString(1));
@ProcessElement public void processElement(ProcessContext c) throws Exception { SpannerSchema.Builder builder = SpannerSchema.builder(); DatabaseClient databaseClient = spannerAccessor.getDatabaseClient(); try (ReadOnlyTransaction tx = databaseClient.readOnlyTransaction()) { ResultSet resultSet = readTableInfo(tx); while (resultSet.next()) { String tableName = resultSet.getString(0); String columnName = resultSet.getString(1); String type = resultSet.getString(2); long cellsMutated = resultSet.getLong(3); builder.addColumn(tableName, columnName, type, cellsMutated); } resultSet = readPrimaryKeyInfo(tx); while (resultSet.next()) { String tableName = resultSet.getString(0); String columnName = resultSet.getString(1); String ordering = resultSet.getString(2); builder.addKeyPart(tableName, columnName, "DESC".equalsIgnoreCase(ordering)); } } c.output(builder.build()); }
private void listColumns(Ddl.Builder builder) { ResultSet resultSet = context.executeQuery( Statement.newBuilder( "SELECT c.table_name, c.column_name," + " c.ordinal_position, c.spanner_type, c.is_nullable" + " FROM information_schema.columns as c" + " WHERE c.table_catalog = '' AND c.table_schema = '' " + " ORDER BY c.table_name, c.ordinal_position") .build()); while (resultSet.next()) { String tableName = resultSet.getString(0); String columnName = resultSet.getString(1); String spannerType = resultSet.getString(3); boolean nullable = resultSet.getString(4).equalsIgnoreCase("YES"); builder .createTable(tableName) .column(columnName) .parseType(spannerType) .notNull(!nullable) .endColumn() .endTable(); } }