Getting started with Spanner in JDBC

Objectives

This tutorial walks you through the following steps using the SpannerJDBC 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 theGoogle Cloud. For information on the cost of using Spanner, seePricing.

Before you begin

Complete the steps described inSet up, which cover creating andsetting a default Google Cloud project, enabling billing, enabling theCloud Spanner API, and setting up OAuth 2.0 to get authentication credentials to usethe Cloud Spanner API.

In particular, make sure that you rungcloud authapplication-default loginto set up your local development environment with authenticationcredentials.

Note: If you don't plan to keep the resources that you create in this tutorial,consider creating a new Google Cloud project instead of selecting an existingproject. After you finish the tutorial, you can delete the project, removing allresources associated with the project.

Prepare your local JDBC environment

  1. Install the following on your development machine if they are not already installed:

  2. Clone the sample app repository to your local machine:

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

  3. 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 anallocation of resources that are used by Spanner databases. When youcreate an instance, you choose aninstance configuration, which determineswhere your data is stored, and also the number of nodes to use, which determinesthe amount of serving and storage resources in your instance.

SeeCreate an instanceto learn how to create a Spanner instance using any of thefollowing methods. You can name your instancetest-instance to use it withother topics in this document that reference an instance namedtest-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 Spannerwith JDBC.

Thepom.xml adds the Spanner JDBC driver to theproject's dependencies and configures the assembly plugin to build anexecutable JAR file with the Java class defined in this tutorial.

Build the sample from thesamples/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.Note: The subsequent code samples use these two tables. If you don't executethis code, then create the tables by using the Google Cloud console or thegcloud CLI. For more information, see theexample schema.

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 aConnection. All of your interactionswith Spanner must go through aConnection. The database name and otherproperties are specified in the JDBC connection URL and thejava.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, seeConnection URL Properties.

EachConnection uses resources, so it is good practice to either closeconnections when they are no longer needed, or to use a connection pool tore-use connections throughout your application.

Read more in theConnection Javadocreference.

Connect the JDBC driver to the emulator

You can connect the JDBC driver to the Spanner emulator intwo ways:

  • Set theSPANNER_EMULATOR_HOST environment variable: This instructs theJDBC driver to connect to the emulator. The Spannerinstance and database in the JDBC connection URL must already exist on theemulator.
  • AddautoConfigEmulator=true to the connection URL: This instructs theJDBC driver to connect to the emulator, and to automatically create theSpanner instance and database in the JDBC connection URLif these don't exist.

This example shows how to use theautoConfigEmulator=true connection URLoption.

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-writetransaction.

You use thePreparedStatement.executeUpdate() method to execute a DMLstatement.

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.
Note: There are limits to commit size. SeeCRUD limitfor more information.

Write data with a DML batch

You use thePreparedStatement#addBatch() andPreparedStatement#executeBatch() methods to execute multiple DML statements inone batch.

Tip: You can also execute DML batches with theSTART BATCH DML command.

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 usingmutations.

You can write data usingaMutation object.AMutation object is a container for mutation operations. AMutationrepresents a sequence of inserts, updates, and deletes that Spannerapplies atomically to different rows and tables in a Spanner database.

ThenewInsertBuilder()method in theMutation class constructs anINSERT mutation, which inserts anew row in a table. If the row already exists, the write fails. Alternatively,you can use thenewInsertOrUpdateBuildermethod to construct anINSERT_OR_UPDATE mutation, which updates column valuesif the row already exists.

Thewrite()method in theCloudSpannerJdbcConnection interface writes the mutations. Allmutations in a single batch are applied atomically.

You can unwrap theCloudSpannerJdbcConnection interface from a SpannerJDBCConnection.

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.
Note: There are limits to commit size. SeeCRUD limitfor more information.

Query data using SQL

Spanner supports a SQL interface for reading data, which you canaccess on the command line using the Google Cloud CLI orprogrammatically usingthe Spanner JDBC driver.

On the command line

Execute the following SQL statement to read the values of all columns from theAlbums 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'
Note: For the GoogleSQL reference, seeQuery syntax in GoogleSQLand for PostgreSQL reference, seePostgreSQL lexical structure and syntax.

