com.google.cloud.spanner.Statement Java Examples

/** Function to get all column names from the table. */
private LinkedHashMap<String, String> getAllColumns(ReadContext context, String tableName) {
  LinkedHashMap<String, String> columns = Maps.newLinkedHashMap();
  ResultSet resultSet =
      context.executeQuery(
          Statement.newBuilder(
                  "SELECT COLUMN_NAME, SPANNER_TYPE FROM INFORMATION_SCHEMA.COLUMNS "
                      + "WHERE TABLE_NAME=@table_name ORDER BY ORDINAL_POSITION")
              .bind("table_name")
              .to(tableName)
              .build());
  LOG.info("Got schema information. Reading columns.");
  while (resultSet.next()) {
    Struct currentRow = resultSet.getCurrentRowAsStruct();
    columns.put(currentRow.getString(0), currentRow.getString(1));
  }
  return columns;
}
 

@Test
public void readWriteTransactionPartitionedDmlTest() {

	this.expectedException.expectMessage("A read-write transaction template cannot execute partitioned" +
			" DML.");

	TransactionRunner transactionRunner = mock(TransactionRunner.class);
	when(this.databaseClient.readWriteTransaction()).thenReturn(transactionRunner);

	TransactionContext transactionContext = mock(TransactionContext.class);

	when(transactionRunner.run(any())).thenAnswer((invocation) -> {
		TransactionCallable transactionCallable = invocation.getArgument(0);
		return transactionCallable.run(transactionContext);
	});

	this.spannerTemplate
			.performReadWriteTransaction((spannerTemplate) -> {
				spannerTemplate.executePartitionedDmlStatement(Statement.of("DML statement here"));
				return "all done";
			});
}
 

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

static void queryWithInt(DatabaseClient dbClient) {
  long exampleInt = 3000;
  Statement statement =
      Statement.newBuilder(
              "SELECT VenueId, VenueName, Capacity FROM Venues " + "WHERE Capacity >= @capacity")
          .bind("capacity")
          .to(exampleInt)
          .build();
  try (ResultSet resultSet = dbClient.singleUse().executeQuery(statement)) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %s %d\n",
          resultSet.getLong("VenueId"),
          resultSet.getString("VenueName"),
          resultSet.getLong("Capacity"));
    }
  }
}
 

static void queryWithArray(DatabaseClient dbClient) {
  Value exampleArray =
      Value.dateArray(Arrays.asList(Date.parseDate("2020-10-01"), Date.parseDate("2020-11-01")));

  Statement statement =
      Statement.newBuilder(
              "SELECT VenueId, VenueName, AvailableDate FROM Venues v, "
                  + "UNNEST(v.AvailableDates) as AvailableDate "
                  + "WHERE AvailableDate in UNNEST(@availableDates)")
          .bind("availableDates")
          .to(exampleArray)
          .build();
  try (ResultSet resultSet = dbClient.singleUse().executeQuery(statement)) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %s %s\n",
          resultSet.getLong("VenueId"),
          resultSet.getString("VenueName"),
          resultSet.getDate("AvailableDate"));
    }
  }
}
 

private void prepareColumnMetadata(ReadOnlyTransaction tx, List<Struct> rows) {
  Type type =
      Type.struct(
          Type.StructField.of("table_name", Type.string()),
          Type.StructField.of("column_name", Type.string()),
          Type.StructField.of("spanner_type", Type.string()),
          Type.StructField.of("cells_mutated", Type.int64()));
  when(tx.executeQuery(
          argThat(
              new ArgumentMatcher<Statement>() {

                @Override
                public boolean matches(Statement argument) {
                  if (!(argument instanceof Statement)) {
                    return false;
                  }
                  Statement st = (Statement) argument;
                  return st.getSql().contains("information_schema.columns");
                }
              })))
      .thenReturn(ResultSets.forRows(type, rows));
}
 

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 insertUsingDml(DatabaseClient dbClient) {
  dbClient
      .readWriteTransaction()
      .run(
          new TransactionCallable<Void>() {
            @Override
            public Void run(TransactionContext transaction) throws Exception {
              String sql =
                  "INSERT INTO Singers (SingerId, FirstName, LastName) "
                      + " VALUES (10, 'Virginia', 'Watson')";
              long rowCount = transaction.executeUpdate(Statement.of(sql));
              System.out.printf("%d record inserted.\n", rowCount);
              return null;
            }
          });
}
 

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 "
                  + "FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle} "
                  + "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();
  try (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"));
    }
  }
}
 

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]

}
 

