Java Code Examples for com.google.cloud.spanner.ResultSet#next()

static void query(DatabaseClient dbClient) {
  Statement statement = Statement.of(
      "SELECT p.PlayerId, p.PlayerName, s.Score, s.Timestamp "
        + "FROM Players p "
        + "JOIN Scores s ON p.PlayerId = s.PlayerId "
        + "ORDER BY s.Score DESC LIMIT 10");
  ResultSet resultSet = dbClient.singleUse().executeQuery(statement);
  while (resultSet.next()) {
    String scoreDate = String.valueOf(resultSet.getTimestamp("Timestamp"));
    String score = String.format("%,d", resultSet.getLong("Score"));
    System.out.printf(
        "PlayerId: %d  PlayerName: %s  Score: %s  Timestamp: %s\n",
        resultSet.getLong("PlayerId"), resultSet.getString("PlayerName"), score,
        scoreDate.substring(0,10));
  }
}
 

private static void queryMarketingBudget(PrintWriter pw) {
  // Rows without an explicit value for MarketingBudget will have a MarketingBudget equal to
  // null.
  ResultSet resultSet =
      SpannerClient.getDatabaseClient()
          .singleUse()
          .executeQuery(Statement.of("SELECT SingerId, AlbumId, MarketingBudget FROM Albums"));
  while (resultSet.next()) {
    pw.printf(
        "%d %d %s\n",
        resultSet.getLong("SingerId"),
        resultSet.getLong("AlbumId"),
        // We check that the value is non null. ResultSet getters can only be used to retrieve
        // non null values.
        resultSet.isNull("MarketingBudget") ? "NULL" : resultSet.getLong("MarketingBudget"));
  }
}
 

private static void readOnlyTransaction(PrintWriter pw) {
  // 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 =
      SpannerClient.getDatabaseClient().readOnlyTransaction()) {
    ResultSet queryResultSet =
        transaction.executeQuery(
            Statement.of("SELECT SingerId, AlbumId, AlbumTitle FROM Albums"));
    while (queryResultSet.next()) {
      pw.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()) {
      pw.printf(
          "%d %d %s\n",
          readResultSet.getLong(0), readResultSet.getLong(1), readResultSet.getString(2));
    }
  }
}
 

private static void readStoringIndex(PrintWriter pw) {
  // We can read MarketingBudget also from the index since it stores a copy of MarketingBudget.
  ResultSet resultSet =
      SpannerClient.getDatabaseClient()
          .singleUse()
          .readUsingIndex(
              "Albums",
              "AlbumsByAlbumTitle2",
              KeySet.all(),
              Arrays.asList("AlbumId", "AlbumTitle", "MarketingBudget"));
  while (resultSet.next()) {
    pw.printf(
        "%d %s %s\n",
        resultSet.getLong(0),
        resultSet.getString(1),
        resultSet.isNull("MarketingBudget") ? "NULL" : resultSet.getLong("MarketingBudget"));
  }
}
 

private static void queryUsingIndex(PrintWriter pw) {
  ResultSet resultSet =
      SpannerClient.getDatabaseClient()
          .singleUse()
          .executeQuery(
              // 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
              Statement.of(
                  "SELECT AlbumId, AlbumTitle, MarketingBudget\n"
                      + "FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}\n"
                      + "WHERE AlbumTitle >= 'Aardvark' AND AlbumTitle < 'Goo'"));
  while (resultSet.next()) {
    pw.printf(
        "%d %s %s\n",
        resultSet.getLong("AlbumId"),
        resultSet.getString("AlbumTitle"),
        resultSet.isNull("MarketingBudget") ? "NULL" : resultSet.getLong("MarketingBudget"));
  }
}
 

