Stay organized with collections
Save and categorize content based on your preferences.
This page shows you how to perform basic operations in Spanner using the
Spanner JDBC driver.
Install the JDBC driver
Follow the steps in Spanner client libraries
to set up authentication, and then add the Spanner JDBC driver
dependencies, shown in the following snippet, to your pom.xml file.
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 documentation for JdbcDriver
for how to set up a connection.
Connect to a Spanner database
The JdbcDriver
class description shows the connection string syntax and includes sample code to
create a connection and run a query.
The driver automatically detects the SQL dialect (GoogleSQL or PostgreSQL)
of the specified database. A dialect parameter is not required or allowed.
Connect 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 Spanner JDBC driver driver to connect to the
emulator running on the localhost instead of the default production service.
Examples
The following code examples cover some common use cases.
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:
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 java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Arrays;
import java.util.List;
class InsertDataExample {
// Class to contain singer sample data.
static class Singer {
final long singerId;
final String firstName;
final String lastName;
final BigDecimal revenues;
Singer(long singerId, String firstName, String lastName, BigDecimal revenues) {
this.singerId = singerId;
this.firstName = firstName;
this.lastName = lastName;
this.revenues = revenues;
}
}
static final List<Singer> SINGERS =
Arrays.asList(
new Singer(10, "Marc", "Richards", new BigDecimal("104100.00")),
new Singer(20, "Catalina", "Smith", new BigDecimal("9880.99")),
new Singer(30, "Alice", "Trentor", new BigDecimal("300183")),
new Singer(40, "Lea", "Martin", new BigDecimal("20118.12")),
new Singer(50, "David", "Lomond", new BigDecimal("311399.26")));
static void insertData() throws SQLException {
// TODO(developer): Replace these variables before running the sample.
String projectId = "my-project";
String instanceId = "my-instance";
String databaseId = "my-database";
insertData(projectId, instanceId, databaseId);
}
static void insertData(String projectId, String instanceId, String databaseId)
throws SQLException {
String connectionUrl =
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
projectId, instanceId, databaseId);
try (Connection connection = DriverManager.getConnection(connectionUrl)) {
try (PreparedStatement ps =
connection.prepareStatement(
"INSERT INTO Singers\n"
+ "(SingerId, FirstName, LastName, SingerInfo, Revenues)\n"
+ "VALUES\n"
+ "(?, ?, ?, ?, ?)")) {
for (Singer singer : SINGERS) {
ps.setLong(1, singer.singerId);
ps.setString(2, singer.firstName);
ps.setString(3, singer.lastName);
ps.setNull(4, Types.BINARY);
ps.setBigDecimal(5, singer.revenues);
ps.addBatch();
}
int[] updateCounts = ps.executeBatch();
System.out.printf("Insert counts: %s%n", Arrays.toString(updateCounts));
}
}
}
}
Control how multiple operations are committed as a group
If you want to control whether 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 com.google.common.collect.ImmutableList;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
class BatchDmlExample {
static class Singer {
final long singerId;
final String firstName;
final String lastName;
final BigDecimal revenues;
Singer(long singerId, String firstName, String lastName, BigDecimal revenues) {
this.singerId = singerId;
this.firstName = firstName;
this.lastName = lastName;
this.revenues = revenues;
}
}
static void batchDml() throws SQLException {
// TODO(developer): Replace these variables before running the sample.
String projectId = "my-project";
String instanceId = "my-instance";
String databaseId = "my-database";
batchDml(projectId, instanceId, databaseId);
}
// This example shows how to execute a batch of DML statements with the JDBC driver.
static void batchDml(String projectId, String instanceId, String databaseId) throws SQLException {
String connectionUrl =
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
projectId, instanceId, databaseId);
ImmutableList<Singer> singers = ImmutableList.of(
new Singer(10, "Marc", "Richards", BigDecimal.valueOf(10000)),
new Singer(11, "Amirah", "Finney", BigDecimal.valueOf(195944.10d)),
new Singer(12, "Reece", "Dunn", BigDecimal.valueOf(10449.90))
);
try (Connection connection = DriverManager.getConnection(connectionUrl)) {
connection.setAutoCommit(false);
// Use prepared statements for the lowest possible latency when executing the same SQL string
// multiple times.
try (PreparedStatement statement = connection.prepareStatement(
"INSERT INTO Singers (SingerId, FirstName, LastName, Revenues)\n"
+ "VALUES (?, ?, ?, ?)")) {
for (Singer singer : singers) {
statement.setLong(1, singer.singerId);
statement.setString(2, singer.firstName);
statement.setString(3, singer.lastName);
statement.setBigDecimal(4, singer.revenues);
// Add the current parameter values to the batch.
statement.addBatch();
}
// Execute the batched statements.
int[] updateCounts = statement.executeBatch();
connection.commit();
System.out.printf("Batch insert counts: %s%n", Arrays.toString(updateCounts));
}
}
}
}
Run a SQL query
The following code example returns all rows in the Singers table ordered by the
singer's last name:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SingleUseReadOnlyExample {
static void singleUseReadOnly() throws SQLException {
// TODO(developer): Replace these variables before running the sample.
String projectId = "my-project";
String instanceId = "my-instance";
String databaseId = "my-database";
singleUseReadOnly(projectId, instanceId, databaseId);
}
static void singleUseReadOnly(String projectId, String instanceId, String databaseId)
throws SQLException {
String connectionUrl =
String.format(
"jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
projectId, instanceId, databaseId);
try (Connection connection = DriverManager.getConnection(connectionUrl);
Statement statement = connection.createStatement()) {
// When the connection is in autocommit mode, any query that is executed will automatically
// be executed using a single-use read-only transaction, even if the connection itself is in
// read/write mode.
try (ResultSet rs =
statement.executeQuery(
"SELECT SingerId, FirstName, LastName, Revenues FROM Singers ORDER BY LastName")) {
while (rs.next()) {
System.out.printf(
"%d %s %s %s%n",
rs.getLong(1), rs.getString(2), rs.getString(3), rs.getBigDecimal(4));
}
}
}
}
}
