Getting started with Spanner in JDBC

Objectives

This tutorial walks you through the following steps using the Spanner JDBC driver:

Create a Spanner instance and database.
Write, read, and execute SQL queries on data in the database.
Update the database schema.
Update data using a read-write transaction.
Add a secondary index to the database.
Use the index to read and execute SQL queries on data.
Retrieve data using a read-only transaction.

Costs

This tutorial uses Spanner, which is a billable component of the Google Cloud. For information on the cost of using Spanner, see Pricing.

Before you begin

Complete the steps described in Set up, which cover creating and setting a default Google Cloud project, enabling billing, enabling the Cloud Spanner API, and setting up OAuth 2.0 to get authentication credentials to use the Cloud Spanner API.

In particular, make sure that you run gcloud auth application-default login to set up your local development environment with authentication credentials.

Prepare your local JDBC environment

Install the following on your development machine if they are not already installed:
- Java 8 JDK (download).
- Maven 3 (download).

Clone the sample app repository to your local machine:

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

Change to the directory that contains the Spanner sample code:
```
cd java-spanner-jdbc/samples/snippets
```

Create an instance

When you first use Spanner, you must create an instance, which is an allocation of resources that are used by Spanner databases. When you create an instance, you choose an instance configuration, which determines where your data is stored, and also the number of nodes to use, which determines the amount of serving and storage resources in your instance.

See Create an instance to learn how to create a Spanner instance using any of the following methods. You can name your instance test-instance to use it with other topics in this document that reference an instance named test-instance.

The Google Cloud CLI
The Google Cloud console
A client library (C++, C#, Go, Java, Node.js, PHP, Python, or Ruby)

Look through sample files

The samples repository contains a sample that shows how to use Spanner with JDBC.

The pom.xml adds the Spanner JDBC driver to the project's dependencies and configures the assembly plugin to build an executable JAR file with the Java class defined in this tutorial.

Build the sample from the samples/snippets directory:

mvnpackage-DskipTests

Create a database

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

You should see:

Created database [projects/my-project/instances/test-instance/databases/example-db]

The following code creates a database and two tables in the database.

GoogleSQL

staticvoidcreateDatabase(DatabaseAdminClientdbAdminClient,
InstanceNameinstanceName,StringdatabaseId){
CreateDatabaseRequestcreateDatabaseRequest=
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{
//InitiatetherequestwhichreturnsanOperationFuture.
com.google.spanner.admin.database.v1.Databasedb=
dbAdminClient.createDatabaseAsync(createDatabaseRequest).get();
System.out.println("Created database ["+db.getName()+"]");
}catch(ExecutionExceptione){
//Iftheoperationfailedduringexecution,exposethecause.
throw(SpannerException)e.getCause();
}catch(InterruptedExceptione){
//Throwwhenathreadiswaiting,sleeping,orotherwiseoccupied,
//andthethreadisinterrupted,eitherbeforeorduringtheactivity.
throwSpannerExceptionFactory.propagateInterrupt(e);
}
}

PostgreSQL

staticvoidcreatePostgreSqlDatabase(
DatabaseAdminClientdbAdminClient,StringprojectId,StringinstanceId,StringdatabaseId){
finalCreateDatabaseRequestrequest=
CreateDatabaseRequest.newBuilder()
.setCreateStatement("CREATE DATABASE \""+databaseId+"\"")
.setParent(InstanceName.of(projectId,instanceId).toString())
.setDatabaseDialect(DatabaseDialect.POSTGRESQL).build();
try{
//InitiatetherequestwhichreturnsanOperationFuture.
Databasedb=dbAdminClient.createDatabaseAsync(request).get();
System.out.println("Created database ["+db.getName()+"]");
}catch(ExecutionExceptione){
//Iftheoperationfailedduringexecution,exposethecause.
throw(SpannerException)e.getCause();
}catch(InterruptedExceptione){
//Throwwhenathreadiswaiting,sleeping,orotherwiseoccupied,
//andthethreadisinterrupted,eitherbeforeorduringtheactivity.
throwSpannerExceptionFactory.propagateInterrupt(e);
}
}
staticvoidcreateTableUsingDdl(DatabaseAdminClientdbAdminClient,DatabaseNamedatabaseName){
try{
//InitiatetherequestwhichreturnsanOperationFuture.
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(ExecutionExceptione){
//Iftheoperationfailedduringexecution,exposethecause.
throwSpannerExceptionFactory.asSpannerException(e);
}catch(InterruptedExceptione){
//Throwwhenathreadiswaiting,sleeping,orotherwiseoccupied,
//andthethreadisinterrupted,eitherbeforeorduringtheactivity.
throwSpannerExceptionFactory.propagateInterrupt(e);
}
}

The next step is to write data to your database.

Create a JDBC connection

Before you can do reads or writes, you must create a Connection. All of your interactions with Spanner must go through a Connection. The database name and other properties are specified in the JDBC connection URL and the java.util.Properties set.

GoogleSQL

staticvoidcreateConnection(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
//ConnectionpropertiescanbespecifiedbothwithinaPropertiesobject
//andintheconnectionURL.
properties.put("numChannels","8");
try(Connectionconnection=
DriverManager.getConnection(
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s"
 + ";minSessions=400;maxSessions=400",
project,instance,database),
properties)){
try(ResultSetresultSet=
connection.createStatement().executeQuery("select'HelloWorld!'")){
while(resultSet.next()){
System.out.println(resultSet.getString(1));
}
}
}
}

PostgreSQL

staticvoidcreateConnection(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
//ConnectionpropertiescanbespecifiedbothwithinaPropertiesobject
//andintheconnectionURL.
properties.put("numChannels","8");
try(Connectionconnection=
DriverManager.getConnection(
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s"
 + ";minSessions=400;maxSessions=400",
project,instance,database),
properties)){
try(ResultSetresultSet=
connection.createStatement().executeQuery("select'HelloWorld!'")){
while(resultSet.next()){
System.out.println(resultSet.getString(1));
}
}
}
}