static void query(DatabaseClient dbClient, int timespan) {
  Statement statement =
      Statement
          .newBuilder(
            "SELECT p.PlayerId, p.PlayerName, s.Score, s.Timestamp "
            + "FROM Players p "
            + "JOIN Scores s ON p.PlayerId = s.PlayerId "
            + "WHERE s.Timestamp > "
            + "TIMESTAMP_SUB(CURRENT_TIMESTAMP(), "
            + "    INTERVAL @Timespan HOUR) "
            + "ORDER BY s.Score DESC LIMIT 10")
          .bind("Timespan")
          .to(timespan)
          .build();
  ResultSet resultSet = dbClient.singleUse().executeQuery(statement);
  while (resultSet.next()) {
    String scoreDate = String.valueOf(resultSet.getTimestamp("Timestamp"));
    String score = String.format("%,d", resultSet.getLong("Score"));
    System.out.printf(
        "PlayerId: %d  PlayerName: %s  Score: %s  Timestamp: %s\n",
        resultSet.getLong("PlayerId"), resultSet.getString("PlayerName"), score,
        scoreDate.substring(0,10));
  }
}
 

private static void queryUsingIndex(PrintWriter pw) {
  ResultSet resultSet =
      SpannerClient.getDatabaseClient()
          .singleUse()
          .executeQuery(
              // 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
              Statement.of(
                  "SELECT AlbumId, AlbumTitle, MarketingBudget\n"
                      + "FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}\n"
                      + "WHERE AlbumTitle >= 'Aardvark' AND AlbumTitle < 'Goo'"));
  while (resultSet.next()) {
    pw.printf(
        "%d %s %s\n",
        resultSet.getLong("AlbumId"),
        resultSet.getString("AlbumTitle"),
        resultSet.isNull("MarketingBudget") ? "NULL" : resultSet.getLong("MarketingBudget"));
  }
}
 

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();
  }
}
 

static void query(DatabaseClient dbClient, int timespan) {
  Statement statement =
      Statement
          .newBuilder(
            "SELECT p.PlayerId, p.PlayerName, s.Score, s.Timestamp "
            + "FROM Players p "
            + "JOIN Scores s ON p.PlayerId = s.PlayerId "
            + "WHERE s.Timestamp > "
            + "TIMESTAMP_SUB(CURRENT_TIMESTAMP(), "
            + "    INTERVAL @Timespan HOUR) "
            + "ORDER BY s.Score DESC LIMIT 10")
          .bind("Timespan")
          .to(timespan)
          .build();
  ResultSet resultSet = dbClient.singleUse().executeQuery(statement);
  while (resultSet.next()) {
    String scoreDate = String.valueOf(resultSet.getTimestamp("Timestamp"));
    String score = String.format("%,d", resultSet.getLong("Score"));
    System.out.printf(
        "PlayerId: %d  PlayerName: %s  Score: %s  Timestamp: %s\n",
        resultSet.getLong("PlayerId"), resultSet.getString("PlayerName"), score,
        scoreDate.substring(0,10));
  }
}
 

public void read(ReadQuery request, StreamObserver<EmptyResponse> responseObserver) {
  System.out.println("read has been called");

  try (ReadOnlyTransaction transaction = dbClient.readOnlyTransaction()) {
    ResultSet resultSet = transaction.executeQuery(Statement.of(request.getQuery()));
    while (resultSet.next()) {
      // Do nothing with the data.
    }
  }

  EmptyResponse reply = EmptyResponse.newBuilder().build();
  responseObserver.onNext(reply);
  responseObserver.onCompleted();
}
 

private static void query(PrintWriter pw) {
  // singleUse() can be used to execute a single read or query against Cloud Spanner.
  ResultSet resultSet =
      SpannerClient.getDatabaseClient()
          .singleUse()
          .executeQuery(Statement.of("SELECT SingerId, AlbumId, AlbumTitle FROM Albums"));
  while (resultSet.next()) {
    pw.printf("%d %d %s\n", resultSet.getLong(0), resultSet.getLong(1), resultSet.getString(2));
  }
}
 

