The Java Database Connectivity (JDBC) API

Java Database Connectivity (JDBC) is an API which defines how a client may access a database. JDBC is like a bridge between a Java application and a database.¹

First, add the JDBC driver for PostgreSQL to your project dependencies:

implementation 'org.postgresql:postgresql:42.2.10'

Open the Gradle tool window and refresh the Gradle project.

Let's write a sample code to demonstrate the process of connecting to Heroku Postgres from within your Java program using JDBC:

import java.net.URI;
import java.net.URISyntaxException;
import java.sql.*;

public class Demo {
  public static void main(String[] args) throws SQLException {
    try (Connection conn = getConnection()) {

      // do something with the connection

    } catch (URISyntaxException | SQLException e) {
      e.printStackTrace();
    }
  }

  private static Connection getConnection() throws URISyntaxException, SQLException {
    String databaseUrl = System.getenv("DATABASE_URL");
    if (databaseUrl == null) {
      throw new URISyntaxException(databaseUrl, "DATABASE_URL is not set");
    }

    URI dbUri = new URI(databaseUrl);

    String username = dbUri.getUserInfo().split(":")[0];
    String password = dbUri.getUserInfo().split(":")[1];
    String dbUrl = "jdbc:postgresql://" + dbUri.getHost() + ':'
        + dbUri.getPort() + dbUri.getPath() + "?sslmode=require";

    return DriverManager.getConnection(dbUrl, username, password);
  }
}

Note the use of getConnection() method; the DATABASE_URL for the Heroku Postgres follows the convention below.

postgres://<username>:<password>@<host>:<port>/<dbname>

However, the Postgres JDBC driver uses the following convention:

jdbc:postgresql://<host>:<port>/<dbname>?user=<username>&password=<password>

The code in getConnection() converts the Heroku DATABASE_URL into a JDBC URI.

Aside-1: The DriverManager.getConnection method is a factory method (implements the Factory Design Pattern). This point was elaborated on in the previous module.

Aside-2: Notice the following try-with-resources statement:

try (Connection conn = getConnection()) {

  // do something with the connection

} catch (URISyntaxException | SQLException e) {
  e.printStackTrace();
}

This construct ensures that the resource is closed at the end of the statement. A resource is an object (such as Connection) that must be closed after the program is finished with it.