For a full list of supported properties, see Connection URL Properties.

Each Connection uses resources, so it is good practice to either close connections when they are no longer needed, or to use a connection pool to re-use connections throughout your application.

Read more in the Connection Javadoc reference.

Connect the JDBC driver to the emulator

You can connect the JDBC driver to the Spanner emulator in two ways:

Set the SPANNER_EMULATOR_HOST environment variable: This instructs the JDBC driver to connect to the emulator. The Spanner instance and database in the JDBC connection URL must already exist on the emulator.
Add autoConfigEmulator=true to the connection URL: This instructs the JDBC driver to connect to the emulator, and to automatically create the Spanner instance and database in the JDBC connection URL if these don't exist.

This example shows how to use the autoConfigEmulator=true connection URL option.

GoogleSQL

staticvoidcreateConnectionWithEmulator(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
//AddautoConfigEmulator=truetotheconnectionURLtoinstructtheJDBC
//drivertoconnecttotheSpanneremulatoronlocalhost:9010.
//TheSpannerinstanceanddatabaseareautomaticallycreatedifthese
//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 'HelloWorld!'")){
while(resultSet.next()){
System.out.println(resultSet.getString(1));
}
}
}
}

PostgreSQL

staticvoidcreateConnectionWithEmulator(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
//AddautoConfigEmulator=truetotheconnectionURLtoinstructtheJDBC
//drivertoconnecttotheSpanneremulatoronlocalhost:9010.
//TheSpannerinstanceanddatabaseareautomaticallycreatedifthese
//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 'HelloWorld!'")){
while(resultSet.next()){
System.out.println(resultSet.getString(1));
}
}
}
}

Write data with DML

You can insert data using Data Manipulation Language (DML) in a read-write transaction.

You use the PreparedStatement.executeUpdate() method to execute a DML statement.

GoogleSQL

staticvoidwriteDataWithDml(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
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(
 "INSERTINTOSingers(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("%drecordsinserted.\n",updateCount);
}
}
}

PostgreSQL

staticvoidwriteDataWithDmlPostgreSQL(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
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(
 "INSERTINTOsingers(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("%drecordsinserted.\n",updateCount);
}
}
}

Run the sample with this command:

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

You should see:

4recordsinserted.

Write data with a DML batch

You use the PreparedStatement#addBatch() and PreparedStatement#executeBatch() methods to execute multiple DML statements in one batch.

GoogleSQL

staticvoidwriteDataWithDmlBatch(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
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(
 "INSERTINTOSingers(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(
 "%drecordsinserted.\n",
Arrays.stream(updateCounts).sum());
}
}
}

PostgreSQL