static void updateUsingDmlWithStruct(DatabaseClient dbClient) {
  Struct name =
      Struct.newBuilder().set("FirstName").to("Timothy").set("LastName").to("Campbell").build();
  Statement s =
      Statement.newBuilder(
              "UPDATE Singers SET LastName = 'Grant' "
                  + "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
                  + "= @name")
          .bind("name")
          .to(name)
          .build();
  dbClient
      .readWriteTransaction()
      .run(
          new TransactionCallable<Void>() {
            @Override
            public Void run(TransactionContext transaction) throws Exception {
              long rowCount = transaction.executeUpdate(s);
              System.out.printf("%d record updated.\n", rowCount);
              return null;
            }
          });
}
 

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

/**
 * Updates batch of existing records to different values.
 */
public void batchUpdate(int count) {
  String query = "SELECT id FROM " + AIRPORT_TABLE + " LIMIT " + count;
  Statement statement = Statement.newBuilder(query).build();
  ResultSet resultSet =
      databaseClient.singleUseReadOnlyTransaction().executeQuery(statement);

  ArrayList<String> allRowIds = new ArrayList<>();
  while (resultSet.next()) {
    allRowIds.add(resultSet.getCurrentRowAsStruct().getString("id"));
  }

  // Updates all airport's plane_capacity field.
  ArrayList<Mutation> updateMutations = new ArrayList<>();
  for (String id : allRowIds) {
    Mutation mutation = Mutation.newUpdateBuilder(AIRPORT_TABLE)
        .set("id").to(id)
        .set("plane_capacity").to("2222")
        .build();

    updateMutations.add(mutation);
  }

  this.databaseClient.write(updateMutations);
}
 

/**
 * Acknowledges the {@link QueueMessage} and removes it from the processing Queue.
 *
 * @param dbClient the Spanner database client
 * @param message the message to acknowledge
 */
public static void ack(DatabaseClient dbClient, QueueMessage message) {
  Preconditions.checkNotNull(dbClient);
  Preconditions.checkNotNull(message);

  dbClient
      .readWriteTransaction()
      .run(
          new TransactionCallable<Void>() {
            @Override
            public Void run(TransactionContext transaction) throws Exception {
              String sql =
                  "UPDATE "
                      + message.queueName()
                      + " SET Ack = true"
                      + " WHERE MessageId = "
                      + message.id();
              final long rowCount = transaction.executeUpdate(Statement.of(sql));
              Preconditions.checkArgument(
                  rowCount == 1,
                  "Ack function acknowledged too many messages on a single commit");

              return null;
            }
          });
}
 

public Poller(SpezConfig config) {
  this.avroNamespace = config.avroNamespace;
  this.instanceName = config.instanceName;
  this.dbName = config.dbName;
  this.tableName = config.tableName;
  this.pollRate = config.pollRate;
  this.recordLimit = config.recordLimit;
  this.startingTimestamp = config.startingTimestamp;
  this.publishToPubSub = config.publishToPubSub;
  this.lastTimestamp = this.startingTimestamp;
  this.dbClient = configureDb();
  this.publisher = configurePubSub();
  this.schemaQuery =
      Statement.newBuilder(
              "SELECT COLUMN_NAME, SPANNER_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@tablename ORDER BY ORDINAL_POSITION")
          .bind("tablename")
          .to(tableName)
          .build();
  configureTracing();
}
 

private ResultSet readTableInfo(ReadOnlyTransaction tx) {
  // retrieve schema information for all tables, as well as aggregating the
  // number of indexes that cover each column. this will be used to estimate
  // the number of cells (table column plus indexes) mutated in an upsert operation
  // in order to stay below the 20k threshold
  return tx.executeQuery(
      Statement.of(
          "SELECT"
              + "    c.table_name"
              + "  , c.column_name"
              + "  , c.spanner_type"
              + "  , (1 + COALESCE(t.indices, 0)) AS cells_mutated"
              + "  FROM ("
              + "    SELECT c.table_name, c.column_name, c.spanner_type, c.ordinal_position"
              + "     FROM information_schema.columns as c"
              + "     WHERE c.table_catalog = '' AND c.table_schema = '') AS c"
              + "  LEFT OUTER JOIN ("
              + "    SELECT t.table_name, t.column_name, COUNT(*) AS indices"
              + "      FROM information_schema.index_columns AS t "
              + "      WHERE t.index_name != 'PRIMARY_KEY' AND t.table_catalog = ''"
              + "      AND t.table_schema = ''"
              + "      GROUP BY t.table_name, t.column_name) AS t"
              + "  USING (table_name, column_name)"
              + "  ORDER BY c.table_name, c.ordinal_position"));
}
 

