Stay organized with collections
Save and categorize content based on your preferences.
This page shows you how to perform basic operations in Cloud 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 Cloud 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.