本頁說明如何使用 Spanner JDBC 驅動程式在 Spanner 中執行基本作業。
安裝 JDBC 驅動程式
按照「Spanner 用戶端程式庫」中的步驟設定驗證，然後將下列程式碼片段中顯示的 Spanner JDBC 驅動程式依附元件新增至 pom.xml 檔案。
<dependencyManagement>
<dependencies>
<dependency>
<groupId>com.google.cloud</groupId>
<artifactId>libraries-bom</artifactId>
<version>26.63.0</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<dependencies>
<dependency>
<groupId>com.google.cloud</groupId>
<artifactId>google-cloud-spanner-jdbc</artifactId>
<exclusions>
<exclusion>
<groupId>com.google.api.grpc</groupId>
<artifactId>proto-google-cloud-spanner-executor-v1</artifactId>
</exclusion>
</exclusions>
</dependency>com.google.cloud.spanner.jdbc.JdbcDriver。如要瞭解如何設定連線，請參閱 JdbcDriver 的 API 說明文件。
連線至 Spanner 資料庫
JdbcDriver 類別說明會顯示連線字串語法，並包含用來建立連線及執行查詢的程式碼範例。
驅動程式會自動偵測指定資料庫的 SQL 方言 (GoogleSQL 或 PostgreSQL)。不需要或不允許使用方言參數。
連線至模擬器
如要連線至模擬器，請設定 SPANNER_EMULATOR_HOST 環境變數，例如：
Linux / macOS
export SPANNER_EMULATOR_HOST=localhost:9010Windows
set SPANNER_EMULATOR_HOST=localhost:9010這會指示 Spanner JDBC 驅動程式連線至 localhost 上執行的模擬器，而非預設的正式版服務。
範例
下列程式碼範例涵蓋一些常見用途。
執行結構定義更新
下列程式碼範例會先建立 JDBC 連線，然後建立資料表，將 Singers 資料表新增至資料庫：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
class CreateTableExample {
static void createTable() throws SQLException {
// TODO(developer): Replace these variables before running the sample.
String projectId = "my-project";
String instanceId = "my-instance";
String databaseId = "my-database";
createTable(projectId, instanceId, databaseId);
}
static void createTable(String projectId, String instanceId, String databaseId)
throws SQLException {
String connectionUrl =
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
projectId, instanceId, databaseId);
try (Connection connection = DriverManager.getConnection(connectionUrl)) {
try (Statement statement = connection.createStatement()) {
statement.execute(
"CREATE TABLE Singers (\n"
+ " SingerId INT64 NOT NULL,\n"
+ " FirstName STRING(1024),\n"
+ " LastName STRING(1024),\n"
+ " SingerInfo BYTES(MAX),\n"
+ " Revenues NUMERIC,\n"
+ ") PRIMARY KEY (SingerId)\n");
}
}
System.out.println("Created table [Singers]");
}
}在自動提交模式下使用交易新增資料列
如果您不需要以群組形式提交多項作業，可以使用自動提交模式的交易，這是預設行為。下列程式碼範例會使用自動提交模式的交易，將資料列新增至 Singers 資料表：
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Arrays;
import java.util.List;
class InsertDataExample {
// Class to contain singer sample data.
static class Singer {
final long singerId;
final String firstName;
final String lastName;
final BigDecimal revenues;
Singer(long singerId, String firstName, String lastName, BigDecimal revenues) {
this.singerId = singerId;
this.firstName = firstName;
this.lastName = lastName;
this.revenues = revenues;
}
}
static final List<Singer> SINGERS =
Arrays.asList(
new Singer(10, "Marc", "Richards", new BigDecimal("104100.00")),
new Singer(20, "Catalina", "Smith", new BigDecimal("9880.99")),
new Singer(30, "Alice", "Trentor", new BigDecimal("300183")),
new Singer(40, "Lea", "Martin", new BigDecimal("20118.12")),
new Singer(50, "David", "Lomond", new BigDecimal("311399.26")));
static void insertData() throws SQLException {
// TODO(developer): Replace these variables before running the sample.
String projectId = "my-project";
String instanceId = "my-instance";
String databaseId = "my-database";
insertData(projectId, instanceId, databaseId);
}
static void insertData(String projectId, String instanceId, String databaseId)
throws SQLException {
String connectionUrl =
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
projectId, instanceId, databaseId);
try (Connection connection = DriverManager.getConnection(connectionUrl)) {
try (PreparedStatement ps =
connection.prepareStatement(
"INSERT INTO Singers\n"
+ "(SingerId, FirstName, LastName, SingerInfo, Revenues)\n"
+ "VALUES\n"
+ "(?, ?, ?, ?, ?)")) {
for (Singer singer : SINGERS) {
ps.setLong(1, singer.singerId);
ps.setString(2, singer.firstName);
ps.setString(3, singer.lastName);
ps.setNull(4, Types.BINARY);
ps.setBigDecimal(5, singer.revenues);
ps.addBatch();
}
int[] updateCounts = ps.executeBatch();
System.out.printf("Insert counts: %s%n", Arrays.toString(updateCounts));
}
}
}
}控管多項作業如何以群組形式提交
如要控制 Spanner 是否要將多項作業一起以群組形式提交，可以停用自動提交模式。下列程式碼範例使用 connection.setAutoCommit(false) 和 connection.commit()，在 Singers 資料表中新增資料列。
import com.google.common.collect.ImmutableList;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
class BatchDmlExample {
static class Singer {
final long singerId;
final String firstName;
final String lastName;
final BigDecimal revenues;
Singer(long singerId, String firstName, String lastName, BigDecimal revenues) {
this.singerId = singerId;
this.firstName = firstName;
this.lastName = lastName;
this.revenues = revenues;
}
}
static void batchDml() throws SQLException {
// TODO(developer): Replace these variables before running the sample.
String projectId = "my-project";
String instanceId = "my-instance";
String databaseId = "my-database";
batchDml(projectId, instanceId, databaseId);
}
// This example shows how to execute a batch of DML statements with the JDBC driver.
static void batchDml(String projectId, String instanceId, String databaseId) throws SQLException {
String connectionUrl =
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
projectId, instanceId, databaseId);
ImmutableList<Singer> singers = ImmutableList.of(
new Singer(10, "Marc", "Richards", BigDecimal.valueOf(10000)),
new Singer(11, "Amirah", "Finney", BigDecimal.valueOf(195944.10d)),
new Singer(12, "Reece", "Dunn", BigDecimal.valueOf(10449.90))
);
try (Connection connection = DriverManager.getConnection(connectionUrl)) {
connection.setAutoCommit(false);
// Use prepared statements for the lowest possible latency when executing the same SQL string
// multiple times.
try (PreparedStatement statement = connection.prepareStatement(
"INSERT INTO Singers (SingerId, FirstName, LastName, Revenues)\n"
+ "VALUES (?, ?, ?, ?)")) {
for (Singer singer : singers) {
statement.setLong(1, singer.singerId);
statement.setString(2, singer.firstName);
statement.setString(3, singer.lastName);
statement.setBigDecimal(4, singer.revenues);
// Add the current parameter values to the batch.
statement.addBatch();
}
// Execute the batched statements.
int[] updateCounts = statement.executeBatch();
connection.commit();
System.out.printf("Batch insert counts: %s%n", Arrays.toString(updateCounts));
}
}
}
}執行 SQL 查詢
下列程式碼範例會傳回 Singers 資料表中的所有資料列，並依歌手的姓氏排序：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SingleUseReadOnlyExample {
static void singleUseReadOnly() throws SQLException {
// TODO(developer): Replace these variables before running the sample.
String projectId = "my-project";
String instanceId = "my-instance";
String databaseId = "my-database";
singleUseReadOnly(projectId, instanceId, databaseId);
}
static void singleUseReadOnly(String projectId, String instanceId, String databaseId)
throws SQLException {
String connectionUrl =
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
projectId, instanceId, databaseId);
try (Connection connection = DriverManager.getConnection(connectionUrl);
Statement statement = connection.createStatement()) {
// When the connection is in autocommit mode, any query that is executed will automatically
// be executed using a single-use read-only transaction, even if the connection itself is in
// read/write mode.
try (ResultSet rs =
statement.executeQuery(
"SELECT SingerId, FirstName, LastName, Revenues FROM Singers ORDER BY LastName")) {
while (rs.next()) {
System.out.printf(
"%d %s %s %s%n",
rs.getLong(1), rs.getString(2), rs.getString(3), rs.getBigDecimal(4));
}
}
}
}
}