static void queryWithBytes(DatabaseClient dbClient) {
  ByteArray exampleBytes =
      ByteArray.fromBase64(BaseEncoding.base64().encode("Hello World 1".getBytes()));
  Statement statement =
      Statement.newBuilder(
              "SELECT VenueId, VenueName FROM Venues " + "WHERE VenueInfo = @venueInfo")
          .bind("venueInfo")
          .to(exampleBytes)
          .build();
  try (ResultSet resultSet = dbClient.singleUse().executeQuery(statement)) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %s\n", resultSet.getLong("VenueId"), resultSet.getString("VenueName"));
    }
  }
}
 

static void queryMarketingBudget(DatabaseClient dbClient) {
  // Rows without an explicit value for MarketingBudget will have a MarketingBudget equal to
  // null. A try-with-resource block is used to automatically release resources held by
  // ResultSet.
  try (ResultSet resultSet =
      dbClient
          .singleUse()
          .executeQuery(Statement.of("SELECT SingerId, AlbumId, MarketingBudget FROM Albums"))) {
    while (resultSet.next()) {
      System.out.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"));
    }
  }
}
 

static void queryWithBool(DatabaseClient dbClient) {
  boolean exampleBool = true;
  Statement statement =
      Statement.newBuilder(
              "SELECT VenueId, VenueName, OutdoorVenue FROM Venues "
                  + "WHERE OutdoorVenue = @outdoorVenue")
          .bind("outdoorVenue")
          .to(exampleBool)
          .build();
  try (ResultSet resultSet = dbClient.singleUse().executeQuery(statement)) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %s %b\n",
          resultSet.getLong("VenueId"),
          resultSet.getString("VenueName"),
          resultSet.getBoolean("OutdoorVenue"));
    }
  }
}
 

static void queryWithDate(DatabaseClient dbClient) {
  String exampleDate = "2019-01-01";
  Statement statement =
      Statement.newBuilder(
              "SELECT VenueId, VenueName, LastContactDate FROM Venues "
                  + "WHERE LastContactDate < @lastContactDate")
          .bind("lastContactDate")
          .to(exampleDate)
          .build();
  try (ResultSet resultSet = dbClient.singleUse().executeQuery(statement)) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %s %s\n",
          resultSet.getLong("VenueId"),
          resultSet.getString("VenueName"),
          resultSet.getDate("LastContactDate"));
    }
  }
}
 

@Test
public void equalsHashcodeTest() {
	BeforeExecuteDmlEvent beforeExecuteDmlEvent = new BeforeExecuteDmlEvent(Statement.of("a"));
	BeforeExecuteDmlEvent beforeExecuteDmlEvent1 = new BeforeExecuteDmlEvent(Statement.of("a"));

	BeforeExecuteDmlEvent beforeExecuteDmlEvent2 = new BeforeExecuteDmlEvent(Statement.of("b"));

	assertThat(beforeExecuteDmlEvent).isEqualTo(beforeExecuteDmlEvent);
	assertThat(beforeExecuteDmlEvent).isEqualTo(beforeExecuteDmlEvent1);
	assertThat(beforeExecuteDmlEvent2).isNotEqualTo(beforeExecuteDmlEvent);
	assertThat(beforeExecuteDmlEvent).isNotEqualTo(null);
	assertThat(beforeExecuteDmlEvent).isNotEqualTo(new Object());

	assertThat(beforeExecuteDmlEvent.hashCode()).isEqualTo(beforeExecuteDmlEvent.hashCode());
	assertThat(beforeExecuteDmlEvent.hashCode()).isEqualTo(beforeExecuteDmlEvent1.hashCode());
	assertThat(beforeExecuteDmlEvent2.hashCode()).isNotEqualTo(beforeExecuteDmlEvent.hashCode());
}
 

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

/**
 * Creates a Cloud Spanner statement.
 * @param sql the SQL string with tags.
 * @param tags the tags that appear in the SQL string.
 * @param paramStructConvertFunc a function to use to convert params to {@link Struct}
 *     objects if they cannot be directly mapped to Cloud Spanner supported param types.
 *     If null then this last-attempt conversion is skipped.
 * @param spannerCustomConverter a converter used to convert params that aren't Cloud
 *     Spanner native types. if {@code null} then this conversion is not attempted.
 * @param params the parameters to substitute the tags. The ordering must be the same as
 *     the tags.
 * @param queryMethodParams the parameter metadata from Query Method if available.
 * @return an SQL statement ready to use with Spanner.
 * @throws IllegalArgumentException if the number of tags does not match the number of
 *     params, or if a param of an unsupported type is given.
 */
