Connect JDBC to a PostgreSQL-dialect database

You can use both the PostgreSQL JDBC driver and the Spanner JDBC driver with a 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 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 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>0.42.3</version>
</dependency>
<dependency>
  <groupId>com.google.cloud</groupId>
  <artifactId>google-cloud-spanner-pgadapter</artifactId>
  <version>0.42.3</version>
</dependency>
  2. Start the PGAdapter in-process with your application. 

              OptionsMetadata.Builder builder =
            OptionsMetadata.newBuilder()
              .setProject("my-project")
              .setInstance("my-instance")
              .setPort(5432);
          ProxyServer server = new ProxyServer(builder.build());
          server.startServer();
          server.awaitRunning();

    Replace the following:

    • my-project: The project ID of the Google Cloud project where your PostgreSQL-dialect database is located.
    • my-instance: The ID of the Spanner instance where your PostgreSQL-dialect database is located.
    • 5432: The port number where PGAdapter is running. Set the port to `0` to use a dynamically assigned port.
  3. Make sure the PostgreSQL JDBC driver driver is loaded. 

            Class.forName("org.postgresql.Driver");

        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(
                "Greetings from Cloud Spanner PostgreSQL: %s\n", resultSet.getString(1));
            }
          }
        }

    Replace the following:

    • localhost: The host where PGAdapter is running.
    • 5432: The port number where PGAdapter is running.
    • my-database: The name of the PostgreSQL-dialect database.

    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. 

    <dependencyManagement>
  <dependencies>
    <dependency>
      <groupId>com.google.cloud</groupId>
      <artifactId>libraries-bom</artifactId>
      <version>26.52.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>
  2. Use a 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(
              "Greetings from Cloud Spanner PostgreSQL: %s\n", resultSet.getString(1));
          }
        }
      }

    Replace the following:

    • my-project: The project ID of the Google Cloud project where your PostgreSQL-dialect database is located.
    • my-instance: The ID of the Spanner instance where your PostgreSQL-dialect database is located.
    • my-database: The name of the PostgreSQL-dialect database.

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

What's next