Refine search
spanner.getDatabaseClient(DatabaseId.of(options.getProjectId(), instanceId, databaseId)); try (ResultSet resultSet = dbClient.singleUse().executeQuery(Statement.of("SELECT 1"))) { System.out.println("\n\nResults:"); while (resultSet.next()) { System.out.printf("%d\n\n", resultSet.getLong(0));
ResultSetStats analyzeQuery() { // [START read_context_analyze_query] ReadContext rc = dbClient.singleUse(); ResultSet resultSet = rc.analyzeQuery( Statement.of("SELECT SingerId, AlbumId, MarketingBudget FROM Albums"), ReadContext.QueryAnalyzeMode.PROFILE); while (resultSet.next()) { // Discard the results. We're only processing because getStats() below requires it. resultSet.getCurrentRowAsStruct(); } ResultSetStats stats = resultSet.getStats(); // [END read_context_analyze_query] return stats; } }
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] }
private Struct consumeSingleRow(ResultSet resultSet) { if (!resultSet.next()) { return null; } Struct row = resultSet.getCurrentRowAsStruct(); if (resultSet.next()) { throw newSpannerException(ErrorCode.INTERNAL, "Multiple rows returned for single key"); } return row; } }
private Status readUsingQuery( String table, String key, Set<String> fields, Map<String, ByteIterator> result) { Statement query; Iterable<String> columns = fields == null ? STANDARD_FIELDS : fields; if (fields == null || fields.size() == fieldCount) { query = Statement.newBuilder(standardQuery).bind("key").to(key).build(); } else { Joiner joiner = Joiner.on(','); query = Statement.newBuilder("SELECT ") .append(joiner.join(fields)) .append(" FROM ") .append(table) .append(" WHERE id=@key") .bind("key").to(key) .build(); } try (ResultSet resultSet = dbClient.singleUse(timestampBound).executeQuery(query)) { resultSet.next(); decodeStruct(columns, resultSet, result); if (resultSet.next()) { throw new Exception("Expected exactly one row for each read."); } return Status.OK; } catch (Exception e) { LOGGER.log(Level.INFO, "readUsingQuery()", e); return Status.ERROR; } }
private void listTables(Ddl.Builder builder) { ResultSet resultSet = context.executeQuery( Statement.of( "SELECT t.table_name, t.parent_table_name, t.on_delete_action" + " FROM information_schema.tables AS t" + " WHERE t.table_catalog = '' AND t.table_schema = ''")); while (resultSet.next()) { String tableName = resultSet.getString(0); String parentTableName = resultSet.isNull(1) ? null : resultSet.getString(1); String onDeleteAction = resultSet.isNull(2) ? null : resultSet.getString(2);
rs.getType(); fail("Exception expected"); } catch (IllegalStateException e) { assertThat(rs.next()).isTrue(); assertThat(rs.getType()).isEqualTo(type); assertThat(rs.getColumnCount()).isEqualTo(type.getStructFields().size()); assertThat(rs.getColumnIndex("f1")).isEqualTo(0); assertThat(rs.getColumnType("nonexistent")).isNull(); assertThat(rs.getColumnType("f1")).isEqualTo(Type.string()); assertThat(rs.getColumnType(0)).isEqualTo(Type.string()); assertThat(rs.getColumnIndex("f2")).isEqualTo(1); assertThat(rs.getColumnType("f2")).isEqualTo(Type.int64()); assertThat(rs.getColumnType(1)).isEqualTo(Type.int64()); assertThat(rs.getColumnIndex("f3")).isEqualTo(2); assertThat(rs.getColumnType("f3")).isEqualTo(Type.bool()); 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.getBoolean("f3")).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.getTimestamp(6)).isEqualTo(Timestamp.ofTimeMicroseconds(usecs));
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]
@Test public void analyzeProfile() { Statement statement = Statement.of("SELECT 1 AS column UNION ALL SELECT 2 AS column ORDER BY column"); ResultSet resultSet = statement.analyzeQuery(client.singleUse(TimestampBound.strong()), QueryAnalyzeMode.PROFILE); assertThat(resultSet.next()).isTrue(); assertThat(resultSet.getType()).isEqualTo(Type.struct(StructField.of("column", Type.int64()))); assertThat(resultSet.getLong(0)).isEqualTo(1); assertThat(resultSet.next()).isTrue(); assertThat(resultSet.getLong(0)).isEqualTo(2); assertThat(resultSet.next()).isFalse(); ResultSetStats receivedStats = resultSet.getStats(); assertThat(receivedStats).isNotNull(); assertThat(receivedStats.hasQueryPlan()).isTrue(); assertThat(receivedStats.hasQueryStats()).isTrue(); }
/** * Return a map where key is the table name and the value is the parent table name. If * the table name in the key has no parent then the value is null. * @return the map of the table names. */ public Map<String, String> getChildParentTablesMap() { Map<String, String> relationships = new HashMap<>(); ResultSet results = this.databaseClient.singleUse() .executeQuery(TABLE_AND_PARENT_QUERY); while (results.next()) { Struct row = results.getCurrentRowAsStruct(); relationships.put(row.getString(TABLE_NAME_COL_NAME), row.isNull(PARENT_TABLE_NAME_COL_NAME) ? null : row.getString(PARENT_TABLE_NAME_COL_NAME)); } results.close(); return relationships; }
@Override public Struct read(ReadContext ctx, String key) { ResultSet resultSet = ctx.executeQuery( Statement.newBuilder("SELECT V FROM T WHERE K = @key") .bind("key") .to(key) .build()); assertThat(resultSet.next()).isTrue(); Struct row = resultSet.getCurrentRowAsStruct(); assertThat(resultSet.next()).isFalse(); return row; } });
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(); } }
TransactionRunner runner = client.readWriteTransaction(); runner.run(callable); fail("Expected user exception"); .singleUse(TimestampBound.strong()) .read("T", KeySet.range(KeyRange.prefix(Key.of("boo"))), Arrays.asList("K")); assertThat(resultSet.next()).isFalse();
"CREATE TABLE T ( K STRING(MAX) NOT NULL, V STRING(MAX) ) PRIMARY KEY (K)"); DatabaseClient client = env.getTestHelper().getDatabaseClient(populatedDb); client.writeAtLeastOnce( asList( Mutation.newInsertBuilder("T").set("K").to("k1").set("V").to("v1").build(), Statement.newBuilder("SELECT K, V FROM T WHERE K >= @min AND K < @max ORDER BY K ASC") .bind("min") .to("k13") .to("k32") .build(); ResultSet resultSet = statement.executeQuery(client.singleUse(TimestampBound.strong())); assertThat(resultSet.next()).isTrue(); assertThat(resultSet.getType()) .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();
limit != 0 ? client .singleUse(TimestampBound.strong()) .readUsingIndex( TABLE_NAME, INDEX_NAME, keySet, ALL_COLUMNS, Options.limit(limit)) : client .singleUse(TimestampBound.strong()) .readUsingIndex(TABLE_NAME, INDEX_NAME, keySet, ALL_COLUMNS); break; case DESC_INDEX: limit != 0 ? client .singleUse(TimestampBound.strong()) .readUsingIndex( TABLE_NAME, DESC_INDEX_NAME, keySet, ALL_COLUMNS, Options.limit(limit)) : client while (resultSet.next()) { rows.put(resultSet.getString(0), resultSet.getString(1));
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]
private List<Struct> resultRows(Statement statement, Type expectedRowType) { ArrayList<Struct> results = new ArrayList<>(); ResultSet resultSet = statement.executeQuery(client.singleUse(TimestampBound.strong())); while (resultSet.next()) { Struct row = resultSet.getCurrentRowAsStruct(); results.add(row); } assertThat(resultSet.getType()).isEqualTo(expectedRowType); assertThat(resultSet.next()).isFalse(); return results; }
private void keepAlive() { markUsed(); delegate .singleUse(TimestampBound.ofMaxStaleness(60, TimeUnit.SECONDS)) .executeQuery(Statement.newBuilder("SELECT 1").build()) .next(); }
static void readStaleData(DatabaseClient dbClient) { ResultSet resultSet = dbClient .singleUse(TimestampBound.ofExactStaleness(15, TimeUnit.SECONDS)) .read("Albums", KeySet.all(), Arrays.asList("SingerId", "AlbumId", "MarketingBudget")); while (resultSet.next()) { System.out.printf( "%d %d %s\n", resultSet.getLong(0), resultSet.getLong(1), resultSet.isNull(2) ? "NULL" : resultSet.getLong("MarketingBudget")); } } // [END spanner_read_stale_data]
@Override public long count(Class entityClass) { SpannerPersistentEntity<?> persistentEntity = this.mappingContext .getPersistentEntity(entityClass); Statement statement = Statement.of( String.format("SELECT COUNT(*) FROM %s", persistentEntity.tableName())); try (ResultSet resultSet = executeQuery(statement, null)) { resultSet.next(); return resultSet.getLong(0); } }