Skip to content
Advertisement

Cloud Spanner ResultSet Get Length

dmo

I have the following piece of code:

static void query(DatabaseClient dbClient) {
  try (ResultSet resultSet =
      dbClient
          .singleUse() // Execute a single read or query against Cloud Spanner.
          .executeQuery(Statement.of("SELECT SingerId, AlbumId, AlbumTitle FROM Albums"))) {
    if (resultSet is of size 1) { do something }
    while (resultSet.next()) {
      System.out.printf(
          "%d %d %sn", resultSet.getLong(0), resultSet.getLong(1), resultSet.getString(2));
    }
  }
}

Problem: I want to use the see if resultSet has strictly a length of 1.

Attempt at solution: resultSet.getStats().getRowCountExact() == 1

But:

ResultSetStats getStats() returns the ResultSetStats for the query only if the query was executed in either the PLAN or the PROFILE mode via the ReadContext.analyzeQuery(Statement, com.google.cloud.spanner.ReadContext.QueryAnalyzeMode) method or for DML statements in ReadContext#executeQuery(Statement, QueryOption…).

I’m not sure how to execute a query in PLAN or PROFILE mode, is there some sort of file in my Java project that allows me to declare this, or a setting on Cloud Spanner?

I’m also open to other solutions to my problem if anyone has any ideas, not sure what else could be done given the 4 methods (check the link above).

Advertisement

Answer

Unfortunately, what you are trying to do cannot be achieved by looking at the statistics of the ResultSet.

You can execute a query in PROFILE or PLAN mode by calling the method DatabaseClient#analyzeQuery(Statement, QueryAnalyzeMode). That would look like this based on your example:

static void query(DatabaseClient dbClient) {
  try (ResultSet resultSet =
      dbClient
          .singleUse() // Execute a single read or query against Cloud Spanner.
          .analyzeQuery(Statement.of("SELECT SingerId, AlbumId, AlbumTitle FROM Albums", QueryAnalyzeMode.PROFILE))) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %d %sn", resultSet.getLong(0), resultSet.getLong(1), resultSet.getString(2));
    }
  }
}

BUT: The statistics of the result set will only be available once you have consumed the entire result set (i.e. once the ResultSet#next() method has returned false). So you cannot do the check ‘what is the number of records in the result set’ before consuming it.

The best way to achieve what you are trying to do, is to execute a SELECT COUNT(*) FROM ... query first, and then based on that, determine whether you want to execute the second query as well.

Advertisement