Using the open-source JDBC driver

Note: This page describes the open-source JDBC driver for Cloud Spanner, which is recommended and authored by Google. There is also a JDBC driver created by Simba.

This page shows you how to perform basic operations in Cloud Spanner using the open-source JDBC driver.

Install the JDBC driver

Follow the steps in Cloud Spanner client libraries to set up authentication, and then add the Cloud Spanner JDBC driver dependencies, shown in the following snippet, to your pom.xml file.

spanner/jdbc/pom.xml

View on GitHub

<dependencies>
  <!-- The Spanner JDBC driver dependency -->
  <dependency>
    <groupId>com.google.cloud</groupId>
    <artifactId>google-cloud-spanner-jdbc</artifactId>
  </dependency>

  <!-- Test dependencies -->
  <dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13.2</version>
    <scope>test</scope>
  </dependency>
  <dependency>
    <groupId>com.google.truth</groupId>
    <artifactId>truth</artifactId>
    <version>1.1.2</version>
    <scope>test</scope>
  </dependency>
</dependencies>

In case you are using a framework that requires the Java class name in order to load the JDBC driver, it is com.google.cloud.spanner.jdbc.JdbcDriver. See the API documentatation for JdbcDriver for how to setup a connection.

Connecting to the emulator

To connect to the emulator, set the SPANNER_EMULATOR_HOST environment variable, for example:

Linux / macOS

export SPANNER_EMULATOR_HOST=localhost:9010

Windows

set SPANNER_EMULATOR_HOST=localhost:9010

This instructs the JDBC driver to connect to the emulator running on the localhost instead of the default production service.

Note: Although less common, you can specify the host in the connection string, for example jdbc:cloudspanner://localhost:9010/projects/test-project/instances/test-instance/databases/test-db;usePlainText=true. The host in the connection string always takes precedence regardless of whether SPANNER_EMULATOR_HOST is set. Also, you must specify usePlainText=true in the connection string because the emulator does not support SSL/TLS.

Run a schema update

The following code example adds the Singers table to the database by first creating a JDBC connection and then creating the table:

import edu.umd.cs.findbugs.annotations.SuppressFBWarnings;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

class CreateTableExample {

  static void createTable() throws SQLException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my-project";
    String instanceId = "my-instance";
    String databaseId = "my-database";
    createTable(projectId, instanceId, databaseId);
  }

  @SuppressFBWarnings(
      value = "OBL_UNSATISFIED_OBLIGATION",
      justification = "https://github.com/spotbugs/spotbugs/issues/293")
  static void createTable(String projectId, String instanceId, String databaseId)
      throws SQLException {
    String connectionUrl =
        String.format(
            "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
            projectId, instanceId, databaseId);
    try (Connection connection = DriverManager.getConnection(connectionUrl)) {
      try (Statement statement = connection.createStatement()) {
        statement.execute(
            "CREATE TABLE Singers (\n"
                + "  SingerId   INT64 NOT NULL,\n"
                + "  FirstName  STRING(1024),\n"
                + "  LastName   STRING(1024),\n"
                + "  SingerInfo BYTES(MAX),\n"
                + "  Revenues   NUMERIC,\n"
                + ") PRIMARY KEY (SingerId)\n");
      }
    }
    System.out.println("Created table [Singers]");
  }
}

Use a transaction in autocommit mode to add rows

If you don't need to commit multiple operations as a group, you can use a transaction in autocommit mode, which is the default behavior. The following code example uses a transaction in autocommit mode to add rows to the Singers table:

import edu.umd.cs.findbugs.annotations.SuppressFBWarnings;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Arrays;
import java.util.List;

class InsertDataExample {
  // Class to contain singer sample data.
  static class Singer {
    final long singerId;
    final String firstName;
    final String lastName;
    final BigDecimal revenues;

    Singer(long singerId, String firstName, String lastName, BigDecimal revenues) {
      this.singerId = singerId;
      this.firstName = firstName;
      this.lastName = lastName;
      this.revenues = revenues;
    }
  }

  static final List<Singer> SINGERS =
      Arrays.asList(
          new Singer(10, "Marc", "Richards", new BigDecimal("104100.00")),
          new Singer(20, "Catalina", "Smith", new BigDecimal("9880.99")),
          new Singer(30, "Alice", "Trentor", new BigDecimal("300183")),
          new Singer(40, "Lea", "Martin", new BigDecimal("20118.12")),
          new Singer(50, "David", "Lomond", new BigDecimal("311399.26")));