staticvoidwriteDataWithDmlBatchPostgreSQL(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
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(
 "INSERTINTOsingers(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(
 "%drecordsinserted.\n",
Arrays.stream(updateCounts).sum());
}
}
}

Run the sample with this command:

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

You should see:

3recordsinserted.

Write data with mutations

You can also insert data using mutations.

You can write data using a Mutation object. A Mutation object is a container for mutation operations. A Mutation represents a sequence of inserts, updates, and deletes that Spanner applies atomically to different rows and tables in a Spanner database.

The newInsertBuilder() method in the Mutation class constructs an INSERT mutation, which inserts a new row in a table. If the row already exists, the write fails. Alternatively, you can use the newInsertOrUpdateBuilder method to construct an INSERT_OR_UPDATE mutation, which updates column values if the row already exists.

The write() method in the CloudSpannerJdbcConnection interface writes the mutations. All mutations in a single batch are applied atomically.

You can unwrap the CloudSpannerJdbcConnection interface from a Spanner JDBC Connection.

This code shows how to write the data using mutations:

GoogleSQL

/**ThelistofSingerstoinsert.*/
staticfinalList<Singer>SINGERS=
Arrays.asList(
newSinger(1,"Marc","Richards"),
newSinger(2,"Catalina","Smith"),
newSinger(3,"Alice","Trentor"),
newSinger(4,"Lea","Martin"),
newSinger(5,"David","Lomond"));
/**ThelistofAlbumstoinsert.*/
staticfinalList<Album>ALBUMS=
Arrays.asList(
newAlbum(1,1,"Total Junk"),
newAlbum(1,2,"Go, Go, Go"),
newAlbum(2,1,"Green"),
newAlbum(2,2,"Forever Hold Your Peace"),
newAlbum(2,3,"Terrified"));
staticvoidwriteDataWithMutations(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
DriverManager.getConnection(
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
project,instance,database),
properties)){
//UnwraptheCloudSpannerJdbcConnectioninterface
//fromthejava.sql.Connection.
CloudSpannerJdbcConnectioncloudSpannerJdbcConnection=
connection.unwrap(CloudSpannerJdbcConnection.class);
List<Mutation>mutations=newArrayList<>();
for(Singersinger:SINGERS){
mutations.add(
Mutation.newInsertBuilder("Singers")
.set("SingerId")
.to(singer.singerId)
.set("FirstName")
.to(singer.firstName)
.set("LastName")
.to(singer.lastName)
.build());
}
for(Albumalbum:ALBUMS){
mutations.add(
Mutation.newInsertBuilder("Albums")
.set("SingerId")
.to(album.singerId)
.set("AlbumId")
.to(album.albumId)
.set("AlbumTitle")
.to(album.albumTitle)
.build());
}
//ApplythemutationsatomicallytoSpanner.
cloudSpannerJdbcConnection.write(mutations);
System.out.printf("Inserted%drows.\n",mutations.size());
}
}

PostgreSQL

/**ThelistofSingerstoinsert.*/
staticfinalList<Singer>SINGERS=
Arrays.asList(
newSinger(1,"Marc","Richards"),
newSinger(2,"Catalina","Smith"),
newSinger(3,"Alice","Trentor"),
newSinger(4,"Lea","Martin"),
newSinger(5,"David","Lomond"));
/**ThelistofAlbumstoinsert.*/
staticfinalList<Album>ALBUMS=
Arrays.asList(
newAlbum(1,1,"Total Junk"),
newAlbum(1,2,"Go, Go, Go"),
newAlbum(2,1,"Green"),
newAlbum(2,2,"Forever Hold Your Peace"),
newAlbum(2,3,"Terrified"));
staticvoidwriteDataWithMutationsPostgreSQL(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
DriverManager.getConnection(
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
project,instance,database),
properties)){
//UnwraptheCloudSpannerJdbcConnectioninterface
//fromthejava.sql.Connection.
CloudSpannerJdbcConnectioncloudSpannerJdbcConnection=
connection.unwrap(CloudSpannerJdbcConnection.class);
List<Mutation>mutations=newArrayList<>();
for(Singersinger: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(Albumalbum: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());
}
//ApplythemutationsatomicallytoSpanner.
cloudSpannerJdbcConnection.write(mutations);
System.out.printf("Inserted%drows.\n",mutations.size());
}
}

Run the sample with this command:

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

You should see:

Inserted 10 rows.

Query data using SQL

Spanner supports a SQL interface for reading data, which you can access on the command line using the Google Cloud CLI or programmatically using the Spanner JDBC driver.

On the command line

Execute the following SQL statement to read the values of all columns from the Albums table:

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'

The result shows:

SingerIdAlbumIdAlbumTitle
11TotalJunk
12Go,Go,Go
21Green
22ForeverHoldYourPeace
23Terrified

Use the Spanner JDBC driver

In addition to executing a SQL statement on the command line, you can issue the same SQL statement programmatically using the Spanner JDBC driver.

The following methods and classes are used to run the SQL query:

The createStatement() method in the Connection interface: use this to create a new statement object for running a SQL statement.
The executeQuery(String) method of the Statement class: use this method to execute a query against a database.
The Statement class: use this to execute a SQL string.
The ResultSet class: use this to access the data returned by a SQL statement.

Here's how to issue the query and access the data:

GoogleSQL

staticvoidqueryData(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
DriverManager.getConnection(
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
 project, instance, database),
 properties)) {
 try (ResultSet resultSet =
 connection
 .createStatement()
 .executeQuery(
 "SELECTSingerId,AlbumId,AlbumTitle"
 + "FROMAlbums")) {
 while (resultSet.next()) {
 System.out.printf(
 "%d%d%s\n",
 resultSet.getLong("SingerId"),
 resultSet.getLong("AlbumId"),
 resultSet.getString("AlbumTitle"));
}
}
}
}

PostgreSQL

staticvoidqueryDataPostgreSQL(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
DriverManager.getConnection(
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
 project, instance, database),
 properties)) {
 try (ResultSet resultSet =
 connection
 .createStatement()
 .executeQuery(
 "SELECTsinger_id,album_id,album_title"
 + "FROMalbums")) {
 while (resultSet.next()) {
 System.out.printf(
 "%d%d%s\n",
 resultSet.getLong("singer_id"),
 resultSet.getLong("album_id"),
 resultSet.getString("album_title"));
}
}
}
}

Run the sample with this command:

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

You should see the following result:

11TotalJunk
12Go,Go,Go
21Green
22ForeverHoldYourPeace
23Terrified

Query using a SQL parameter

If your application has a frequently executed query, you can improve its performance by parameterizing it. The resulting parametric query can be cached and reused, which reduces compilation costs. For more information, see Use query parameters to speed up frequently executed queries.

Here is an example of using a parameter in the WHERE clause to query records containing a specific value for LastName.

Use a java.sql.PreparedStatement to execute a query with a parameter.

GoogleSQL

staticvoidqueryWithParameter(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
DriverManager.getConnection(
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
 project, instance, database),
 properties)) {
 try (PreparedStatement statement =
 connection.prepareStatement(
 "SELECTSingerId,FirstName,LastName"
 + "FROMSingers"
 + "WHERELastName=?")) {
 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

staticvoidqueryWithParameterPostgreSQL(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
DriverManager.getConnection(
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
 project, instance, database),
 properties)) {
 try (PreparedStatement statement =
 connection.prepareStatement(
 "SELECTsinger_id,first_name,last_name"
 + "FROMsingers"
 + "WHERElast_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"));
}
}
}
}
}

Run the sample with this command:

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

You should see the following result:

12MelissaGarcia

Update the database schema

Assume you need to add a new column called MarketingBudget to the Albums table. Adding a new column to an existing table requires an update to your database schema. Spanner supports schema updates to a database while the database continues to serve traffic. Schema updates don't require taking the database offline and they don't lock entire tables or columns; you can continue writing data to the database during the schema update. Read more about supported schema updates and schema change performance in Make schema updates.

Add a column

You can add a column on the command line using the Google Cloud CLI or programmatically using the Spanner JDBC driver driver.

On the command line

Use the following ALTER TABLE command to add the new column to the 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'

You should see:

Schema updating...done.

Use the Spanner JDBC driver

Use the execute(String) method of the java.sql.Statement class to modify the schema:

GoogleSQL

staticvoidaddColumn(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
DriverManager.getConnection(
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
 project, instance, database),
 properties)) {
 connection
 .createStatement()
 .execute("ALTERTABLEAlbumsADDCOLUMNMarketingBudgetINT64");
 System.out.println("AddedMarketingBudgetcolumn");
}
}