private void listIndexes(Map<String, NavigableMap<String, Index.Builder>> indexes) {
  ResultSet resultSet =
      context.executeQuery(
          Statement.of(
              "SELECT t.table_name, t.index_name, t.parent_table_name,"
                  + " t.is_unique, t.is_null_filtered"
                  + " FROM information_schema.indexes AS t "
                  + " WHERE t.table_catalog = '' AND t.table_schema = '' AND t.index_type='INDEX'"
                  + " AND t.spanner_is_managed = FALSE"
                  + " ORDER BY t.table_name, t.index_name"));
  while (resultSet.next()) {
    String tableName = resultSet.getString(0);
    String indexName = resultSet.getString(1);
    String parent = resultSet.isNull(2) ? null : resultSet.getString(2);
    // should be NULL but is an empty string in practice.
    if (Strings.isNullOrEmpty(parent)) {
      parent = null;
    }
    boolean unique = resultSet.getBoolean(3);
    boolean nullFiltered = resultSet.getBoolean(4);

    Map<String, Index.Builder> tableIndexes =
        indexes.computeIfAbsent(tableName, k -> Maps.newTreeMap());

    tableIndexes.put(
        indexName,
        Index.builder()
            .name(indexName)
            .table(tableName)
            .unique(unique)
            .nullFiltered(nullFiltered)
            .interleaveIn(parent));
  }
}
 

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);

    // Error out when the parent table or on delete action are set incorrectly.
    if (Strings.isNullOrEmpty(parentTableName) != Strings.isNullOrEmpty(onDeleteAction)) {
      throw new IllegalStateException(
          String.format(
              "Invalid combination of parentTableName %s and onDeleteAction %s",
              parentTableName, onDeleteAction));
    }

    boolean onDeleteCascade = false;
    if (onDeleteAction != null) {
      if (onDeleteAction.equals("CASCADE")) {
        onDeleteCascade = true;
      } else if (!onDeleteAction.equals("NO ACTION")) {
        // This is an unknown on delete action.
        throw new IllegalStateException("Unsupported on delete action " + onDeleteAction);
      }
    }
    LOG.debug(
        "Schema Table {} Parent {} OnDelete {} {}", tableName, parentTableName, onDeleteCascade);
    builder
        .createTable(tableName)
        .interleaveInParent(parentTableName)
        .onDeleteCascade(onDeleteCascade)
        .endTable();
  }
}
 

private static void readUsingIndex(PrintWriter pw) {
  ResultSet resultSet =
      SpannerClient.getDatabaseClient()
          .singleUse()
          .readUsingIndex(
              "Albums",
              "AlbumsByAlbumTitle",
              KeySet.all(),
              Arrays.asList("AlbumId", "AlbumTitle"));
  while (resultSet.next()) {
    pw.printf("%d %s\n", resultSet.getLong(0), resultSet.getString(1));
  }
}
 

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;
}
 

private static void query(PrintWriter pw) {
  // singleUse() can be used to execute a single read or query against Cloud Spanner.
  ResultSet resultSet =
      SpannerClient.getDatabaseClient()
          .singleUse()
          .executeQuery(Statement.of("SELECT SingerId, AlbumId, AlbumTitle FROM Albums"));
  while (resultSet.next()) {
    pw.printf("%d %d %s\n", resultSet.getLong(0), resultSet.getLong(1), resultSet.getString(2));
  }
}
 

private static void read(PrintWriter pw) {
  ResultSet resultSet =
      SpannerClient.getDatabaseClient()
          .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()) {
    pw.printf("%d %d %s\n", resultSet.getLong(0), resultSet.getLong(1), resultSet.getString(2));
  }
}
 

private static void readUsingIndex(PrintWriter pw) {
  ResultSet resultSet =
      SpannerClient.getDatabaseClient()
          .singleUse()
          .readUsingIndex(
              "Albums",
              "AlbumsByAlbumTitle",
              KeySet.all(),
              Arrays.asList("AlbumId", "AlbumTitle"));
  while (resultSet.next()) {
    pw.printf("%d %s\n", resultSet.getLong(0), resultSet.getString(1));
  }
}
 