  static void insertData() throws SQLException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my-project";
    String instanceId = "my-instance";
    String databaseId = "my-database";
    insertData(projectId, instanceId, databaseId);
  }

  @SuppressFBWarnings(
      value = "OBL_UNSATISFIED_OBLIGATION",
      justification = "https://github.com/spotbugs/spotbugs/issues/293")
  static void insertData(String projectId, String instanceId, String databaseId)
      throws SQLException {
    String connectionUrl =
        String.format(
            "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
            projectId, instanceId, databaseId);
    try (Connection connection = DriverManager.getConnection(connectionUrl)) {
      try (PreparedStatement ps =
          connection.prepareStatement(
              "INSERT INTO Singers\n"
                  + "(SingerId, FirstName, LastName, SingerInfo, Revenues)\n"
                  + "VALUES\n"
                  + "(?, ?, ?, ?, ?)")) {
        for (Singer singer : SINGERS) {
          ps.setLong(1, singer.singerId);
          ps.setString(2, singer.firstName);
          ps.setString(3, singer.lastName);
          ps.setNull(4, Types.BINARY);
          ps.setBigDecimal(5, singer.revenues);
          ps.addBatch();
        }
        int[] updateCounts = ps.executeBatch();
        System.out.printf("Insert counts: %s%n", Arrays.toString(updateCounts));
      }
    }
  }
}

Control how multiple operations are committed as a group

If you want to control whether Cloud Spanner commits multiple operations together as a group, you can disable autocommit mode. The following code example uses connection.setAutoCommit(false) and connection.commit() to add rows to the Singers table.

import edu.umd.cs.findbugs.annotations.SuppressFBWarnings;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;

class BatchDmlExample {

  static void batchDml() throws SQLException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my-project";
    String instanceId = "my-instance";
    String databaseId = "my-database";
    batchDml(projectId, instanceId, databaseId);
  }

  @SuppressFBWarnings(
      value = "OBL_UNSATISFIED_OBLIGATION",
      justification = "https://github.com/spotbugs/spotbugs/issues/293")
  static void batchDml(String projectId, String instanceId, String databaseId) throws SQLException {
    String connectionUrl =
        String.format(
            "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
            projectId, instanceId, databaseId);
    try (Connection connection = DriverManager.getConnection(connectionUrl)) {
      connection.setAutoCommit(false);
      try (Statement statement = connection.createStatement()) {
        statement.addBatch(
            "INSERT INTO Singers (SingerId, FirstName, LastName, Revenues)\n"
                + "VALUES (10, 'Marc', 'Richards', 100000)");
        statement.addBatch(
            "INSERT INTO Singers (SingerId, FirstName, LastName, Revenues)\n"
                + "VALUES (11, 'Amirah', 'Finney', 195944.10)");
        statement.addBatch(
            "INSERT INTO Singers (SingerId, FirstName, LastName, Revenues)\n"
                + "VALUES (12, 'Reece', 'Dunn', 10449.90)");
        int[] updateCounts = statement.executeBatch();
        connection.commit();
        System.out.printf("Batch insert counts: %s%n", Arrays.toString(updateCounts));
      }
    }
  }
}

Run a SQL query

The following code example returns all rows in the Singers table ordered by the singer's last name:

import edu.umd.cs.findbugs.annotations.SuppressFBWarnings;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SingleUseReadOnlyExample {

  static void singleUseReadOnly() throws SQLException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my-project";
    String instanceId = "my-instance";
    String databaseId = "my-database";
    singleUseReadOnly(projectId, instanceId, databaseId);
  }

  @SuppressFBWarnings(
      value = "OBL_UNSATISFIED_OBLIGATION",
      justification = "https://github.com/spotbugs/spotbugs/issues/293")
  static void singleUseReadOnly(String projectId, String instanceId, String databaseId)
      throws SQLException {
    String connectionUrl =
        String.format(
            "jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
            projectId, instanceId, databaseId);
    try (Connection connection = DriverManager.getConnection(connectionUrl);
        Statement statement = connection.createStatement()) {
      // When the connection is in autocommit mode, any query that is executed will automatically
      // be executed using a single-use read-only transaction, even if the connection itself is in
      // read/write mode.
      try (ResultSet rs =
          statement.executeQuery(
              "SELECT SingerId, FirstName, LastName, Revenues FROM Singers ORDER BY LastName")) {
        while (rs.next()) {
          System.out.printf(
              "%d %s %s %s%n",
              rs.getLong(1), rs.getString(2), rs.getString(3), rs.getBigDecimal(4));
        }
      }
    }
  }
}

Session management statements

In addition to SQL query statements, the open-source JDBC (Java Database Connectivity) driver for Cloud Spanner supports session management statements, which let you modify the state of your connection, execute transactions, and efficiently execute batches of statements.

Note: You can only use these statements with the open-source JDBC driver. Session management statements do not work in the client libraries, the gcloud command-line tool, or the JDBC driver created by Simba.

Connection statements

The following statements make changes to or display properties of the current connection.

