Mulai menggunakan Spanner di JDBC

static void createDatabase(DatabaseAdminClient dbAdminClient,
    InstanceName instanceName, String databaseId) {
  CreateDatabaseRequest createDatabaseRequest =
      CreateDatabaseRequest.newBuilder()
          .setCreateStatement("CREATE DATABASE `" + databaseId + "`")
          .setParent(instanceName.toString())
          .addAllExtraStatements(Arrays.asList(
              "CREATE TABLE Singers ("
                  + "  SingerId   INT64 NOT NULL,"
                  + "  FirstName  STRING(1024),"
                  + "  LastName   STRING(1024),"
                  + "  SingerInfo BYTES(MAX),"
                  + "  FullName STRING(2048) AS "
                  + "  (ARRAY_TO_STRING([FirstName, LastName], \" \")) STORED"
                  + ") PRIMARY KEY (SingerId)",
              "CREATE TABLE Albums ("
                  + "  SingerId     INT64 NOT NULL,"
                  + "  AlbumId      INT64 NOT NULL,"
                  + "  AlbumTitle   STRING(MAX)"
                  + ") PRIMARY KEY (SingerId, AlbumId),"
                  + "  INTERLEAVE IN PARENT Singers ON DELETE CASCADE")).build();
  try {
    // Initiate the request which returns an OperationFuture.
    com.google.spanner.admin.database.v1.Database db =
        dbAdminClient.createDatabaseAsync(createDatabaseRequest).get();
    System.out.println("Created database [" + db.getName() + "]");
  } catch (ExecutionException e) {
    // If the operation failed during execution, expose the cause.
    throw (SpannerException) e.getCause();
  } catch (InterruptedException e) {
    // Throw when a thread is waiting, sleeping, or otherwise occupied,
    // and the thread is interrupted, either before or during the activity.
    throw SpannerExceptionFactory.propagateInterrupt(e);
  }
}

static void createPostgreSqlDatabase(
    DatabaseAdminClient dbAdminClient, String projectId, String instanceId, String databaseId) {
  final CreateDatabaseRequest request =
      CreateDatabaseRequest.newBuilder()
          .setCreateStatement("CREATE DATABASE \"" + databaseId + "\"")
          .setParent(InstanceName.of(projectId, instanceId).toString())
          .setDatabaseDialect(DatabaseDialect.POSTGRESQL).build();

  try {
    // Initiate the request which returns an OperationFuture.
    Database db = dbAdminClient.createDatabaseAsync(request).get();
    System.out.println("Created database [" + db.getName() + "]");
  } catch (ExecutionException e) {
    // If the operation failed during execution, expose the cause.
    throw (SpannerException) e.getCause();
  } catch (InterruptedException e) {
    // Throw when a thread is waiting, sleeping, or otherwise occupied,
    // and the thread is interrupted, either before or during the activity.
    throw SpannerExceptionFactory.propagateInterrupt(e);
  }
}
static void createTableUsingDdl(DatabaseAdminClient dbAdminClient, DatabaseName databaseName) {
  try {
    // Initiate the request which returns an OperationFuture.
    dbAdminClient.updateDatabaseDdlAsync(
        databaseName,
        Arrays.asList(
            "CREATE TABLE Singers ("
                + "  SingerId   bigint NOT NULL,"
                + "  FirstName  character varying(1024),"
                + "  LastName   character varying(1024),"
                + "  SingerInfo bytea,"
                + "  FullName character varying(2048) GENERATED "
                + "  ALWAYS AS (FirstName || ' ' || LastName) STORED,"
                + "  PRIMARY KEY (SingerId)"
                + ")",
            "CREATE TABLE Albums ("
                + "  SingerId     bigint NOT NULL,"
                + "  AlbumId      bigint NOT NULL,"
                + "  AlbumTitle   character varying(1024),"
                + "  PRIMARY KEY (SingerId, AlbumId)"
                + ") INTERLEAVE IN PARENT Singers ON DELETE CASCADE")).get();
    System.out.println("Created Singers & Albums tables in database: [" + databaseName + "]");
  } catch (ExecutionException e) {
    // If the operation failed during execution, expose the cause.
    throw SpannerExceptionFactory.asSpannerException(e);
  } catch (InterruptedException e) {
    // Throw when a thread is waiting, sleeping, or otherwise occupied,
    // and the thread is interrupted, either before or during the activity.
    throw SpannerExceptionFactory.propagateInterrupt(e);
  }
}