private void listIndexColumns(
    Ddl.Builder builder, Map<String, NavigableMap<String, Index.Builder>> indexes) {
  ResultSet resultSet =
      context.executeQuery(
          Statement.of(
              "SELECT t.table_name, t.column_name, t.column_ordering, t.index_name "
                  + "FROM information_schema.index_columns AS t "
                  + "WHERE t.table_catalog = '' AND t.table_schema "
                  + "= ''  ORDER BY t.table_name, t.index_name, t.ordinal_position"));
  while (resultSet.next()) {
    String tableName = resultSet.getString(0);
    String columnName = resultSet.getString(1);
    String ordering = resultSet.isNull(2) ? null : resultSet.getString(2);
    String indexName = resultSet.getString(3);

    if (indexName.equals("PRIMARY_KEY")) {
      IndexColumn.IndexColumnsBuilder<Table.Builder> pkBuilder =
          builder.createTable(tableName).primaryKey();
      if (ordering.equalsIgnoreCase("ASC")) {
        pkBuilder.asc(columnName).end();
      } else {
        pkBuilder.desc(columnName).end();
      }
      pkBuilder.end().endTable();
    } else {
      Map<String, Index.Builder> tableIndexes = indexes.get(tableName);
      if (tableIndexes == null) {
        continue;
      }
      Index.Builder indexBuilder = tableIndexes.get(indexName);
      if (indexBuilder == null) {
        continue;
      }
      if (ordering == null) {
        indexBuilder.columns().storing(columnName).end();
      } else if (ordering.equalsIgnoreCase("ASC")) {
        indexBuilder.columns().asc(columnName).end();
      } else if (ordering.equalsIgnoreCase("DESC")) {
        indexBuilder.columns().desc(columnName).end();
      }
    }
  }
}
 

static void insertScores(DatabaseClient dbClient) {
  boolean playerRecordsFound = false;
  ResultSet resultSet =
      dbClient
          .singleUse()
          .executeQuery(Statement.of("SELECT * FROM Players"));
  while (resultSet.next()) {
    playerRecordsFound = true;
    final long playerId = resultSet.getLong("PlayerId");
    dbClient
        .readWriteTransaction()
        .run(
            new TransactionCallable<Void>() {
              @Override
              public Void run(TransactionContext transaction) throws Exception {
                // Initialize objects for random Score and random Timestamp
                LocalDate endDate = LocalDate.now();
                long end = endDate.toEpochDay();
                int startYear = endDate.getYear() - 2;
                int startMonth = endDate.getMonthValue();
                int startDay = endDate.getDayOfMonth();
                LocalDate startDate = LocalDate.of(startYear, startMonth, startDay);
                long start = startDate.toEpochDay();
                Random r = new Random();
                List<Statement> stmts = new ArrayList<Statement>();
                // Insert 4 score records into the Scores table 
                // for each player in the Players table.
                for (int x = 1; x <= 4; x++) {
                  // Generate random score between 1,000,000 and 1,000
                  long randomScore = r.nextInt(1000000 - 1000) + 1000;
                  // Get random day within the past two years.
                  long randomDay = ThreadLocalRandom.current().nextLong(start, end);
                  LocalDate randomDayDate = LocalDate.ofEpochDay(randomDay);
                  LocalTime randomTime = LocalTime.of(
                      r.nextInt(23), r.nextInt(59), r.nextInt(59), r.nextInt(9999));
                  LocalDateTime randomDate = LocalDateTime.of(randomDayDate, randomTime);
                  Instant randomInstant = randomDate.toInstant(ZoneOffset.UTC);
                  Statement statement =
                      Statement
                      .newBuilder(
                        "INSERT INTO Scores (PlayerId, Score, Timestamp) "
                        + "VALUES (@PlayerId, @Score, @Timestamp) ")
                      .bind("PlayerId")
                      .to(playerId)
                      .bind("Score")
                      .to(randomScore)
                      .bind("Timestamp")
                      .to(randomInstant.toString())
                      .build();
                  stmts.add(statement);
                }
                transaction.batchUpdate(stmts);
                return null;
              }
            });
  }
  if (!playerRecordsFound) {
    System.out.println("Parameter 'scores' is invalid since "
        + "no player records currently exist. First insert players "
        + "then insert scores.");
    System.exit(1);
  } else {
    System.out.println("Done inserting score records...");
  }
}