SHOW VARIABLE READONLY

Returns a result set with one row and one column of type BOOL indicating whether or not the connection is currently in read-only mode.

SET READONLY

Syntax

SET READONLY = { true | false }

Sets whether the connection is in read-only mode.

You can execute this statement only while there is no active transaction.

SHOW VARIABLE AUTOCOMMIT

Returns a result set with one row and one column of type BOOL indicating whether the connection is in AUTOCOMMIT mode.

SET AUTOCOMMIT

Syntax

SET AUTOCOMMIT = { true | false }

Sets the AUTOCOMMIT mode of the connection.

You can execute this statement only while there is no active transaction.

SHOW VARIABLE RETRY_ABORTS_INTERNALLY

Returns a result set with one row and one column of type BOOL indicating whether the connection automatically retries aborted transactions.

SET RETRY_ABORTS_INTERNALLY

Syntax

SET RETRY_ABORTS_INTERNALLY = { true | false }

Sets whether the connection automatically retries aborted transactions.

You can execute this statement only while there is no active transaction.

When you enable RETRY_ABORTS_INTERNALLY, the connection keeps a cryptographic checksum of all data that the connection returns to the client application and all of the update counts that the connection reports during the transaction. If Cloud Spanner aborts the transaction, the connection tries to execute the same transaction and checks if the returned data is exactly equal to data returned in the original transaction. If the data matches, Cloud Spanner continues the transaction. If the data does not match, the transaction fails by throwing AbortedDueToConcurrentModification.

This setting is enabled by default. We recommended turning off this setting if your application already retries aborted transactions.

SHOW VARIABLE AUTOCOMMIT_DML_MODE

Returns a result set with one row and one column of type STRING indicating the autocommit mode for Data Manipulation Language (DML) statements.

This variable has an effect only when you enable AUTOCOMMIT mode on the connection.

SET AUTOCOMMIT_DML_MODE

Syntax

SET AUTOCOMMIT_DML_MODE = { 'TRANSACTIONAL' | 'PARTITIONED_NON_ATOMIC' }

Sets the autocommit mode for DML statements:

• In TRANSACTIONAL mode, the driver executes DML statements as separate atomic transactions. The driver creates a new transaction, executes the DML statement, and either commits the transaction upon successful execution or rolls back the transaction in the case of an error.
• In PARTITIONED_NON_ATOMIC mode, the driver executes DML statements as partitioned update statements. A partitioned update statement can run as a series of many transactions, each covering a subset of the rows impacted, and the partitioned statement provides weakened semantics in exchange for better scalability and performance.

You can execute this statement only if you have enabled AUTOCOMMIT mode.

SHOW VARIABLE STATEMENT_TIMEOUT

Returns a result set with one row and one column of type STRING indicating the current timeout value for statements. The value is a whole number followed by a suffix indicating the time unit. A value of NULL indicates that there is no timeout value set. If a statement timeout value has been set, statements that take longer than the specified timeout value will cause a java.sql.SQLTimeoutException and invalidate the transaction.

SET STATEMENT_TIMEOUT

Syntax

SET STATEMENT_TIMEOUT = { '<INT64>{ s | ms | us | ns }' | NULL }

Sets the statement timeout value for all subsequent statements on the connection. Setting the timeout value to NULL disables statement timeouts for the connection.

The supported time units are:

• s: seconds
• ms: milliseconds
• us: microseconds
• ns: nanoseconds

A statement timeout during a transaction invalidates the transaction, all subsequent statements in the invalidated transaction (except ROLLBACK) fail, and the JDBC driver throws a java.sql.SQLTimeoutException.

SHOW VARIABLE READ_ONLY_STALENESS

Returns a result set with one row and one column of type STRING indicating the current read-only staleness setting that Cloud Spanner uses for read-only transactions and queries in AUTOCOMMIT mode. The default setting is STRONG.

SET READ_ONLY_STALENESS

Syntax

SET READ_ONLY_STALENESS = { 'STRONG' | 'MIN_READ_TIMESTAMP <timestamp>' | 'READ_TIMESTAMP <timestamp>' |
    'MAX_STALENESS <INT64>{ s | ms | us | ns }' | 'EXACT_STALENESS <INT64>{ s | ms | us | ns }' }

Sets the read-only staleness setting for all subsequent read-only transactions while not in AUTOCOMMIT mode, and for all queries while in AUTOCOMMIT mode.

The timestamp bound options are as follows:

• STRONG tells Cloud Spanner to perform a strong read.
• MAX_STALENESS defines the time interval Cloud Spanner uses to perform a bounded staleness read, relative to now().
• MIN_READ_TIMESTAMP defines an absolute time Cloud Spanner uses to perform a bounded staleness read.
• EXACT_STALENESS defines the time interval Cloud Spanner uses to perform an exact staleness read, relative to now().
• READ_TIMESTAMP defines an absolute time Cloud Spanner uses to perform an exact staleness read.

