開始透過 JDBC 使用 Spanner

目標

本教學課程會使用 Spanner JDBC 驅動程式，逐步引導您完成下列步驟：

  • 建立 Spanner 執行個體和資料庫。
  • 對資料庫中的資料進行寫入和讀取，以及執行 SQL 查詢。
  • 更新資料庫結構定義。
  • 使用讀取/寫入交易來更新資料。
  • 將次要索引新增至資料庫。
  • 使用索引對資料執行讀取作業和 SQL 查詢。
  • 使用唯讀交易擷取資料。

費用

本教學課程使用 Spanner，這是Google Cloud的計費元件。如需使用 Spanner 的費用資訊，請參閱定價一文。

事前準備

完成「設定」一文中說明的步驟，包括建立及設定預設 Google Cloud 專案、啟用計費功能、啟用 Cloud Spanner API，以及設定 OAuth 2.0 以取得驗證憑證，進而使用 Cloud Spanner API。

特別提醒您，請務必執行 gcloud auth application-default login，使用驗證憑證設定本機開發環境。

準備本機 JDBC 環境

  1. 請在您的開發機器上安裝下列項目：

  2. 將應用程式存放區範例複製到本機電腦中：

    git clone https://github.com/googleapis/java-spanner-jdbc.git

  3. 變更為包含 Spanner 範例程式碼的目錄：

    cd java-spanner-jdbc/samples/snippets

建立執行個體

初次使用 Spanner 時，必須建立執行個體，這是 Spanner 資料庫會使用的資源分配單位。建立執行個體時，請選擇「執行個體設定」以決定資料儲存的位置，再選擇要使用的節點數量以決定執行個體的服務和儲存空間資源量。

執行下列指令，使用 1 個節點在 us-central1 地區建立 Spanner 執行個體：

gcloud spanner instances create test-instance --config=regional-us-central1 \
    --description="Test Instance" --nodes=1

請注意，如此將建立具備下列特性的執行個體：

  • 執行個體 ID test-instance
  • 顯示名稱 Test Instance
  • 執行個體設定 regional-us-central1 (地區設定會將資料儲存在一個地區，而多地區設定則會讓資料散佈在多個地區。詳情請參閱「關於執行個體」一文。)
  • 節點數量 1 (node_count 與執行個體中的資料庫可用的服務和儲存空間資源數量相對應。詳情請參閱「節點和處理單元」一節)。

畫面上會顯示下列訊息：

Creating instance...done.

瀏覽範例檔案

範例存放區中有一項範例說明如何使用 Spanner 搭配 JDBC。

pom.xml 會將 Spanner JDBC 驅動程式新增至專案的依附元件，並設定組件外掛程式，以使用本教學課程中定義的 Java 類別建構可執行的 JAR 檔案。

從 samples/snippets 目錄建構範例：

mvn package -DskipTests

建立資料庫

GoogleSQL 

java -jar target/jdbc-snippets/jdbc-samples.jar \
createdatabase test-instance example-db

PostgreSQL

java -jar target/jdbc-snippets/jdbc-samples.jar \
createpgdatabase test-instance example-db

畫面上會顯示下列訊息：

Created database [projects/my-project/instances/test-instance/databases/example-db]
下列程式碼會在資料庫中建立資料庫和兩個資料表。

GoogleSQL 

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

PostgreSQL

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

下一個步驟是將資料寫入資料庫。

建立 JDBC 連線

您必須先建立 Connection，才能執行讀取或寫入作業。您與 Spanner 的所有互動都必須透過 Connection。資料庫名稱和其他屬性會在 JDBC 連線網址和 java.util.Properties 集中指定。

GoogleSQL 

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

PostgreSQL

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

如需完整的支援屬性清單，請參閱「連線網址屬性」。

每個 Connection 都會使用資源，因此建議您在不再需要連線時關閉連線，或使用連線集區在整個應用程式中重複使用連線。

詳情請參閱 Connection Javadoc 參考資料。

將 JDBC 驅動程式連線至模擬器