public static Statement buildStatementFromSqlWithArgs(String sql, List<String> tags,
		Function<Object, Struct> paramStructConvertFunc, SpannerCustomConverter spannerCustomConverter,
		Object[] params, Map<String, Parameter> queryMethodParams) {
	if (tags == null && params == null) {
		return Statement.of(sql);
	}
	if (tags == null || params == null || tags.size() != params.length) {
		throw new IllegalArgumentException(
				"The number of tags does not match the number of params.");
	}
	Statement.Builder builder = Statement.newBuilder(sql);
	for (int i = 0; i < tags.size(); i++) {
		bindParameter(builder.bind(tags.get(i)), paramStructConvertFunc, spannerCustomConverter,
				params[i], queryMethodParams == null ? null : queryMethodParams.get(tags.get(i)));
	}
	return builder.build();
}
 

@Test
@SuppressWarnings("unchecked")
public void getColumnsStringForSelectMultipleTest() {
	final SpannerPersistentEntity<TestEntity> entity = (SpannerPersistentEntity<TestEntity>)
			this.spannerMappingContext.getPersistentEntity(TestEntity.class);
	Statement childrenRowsQuery = SpannerStatementQueryExecutor.buildQuery(
			KeySet.newBuilder().addKey(Key.of("k1.1", "k1.2")).addKey(Key.of("k2.1", "k2.2")).build(),
			entity, new SpannerWriteConverter(),
			this.spannerMappingContext, entity.getWhere());

	assertThat(childrenRowsQuery.getSql())
			.isEqualTo(
					"SELECT other, deleted, id, custom_col, id_2, ARRAY (SELECT AS STRUCT deleted, id3, id, id_2 " +
							"FROM child_test_table WHERE (child_test_table.id = custom_test_table.id " +
							"AND child_test_table.id_2 = custom_test_table.id_2) AND (deleted = false)) AS childEntities " +
							"FROM custom_test_table WHERE ((id = @tag0 AND id_2 = @tag1) " +
							"OR (id = @tag2 AND id_2 = @tag3)) AND (deleted = false)");
}
 

static void queryWithTimestampParameter(DatabaseClient dbClient) {
  Instant exampleTimestamp = Instant.now();
  Statement statement =
      Statement.newBuilder(
              "SELECT VenueId, VenueName, LastUpdateTime FROM Venues "
                  + "WHERE LastUpdateTime < @lastUpdateTime")
          .bind("lastUpdateTime")
          .to(exampleTimestamp.toString())
          .build();
  try (ResultSet resultSet = dbClient.singleUse().executeQuery(statement)) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %s %s\n",
          resultSet.getLong("VenueId"),
          resultSet.getString("VenueName"),
          resultSet.getTimestamp("LastUpdateTime"));
    }
  }
}
 

static void queryWithParameter(DatabaseClient dbClient) {
  Statement statement =
      Statement.newBuilder(
              "SELECT SingerId, FirstName, LastName "
                  + "FROM Singers "
                  + "WHERE LastName = @lastName")
          .bind("lastName")
          .to("Garcia")
          .build();
  try (ResultSet resultSet = dbClient.singleUse().executeQuery(statement)) {
    while (resultSet.next()) {
      System.out.printf(
          "%d %s %s\n",
          resultSet.getLong("SingerId"),
          resultSet.getString("FirstName"),
          resultSet.getString("LastName"));
    }
  }
}
 

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

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 updateUsingDmlWithTimestamp(DatabaseClient dbClient) {
  dbClient
      .readWriteTransaction()
      .run(
          new TransactionCallable<Void>() {
            @Override
            public Void run(TransactionContext transaction) throws Exception {
              String sql =
                  "UPDATE Albums "
                      + "SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 1";
              long rowCount = transaction.executeUpdate(Statement.of(sql));
              System.out.printf("%d records updated.\n", rowCount);
              return null;
            }
          });
}
 

@Override
public void service(HttpRequest request, HttpResponse response) throws Exception {
  var writer = new PrintWriter(response.getWriter());
  try {
    DatabaseClient client = getClient();
    try (ResultSet rs =
        client
            .singleUse()
            .executeQuery(Statement.of("SELECT SingerId, AlbumId, AlbumTitle FROM Albums"))) {
      writer.printf("Albums:%n");
      while (rs.next()) {
        writer.printf(
            "%d %d %s%n",
            rs.getLong("SingerId"), rs.getLong("AlbumId"), rs.getString("AlbumTitle"));
      }
    } catch (SpannerException e) {
      writer.printf("Error querying database: %s%n", e.getMessage());
      response.setStatusCode(HttpStatusCodes.STATUS_CODE_SERVER_ERROR, e.getMessage());
    }
  } catch (Throwable t) {
    logger.log(Level.SEVERE, "Spanner example failed", t);
    writer.printf("Error setting up Spanner: %s%n", t.getMessage());
    response.setStatusCode(HttpStatusCodes.STATUS_CODE_SERVER_ERROR, t.getMessage());
  }
}