com.google.cloud.spanner.DatabaseClient Java Examples

static void insert(DatabaseClient dbClient, String insertType) {
  try {
    insertType = insertType.toLowerCase();
  } catch (Exception e) {
    // Invalid input received, set inserttype to empty string.
    insertType = "";
  }
  if (insertType.equals("players")) {
    // Insert players.
    insertPlayers(dbClient);
  } else if (insertType.equals("scores")) {
    // Insert scores.
    insertScores(dbClient);
  } else {
    // Invalid input.
    System.out.println("Invalid value for 'type of insert'. "
        + "Specify a valid value: 'players' or 'scores'.");
    System.exit(1);
  }
}
 

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 writeStructExampleData(DatabaseClient dbClient) {
  final List<Singer> singers =
      Arrays.asList(
          new Singer(6, "Elena", "Campbell"),
          new Singer(7, "Gabriel", "Wright"),
          new Singer(8, "Benjamin", "Martinez"),
          new Singer(9, "Hannah", "Harris"));

  List<Mutation> mutations = new ArrayList<>();
  for (Singer singer : singers) {
    mutations.add(
        Mutation.newInsertBuilder("Singers")
            .set("SingerId")
            .to(singer.singerId)
            .set("FirstName")
            .to(singer.firstName)
            .set("LastName")
            .to(singer.lastName)
            .build());
  }
  dbClient.write(mutations);
  System.out.println("Inserted example data for struct parameter queries.");
}
 

static void insert(DatabaseClient dbClient, String insertType) {
  try {
    insertType = insertType.toLowerCase();
  } catch (Exception e) {
    // Invalid input received, set insertType to empty string.
    insertType = "";
  }
  if (insertType.equals("players")) {
    // Insert players.
    insertPlayers(dbClient);
  } else if (insertType.equals("scores")) {
    // Insert scores.
    insertScores(dbClient);
  } else {
    // Invalid input.
    System.out.println("Invalid value for 'type of insert'. "
        + "Specify a valid value: 'players' or 'scores'.");
    System.exit(1);
  }
}
 

DatabaseClient get() throws Throwable {
  if (!initialized) {
    synchronized (lock) {
      if (!initialized) {
        try {
          DatabaseId db =
              DatabaseId.of(
                  SpannerOptions.getDefaultProjectId(),
                  SPANNER_INSTANCE_ID,
                  SPANNER_DATABASE_ID);
          client = createSpanner().getDatabaseClient(db);
        } catch (Throwable t) {
          error = t;
        }
        initialized = true;
      }
    }
  }
  if (error != null) {
    throw error;
  }
  return client;
}
 

private DatabaseClient configureDb() {
  final SpannerOptions options = SpannerOptions.newBuilder().build();
  spanner = options.getService();
  final DatabaseId db = DatabaseId.of(PROJECT_ID, instanceName, dbName);
  final String clientProject = spanner.getOptions().getProjectId();

  if (!db.getInstanceId().getProject().equals(clientProject)) {
    log.error(
        "Invalid project specified. Project in the database id should match"
            + "the project name set in the environment variable GCLOUD_PROJECT. Expected: "
            + clientProject);

    stop();
    System.exit(1);
  }

  final DatabaseClient dbClient = spanner.getDatabaseClient(db);

  return dbClient;
}
 

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

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

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

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

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 void makeTestData() {
  DatabaseClient databaseClient = getDatabaseClient();

  List<Mutation> mutations = new ArrayList<>();
  for (int i = 0; i < 5L; i++) {
    mutations.add(
        Mutation.newInsertOrUpdateBuilder(options.getTable())
            .set("key")
            .to((long) i)
            .set("value")
            .to(RandomUtils.randomAlphaNumeric(100))
            .build());
  }

  databaseClient.writeAtLeastOnce(mutations);
}
 

/**
 * Sends a QueueMessage to the spanner queue table
 *
 * <p>Example of sending data to a queue.
 *
 * <pre>{@code
 * MyProto myProto = MyProto.newBuilder().setMessage("My-Message").build();
 *
 * try {
 *   Queue.send(dbClient, queueName, "myKey", ByteArray.copyFrom(myProto.toByteArray()));
 * } catch (SpannerException e) {
 *   log.error("Could not write message to Queue", e);
 * }
 * }</pre>
 *
 * @param dbClient the Spanner database client
 * @param queueName the name of the queue to be polled
 * @param key the name used to partition the passed value for storage and lookup
 * @param value the message payload
 * @return Timestamp the timestamp that the message was written
 */