您可以透過兩種方式將 JDBC 驅動程式連線至 Spanner 模擬器：

  • 設定 SPANNER_EMULATOR_HOST 環境變數：這會指示 JDBC 驅動程式連線至模擬器。JDBC 連線網址中的 Spanner 執行個體和資料庫必須已存在於模擬器中。
  • 在連線網址中加入 autoConfigEmulator=true：這會指示 JDBC 驅動程式連線至模擬器，並在 JDBC 連線網址中自動建立 Spanner 執行個體和資料庫 (如果不存在)。

這個範例說明如何使用 autoConfigEmulator=true 連線網址選項。

GoogleSQL 

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

PostgreSQL

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

使用 DML 寫入資料

您可以使用資料操縱語言 (DML) 在讀寫交易中插入資料。

請使用 PreparedStatement.executeUpdate() 方法執行 DML 陳述式。

GoogleSQL 

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

PostgreSQL

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

使用下列指令執行範例：

GoogleSQL 

java -jar target/jdbc-snippets/jdbc-samples.jar \
writeusingdml test-instance example-db

PostgreSQL

java -jar target/jdbc-snippets/jdbc-samples.jar \
writeusingdmlpg test-instance example-db

畫面上會顯示下列訊息：

4 records inserted.

使用 DML 批次寫入資料

您可以使用 PreparedStatement#addBatch() 和 PreparedStatement#executeBatch() 方法，在一個批次中執行多個 DML 陳述式。

GoogleSQL 

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

PostgreSQL

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

使用下列指令執行範例：

GoogleSQL 

java -jar target/jdbc-snippets/jdbc-samples.jar \
writeusingdmlbatch test-instance example-db

PostgreSQL

java -jar target/jdbc-snippets/jdbc-samples.jar \
writeusingdmlbatchpg test-instance example-db

畫面上會顯示下列訊息：

3 records inserted.

使用變異寫入資料

您也可以使用變異來插入資料。

您可以使用 Mutation 物件寫入資料。Mutation 物件是變異作業的容器。Mutation 代表一系列的插入、更新和刪除作業，Spanner 會以不可分割的形式，將這些作業套用至 Spanner 資料庫中不同的資料列和資料表。

Mutation 類別中的 newInsertBuilder() 方法會建構 INSERT 變異，在資料表中插入新的資料列。如果該資料列已存在，寫入就會失敗。或者，您可以使用 newInsertOrUpdateBuilder 方法建構 INSERT_OR_UPDATE 變異，在有資料列的情況下，這個方法會更新資料欄的值。

CloudSpannerJdbcConnection 介面中的 write() 方法會寫入異動事件，系統會以不可分割的形式套用單一批次中的所有變異。

您可以從 Spanner JDBC Connection 解除包裝 CloudSpannerJdbcConnection 介面。

此程式碼顯示如何使用變異寫入資料：

GoogleSQL 

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

PostgreSQL

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

使用下列指令執行範例：

GoogleSQL 

java -jar target/jdbc-snippets/jdbc-samples.jar \
write test-instance example-db

PostgreSQL

java -jar target/jdbc-snippets/jdbc-samples.jar \
writepg test-instance example-db

畫面上會顯示下列訊息：

Inserted 10 rows.

使用 SQL 查詢資料

Spanner 支援用於讀取資料的 SQL 介面。您可以透過 Google Cloud CLI 在指令列上存取介面，也可以透過程式輔助方式使用 Spanner JDBC 驅動程式存取介面。

使用指令列

執行下列 SQL 陳述式，從 Albums 資料表讀取所有資料欄的值：

GoogleSQL 

gcloud spanner databases execute-sql example-db --instance=test-instance \
    --sql='SELECT SingerId, AlbumId, AlbumTitle FROM Albums'

PostgreSQL

gcloud spanner databases execute-sql example-db --instance=test-instance \
    --sql='SELECT singer_id, album_id, album_title FROM albums'

結果應為：

SingerId AlbumId AlbumTitle
1        1       Total Junk
1        2       Go, Go, Go
2        1       Green
2        2       Forever Hold Your Peace
2        3       Terrified