static void createConnection(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  // Connection properties can be specified both with in a Properties object
  // and in the connection URL.
  properties.put("numChannels", "8");
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s"
                  + ";minSessions=400;maxSessions=400",
              project, instance, database),
          properties)) {
    try (ResultSet resultSet =
        connection.createStatement().executeQuery("select 'Hello World!'")) {
      while (resultSet.next()) {
        System.out.println(resultSet.getString(1));
      }
    }
  }
}

static void createConnection(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  // Connection properties can be specified both with in a Properties object
  // and in the connection URL.
  properties.put("numChannels", "8");
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s"
                  + ";minSessions=400;maxSessions=400",
              project, instance, database),
          properties)) {
    try (ResultSet resultSet =
        connection.createStatement().executeQuery("select 'Hello World!'")) {
      while (resultSet.next()) {
        System.out.println(resultSet.getString(1));
      }
    }
  }
}

static void createConnectionWithEmulator(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  // Add autoConfigEmulator=true to the connection URL to instruct the JDBC
  // driver to connect to the Spanner emulator on localhost:9010.
  // The Spanner instance and database are automatically created if these
  // don't already exist.
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s"
                  + ";autoConfigEmulator=true",
              project, instance, database),
          properties)) {
    try (ResultSet resultSet =
        connection.createStatement().executeQuery("select 'Hello World!'")) {
      while (resultSet.next()) {
        System.out.println(resultSet.getString(1));
      }
    }
  }
}

static void createConnectionWithEmulator(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  // Add autoConfigEmulator=true to the connection URL to instruct the JDBC
  // driver to connect to the Spanner emulator on localhost:9010.
  // The Spanner instance and database are automatically created if these
  // don't already exist.
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s"
                  + ";autoConfigEmulator=true",
              project, instance, database),
          properties)) {
    try (ResultSet resultSet =
        connection.createStatement().executeQuery("select 'Hello World!'")) {
      while (resultSet.next()) {
        System.out.println(resultSet.getString(1));
      }
    }
  }
}

static void writeDataWithDml(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    // Add 4 rows in one statement.
    // JDBC always uses '?' as a parameter placeholder.
    try (PreparedStatement preparedStatement =
        connection.prepareStatement(
            "INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
                + "(?, ?, ?), "
                + "(?, ?, ?), "
                + "(?, ?, ?), "
                + "(?, ?, ?)")) {

      final ImmutableList<Singer> singers =
          ImmutableList.of(
              new Singer(/* SingerId = */ 12L, "Melissa", "Garcia"),
              new Singer(/* SingerId = */ 13L, "Russel", "Morales"),
              new Singer(/* SingerId = */ 14L, "Jacqueline", "Long"),
              new Singer(/* SingerId = */ 15L, "Dylan", "Shaw"));

      // Note that JDBC parameters start at index 1.
      int paramIndex = 0;
      for (Singer singer : singers) {
        preparedStatement.setLong(++paramIndex, singer.singerId);
        preparedStatement.setString(++paramIndex, singer.firstName);
        preparedStatement.setString(++paramIndex, singer.lastName);
      }

      int updateCount = preparedStatement.executeUpdate();
      System.out.printf("%d records inserted.\n", updateCount);
    }
  }
}

static void writeDataWithDmlPostgreSQL(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    // Add 4 rows in one statement.
    // JDBC always uses '?' as a parameter placeholder.
    try (PreparedStatement preparedStatement =
        connection.prepareStatement(
            "INSERT INTO singers (singer_id, first_name, last_name) VALUES "
                + "(?, ?, ?), "
                + "(?, ?, ?), "
                + "(?, ?, ?), "
                + "(?, ?, ?)")) {

      final ImmutableList<Singer> singers =
          ImmutableList.of(
              new Singer(/* SingerId = */ 12L, "Melissa", "Garcia"),
              new Singer(/* SingerId = */ 13L, "Russel", "Morales"),
              new Singer(/* SingerId = */ 14L, "Jacqueline", "Long"),
              new Singer(/* SingerId = */ 15L, "Dylan", "Shaw"));

      // Note that JDBC parameters start at index 1.
      int paramIndex = 0;
      for (Singer singer : singers) {
        preparedStatement.setLong(++paramIndex, singer.singerId);
        preparedStatement.setString(++paramIndex, singer.firstName);
        preparedStatement.setString(++paramIndex, singer.lastName);
      }

      int updateCount = preparedStatement.executeUpdate();
      System.out.printf("%d records inserted.\n", updateCount);
    }
  }
}