PostgreSQL

staticvoidaddColumnPostgreSQL(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
DriverManager.getConnection(
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
 project, instance, database),
 properties)) {
 connection
 .createStatement()
 .execute("altertablealbumsaddcolumnmarketing_budgetbigint");
 System.out.println("Addedmarketing_budgetcolumn");
}
}

Run the sample with this command:

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

You should see:

Added MarketingBudget column.

Execute a DDL batch

It is recommended to execute multiple schema modifications in one batch. Use the addBatch(String) method of java.sql.Statement to add multiple DDL statements to a batch.

GoogleSQL

staticvoidddlBatch(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
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(
 "CREATETABLEVenues("
 + "VenueIdINT64NOTNULL,"
 + "NameSTRING(1024),"
 + "DescriptionJSON"
 + ")PRIMARYKEY(VenueId)");
 statement.addBatch(
 "CREATETABLEConcerts("
 + "ConcertIdINT64NOTNULL,"
 + "VenueIdINT64NOTNULL,"
 + "SingerIdINT64NOTNULL,"
 + "StartTimeTIMESTAMP,"
 + "EndTimeTIMESTAMP,"
 + "CONSTRAINTFk_Concerts_VenuesFOREIGNKEY"
 + "(VenueId)REFERENCESVenues(VenueId),"
 + "CONSTRAINTFk_Concerts_SingersFOREIGNKEY"
 + "(SingerId)REFERENCESSingers(SingerId),"
 + ")PRIMARYKEY(ConcertId)");
 statement.executeBatch();
 }
 System.out.println("AddedVenuesandConcertstables");
}
}

PostgreSQL

staticvoidddlBatchPostgreSQL(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
DriverManager.getConnection(
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
project,instance,database),
properties)){
try(Statementstatement=connection.createStatement()){
//Createtwonewtablesinonebatch.
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");
}
}

Run the sample with this command:

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

You should see:

Added Venues and Concerts tables.

Write data to the new column

The following code writes data to the new column. It sets MarketingBudget to 100000 for the row keyed by Albums(1, 1) and to 500000 for the row keyed by Albums(2, 2).

GoogleSQL

staticvoidupdateDataWithMutations(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
DriverManager.getConnection(
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
project,instance,database),
properties)){
//UnwraptheCloudSpannerJdbcConnectioninterface
//fromthejava.sql.Connection.
CloudSpannerJdbcConnectioncloudSpannerJdbcConnection=
connection.unwrap(CloudSpannerJdbcConnection.class);
finallongmarketingBudgetAlbum1=100000L;
finallongmarketingBudgetAlbum2=500000L;
//Mutationcanbeusedtoupdate/insert/deleteasinglerowinatable.
//HereweusenewUpdateBuildertocreateupdatemutations.
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());
//ThiswritesallthemutationstoCloudSpanneratomically.
cloudSpannerJdbcConnection.write(mutations);
System.out.println("Updatedalbums");
}
}

PostgreSQL

staticvoidupdateDataWithMutationsPostgreSQL(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
DriverManager.getConnection(
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
project,instance,database),
properties)){
//UnwraptheCloudSpannerJdbcConnectioninterface
//fromthejava.sql.Connection.
CloudSpannerJdbcConnectioncloudSpannerJdbcConnection=
connection.unwrap(CloudSpannerJdbcConnection.class);
finallongmarketingBudgetAlbum1=100000L;
finallongmarketingBudgetAlbum2=500000L;
//Mutationcanbeusedtoupdate/insert/deleteasinglerowinatable.
//HereweusenewUpdateBuildertocreateupdatemutations.
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());
//ThiswritesallthemutationstoCloudSpanneratomically.
cloudSpannerJdbcConnection.write(mutations);
System.out.println("Updatedalbums");
}
}

Run the sample with this command:

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

You should see output similar to this:

Updated albums

You can also execute a SQL query or a read call to fetch the values that you just wrote.

Here's the code to execute the query:

GoogleSQL

staticvoidqueryDataWithNewColumn(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
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(ResultSetresultSet=
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

staticvoidqueryDataWithNewColumnPostgreSQL(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
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(ResultSetresultSet=
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"));
}
}
}
}

To execute this query, run the following command:

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

The result shows:

11100000
12null
21null
22500000
23null

Update data

You can update data using DML in a read-write transaction.

Set AutoCommit=false to execute read-write transactions in JDBC.