使用 Spanner JDBC 驅動程式

除了在指令列上執行 SQL 陳述式外，您還可以使用 Spanner JDBC 驅動程式，透過程式輔助方式發出相同的 SQL 陳述式。

您可以使用下列方法和類別來執行 SQL 查詢：

  • Connection 介面中的 createStatement() 方法：使用這個方法建立新的陳述式物件，以執行 SQL 陳述式。
  • Statement 類別的 executeQuery(String) 方法：使用這個方法，即可執行資料庫查詢。
  • Statement 類別：使用這個類別執行 SQL 字串。
  • ResultSet 類別：使用這個類別，即可存取 SQL 陳述式傳回的資料。

下面說明如何發出查詢和存取資料：

GoogleSQL 

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

PostgreSQL

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

使用下列指令執行範例：

GoogleSQL 

java -jar target/jdbc-snippets/jdbc-samples.jar \
query test-instance example-db

PostgreSQL

java -jar target/jdbc-snippets/jdbc-samples.jar \
querypg test-instance example-db

畫面上應會顯示下列結果：

1 1 Total Junk
1 2 Go, Go, Go
2 1 Green
2 2 Forever Hold Your Peace
2 3 Terrified

使用 SQL 參數查詢

如果應用程式經常執行查詢，您可以將查詢參數化，藉此提升效能。系統可快取並重新使用產生的參數查詢，減少編譯的成本。詳情請參閱「使用查詢參數，針對經常執行的查詢加快速度」。

以下範例說明如何在 WHERE 子句中使用參數，查詢包含特定 LastName 值的記錄。

使用 java.sql.PreparedStatement 執行含參數的查詢。

GoogleSQL 

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

PostgreSQL

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

使用下列指令執行範例：

GoogleSQL 

java -jar target/jdbc-snippets/jdbc-samples.jar \
querywithparameter test-instance example-db

PostgreSQL

java -jar target/jdbc-snippets/jdbc-samples.jar \
querywithparameterpg test-instance example-db

畫面上應會顯示下列結果：

12 Melissa Garcia

更新資料庫結構定義

假設您需要新增名稱為 MarketingBudget 的新資料欄到 Albums 資料表，必須先更新資料庫結構定義，才能新增新資料欄到現有的資料表。Spanner 可在資料庫持續處理流量時，支援資料庫的結構定義更新作業。結構定義更新作業不需要讓資料庫離線，也不會鎖定整個資料表或資料欄；您可以在結構定義更新期間持續將資料寫入資料庫。詳情請參閱「進行結構定義更新」一文，瞭解支援的結構定義更新和結構定義變更效能。

新增資料欄

您可以使用 Google Cloud CLI 在指令列上新增資料欄，或使用 Spanner JDBC 驅動程式透過程式輔助方式新增資料欄。

使用指令列

使用下列 ALTER TABLE 指令，在資料表中新增資料欄：

GoogleSQL 

gcloud spanner databases ddl update example-db --instance=test-instance \
    --ddl='ALTER TABLE Albums ADD COLUMN MarketingBudget INT64'

PostgreSQL

gcloud spanner databases ddl update example-db --instance=test-instance \
    --ddl='ALTER TABLE albums ADD COLUMN marketing_budget BIGINT'

畫面上會顯示下列訊息：

Schema updating...done.

使用 Spanner JDBC 驅動程式

使用 java.sql.Statement 類別的 execute(String) 方法修改結構定義：

GoogleSQL 

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

PostgreSQL

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

使用下列指令執行範例：

GoogleSQL 

java -jar target/jdbc-snippets/jdbc-samples.jar \
addmarketingbudget test-instance example-db

PostgreSQL

java -jar target/jdbc-snippets/jdbc-samples.jar \
addmarketingbudgetpg test-instance example-db

畫面上會顯示下列訊息：

Added MarketingBudget column.

執行 DDL 批次

建議您一次批次執行多項結構定義修改作業。使用 java.sql.Statement 的 addBatch(String) 方法，將多個 DDL 陳述式新增至批次。