static void writeDataWithDmlBatch(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    // Add multiple rows in one DML batch.
    // JDBC always uses '?' as a parameter placeholder.
    try (PreparedStatement preparedStatement =
        connection.prepareStatement(
            "INSERT INTO Singers (SingerId, FirstName, LastName) "
                + "VALUES (?, ?, ?)")) {
      final ImmutableList<Singer> singers =
          ImmutableList.of(
              new Singer(/* SingerId = */ 16L, "Sarah", "Wilson"),
              new Singer(/* SingerId = */ 17L, "Ethan", "Miller"),
              new Singer(/* SingerId = */ 18L, "Maya", "Patel"));

      for (Singer singer : singers) {
        // Note that JDBC parameters start at index 1.
        int paramIndex = 0;
        preparedStatement.setLong(++paramIndex, singer.singerId);
        preparedStatement.setString(++paramIndex, singer.firstName);
        preparedStatement.setString(++paramIndex, singer.lastName);
        preparedStatement.addBatch();
      }

      int[] updateCounts = preparedStatement.executeBatch();
      System.out.printf(
          "%d records inserted.\n",
          Arrays.stream(updateCounts).sum());
    }
  }
}

static void writeDataWithDmlBatchPostgreSQL(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    // Add multiple rows in one DML batch.
    // JDBC always uses '?' as a parameter placeholder.
    try (PreparedStatement preparedStatement =
        connection.prepareStatement(
            "INSERT INTO singers (singer_id, first_name, last_name)"
                + " VALUES (?, ?, ?)")) {
      final ImmutableList<Singer> singers =
          ImmutableList.of(
              new Singer(/* SingerId = */ 16L, "Sarah", "Wilson"),
              new Singer(/* SingerId = */ 17L, "Ethan", "Miller"),
              new Singer(/* SingerId = */ 18L, "Maya", "Patel"));

      for (Singer singer : singers) {
        // Note that JDBC parameters start at index 1.
        int paramIndex = 0;
        preparedStatement.setLong(++paramIndex, singer.singerId);
        preparedStatement.setString(++paramIndex, singer.firstName);
        preparedStatement.setString(++paramIndex, singer.lastName);
        preparedStatement.addBatch();
      }

      int[] updateCounts = preparedStatement.executeBatch();
      System.out.printf(
          "%d records inserted.\n",
          Arrays.stream(updateCounts).sum());
    }
  }
}

/** The list of Singers to insert. */
static final List<Singer> SINGERS =
    Arrays.asList(
        new Singer(1, "Marc", "Richards"),
        new Singer(2, "Catalina", "Smith"),
        new Singer(3, "Alice", "Trentor"),
        new Singer(4, "Lea", "Martin"),
        new Singer(5, "David", "Lomond"));

/** The list of Albums to insert. */
static final List<Album> ALBUMS =
    Arrays.asList(
        new Album(1, 1, "Total Junk"),
        new Album(1, 2, "Go, Go, Go"),
        new Album(2, 1, "Green"),
        new Album(2, 2, "Forever Hold Your Peace"),
        new Album(2, 3, "Terrified"));

static void writeDataWithMutations(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    // Unwrap the CloudSpannerJdbcConnection interface
    // from the java.sql.Connection.
    CloudSpannerJdbcConnection cloudSpannerJdbcConnection =
        connection.unwrap(CloudSpannerJdbcConnection.class);

    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());
    }
    for (Album album : ALBUMS) {
      mutations.add(
          Mutation.newInsertBuilder("Albums")
              .set("SingerId")
              .to(album.singerId)
              .set("AlbumId")
              .to(album.albumId)
              .set("AlbumTitle")
              .to(album.albumTitle)
              .build());
    }
    // Apply the mutations atomically to Spanner.
    cloudSpannerJdbcConnection.write(mutations);
    System.out.printf("Inserted %d rows.\n", mutations.size());
  }
}

/** The list of Singers to insert. */
static final List<Singer> SINGERS =
    Arrays.asList(
        new Singer(1, "Marc", "Richards"),
        new Singer(2, "Catalina", "Smith"),
        new Singer(3, "Alice", "Trentor"),
        new Singer(4, "Lea", "Martin"),
        new Singer(5, "David", "Lomond"));

/** The list of Albums to insert. */
static final List<Album> ALBUMS =
    Arrays.asList(
        new Album(1, 1, "Total Junk"),
        new Album(1, 2, "Go, Go, Go"),
        new Album(2, 1, "Green"),
        new Album(2, 2, "Forever Hold Your Peace"),
        new Album(2, 3, "Terrified"));