public static Timestamp send(DatabaseClient dbClient, String queueName, String key, byte[] value)
    throws SpannerException {
  Preconditions.checkNotNull(dbClient);
  Preconditions.checkNotNull(queueName);
  Preconditions.checkNotNull(key);
  Preconditions.checkNotNull(value);

  final List<Mutation> mutations = new ArrayList<>();
  mutations.add(
      Mutation.newInsertBuilder(queueName)
          .set("MessageId")
          .to(UUID.randomUUID().toString())
          .set("Key")
          .to(key)
          .set("Payload")
          .to(ByteArray.copyFrom(value))
          .set("Ack")
          .to(false)
          .set("Timestamp")
          .to(Value.COMMIT_TIMESTAMP)
          .build());

  return dbClient.write(mutations);
}
 

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

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

public static void main(String[] args) throws Exception {
  if (!(args.length == 3 || args.length == 4)) {
    printUsageAndExit();
  }
  SpannerOptions options = SpannerOptions.newBuilder().build();
  Spanner spanner = options.getService();
  try {
    String command = args[0];
    DatabaseId db = DatabaseId.of(options.getProjectId(), args[1], args[2]);
    DatabaseClient dbClient = spanner.getDatabaseClient(db);
    DatabaseAdminClient dbAdminClient = spanner.getDatabaseAdminClient();
    switch (command) {
      case "create":
        create(dbAdminClient, db);
        break;
      default:
        printUsageAndExit();
    }
  } finally {
    spanner.close();
  }
  System.out.println("Closed client");
}
 

static void update(DatabaseClient dbClient) {
  // Mutation can be used to update/insert/delete a single row in a table. Here we use
  // newUpdateBuilder to create update mutations.
  List<Mutation> mutations =
      Arrays.asList(
          Mutation.newUpdateBuilder("Albums")
              .set("SingerId")
              .to(1)
              .set("AlbumId")
              .to(1)
              .set("MarketingBudget")
              .to(100000)
              .build(),
          Mutation.newUpdateBuilder("Albums")
              .set("SingerId")
              .to(2)
              .set("AlbumId")
              .to(2)
              .set("MarketingBudget")
              .to(500000)
              .build());
  // This writes all the mutations to Cloud Spanner atomically.
  dbClient.write(mutations);
}
 

static void deleteExampleData(DatabaseClient dbClient) {
  List<Mutation> mutations = new ArrayList<>();

  // KeySet.Builder can be used to delete a specific set of rows.
  // Delete the Albums with the key values (2,1) and (2,3).
  mutations.add(
      Mutation.delete(
          "Albums", KeySet.newBuilder().addKey(Key.of(2, 1)).addKey(Key.of(2, 3)).build()));

  // KeyRange can be used to delete rows with a key in a specific range.
  // Delete a range of rows where the column key is >=3 and <5
  mutations.add(
      Mutation.delete("Singers", KeySet.range(KeyRange.closedOpen(Key.of(3), Key.of(5)))));

  // KeySet.all() can be used to delete all the rows in a table.
  // Delete remaining Singers rows, which will also delete the remaining Albums rows since it was
  // defined with ON DELETE CASCADE.
  mutations.add(Mutation.delete("Singers", KeySet.all()));

  dbClient.write(mutations);
  System.out.printf("Records deleted.\n");
}
 

public static void main(String[] args) {
  SpannerOptions options = SpannerOptions.newBuilder().build();
  Spanner spanner = options.getService();

  try {

    DatabaseId db = DatabaseId.of(options.getProjectId(), args[0], args[1]);
    // [END init_client]
    // This will return the default project id based on the environment.
    String clientProject = spanner.getOptions().getProjectId();
    if (!db.getInstanceId().getProject().equals(clientProject)) {
      System.err.println("Invalid project specified. Project in the database id should match"
          + "the project name set in the environment variable GCLOUD_PROJECT. Expected: "
          + clientProject);
    }
    // [START init_client]
    DatabaseClient dbClient = spanner.getDatabaseClient(db);
    DatabaseAdminClient dbAdminClient = spanner.getDatabaseAdminClient();
    createDatabase(dbAdminClient, db);
  } finally {
    spanner.close();
  }
  
}
 

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 queryWithStruct(DatabaseClient dbClient) {
  // [START spanner_create_struct_with_data]
  Struct name =
      Struct.newBuilder().set("FirstName").to("Elena").set("LastName").to("Campbell").build();
  // [END spanner_create_struct_with_data]

  // [START spanner_query_data_with_struct]
  Statement s =
      Statement.newBuilder(
              "SELECT SingerId FROM Singers "
                  + "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
                  + "= @name")
          .bind("name")
          .to(name)
          .build();
  try (ResultSet resultSet = dbClient.singleUse().executeQuery(s)) {
    while (resultSet.next()) {
      System.out.printf("%d\n", resultSet.getLong("SingerId"));
    }
  }
  // [END spanner_query_data_with_struct]
}
 