GoogleSQL 

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

PostgreSQL

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

使用下列指令執行範例：

GoogleSQL 

java -jar target/jdbc-snippets/jdbc-samples.jar \
ddlbatch test-instance example-db

PostgreSQL

java -jar target/jdbc-snippets/jdbc-samples.jar \
ddlbatchpg test-instance example-db

畫面上會顯示下列訊息：

Added Venues and Concerts tables.

寫入資料到新資料欄

以下程式碼會將資料寫入新資料欄，並在 Albums(1, 1) 和 Albums(2, 2) 這兩個索引鍵表示的資料列中將 MarketingBudget 一欄分別設為 100000 和 500000。

GoogleSQL 

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

PostgreSQL

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

使用下列指令執行範例：

GoogleSQL 

java -jar target/jdbc-snippets/jdbc-samples.jar \
update test-instance example-db

PostgreSQL

java -jar target/jdbc-snippets/jdbc-samples.jar \
updatepg test-instance example-db

畫面會顯示類似以下的輸出：

Updated albums

您也可以執行 SQL 查詢或讀取呼叫，以擷取剛寫入的值。

以下是執行查詢的程式碼：

GoogleSQL 

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

PostgreSQL

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

如要執行這項查詢，請執行下列指令：

GoogleSQL 

java -jar target/jdbc-snippets/jdbc-samples.jar \
querymarketingbudget test-instance example-db

PostgreSQL

java -jar target/jdbc-snippets/jdbc-samples.jar \
querymarketingbudgetpg test-instance example-db

結果會顯示：

1 1 100000
1 2 null
2 1 null
2 2 500000
2 3 null

更新資料

您可以在讀寫交易中使用 DML 來更新資料。

設定 AutoCommit=false，在 JDBC 中執行讀寫交易。

GoogleSQL 

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

PostgreSQL

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

使用下列指令執行範例：

GoogleSQL 

java -jar target/jdbc-snippets/jdbc-samples.jar \
writewithtransactionusingdml test-instance example-db

PostgreSQL

java -jar target/jdbc-snippets/jdbc-samples.jar \
writewithtransactionusingdmlpg test-instance example-db

交易代碼和要求代碼

使用交易標記和要求標記，排解 Spanner 中的交易和查詢問題。您可以在 JDBC 中使用 TRANSACTION_TAG 和 STATEMENT_TAG 工作階段變數，設定交易代碼和要求代碼。

GoogleSQL 

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

PostgreSQL

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

使用下列指令執行範例：

GoogleSQL 

java -jar target/jdbc-snippets/jdbc-samples.jar \
tags test-instance example-db

PostgreSQL

java -jar target/jdbc-snippets/jdbc-samples.jar \
tagspg test-instance example-db

使用唯讀交易擷取資料

假設您想要在相同時間戳記執行一次以上的讀取作業。唯讀交易會觀察出交易修訂記錄中一致的前置字串，讓應用程式取得的資料始終保持一致。 在 java.sql.Connection 上設定 ReadOnly=true 和 AutoCommit=false，或使用 SET TRANSACTION READ ONLY SQL 陳述式，即可執行唯讀交易。

以下顯示如何執行查詢，並在同一個唯讀交易中執行讀取作業：

GoogleSQL 

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

PostgreSQL

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

使用下列指令執行範例：

GoogleSQL 

java -jar target/jdbc-snippets/jdbc-samples.jar \
readonlytransaction test-instance example-db

PostgreSQL

java -jar target/jdbc-snippets/jdbc-samples.jar \
readonlytransactionpg test-instance example-db

畫面會顯示類似以下的輸出：

    1 1 Total Junk
    1 2 Go, Go, Go
    2 1 Green
    2 2 Forever Hold Your Peace
    2 3 Terrified
    2 2 Forever Hold Your Peace
    1 2 Go, Go, Go
    2 1 Green
    2 3 Terrified
    1 1 Total Junk

分區查詢和 Data Boost