static void writeDataWithMutationsPostgreSQL(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    // Unwrap the CloudSpannerJdbcConnection interface
    // from the java.sql.Connection.
    CloudSpannerJdbcConnection cloudSpannerJdbcConnection =
        connection.unwrap(CloudSpannerJdbcConnection.class);

    List<Mutation> mutations = new ArrayList<>();
    for (Singer singer : SINGERS) {
      mutations.add(
          Mutation.newInsertBuilder("singers")
              .set("singer_id")
              .to(singer.singerId)
              .set("first_name")
              .to(singer.firstName)
              .set("last_name")
              .to(singer.lastName)
              .build());
    }
    for (Album album : ALBUMS) {
      mutations.add(
          Mutation.newInsertBuilder("albums")
              .set("singer_id")
              .to(album.singerId)
              .set("album_id")
              .to(album.albumId)
              .set("album_title")
              .to(album.albumTitle)
              .build());
    }
    // Apply the mutations atomically to Spanner.
    cloudSpannerJdbcConnection.write(mutations);
    System.out.printf("Inserted %d rows.\n", mutations.size());
  }
}

static void queryData(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    try (ResultSet resultSet =
        connection
            .createStatement()
            .executeQuery(
                "SELECT SingerId, AlbumId, AlbumTitle "
                + "FROM Albums")) {
      while (resultSet.next()) {
        System.out.printf(
            "%d %d %s\n",
            resultSet.getLong("SingerId"),
            resultSet.getLong("AlbumId"),
            resultSet.getString("AlbumTitle"));
      }
    }
  }
}

static void queryDataPostgreSQL(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    try (ResultSet resultSet =
        connection
            .createStatement()
            .executeQuery(
                "SELECT singer_id, album_id, album_title "
                    + "FROM albums")) {
      while (resultSet.next()) {
        System.out.printf(
            "%d %d %s\n",
            resultSet.getLong("singer_id"),
            resultSet.getLong("album_id"),
            resultSet.getString("album_title"));
      }
    }
  }
}

static void queryWithParameter(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    try (PreparedStatement statement =
        connection.prepareStatement(
            "SELECT SingerId, FirstName, LastName "
                + "FROM Singers "
                + "WHERE LastName = ?")) {
      statement.setString(1, "Garcia");
      try (ResultSet resultSet = statement.executeQuery()) {
        while (resultSet.next()) {
          System.out.printf(
              "%d %s %s\n",
              resultSet.getLong("SingerId"),
              resultSet.getString("FirstName"),
              resultSet.getString("LastName"));
        }
      }
    }
  }
}

static void queryWithParameterPostgreSQL(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    try (PreparedStatement statement =
        connection.prepareStatement(
            "SELECT singer_id, first_name, last_name "
                + "FROM singers "
                + "WHERE last_name = ?")) {
      statement.setString(1, "Garcia");
      try (ResultSet resultSet = statement.executeQuery()) {
        while (resultSet.next()) {
          System.out.printf(
              "%d %s %s\n",
              resultSet.getLong("singer_id"),
              resultSet.getString("first_name"),
              resultSet.getString("last_name"));
        }
      }
    }
  }
}

static void addColumn(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    connection
        .createStatement()
        .execute("ALTER TABLE Albums ADD COLUMN MarketingBudget INT64");
    System.out.println("Added MarketingBudget column");
  }
}

static void addColumnPostgreSQL(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    connection
        .createStatement()
        .execute("alter table albums add column marketing_budget bigint");
    System.out.println("Added marketing_budget column");
  }
}

static void ddlBatch(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    try (Statement statement = connection.createStatement()) {
      // Create two new tables in one batch.
      statement.addBatch(
          "CREATE TABLE Venues ("
              + "  VenueId     INT64 NOT NULL,"
              + "  Name        STRING(1024),"
              + "  Description JSON"
              + ") PRIMARY KEY (VenueId)");
      statement.addBatch(
          "CREATE TABLE Concerts ("
              + "  ConcertId INT64 NOT NULL,"
              + "  VenueId   INT64 NOT NULL,"
              + "  SingerId  INT64 NOT NULL,"
              + "  StartTime TIMESTAMP,"
              + "  EndTime   TIMESTAMP,"
              + "  CONSTRAINT Fk_Concerts_Venues FOREIGN KEY"
              + "    (VenueId) REFERENCES Venues (VenueId),"
              + "  CONSTRAINT Fk_Concerts_Singers FOREIGN KEY"
              + "    (SingerId) REFERENCES Singers (SingerId),"
              + ") PRIMARY KEY (ConcertId)");
      statement.executeBatch();
    }
    System.out.println("Added Venues and Concerts tables");
  }
}