Timestamps must use the following format:

YYYY-[M]M-[D]DT[[H]H:[M]M:[S]S[.DDDDDD]][timezone]

The supported time units for setting MAX_STALENESS and EXACT_STALENESS values are:

• s: seconds
• ms: milliseconds
• us: microseconds
• ns: nanoseconds

You can execute this statement only while there is no active transaction.

Note: You can only use the values MIN_READ_TIMESTAMP and MAX_STALENESS while Cloud Spanner is in AUTOCOMMIT mode.

Transaction statements

The following statements manage and commit Cloud Spanner transactions.

SHOW VARIABLE READ_TIMESTAMP

Returns a result set with one row and one column of type TIMESTAMP containing the read timestamp of the most recent read-only transaction. This statement returns a timestamp only when either a read-only transaction is still active and has executed at least one query, or immediately after a read-only transaction is committed and before a new transaction starts. Otherwise, the result is NULL.

SHOW VARIABLE COMMIT_TIMESTAMP

Returns a result set with one row and one column of type TIMESTAMP containing the commit timestamp of the last read-write transaction Cloud Spanner committed. This statement returns a timestamp only when you execute it after you commit a read-write transaction and before you execute any subsequent SELECT, DML, or schema change statements. Otherwise, the result is NULL.

BEGIN [TRANSACTION]

Starts a new transaction.

• If you have enabled AUTOCOMMIT mode, this statement temporarily takes the connection out of AUTOCOMMIT mode. The connection returns to AUTOCOMMIT mode when the transaction ends.
• If you have disabled AUTOCOMMIT mode, this statement is optional and has no effect.

You can execute this statement only while there is no active transaction.

COMMIT [TRANSACTION]

Commits the current transaction.

• Committing a read-write transaction makes all updates of this transaction visible to other transactions and releases all of the transaction's locks on Cloud Spanner.
• Committing a read-only transaction ends the current read-only transaction. Any subsequent statement starts a new transaction. There is no semantic difference between COMMIT and ROLLBACK for a read-only transaction.

You can execute this statement only while there is an active transaction.

ROLLBACK [TRANSACTION]

Performs a ROLLBACK of the current transaction.

• Performing a ROLLBACK of a read-write transaction clears any buffered mutations, rolls back the transaction on Cloud Spanner, and releases any locks the transaction held.
• Performing a ROLLBACK of a read-only transaction ends the current read-only transaction. Any subsequent statements start a new transaction. There is no semantic difference between COMMIT and ROLLBACK for a read-only transaction on a connection.

You can execute this statement only while there is an active transaction.

SET TRANSACTION

Syntax

SET TRANSACTION { READ ONLY | READ WRITE }

Sets the transaction mode for the current transaction.

You can execute this statement only when you do not enable AUTOCOMMIT mode, or if you have started a temporary transaction by executing BEGIN [TRANSACTION] and have not yet executed any statements in the transaction.

Note: You cannot set the transaction mode to READ WRITE if the connection is in READ ONLY mode.

Batch statements

The following statements manage batches of DDL statements and send those batches to Cloud Spanner.

START BATCH DDL

Starts a batch of DDL statements on the connection. All subsequent statements during the batch must be DDL statements. The DDL statements are buffered locally and sent to Cloud Spanner as one batch when you execute RUN BATCH. Executing multiple DDL statements as one batch is typically faster than running the statements separately.

You can execute this statement only while there is no active transaction.

RUN BATCH

Sends all buffered DDL statements in the current DDL batch to the database, waits for Cloud Spanner to execute these statements, and ends the current DDL batch.

If Cloud Spanner cannot execute at least one DDL statement, RUN BATCH returns an error for the first DDL statement that Cloud Spanner cannot execute. Otherwise, RUN BATCH returns successfully.

Note: If a DDL statement in the batch returns an error, Cloud Spanner might still have applied the preceding DDL statements in the same batch to the database.

ABORT BATCH

Clears all buffered DDL statements in the current DDL batch and ends the batch.

You can execute this statement only when a DDL batch is active. You can use ABORT BATCH regardless of whether or not the batch has buffered DDL statements.

START BATCH DML

The following statements batch the two DML statements together and send these in one call to the server. A DML batch can be executed as part of a transaction or in autocommit mode.

START BATCH DML
INSERT INTO MYTABLE (ID, NAME) VALUES (1, 'ONE')
INSERT INTO MYTABLE (ID, NAME) VALUES (2, 'TWO')
RUN BATCH

What's next

Get answers to frequently asked questions about the open-source JDBC driver.