partitionQuery API 會將查詢分成較小的片段 (稱為「分區」)，並使用多部機器平行擷取分區。每個分割區都會以分割區權杖識別。PartitionQuery API 的延遲時間比標準查詢 API 長，因為這個 API 僅適用於大量作業，例如匯出或掃描整個資料庫。

Data Boost 可讓您執行分析查詢和資料匯出作業，對已佈建 Spanner 執行個體的現有工作負載幾乎沒有影響。Data Boost 僅支援分區查詢。

GoogleSQL 

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

PostgreSQL

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

使用下列指令執行範例：

GoogleSQL 

java -jar target/jdbc-snippets/jdbc-samples.jar \
databoost test-instance example-db

PostgreSQL

java -jar target/jdbc-snippets/jdbc-samples.jar \
databoostpg test-instance example-db

如要進一步瞭解如何執行分區查詢，以及如何搭配使用 JDBC 驅動程式與 Data Boost，請參閱：

分區 DML

分區資料操縱語言 (DML) 專為下列類型的批次更新和刪除作業而設計：

  • 定期清理和垃圾收集。
  • 對具有預設值的新資料欄進行補充作業。
* { GoogleSQL }

static void partitionedDml(
    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)) {
    // Enable Partitioned DML on this connection.
    connection
        .createStatement()
        .execute("SET AUTOCOMMIT_DML_MODE='PARTITIONED_NON_ATOMIC'");
    // Back-fill a default value for the MarketingBudget column.
    long lowerBoundUpdateCount =
        connection
            .createStatement()
            .executeUpdate("UPDATE Albums "
                + "SET MarketingBudget=0 "
                + "WHERE MarketingBudget IS NULL");
    System.out.printf("Updated at least %d albums\n", lowerBoundUpdateCount);
  }
}

PostgreSQL

static void partitionedDmlPostgreSQL(
    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)) {
    // Enable Partitioned DML on this connection.
    connection
        .createStatement()
        .execute("set spanner.autocommit_dml_mode='partitioned_non_atomic'");
    // Back-fill a default value for the MarketingBudget column.
    long lowerBoundUpdateCount =
        connection
            .createStatement()
            .executeUpdate("update albums "
                + "set marketing_budget=0 "
                + "where marketing_budget is null");
    System.out.printf("Updated at least %d albums\n", lowerBoundUpdateCount);
  }
}

使用下列指令執行範例：

GoogleSQL 

java -jar target/jdbc-snippets/jdbc-samples.jar \
pdml test-instance example-db

PostgreSQL

java -jar target/jdbc-snippets/jdbc-samples.jar \
pdmlpg test-instance example-db

如要進一步瞭解 AUTOCOMMIT_DML_MODE，請參閱：

清除所用資源

如要避免系統向您的 Cloud 帳單帳戶收取您在本教學課程中所用資源的相關費用，請捨棄資料庫並刪除您建立的執行個體。

刪除資料庫

您刪除執行個體時，也會自動刪除其中所有資料庫。 以下步驟將示範如何在保留執行個體的情況下刪除資料庫 (您仍須支付執行個體費用)。

使用指令列

gcloud spanner databases delete example-db --instance=test-instance

使用 Google Cloud 控制台

  1. 前往 Google Cloud 控制台的「Spanner Instances」(Spanner 執行個體) 頁面。

    前往「Instances」(執行個體) 頁面

  2. 點選執行個體。

  3. 點選您要刪除的資料庫。

  4. 在「Database details」(資料庫詳細資料) 頁面，按一下 [Delete] (刪除)。

  5. 確認您要刪除資料庫，然後按一下 [Delete] (刪除)。

刪除執行個體

您刪除執行個體時，也會自動捨棄您在其中建立的所有資料庫。

使用指令列

gcloud spanner instances delete test-instance

使用 Google Cloud 控制台

  1. 前往 Google Cloud 控制台的「Spanner Instances」(Spanner 執行個體) 頁面。

    前往「Instances」(執行個體) 頁面

  2. 點選執行個體。

  3. 按一下 [Delete] (刪除)。

  4. 確認您要刪除執行個體，然後按一下 [Delete] (刪除)。

後續步驟