static void ddlBatchPostgreSQL(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    try (Statement statement = connection.createStatement()) {
      // Create two new tables in one batch.
      statement.addBatch(
          "CREATE TABLE venues ("
              + "  venue_id    bigint not null primary key,"
              + "  name        varchar(1024),"
              + "  description jsonb"
              + ")");
      statement.addBatch(
          "CREATE TABLE concerts ("
              + "  concert_id bigint not null primary key ,"
              + "  venue_id   bigint not null,"
              + "  singer_id  bigint not null,"
              + "  start_time timestamptz,"
              + "  end_time   timestamptz,"
              + "  constraint fk_concerts_venues foreign key"
              + "    (venue_id) references venues (venue_id),"
              + "  constraint fk_concerts_singers foreign key"
              + "    (singer_id) references singers (singer_id)"
              + ")");
      statement.executeBatch();
    }
    System.out.println("Added venues and concerts tables");
  }
}

static void updateDataWithMutations(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    // Unwrap the CloudSpannerJdbcConnection interface
    // from the java.sql.Connection.
    CloudSpannerJdbcConnection cloudSpannerJdbcConnection =
        connection.unwrap(CloudSpannerJdbcConnection.class);

    final long marketingBudgetAlbum1 = 100000L;
    final long marketingBudgetAlbum2 = 500000L;
    // 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(marketingBudgetAlbum1)
                .build(),
            Mutation.newUpdateBuilder("Albums")
                .set("SingerId")
                .to(2)
                .set("AlbumId")
                .to(2)
                .set("MarketingBudget")
                .to(marketingBudgetAlbum2)
                .build());
    // This writes all the mutations to Cloud Spanner atomically.
    cloudSpannerJdbcConnection.write(mutations);
    System.out.println("Updated albums");
  }
}

static void updateDataWithMutationsPostgreSQL(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    // Unwrap the CloudSpannerJdbcConnection interface
    // from the java.sql.Connection.
    CloudSpannerJdbcConnection cloudSpannerJdbcConnection =
        connection.unwrap(CloudSpannerJdbcConnection.class);

    final long marketingBudgetAlbum1 = 100000L;
    final long marketingBudgetAlbum2 = 500000L;
    // 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("singer_id")
                .to(1)
                .set("album_id")
                .to(1)
                .set("marketing_budget")
                .to(marketingBudgetAlbum1)
                .build(),
            Mutation.newUpdateBuilder("albums")
                .set("singer_id")
                .to(2)
                .set("album_id")
                .to(2)
                .set("marketing_budget")
                .to(marketingBudgetAlbum2)
                .build());
    // This writes all the mutations to Cloud Spanner atomically.
    cloudSpannerJdbcConnection.write(mutations);
    System.out.println("Updated albums");
  }
}

static void queryDataWithNewColumn(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    // Rows without an explicit value for MarketingBudget will have a
    // MarketingBudget equal to null.
    try (ResultSet resultSet =
        connection
            .createStatement()
            .executeQuery(
                "SELECT SingerId, AlbumId, MarketingBudget "
                + "FROM Albums")) {
      while (resultSet.next()) {
        // Use the ResultSet#getObject(String) method to get data
        // of any type from the ResultSet.
        System.out.printf(
            "%s %s %s\n",
            resultSet.getObject("SingerId"),
            resultSet.getObject("AlbumId"),
            resultSet.getObject("MarketingBudget"));
      }
    }
  }
}

static void queryDataWithNewColumnPostgreSQL(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    // Rows without an explicit value for marketing_budget will have a
    // marketing_budget equal to null.
    try (ResultSet resultSet =
        connection
            .createStatement()
            .executeQuery(
                "select singer_id, album_id, marketing_budget "
                    + "from albums")) {
      while (resultSet.next()) {
        // Use the ResultSet#getObject(String) method to get data
        // of any type from the ResultSet.
        System.out.printf(
            "%s %s %s\n",
            resultSet.getObject("singer_id"),
            resultSet.getObject("album_id"),
            resultSet.getObject("marketing_budget"));
      }
    }
  }
}