GoogleSQL

staticvoidwriteWithTransactionUsingDml(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
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 =
 "SELECTMarketingBudget"
 + "FROMAlbums"
 + "WHERESingerId=?ANDAlbumId=?";
 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 =
 "UPDATEAlbums"
 + "SETMarketingBudget=?"
 + "WHERESingerId=?andAlbumId=?";
 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(
 "TransferredmarketingbudgetfromAlbum2toAlbum1");
}
}

PostgreSQL

staticvoidwriteWithTransactionUsingDmlPostgreSQL(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
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 =
 "SELECTmarketing_budget"
 + "fromalbums"
 + "WHEREsinger_id=?andalbum_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 =
 "UPDATEalbums"
 + "SETmarketing_budget=?"
 + "WHEREsinger_id=?andalbum_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(
 "TransferredmarketingbudgetfromAlbum2toAlbum1");
}
}

Run the sample with this command:

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

Transaction tags and request tags

Use transaction tags and request tags to troubleshoot transactions and queries in Spanner. You can set transaction tags and request tags in the JDBC with the TRANSACTION_TAG and STATEMENT_TAG session variables.

GoogleSQL

staticvoidtags(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
DriverManager.getConnection(
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
project,instance,database),
properties)){
//SetAutoCommit=falsetoenabletransactions.
connection.setAutoCommit(false);
//SettheTRANSACTION_TAGsessionvariabletosetatransactiontag
//forthecurrenttransaction.
connection
.createStatement()
.execute("SET TRANSACTION_TAG='example-tx-tag'");
//SettheSTATEMENT_TAGsessionvariabletosettherequesttag
//thatshouldbeincludedwiththenextSQLstatement.
connection
.createStatement()
.execute("SET STATEMENT_TAG='query-marketing-budget'");
longmarketingBudget=0L;
longsingerId=1L;
longalbumId=1L;
try(PreparedStatementstatement=connection.prepareStatement(
"SELECT MarketingBudget "
+"FROM Albums "
+"WHERE SingerId=? AND AlbumId=?")){
statement.setLong(1,singerId);
statement.setLong(2,albumId);
try(ResultSetalbumResultSet=statement.executeQuery()){
while(albumResultSet.next()){
marketingBudget=albumResultSet.getLong(1);
}
}
}
//Reducethemarketingbudgetby10%ifitismorethan1,000.
finallongmaxMarketingBudget=1000L;
finalfloatreduction=0.1f;
if(marketingBudget > maxMarketingBudget){
marketingBudget-=(long)(marketingBudget*reduction);
connection
.createStatement()
.execute("SET STATEMENT_TAG='reduce-marketing-budget'");
try(PreparedStatementstatement=connection.prepareStatement(
"UPDATE Albums SET MarketingBudget=? "
+"WHERE SingerId=? AND AlbumId=?")){
intparamIndex=0;
statement.setLong(++paramIndex,marketingBudget);
statement.setLong(++paramIndex,singerId);
statement.setLong(++paramIndex,albumId);
statement.executeUpdate();
}
}
//Committhecurrenttransaction.
connection.commit();
System.out.println("Reduced marketing budget");
}
}

PostgreSQL

staticvoidtagsPostgreSQL(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
DriverManager.getConnection(
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
project,instance,database),
properties)){
//SetAutoCommit=falsetoenabletransactions.
connection.setAutoCommit(false);
//SettheTRANSACTION_TAGsessionvariabletosetatransactiontag
//forthecurrenttransaction.
connection
.createStatement()
.execute("set spanner.transaction_tag='example-tx-tag'");
//SettheSTATEMENT_TAGsessionvariabletosettherequesttag
//thatshouldbeincludedwiththenextSQLstatement.
connection
.createStatement()
.execute("set spanner.statement_tag='query-marketing-budget'");
longmarketingBudget=0L;
longsingerId=1L;
longalbumId=1L;
try(PreparedStatementstatement=connection.prepareStatement(
"select marketing_budget "
+"from albums "
+"where singer_id=? and album_id=?")){
statement.setLong(1,singerId);
statement.setLong(2,albumId);
try(ResultSetalbumResultSet=statement.executeQuery()){
while(albumResultSet.next()){
marketingBudget=albumResultSet.getLong(1);
}
}
}
//Reducethemarketingbudgetby10%ifitismorethan1,000.
finallongmaxMarketingBudget=1000L;
finalfloatreduction=0.1f;
if(marketingBudget > maxMarketingBudget){
marketingBudget-=(long)(marketingBudget*reduction);
connection
.createStatement()
.execute("set spanner.statement_tag='reduce-marketing-budget'");
try(PreparedStatementstatement=connection.prepareStatement(
"update albums set marketing_budget=? "
+"where singer_id=? AND album_id=?")){
intparamIndex=0;
statement.setLong(++paramIndex,marketingBudget);
statement.setLong(++paramIndex,singerId);
statement.setLong(++paramIndex,albumId);
statement.executeUpdate();
}
}
//Committhecurrenttransaction.
connection.commit();
System.out.println("Reduced marketing budget");
}
}

