Connect JDBC to a GoogleSQL-dialect database

Note: This page describes theSpanner JDBC driver,which is recommended and authored by Google.

This page shows you how to perform basic operations in Spanner using theSpanner JDBC driver.

Install the JDBC driver

Follow the steps inSpanner clientlibraries to set up authentication, and thenadd the Spanner JDBC driver dependencies, shown in the followingsnippet, to yourpom.xml file.

<dependencyManagement><dependencies><dependency><groupId>com.google.cloud</groupId><artifactId>libraries-bom</artifactId><version>26.73.0</version><type>pom</type><scope>import</scope></dependency></dependencies></dependencyManagement><dependencies><dependency><groupId>com.google.cloud</groupId><artifactId>google-cloud-spanner-jdbc</artifactId><exclusions><exclusion><groupId>com.google.api.grpc</groupId><artifactId>proto-google-cloud-spanner-executor-v1</artifactId></exclusion></exclusions></dependency>
In case you are using a framework that requires the Java class name in order toload the JDBC driver, it iscom.google.cloud.spanner.jdbc.JdbcDriver. See theAPI documentation for JdbcDriverfor how to set up a connection.

Connect to a Spanner database

TheJdbcDriverclass description shows the connection string syntax and includes sample code tocreate a connection and run a query.

The driver automatically detects the SQL dialect (GoogleSQL orPostgreSQL) of the specified database. A dialect parameter is notrequired or allowed.

Connect to the emulator

To connect to theemulator, set theSPANNER_EMULATOR_HOST environment variable, for example:

Linux / macOS

exportSPANNER_EMULATOR_HOST=localhost:9010

Windows

setSPANNER_EMULATOR_HOST=localhost:9010

This instructs the Spanner JDBC driver driver to connect to theemulator running on thelocalhost instead of the default production service.

Note: Although less common, you can specify the host in the connection string,for examplejdbc: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 whetherSPANNER_EMULATOR_HOST is set. Also, you must specifyusePlainText=true inthe connection string because the emulator does not support SSL/TLS.

Examples

The following code examples cover some common use cases.

Run a schema update

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

importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.SQLException;importjava.sql.Statement;classCreateTableExample{staticvoidcreateTable()throwsSQLException{// TODO(developer): Replace these variables before running the sample.StringprojectId="my-project";StringinstanceId="my-instance";StringdatabaseId="my-database";createTable(projectId,instanceId,databaseId);}staticvoidcreateTable(StringprojectId,StringinstanceId,StringdatabaseId)throwsSQLException{StringconnectionUrl=String.format("jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",projectId,instanceId,databaseId);try(Connectionconnection=DriverManager.getConnection(connectionUrl)){try(Statementstatement=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 atransaction in autocommit mode, which is the default behavior. The followingcode example uses a transaction in autocommit mode to add rows to theSingerstable:

importjava.math.BigDecimal;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.SQLException;importjava.sql.Types;importjava.util.Arrays;importjava.util.List;classInsertDataExample{// Class to contain singer sample data.staticclassSinger{finallongsingerId;finalStringfirstName;finalStringlastName;finalBigDecimalrevenues;Singer(longsingerId,StringfirstName,StringlastName,BigDecimalrevenues){this.singerId=singerId;this.firstName=firstName;this.lastName=lastName;this.revenues=revenues;}}staticfinalList<Singer>SINGERS=Arrays.asList(newSinger(10,"Marc","Richards",newBigDecimal("104100.00")),newSinger(20,"Catalina","Smith",newBigDecimal("9880.99")),newSinger(30,"Alice","Trentor",newBigDecimal("300183")),newSinger(40,"Lea","Martin",newBigDecimal("20118.12")),newSinger(50,"David","Lomond",newBigDecimal("311399.26")));staticvoidinsertData()throwsSQLException{// TODO(developer): Replace these variables before running the sample.StringprojectId="my-project";StringinstanceId="my-instance";StringdatabaseId="my-database";insertData(projectId,instanceId,databaseId);}staticvoidinsertData(StringprojectId,StringinstanceId,StringdatabaseId)throwsSQLException{StringconnectionUrl=String.format("jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",projectId,instanceId,databaseId);try(Connectionconnection=DriverManager.getConnection(connectionUrl)){try(PreparedStatementps=connection.prepareStatement("INSERT INTO Singers\n"+"(SingerId, FirstName, LastName, SingerInfo, Revenues)\n"+"VALUES\n"+"(?, ?, ?, ?, ?)")){for(Singersinger: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 multipleoperations together as a group, you can disable autocommit mode. The followingcode example usesconnection.setAutoCommit(false) andconnection.commit() toadd rows to theSingers table.

importcom.google.common.collect.ImmutableList;importjava.math.BigDecimal;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.SQLException;importjava.util.Arrays;classBatchDmlExample{staticclassSinger{finallongsingerId;finalStringfirstName;finalStringlastName;finalBigDecimalrevenues;Singer(longsingerId,StringfirstName,StringlastName,BigDecimalrevenues){this.singerId=singerId;this.firstName=firstName;this.lastName=lastName;this.revenues=revenues;}}staticvoidbatchDml()throwsSQLException{// TODO(developer): Replace these variables before running the sample.StringprojectId="my-project";StringinstanceId="my-instance";StringdatabaseId="my-database";batchDml(projectId,instanceId,databaseId);}// This example shows how to execute a batch of DML statements with the JDBC driver.staticvoidbatchDml(StringprojectId,StringinstanceId,StringdatabaseId)throwsSQLException{StringconnectionUrl=String.format("jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",projectId,instanceId,databaseId);ImmutableList<Singer>singers=ImmutableList.of(newSinger(10,"Marc","Richards",BigDecimal.valueOf(10000)),newSinger(11,"Amirah","Finney",BigDecimal.valueOf(195944.10d)),newSinger(12,"Reece","Dunn",BigDecimal.valueOf(10449.90)));try(Connectionconnection=DriverManager.getConnection(connectionUrl)){connection.setAutoCommit(false);// Use prepared statements for the lowest possible latency when executing the same SQL string// multiple times.try(PreparedStatementstatement=connection.prepareStatement("INSERT INTO Singers (SingerId, FirstName, LastName, Revenues)\n"+"VALUES (?, ?, ?, ?)")){for(Singersinger: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 theSingers table ordered bythe singer's last name:

importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;publicclassSingleUseReadOnlyExample{staticvoidsingleUseReadOnly()throwsSQLException{// TODO(developer): Replace these variables before running the sample.StringprojectId="my-project";StringinstanceId="my-instance";StringdatabaseId="my-database";singleUseReadOnly(projectId,instanceId,databaseId);}staticvoidsingleUseReadOnly(StringprojectId,StringinstanceId,StringdatabaseId)throwsSQLException{StringconnectionUrl=String.format("jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",projectId,instanceId,databaseId);try(Connectionconnection=DriverManager.getConnection(connectionUrl);Statementstatement=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(ResultSetrs=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));}}}}}

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 2025-12-17 UTC.