static void writeWithTransactionUsingDml(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    // Set AutoCommit=false to enable transactions.
    connection.setAutoCommit(false);

    // Transfer marketing budget from one album to another.
    // We do it in a transaction to ensure that the transfer is atomic.
    // There is no need to explicitly start the transaction. The first
    // statement on the connection will start a transaction when
    // AutoCommit=false.
    String selectMarketingBudgetSql =
        "SELECT MarketingBudget "
        + "FROM Albums "
        + "WHERE SingerId = ? AND AlbumId = ?";
    long album2Budget = 0;
    try (PreparedStatement selectMarketingBudgetStatement =
        connection.prepareStatement(selectMarketingBudgetSql)) {
      // Bind the query parameters to SingerId=2 and AlbumId=2.
      selectMarketingBudgetStatement.setLong(1, 2);
      selectMarketingBudgetStatement.setLong(2, 2);
      try (ResultSet resultSet =
          selectMarketingBudgetStatement.executeQuery()) {
        while (resultSet.next()) {
          album2Budget = resultSet.getLong("MarketingBudget");
        }
      }
      // The transaction will only be committed if this condition still holds
      // at the time of commit. Otherwise, the transaction will be aborted.
      final long transfer = 200000;
      if (album2Budget >= transfer) {
        long album1Budget = 0;
        // Re-use the existing PreparedStatement for selecting the
        // MarketingBudget to get the budget for Album 1.
        // Bind the query parameters to SingerId=1 and AlbumId=1.
        selectMarketingBudgetStatement.setLong(1, 1);
        selectMarketingBudgetStatement.setLong(2, 1);
        try (ResultSet resultSet =
            selectMarketingBudgetStatement.executeQuery()) {
          while (resultSet.next()) {
            album1Budget = resultSet.getLong("MarketingBudget");
          }
        }

        // Transfer part of the marketing budget of Album 2 to Album 1.
        album1Budget += transfer;
        album2Budget -= transfer;
        String updateSql =
            "UPDATE Albums "
                + "SET MarketingBudget = ? "
                + "WHERE SingerId = ? and AlbumId = ?";
        try (PreparedStatement updateStatement =
            connection.prepareStatement(updateSql)) {
          // Update Album 1.
          int paramIndex = 0;
          updateStatement.setLong(++paramIndex, album1Budget);
          updateStatement.setLong(++paramIndex, 1);
          updateStatement.setLong(++paramIndex, 1);
          // Create a DML batch by calling addBatch on
          // the current PreparedStatement.
          updateStatement.addBatch();

          // Update Album 2 in the same DML batch.
          paramIndex = 0;
          updateStatement.setLong(++paramIndex, album2Budget);
          updateStatement.setLong(++paramIndex, 2);
          updateStatement.setLong(++paramIndex, 2);
          updateStatement.addBatch();

          // Execute both DML statements in one batch.
          updateStatement.executeBatch();
        }
      }
    }
    // Commit the current transaction.
    connection.commit();
    System.out.println(
        "Transferred marketing budget from Album 2 to Album 1");
  }
}

static void writeWithTransactionUsingDmlPostgreSQL(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    // Set AutoCommit=false to enable transactions.
    connection.setAutoCommit(false);

    // Transfer marketing budget from one album to another. We do it in a
    // transaction to ensure that the transfer is atomic. There is no need
    // to explicitly start the transaction. The first statement on the
    // connection will start a transaction when AutoCommit=false.
    String selectMarketingBudgetSql =
        "SELECT marketing_budget "
            + "from albums "
            + "WHERE singer_id = ? and album_id = ?";
    long album2Budget = 0;
    try (PreparedStatement selectMarketingBudgetStatement =
        connection.prepareStatement(selectMarketingBudgetSql)) {
      // Bind the query parameters to SingerId=2 and AlbumId=2.
      selectMarketingBudgetStatement.setLong(1, 2);
      selectMarketingBudgetStatement.setLong(2, 2);
      try (ResultSet resultSet =
          selectMarketingBudgetStatement.executeQuery()) {
        while (resultSet.next()) {
          album2Budget = resultSet.getLong("marketing_budget");
        }
      }
      // The transaction will only be committed if this condition still holds
      // at the time of commit. Otherwise, the transaction will be aborted.
      final long transfer = 200000;
      if (album2Budget >= transfer) {
        long album1Budget = 0;
        // Re-use the existing PreparedStatement for selecting the
        // marketing_budget to get the budget for Album 1.
        // Bind the query parameters to SingerId=1 and AlbumId=1.
        selectMarketingBudgetStatement.setLong(1, 1);
        selectMarketingBudgetStatement.setLong(2, 1);
        try (ResultSet resultSet =
            selectMarketingBudgetStatement.executeQuery()) {
          while (resultSet.next()) {
            album1Budget = resultSet.getLong("marketing_budget");
          }
        }

        // Transfer part of the marketing budget of Album 2 to Album 1.
        album1Budget += transfer;
        album2Budget -= transfer;
        String updateSql =
            "UPDATE albums "
                + "SET marketing_budget = ? "
                + "WHERE singer_id = ? and album_id = ?";
        try (PreparedStatement updateStatement =
            connection.prepareStatement(updateSql)) {
          // Update Album 1.
          int paramIndex = 0;
          updateStatement.setLong(++paramIndex, album1Budget);
          updateStatement.setLong(++paramIndex, 1);
          updateStatement.setLong(++paramIndex, 1);
          // Create a DML batch by calling addBatch
          // on the current PreparedStatement.
          updateStatement.addBatch();

          // Update Album 2 in the same DML batch.
          paramIndex = 0;
          updateStatement.setLong(++paramIndex, album2Budget);
          updateStatement.setLong(++paramIndex, 2);
          updateStatement.setLong(++paramIndex, 2);
          updateStatement.addBatch();

          // Execute both DML statements in one batch.
          updateStatement.executeBatch();
        }
      }
    }
    // Commit the current transaction.
    connection.commit();
    System.out.println(
        "Transferred marketing budget from Album 2 to Album 1");
  }
}