@Bean
public SpannerTemplate spannerTemplate(SpannerMappingContext spannerMappingContext) {
	SpannerEntityProcessor objectMapper = mock(SpannerEntityProcessor.class);
	SpannerMutationFactory mutationFactory = mock(SpannerMutationFactory.class);

	when(mutationFactory.upsert(Mockito.any(TestEntity.class), Mockito.any()))
			.thenAnswer(invocation -> {
				TestEntity testEntity = invocation.getArgument(0);
				assertThat(testEntity.lastTouched).isNotNull();
				assertThat(testEntity.lastTouched).isAfter(LONG_AGO);
				assertThat(testEntity.lastUser).isEqualTo("test_user");
				return UPSERT_MUTATION;
			});

	SpannerSchemaUtils schemaUtils = new SpannerSchemaUtils(spannerMappingContext, objectMapper, true);

	return new SpannerTemplate(() -> mock(DatabaseClient.class),
			spannerMappingContext, objectMapper, mutationFactory, schemaUtils);
}
 

@Test
public void functionsHelloSpanner_shouldShowQueryError() throws Exception {
  setupFailedMockQuery();
  new HelloSpanner() {
    @Override
    DatabaseClient getClient() {
      return client;
    }
  }.service(request, response);
  writerOut.flush();
  assertThat(responseOut.toString())
      .isEqualTo("Error querying database: NOT_FOUND: Table `Albums` not found\n");
}
 

static void insertPlayers(DatabaseClient dbClient) {
  dbClient
      .readWriteTransaction()
      .run(
          new TransactionCallable<Void>() {
            @Override
            public Void run(TransactionContext transaction) throws Exception {
              // Get the number of players.
              String sql = "SELECT Count(PlayerId) as PlayerCount FROM Players";
              ResultSet resultSet = transaction.executeQuery(Statement.of(sql));
              long numberOfPlayers = 0;
              if (resultSet.next()) {
                numberOfPlayers = resultSet.getLong("PlayerCount");
              }
              // Insert 100 player records into the Players table.
              List<Statement> stmts = new ArrayList<Statement>();
              long randomId;
              for (int x = 1; x <= 100; x++) {
                numberOfPlayers++;
                randomId = (long) Math.floor(Math.random() * 9_000_000_000L) + 1_000_000_000L;
                Statement statement =
                    Statement
                      .newBuilder(
                          "INSERT INTO Players (PlayerId, PlayerName) "
                          + "VALUES (@PlayerId, @PlayerName) ")
                      .bind("PlayerId")
                      .to(randomId)
                      .bind("PlayerName")
                      .to("Player " + numberOfPlayers)
                      .build();
                stmts.add(statement);
              }
              transaction.batchUpdate(stmts);
              return null;
            }
          });
  System.out.println("Done inserting player records...");
}
 

@Before
public void setUp() {
	this.databaseClient = mock(DatabaseClient.class);
	this.mappingContext = new SpannerMappingContext();
	this.objectMapper = mock(SpannerEntityProcessor.class);
	this.mutationFactory = mock(SpannerMutationFactory.class);
	this.schemaUtils = new SpannerSchemaUtils(this.mappingContext, this.objectMapper,
			true);
	this.readContext = mock(ReadContext.class);
	when(this.databaseClient.singleUse()).thenReturn(this.readContext);
	when(this.objectMapper.getWriteConverter()).thenReturn(new SpannerWriteConverter());
	this.spannerTemplate = new SpannerTemplate(() -> this.databaseClient,
			this.mappingContext, this.objectMapper, this.mutationFactory,
			this.schemaUtils);
}
 

@Test
public void functionsHelloSpanner_shouldListAlbums() throws Exception {
  setupSuccessfulMockQuery();
  new HelloSpanner() {
    @Override
    DatabaseClient getClient() {
      return client;
    }
  }.service(request, response);
  writerOut.flush();
  assertThat(responseOut.toString()).isEqualTo("Albums:\n1 1 Album 1\n2 1 Album 2\n");
}
 

public static void main(String... args) throws Exception {

    if (args.length != 2) {
      System.err.println("Usage: QuickStartSample <instance_id> <database_id>");
      return;
    }
    // Instantiates a client
    SpannerOptions options = SpannerOptions.newBuilder().build();
    Spanner spanner = options.getService();

    // Name of your instance & database.
    String instanceId = args[0];
    String databaseId = args[1];
    try {
      // Creates a database client
      DatabaseClient dbClient =
          spanner.getDatabaseClient(DatabaseId.of(options.getProjectId(), instanceId, databaseId));
      // Queries the database
      ResultSet resultSet = dbClient.singleUse().executeQuery(Statement.of("SELECT 1"));

      System.out.println("\n\nResults:");
      // Prints the results
      while (resultSet.next()) {
        System.out.printf("%d\n\n", resultSet.getLong(0));
      }
    } finally {
      // Closes the client which will free up the resources used
      spanner.close();
    }
  }