Objectives
This tutorial walks you through the following steps using the Spanner JDBC driver:
- Create a Spanner instance and database.
- Write, read, and execute SQL queries on data in the database.
- Update the database schema.
- Update data using a read-write transaction.
- Add a secondary index to the database.
- Use the index to read and execute SQL queries on data.
- Retrieve data using a read-only transaction.
Costs
This tutorial uses Spanner, which is a billable component of the Google Cloud. For information on the cost of using Spanner, see Pricing.
Before you begin
Complete the steps described in Set up, which cover creating and setting a default Google Cloud project, enabling billing, enabling the Cloud Spanner API, and setting up OAuth 2.0 to get authentication credentials to use the Cloud Spanner API.
In particular, make sure that you run gcloud auth
application-default login
to set up your local development environment with authentication
credentials.
Prepare your local JDBC environment
Install the following on your development machine if they are not already installed:
Clone the sample app repository to your local machine:
git clone https://github.com/googleapis/java-spanner-jdbc.git
Change to the directory that contains the Spanner sample code:
cd java-spanner-jdbc/samples/snippets
Create an instance
When you first use Spanner, you must create an instance, which is an allocation of resources that are used by Spanner databases. When you create an instance, you choose an instance configuration, which determines where your data is stored, and also the number of nodes to use, which determines the amount of serving and storage resources in your instance.
Execute the following command to create a Spanner instance in the region
us-central1
with 1 node:
gcloud spanner instances create test-instance --config=regional-us-central1 \
--description="Test Instance" --nodes=1
Note that this creates an instance with the following characteristics:
- Instance ID
test-instance
- Display name
Test Instance
- Instance configuration
regional-us-central1
(Regional configurations store data in one region, while multi-region configurations distribute data across multiple regions. For more information, see About instances.) - Node count of 1 (
node_count
corresponds to the amount of serving and storage resources available to databases in the instance. Learn more in Nodes and processing units.)
You should see:
Creating instance...done.
Look through sample files
The samples repository contains a sample that shows how to use Spanner with JDBC.
Thepom.xml
adds the Spanner JDBC driver to the
project's dependencies and configures the assembly plugin to build an
executable JAR file with the Java class defined in this tutorial.
Build the sample from the
samples/snippets
directory:
mvn package -DskipTests
Create a database
Create a database called example-db
in the instance called test-instance
by
running the following on the command line.
GoogleSQL
java -jar target/jdbc-snippets/jdbc-samples.jar \
createdatabase test-instance example-db
PostgreSQL
java -jar target/jdbc-snippets/jdbc-samples.jar \
createpgdatabase test-instance example-db
You should see:
Created database [projects/my-project/instances/test-instance/databases/example-db]
The following code creates a database and two tables in the database.
GoogleSQL
PostgreSQL
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 interactions
with Spanner must go through a Connection
. The database name and other
properties are specified in the JDBC connection URL and the
java.util.Properties
set.
GoogleSQL
PostgreSQL
For a full list of supported properties, see Connection URL Properties.
Each Connection
uses resources, so it is good practice to either close
connections when they are no longer needed, or to use a connection pool to
re-use connections throughout your application.
Read more in the Connection
Javadoc
reference.
Connect the JDBC driver to the emulator
You can connect the JDBC driver to the Spanner emulator in two ways:
- Set the
SPANNER_EMULATOR_HOST
environment variable: This instructs the JDBC driver to connect to the emulator. The Spanner instance and database in the JDBC connection URL must already exist on the emulator. - Add
autoConfigEmulator=true
to the connection URL: This instructs the JDBC driver to connect to the emulator, and to automatically create the Spanner instance and database in the JDBC connection URL if these don't exist.
This example shows how to use the autoConfigEmulator=true
connection URL
option.
GoogleSQL
PostgreSQL
Write data with DML
You can insert data using Data Manipulation Language (DML) in a read-write transaction.
You use the PreparedStatement.executeUpdate()
method to execute a DML
statement.
GoogleSQL
PostgreSQL
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:
4 records inserted.
Write data with a DML batch
You use thePreparedStatement#addBatch()
and
PreparedStatement#executeBatch()
methods to execute multiple DML statements in
one batch.
GoogleSQL
PostgreSQL
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:
3 records inserted.
Write data with mutations
You can also insert data using mutations.
You can write data using
a Mutation
object.
A Mutation
object is a container for mutation operations. A Mutation
represents a sequence of inserts, updates, and deletes that Spanner
applies atomically to different rows and tables in a Spanner database.
The newInsertBuilder()
method in the Mutation
class constructs an INSERT
mutation, which inserts a
new row in a table. If the row already exists, the write fails. Alternatively,
you can use the newInsertOrUpdateBuilder
method to construct an INSERT_OR_UPDATE
mutation, which updates column values
if the row already exists.
write()
method in the CloudSpannerJdbcConnection
interface writes the mutations. All
mutations in a single batch are applied atomically.
You can unwrap the CloudSpannerJdbcConnection
interface from a Spanner
JDBC Connection
.
This code shows how to write the data using mutations:
GoogleSQL
PostgreSQL
Run the sample with this command:
GoogleSQL
java -jar target/jdbc-snippets/jdbc-samples.jar \
write test-instance example-db
PostgreSQL
java -jar target/jdbc-snippets/jdbc-samples.jar \
writepg test-instance example-db
You should see:
Inserted 10 rows.
Query data using SQL
Spanner supports a SQL interface for reading data, which you can access on the command line using the Google Cloud CLI or programmatically using the Spanner JDBC driver.
On the command line
Execute the following SQL statement to read the values of all columns from the
Albums
table:
GoogleSQL
gcloud spanner databases execute-sql example-db --instance=test-instance \
--sql='SELECT SingerId, AlbumId, AlbumTitle FROM Albums'
PostgreSQL
gcloud spanner databases execute-sql example-db --instance=test-instance \
--sql='SELECT singer_id, album_id, album_title FROM albums'
The result should be:
SingerId AlbumId AlbumTitle
1 1 Total Junk
1 2 Go, Go, Go
2 1 Green
2 2 Forever Hold Your Peace
2 3 Terrified
Use the Spanner JDBC driver
In addition to executing a SQL statement on the command line, you can issue the same SQL statement programmatically using the Spanner JDBC driver.
The following methods and classes are used to run the SQL query:- The
createStatement()
method in theConnection
interface: use this to create a new statement object for running a SQL statement. - The
executeQuery(String)
method of theStatement
class: use this method to execute a query against a database. - The
Statement
class: use this to execute a SQL string. - The
ResultSet
class: use this to access the data returned by a SQL statement.
Here's how to issue the query and access the data:
GoogleSQL
PostgreSQL
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:
1 1 Total Junk
1 2 Go, Go, Go
2 1 Green
2 2 Forever Hold Your Peace
2 3 Terrified
Query using a SQL parameter
If your application has a frequently executed query, you can improve its performance by parameterizing it. The resulting parametric query can be cached and reused, which reduces compilation costs. For more information, see Use query parameters to speed up frequently executed queries.
Here is an example of using a parameter in the WHERE
clause to
query records containing a specific value for LastName
.
Use a java.sql.PreparedStatement
to execute a query with a parameter.
GoogleSQL
PostgreSQL
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:
12 Melissa Garcia
Update the database schema
Assume you need to add a new column called MarketingBudget
to the Albums
table. Adding a new column to an existing table requires an update to your
database schema. Spanner supports schema updates to a database while the
database continues to serve traffic. Schema updates don't require taking the
database offline and they don't lock entire tables or columns; you can continue
writing data to the database during the schema update. Read more about supported
schema updates and schema change performance in
Make schema updates.
Add a column
You can add a column on the command line using the Google Cloud CLI or programmatically using the Spanner JDBC driver driver.
On the command line
Use the following ALTER TABLE
command to
add the new column to the table:
GoogleSQL
gcloud spanner databases ddl update example-db --instance=test-instance \
--ddl='ALTER TABLE Albums ADD COLUMN MarketingBudget INT64'
PostgreSQL
gcloud spanner databases ddl update example-db --instance=test-instance \
--ddl='ALTER TABLE albums ADD COLUMN marketing_budget BIGINT'
You should see:
Schema updating...done.
Use the Spanner JDBC driver
Use theexecute(String)
method of the java.sql.Statement
class to modify the schema:
GoogleSQL
PostgreSQL
Run the sample with this command:
GoogleSQL
java -jar target/jdbc-snippets/jdbc-samples.jar \
addmarketingbudget test-instance example-db
PostgreSQL
java -jar target/jdbc-snippets/jdbc-samples.jar \
addmarketingbudgetpg test-instance example-db
You should see:
Added MarketingBudget column.
Execute a DDL batch
It is recommended to execute multiple schema modifications in one batch. Use the
addBatch(String)
method of java.sql.Statement
to add multiple DDL statements to a batch.
GoogleSQL
PostgreSQL
Run the sample with this command:
GoogleSQL
java -jar target/jdbc-snippets/jdbc-samples.jar \
ddlbatch test-instance example-db
PostgreSQL
java -jar target/jdbc-snippets/jdbc-samples.jar \
ddlbatchpg test-instance example-db
You should see:
Added Venues and Concerts tables.
Write data to the new column
The following code writes data to the new column. It sets MarketingBudget
to
100000
for the row keyed by Albums(1, 1)
and to 500000
for the row keyed
by Albums(2, 2)
.
GoogleSQL
PostgreSQL
Run the sample with this command:
GoogleSQL
java -jar target/jdbc-snippets/jdbc-samples.jar \
update test-instance example-db
PostgreSQL
java -jar target/jdbc-snippets/jdbc-samples.jar \
updatepg test-instance example-db
You should see output similar to this:
Updated albums
You can also execute a SQL query or a read call to fetch the values that you just wrote.
Here's the code to execute the query:
GoogleSQL
PostgreSQL
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
You should see:
1 1 100000
1 2 null
2 1 null
2 2 500000
2 3 null
Update data
You can update data using DML in a read-write transaction.
Set AutoCommit=false
to execute read-write transactions in JDBC.
GoogleSQL
PostgreSQL
Run the sample with this command:
GoogleSQL
java -jar target/jdbc-snippets/jdbc-samples.jar \
writewithtransactionusingdml test-instance example-db
PostgreSQL
java -jar target/jdbc-snippets/jdbc-samples.jar \
writewithtransactionusingdmlpg test-instance example-db
Transaction tags and request tags
Use transaction tags and request tags
to troubleshoot transactions and queries in Spanner. You can set
transaction tags and request tags in the JDBC with the TRANSACTION_TAG
and STATEMENT_TAG
session variables.
GoogleSQL
PostgreSQL
Run the sample with this command:
GoogleSQL
java -jar target/jdbc-snippets/jdbc-samples.jar \
tags test-instance example-db
PostgreSQL
java -jar target/jdbc-snippets/jdbc-samples.jar \
tagspg test-instance example-db
Retrieve data using read-only transactions
Suppose you want to execute more than one read at the same timestamp. Read-only
transactions observe a consistent
prefix of the transaction commit history, so your application always gets
consistent data.
Set ReadOnly=true
and AutoCommit=false
on a java.sql.Connection
, or use
the SET TRANSACTION READ ONLY
SQL statement, to execute a read-only
transaction.
The following shows how to run a query and perform a read in the same read-only transaction:
GoogleSQL
PostgreSQL
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:
1 1 Total Junk
1 2 Go, Go, Go
2 1 Green
2 2 Forever Hold Your Peace
2 3 Terrified
2 2 Forever Hold Your Peace
1 2 Go, Go, Go
2 1 Green
2 3 Terrified
1 1 Total Junk
Partitioned queries and Data Boost
The partitionQuery
API divides a query into smaller pieces, or partitions, and uses multiple
machines to fetch the partitions in parallel. Each partition is identified by a
partition token. The PartitionQuery API has higher latency than the standard
query API, because it is only intended for bulk operations such as exporting or
scanning the whole database.
Data Boost lets you execute analytics queries and data exports with near-zero impact to existing workloads on the provisioned Spanner instance. Data Boost only supports partitioned queries.
GoogleSQL
PostgreSQL
Run the sample with this command:
GoogleSQL
java -jar target/jdbc-snippets/jdbc-samples.jar \
databoost test-instance example-db
PostgreSQL
java -jar target/jdbc-snippets/jdbc-samples.jar \
databoostpg test-instance example-db
For more information on running partitioned queries and using Data Boost with the JDBC driver, see:
- GoogleSQL: Data Boost and partitioned query statements
- PostgreSQL: Data Boost and partitioned query statements
Partitioned DML
Partitioned Data Manipulation Language (DML) is designed for the following types of bulk updates and deletes:
- Periodic cleanup and garbage collection.
- Backfilling new columns with default values.
PostgreSQL
Run the sample with this command:
GoogleSQL
java -jar target/jdbc-snippets/jdbc-samples.jar \
pdml test-instance example-db
PostgreSQL
java -jar target/jdbc-snippets/jdbc-samples.jar \
pdmlpg test-instance example-db
For more information on AUTOCOMMIT_DML_MODE
, see:
Cleanup
To avoid incurring additional charges to your Cloud Billing account for the resources used in this tutorial, drop the database and delete the instance that you created.
Delete the database
If you delete an instance, all databases within it are automatically deleted. This step shows how to delete a database without deleting an instance (you would still incur charges for the instance).
On the command line
gcloud spanner databases delete example-db --instance=test-instance
Using the Google Cloud console
Go to the Spanner Instances page in the Google Cloud console.
Click the instance.
Click the database that you want to delete.
In the Database details page, click Delete.
Confirm that you want to delete the database and click Delete.
Delete the instance
Deleting an instance automatically drops all databases created in that instance.
On the command line
gcloud spanner instances delete test-instance
Using the Google Cloud console
Go to the Spanner Instances page in the Google Cloud console.
Click your instance.
Click Delete.
Confirm that you want to delete the instance and click Delete.
What's next
- Learn how to Integrate Spanner with Spring Data JPA (GoogleSQL dialect).
- Learn how toIntegrate Spanner with Spring Data JPA (PostgreSQL dialect).
- Learn how to Integrate Spanner with Hibernate ORM (GoogleSQL dialect).
- Learn how toIntegrate Spanner with Hibernate ORM (PostgreSQL dialect).
- Learn more about JDBC session management commands (GoogleSQL).
- Learn more about JDBC session management commands (PostgreSQL).
Learn how to access Spanner with a virtual machine instance.
Learn about authorization and authentication credentials in Authenticate to Cloud services using client libraries.
Learn more about Spanner Schema design best practices.