static void tags(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    // Set AutoCommit=false to enable transactions.
    connection.setAutoCommit(false);
    // Set the TRANSACTION_TAG session variable to set a transaction tag
    // for the current transaction.
    connection
        .createStatement()
        .execute("SET TRANSACTION_TAG='example-tx-tag'");

    // Set the STATEMENT_TAG session variable to set the request tag
    // that should be included with the next SQL statement.
    connection
        .createStatement()
        .execute("SET STATEMENT_TAG='query-marketing-budget'");
    long marketingBudget = 0L;
    long singerId = 1L;
    long albumId = 1L;
    try (PreparedStatement statement = connection.prepareStatement(
        "SELECT MarketingBudget "
        + "FROM Albums "
        + "WHERE SingerId=? AND AlbumId=?")) {
      statement.setLong(1, singerId);
      statement.setLong(2, albumId);
      try (ResultSet albumResultSet = statement.executeQuery()) {
        while (albumResultSet.next()) {
          marketingBudget = albumResultSet.getLong(1);
        }
      }
    }
    // Reduce the marketing budget by 10% if it is more than 1,000.
    final long maxMarketingBudget = 1000L;
    final float reduction = 0.1f;
    if (marketingBudget > maxMarketingBudget) {
      marketingBudget -= (long) (marketingBudget * reduction);
      connection
          .createStatement()
          .execute("SET STATEMENT_TAG='reduce-marketing-budget'");
      try (PreparedStatement statement = connection.prepareStatement(
          "UPDATE Albums SET MarketingBudget=? "
              + "WHERE SingerId=? AND AlbumId=?")) {
        int paramIndex = 0;
        statement.setLong(++paramIndex, marketingBudget);
        statement.setLong(++paramIndex, singerId);
        statement.setLong(++paramIndex, albumId);
        statement.executeUpdate();
      }
    }

    // Commit the current transaction.
    connection.commit();
    System.out.println("Reduced marketing budget");
  }
}

static void tagsPostgreSQL(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    // Set AutoCommit=false to enable transactions.
    connection.setAutoCommit(false);
    // Set the TRANSACTION_TAG session variable to set a transaction tag
    // for the current transaction.
    connection
        .createStatement()
        .execute("set spanner.transaction_tag='example-tx-tag'");

    // Set the STATEMENT_TAG session variable to set the request tag
    // that should be included with the next SQL statement.
    connection
        .createStatement()
        .execute("set spanner.statement_tag='query-marketing-budget'");
    long marketingBudget = 0L;
    long singerId = 1L;
    long albumId = 1L;
    try (PreparedStatement statement = connection.prepareStatement(
        "select marketing_budget "
            + "from albums "
            + "where singer_id=? and album_id=?")) {
      statement.setLong(1, singerId);
      statement.setLong(2, albumId);
      try (ResultSet albumResultSet = statement.executeQuery()) {
        while (albumResultSet.next()) {
          marketingBudget = albumResultSet.getLong(1);
        }
      }
    }
    // Reduce the marketing budget by 10% if it is more than 1,000.
    final long maxMarketingBudget = 1000L;
    final float reduction = 0.1f;
    if (marketingBudget > maxMarketingBudget) {
      marketingBudget -= (long) (marketingBudget * reduction);
      connection
          .createStatement()
          .execute("set spanner.statement_tag='reduce-marketing-budget'");
      try (PreparedStatement statement = connection.prepareStatement(
          "update albums set marketing_budget=? "
              + "where singer_id=? AND album_id=?")) {
        int paramIndex = 0;
        statement.setLong(++paramIndex, marketingBudget);
        statement.setLong(++paramIndex, singerId);
        statement.setLong(++paramIndex, albumId);
        statement.executeUpdate();
      }
    }

    // Commit the current transaction.
    connection.commit();
    System.out.println("Reduced marketing budget");
  }
}