Run the sample with this command:

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

Retrieve data using read-only transactions

Suppose you want to execute more than one read at the same timestamp. Read-only transactions observe a consistent prefix of the transaction commit history, so your application always gets consistent data. Set ReadOnly=true and AutoCommit=false on a java.sql.Connection, or use the SET TRANSACTION READ ONLY SQL statement, to execute a read-only transaction.

The following shows how to run a query and perform a read in the same read-only transaction:

GoogleSQL

staticvoidreadOnlyTransaction(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
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("SETTRANSACTIONREADONLY");
 try (ResultSet resultSet =
 connection
 .createStatement()
 .executeQuery(
 "SELECTSingerId,AlbumId,AlbumTitle"
 + "FROMAlbums"
 + "ORDERBYSingerId,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(
 "SELECTSingerId,AlbumId,AlbumTitle"
 + "FROMAlbums"
 + "ORDERBYAlbumTitle")) {
 while (resultSet.next()) {
 System.out.printf(
 "%d%d%s\n",
 resultSet.getLong("SingerId"),
 resultSet.getLong("AlbumId"),
 resultSet.getString("AlbumTitle"));
}
}
//Endtheread-onlytransactionbycallingcommit().
connection.commit();
}
}

PostgreSQL

staticvoidreadOnlyTransactionPostgreSQL(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
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("settransactionreadonly");
 try (ResultSet resultSet =
 connection
 .createStatement()
 .executeQuery(
 "SELECTsinger_id,album_id,album_title"
 + "FROMalbums"
 + "ORDERBYsinger_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(
 "SELECTsinger_id,album_id,album_title"
 + "FROMalbums"
 + "ORDERBYalbum_title")) {
 while (resultSet.next()) {
 System.out.printf(
 "%d%d%s\n",
 resultSet.getLong("singer_id"),
 resultSet.getLong("album_id"),
 resultSet.getString("album_title"));
}
}
//Endtheread-onlytransactionbycallingcommit().
connection.commit();
}
}

Run the sample with this command:

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

You should see output similar to:

11TotalJunk
12Go,Go,Go
21Green
22ForeverHoldYourPeace
23Terrified
22ForeverHoldYourPeace
12Go,Go,Go
21Green
23Terrified
11TotalJunk

Partitioned queries and Data Boost

The partitionQuery API divides a query into smaller pieces, or partitions, and uses multiple machines to fetch the partitions in parallel. Each partition is identified by a partition token. The PartitionQuery API has higher latency than the standard query API, because it is only intended for bulk operations such as exporting or scanning the whole database.

Data Boost lets you execute analytics queries and data exports with near-zero impact to existing workloads on the provisioned Spanner instance. Data Boost only supports partitioned queries.

GoogleSQL

staticvoiddataBoost(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
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("SETDATA_BOOST_ENABLED=TRUE");
 // Run a partitioned query. This query will use Data Boost.
 try (ResultSet resultSet =
 connection
 .createStatement()
 .executeQuery(
 "RUNPARTITIONEDQUERY"
 + "SELECTSingerId,FirstName,LastName"
 + "FROMSingers")) {
 while (resultSet.next()) {
 System.out.printf(
 "%d%s%s\n",
 resultSet.getLong("SingerId"),
 resultSet.getString("FirstName"),
 resultSet.getString("LastName"));
}
}
}
}

PostgreSQL

