此页面由 Cloud Translation API 翻译。

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 环境

在开发机器上安装以下内容（如果尚未安装）：
- Java 8 JDK（下载）。
- Maven 3（下载）。

将示例应用代码库克隆到本地机器：

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

切换到包含 Spanner 示例代码的目录：
```
cd java-spanner-jdbc/samples/snippets
```

创建实例

在首次使用 Spanner 时，必须创建一个实例，实例是 Spanner 数据库使用的资源分配单位。创建实例时，请选择一个实例配置（决定数据的存储位置），同时选择要使用的节点数（决定实例中服务资源和存储资源的数量）。

执行以下命令，在区域 us-central1 中创建具有 1 个节点的 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 驱动程序添加到项目的依赖项中，并配置 assembly 插件以使用本教程中定义的 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) 键控的行，该代码会将 MarketingBudget 设置为 100000；而对于 Albums(2, 2) 键控的行，该代码会将其设置为 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 中事务和查询的问题。您可以使用 TRANSACTION_TAG 和 STATEMENT_TAG 会话变量在 JDBC 中设置事务标记和请求标记。

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 更长，因为它仅适用于导出或扫描整个数据库等批量操作。

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

如需详细了解如何运行分区查询以及如何将 Data Boost 与 JDBC 驱动程序搭配使用，请参阅：

分区 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，请参阅：

清理

为避免因本教程中使用的资源导致您的 Google Cloud 账号产生额外费用，请删除数据库和您创建的实例。

删除数据库

如果您删除一个实例，则该实例中的所有数据库都会自动删除。本步骤演示了如何在不删除实例的情况下删除数据库（您仍需为该实例付费）。

在命令行中

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

使用 Google Cloud 控制台

前往 Google Cloud 控制台中的 Spanner 实例页面。

转到“实例”页面
点击实例。
点击您想删除的数据库。
在数据库详细信息页面中，点击删除。
确认您要删除数据库并点击删除。

删除实例

删除实例会自动删除在该实例中创建的所有数据库。

在命令行中

gcloud spanner instances delete test-instance

使用 Google Cloud 控制台

前往 Google Cloud 控制台中的 Spanner 实例页面。

转到“实例”页面
点击您的实例。
点击删除。
确认您要删除实例并点击删除。

后续步骤

了解如何将 Spanner 与 Spring Data JPA（GoogleSQL 方言）集成。
了解如何将 Spanner 与 Spring Data JPA（PostgreSQL 方言）集成。
了解如何将 Spanner 与 Hibernate ORM（GoogleSQL 方言）集成。
了解如何将 Spanner 与 Hibernate ORM（PostgreSQL 方言）集成。
详细了解 JDBC 会话管理命令 (GoogleSQL)。
详细了解 JDBC 会话管理命令 (PostgreSQL)。

了解如何使用虚拟机实例访问 Spanner。
如需了解授权和身份验证凭据，请参阅使用客户端库向 Cloud 服务进行身份验证。
详细了解 Spanner 架构设计最佳实践。