Connect JDBC to a PostgreSQL-dialect database

You can use both the PostgreSQL JDBC driver and the Spanner JDBC driver with a Cloud Spanner PostgreSQL-dialect database. This page explains how to connect to your database with either of these drivers.

PostgreSQL JDBC driver

This section explains how to connect the PostgreSQL JDBC driver to a PostgreSQL-dialect database in Cloud Spanner. JDBC is the standard Java driver for PostgreSQL.

Using the PostgreSQL JDBC driver requires using PGAdapter to translate between the PostgreSQL network protocol and the Cloud Spanner network protocol. You can add PGAdapter as a dependency and run it in-process with your application.

1. Add PGAdapter and the PostgreSQL JDBC driver as dependencies to your application. 

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.6.0</version>
</dependency>
<dependency>
  <groupId>com.google.cloud</groupId>
  <artifactId>google-cloud-spanner-pgadapter</artifactId><!-- {x-version-update:google-cloud-spanner-pgadapter:current} -->
  <version>0.23.1</version>
</dependency>

2. Start PGAdapter in-process with your application. 

OptionsMetadata.Builder builder =
    OptionsMetadata.newBuilder()
        .setProject("my-project")
        .setInstance("my-instance")
        // Set the port to 0 to use a dynamically assigned port.
        .setPort(5432);
ProxyServer server = new ProxyServer(builder.build());
server.startServer();
server.awaitRunning();

3. Specify localhost and 5432 as the database server host and port in the JDBC connection string. 

// Make sure the PG JDBC driver is loaded.
Class.forName("org.postgresql.Driver");

// Replace localhost and 5432 with the host and port number where PGAdapter is running.
try (Connection connection =
    DriverManager.getConnection("jdbc:postgresql://localhost:5432/my-database")) {
  try (ResultSet resultSet =
      connection.createStatement().executeQuery("select 'Hello world!' as hello")) {
    while (resultSet.next()) {
      System.out.printf(
        "Greeting from Cloud Spanner PostgreSQL: %s\n", resultSet.getString(1));
    }
  }
}

The PGAdapter GitHub repository contains a working sample application.

Spanner JDBC driver

This section explains how to use the Spanner JDBC driver to connect to a PostgreSQL-dialect database database.

1. Add the Spanner JDBC driver as a dependency to your application.

samples/snippets/pom.xml 
<dependencyManagement>
  <dependencies>
    <dependency>
      <groupId>com.google.cloud</groupId>
      <artifactId>libraries-bom</artifactId>
      <version>26.27.0</version>
      <type>pom</type>
      <scope>import</scope>
    </dependency>
  </dependencies>
</dependencyManagement>

<dependencies>
  <dependency>
    <groupId>com.google.cloud</groupId>
    <artifactId>google-cloud-spanner-jdbc</artifactId>
  </dependency>
</dependencies>

2. Use a Cloud Spanner JDBC connection URL to connect to the PostgreSQL-dialect database. 

try (Connection connection = DriverManager.getConnection(
    "jdbc:cloudspanner:/projects/my-project/instances/my-instance/databases/my-database")) {
  try (ResultSet resultSet =
      connection.createStatement().executeQuery("select 'Hello world!' as hello")) {
    while (resultSet.next()) {
      System.out.printf(
        "Greeting from Cloud Spanner PostgreSQL: %s\n", resultSet.getString(1));
    }
  }
}

The driver automatically detects the SQL dialect of the specified database. A dialect parameter in the connection URL is not required.

What's next