staticvoiddataBoostPostgreSQL(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
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("setspanner.data_boost_enabled=true");
 // Run a partitioned query. This query will use Data Boost.
 try (ResultSet resultSet =
 connection
 .createStatement()
 .executeQuery(
 "runpartitionedquery"
 + "selectsinger_id,first_name,last_name"
 + "fromsingers")) {
 while (resultSet.next()) {
 System.out.printf(
 "%d%s%s\n",
 resultSet.getLong("singer_id"),
 resultSet.getString("first_name"),
 resultSet.getString("last_name"));
}
}
}
}

Run the sample with this command:

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

For more information on running partitioned queries and using Data Boost with the JDBC driver, see:

Partitioned DML

Partitioned Data Manipulation Language (DML) is designed for the following types of bulk updates and deletes:

Periodic cleanup and garbage collection.
Backfilling new columns with default values.

* { GoogleSQL }

staticvoidpartitionedDml(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
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("SETAUTOCOMMIT_DML_MODE='PARTITIONED_NON_ATOMIC'");
 // Back-fill a default value for the MarketingBudget column.
 long lowerBoundUpdateCount =
 connection
 .createStatement()
 .executeUpdate("UPDATEAlbums"
 + "SETMarketingBudget=0"
 + "WHEREMarketingBudgetISNULL");
 System.out.printf("Updatedatleast%dalbums\n",lowerBoundUpdateCount);
}
}

PostgreSQL

staticvoidpartitionedDmlPostgreSQL(
finalStringproject,
finalStringinstance,
finalStringdatabase,
finalPropertiesproperties)throwsSQLException{
try(Connectionconnection=
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("setspanner.autocommit_dml_mode='partitioned_non_atomic'");
 // Back-fill a default value for the MarketingBudget column.
 long lowerBoundUpdateCount =
 connection
 .createStatement()
 .executeUpdate("updatealbums"
 + "setmarketing_budget=0"
 + "wheremarketing_budgetisnull");
 System.out.printf("Updatedatleast%dalbums\n",lowerBoundUpdateCount);
}
}

Run the sample with this command:

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

For more information on AUTOCOMMIT_DML_MODE, see:

Cleanup

To avoid incurring additional charges to your Cloud Billing account for the resources used in this tutorial, drop the database and delete the instance that you created.

Delete the database

If you delete an instance, all databases within it are automatically deleted. This step shows how to delete a database without deleting an instance (you would still incur charges for the instance).

On the command line

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

Using the Google Cloud console

Go to the Spanner Instances page in the Google Cloud console.

Go to the Instances page
Click the instance.
Click the database that you want to delete.
In the Database details page, click Delete.
Confirm that you want to delete the database and click Delete.

Delete the instance

Deleting an instance automatically drops all databases created in that instance.

On the command line

gcloud spanner instances delete test-instance

Using the Google Cloud console

Go to the Spanner Instances page in the Google Cloud console.

Go to the Instances page
Click your instance.
Click Delete.
Confirm that you want to delete the instance and click Delete.

What's next

Learn how to Integrate Spanner with Spring Data JPA (GoogleSQL dialect).
Learn how toIntegrate Spanner with Spring Data JPA (PostgreSQL dialect).
Learn how to Integrate Spanner with Hibernate ORM (GoogleSQL dialect).
Learn how toIntegrate Spanner with Hibernate ORM (PostgreSQL dialect).
Learn more about JDBC session management commands (GoogleSQL).
Learn more about JDBC session management commands (PostgreSQL).

Learn how to access Spanner with a virtual machine instance.
Learn about authorization and authentication credentials in Authenticate to Cloud services using client libraries.
Learn more about Spanner Schema design best practices.

Getting started with Spanner in JDBC Stay organized with collections Save and categorize content based on your preferences.

Objectives

Costs

Before you begin

Prepare your local JDBC environment

Create an instance

Look through sample files

Create a database

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

Create a JDBC connection

GoogleSQL

PostgreSQL

Connect the JDBC driver to the emulator

GoogleSQL

PostgreSQL

Write data with DML

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

Write data with a DML batch

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

Write data with mutations

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

Query data using SQL

On the command line

GoogleSQL

PostgreSQL

Use the Spanner JDBC driver

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

Query using a SQL parameter

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

Update the database schema

Add a column

On the command line

GoogleSQL

PostgreSQL

Use the Spanner JDBC driver

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

Execute a DDL batch

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

Write data to the new column

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

Update data

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

Transaction tags and request tags

GoogleSQL

PostgreSQL

GoogleSQL

Getting started with Spanner in JDBC