static void readOnlyTransaction(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    // Set AutoCommit=false to enable transactions.
    connection.setAutoCommit(false);
    // This SQL statement instructs the JDBC driver to use
    // a read-only transaction.
    connection.createStatement().execute("SET TRANSACTION READ ONLY");

    try (ResultSet resultSet =
        connection
            .createStatement()
            .executeQuery(
                "SELECT SingerId, AlbumId, AlbumTitle "
                    + "FROM Albums "
                    + "ORDER BY SingerId, AlbumId")) {
      while (resultSet.next()) {
        System.out.printf(
            "%d %d %s\n",
            resultSet.getLong("SingerId"),
            resultSet.getLong("AlbumId"),
            resultSet.getString("AlbumTitle"));
      }
    }
    try (ResultSet resultSet =
        connection
            .createStatement()
            .executeQuery(
                "SELECT SingerId, AlbumId, AlbumTitle "
                    + "FROM Albums "
                    + "ORDER BY AlbumTitle")) {
      while (resultSet.next()) {
        System.out.printf(
            "%d %d %s\n",
            resultSet.getLong("SingerId"),
            resultSet.getLong("AlbumId"),
            resultSet.getString("AlbumTitle"));
      }
    }
    // End the read-only transaction by calling commit().
    connection.commit();
  }
}

static void readOnlyTransactionPostgreSQL(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    // Set AutoCommit=false to enable transactions.
    connection.setAutoCommit(false);
    // This SQL statement instructs the JDBC driver to use
    // a read-only transaction.
    connection.createStatement().execute("set transaction read only");

    try (ResultSet resultSet =
        connection
            .createStatement()
            .executeQuery(
                "SELECT singer_id, album_id, album_title "
                    + "FROM albums "
                    + "ORDER BY singer_id, album_id")) {
      while (resultSet.next()) {
        System.out.printf(
            "%d %d %s\n",
            resultSet.getLong("singer_id"),
            resultSet.getLong("album_id"),
            resultSet.getString("album_title"));
      }
    }
    try (ResultSet resultSet =
        connection
            .createStatement()
            .executeQuery(
                "SELECT singer_id, album_id, album_title "
                    + "FROM albums "
                    + "ORDER BY album_title")) {
      while (resultSet.next()) {
        System.out.printf(
            "%d %d %s\n",
            resultSet.getLong("singer_id"),
            resultSet.getLong("album_id"),
            resultSet.getString("album_title"));
      }
    }
    // End the read-only transaction by calling commit().
    connection.commit();
  }
}

static void dataBoost(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    // This enables Data Boost for all partitioned queries on this connection.
    connection.createStatement().execute("SET DATA_BOOST_ENABLED=TRUE");

    // Run a partitioned query. This query will use Data Boost.
    try (ResultSet resultSet =
        connection
            .createStatement()
            .executeQuery(
                "RUN PARTITIONED QUERY "
                    + "SELECT SingerId, FirstName, LastName "
                    + "FROM Singers")) {
      while (resultSet.next()) {
        System.out.printf(
            "%d %s %s\n",
            resultSet.getLong("SingerId"),
            resultSet.getString("FirstName"),
            resultSet.getString("LastName"));
      }
    }
  }
}

static void dataBoostPostgreSQL(
    final String project,
    final String instance,
    final String database,
    final Properties properties) throws SQLException {
  try (Connection connection =
      DriverManager.getConnection(
          String.format(
              "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
              project, instance, database),
          properties)) {
    // This enables Data Boost for all partitioned queries on this connection.
    connection
        .createStatement()
        .execute("set spanner.data_boost_enabled=true");

    // Run a partitioned query. This query will use Data Boost.
    try (ResultSet resultSet =
        connection
            .createStatement()
            .executeQuery(
                "run partitioned query "
                    + "select singer_id, first_name, last_name "
                    + "from singers")) {
      while (resultSet.next()) {
        System.out.printf(
            "%d %s %s\n",
            resultSet.getLong("singer_id"),
            resultSet.getString("first_name"),
            resultSet.getString("last_name"));
      }
    }
  }
}