The result shows:

SingerIdAlbumIdAlbumTitle11TotalJunk12Go,Go,Go21Green22ForeverHoldYourPeace23Terrified

Use the Spanner JDBC driver

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

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

  • ThecreateStatement()method in theConnection interface: use this to create a new statementobject for running a SQL statement.
  • TheexecuteQuery(String)method of theStatement class: use this method to execute a query against adatabase.
  • TheStatement class: use this toexecute a SQL string.
  • TheResultSet class: use this toaccess 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:

11TotalJunk12Go,Go,Go21Green22ForeverHoldYourPeace23Terrified

Query using a SQL parameter

If your application has a frequently executed query, you can improve itsperformance by parameterizing it. The resulting parametric query can be cachedand reused, which reduces compilation costs. For more information, seeUse query parameters to speed up frequently executed queries.

Here is an example of using a parameter in theWHERE clause to query recordscontaining a specific value forLastName.

Use ajava.sql.PreparedStatementto 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 calledMarketingBudget to theAlbumstable. Adding a new column to an existing table requires an update to yourdatabase schema. Spanner supports schema updates to a database while thedatabase continues to serve traffic. Schema updates don't require taking thedatabase offline and they don't lock entire tables or columns; you can continuewriting data to the database during the schema update. Read more about supportedschema updates and schema change performance inMake schema updates.

Add a column

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

On the command line

Use the followingALTER TABLE command toadd 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 theexecute(String)method of thejava.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

We recommend that you execute multiple schema modifications in one batch. UsetheaddBatch(String)method ofjava.sql.Statement to add multiple DDL statements to a batch.

Tip: You can also execute DDL batches with theSTART BATCH DDL command.

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 setsMarketingBudget to100000 for the row keyed byAlbums(1, 1) and to500000 for the row keyedbyAlbums(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 youjust 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:

1110000012null21null2250000023null

Update data

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

SetAutoCommit=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

Usetransaction tags and request tagsto troubleshoot transactions and queries in Spanner. You can settransaction tags and request tags in the JDBC with theTRANSACTION_TAGandSTATEMENT_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
Tip: For a full list of commands that can be used to accessSpanner features in JDBC, seeSession management commands.

Retrieve data using read-only transactions

Suppose you want to execute more than one read at the same timestamp.Read-onlytransactions observe a consistentprefix of the transaction commit history, so your application always getsconsistent data.SetReadOnly=true andAutoCommit=false on ajava.sql.Connection, or usetheSET TRANSACTION READ ONLY SQL statement, to execute a read-onlytransaction.

Tip: The JDBC driver supports multiple additional SQL statements forexecuting specific types of transactions and batches, and for accessing specificSpanner features. For a full list of supported statements, seeJDBC session management commands (GoogleSQL)orJDBC session management commands (PostgreSQL).

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

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:

11TotalJunk12Go,Go,Go21Green22ForeverHoldYourPeace23Terrified22ForeverHoldYourPeace12Go,Go,Go21Green23Terrified11TotalJunk

Partitioned queries and Data Boost

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

Data Boostlets you execute analytics queries and data exports with near-zeroimpact to existing workloads on the provisioned Spanner instance.Data Boost only supportspartitioned 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 withthe JDBC driver, see:

Partitioned DML

Partitioned Data Manipulation Language (DML) isdesigned 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 onAUTOCOMMIT_DML_MODE, see:

Cleanup

To avoid incurring additional charges to your Cloud Billing account for theresources used in this tutorial, drop the database and delete the instance thatyou 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 wouldstill incur charges for the instance).

On the command line

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

Using the Google Cloud console

  1. Go to theSpanner Instances page in the Google Cloud console.

    Go to the Instances page

  2. Click the instance.

  3. Click the database that you want to delete.

  4. In theDatabase details page, clickDelete.

  5. Confirm that you want to delete the database and clickDelete.

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

  1. Go to theSpanner Instances page in the Google Cloud console.

    Go to the Instances page

  2. Click your instance.

  3. ClickDelete.

  4. Confirm that you want to delete the instance and clickDelete.

What's next

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2026-02-19 UTC.