Using pgJDBC to interact with Amazon Aurora DSQL

This section describes how to use pgJDBC to interact with Aurora DSQL.

Before you begin, make sure that you have completed the following prerequisites.

Connect to an Aurora DSQL cluster and run queries

package org.example;

import software.amazon.awssdk.auth.credentials.DefaultCredentialsProvider;
import software.amazon.awssdk.services.dsql.DsqlUtilities;
import software.amazon.awssdk.regions.Region;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.Duration;
import java.util.Properties;
import java.util.UUID;

public class Example {

    // Get a connection to Aurora DSQL.
    public static Connection getConnection(String clusterEndpoint, String region) throws SQLException {
        Properties props = new Properties();

        // Use the DefaultJavaSSLFactory so that Java's default trust store can be used
        // to verify the server's root cert.
        String url = "jdbc:postgresql://" + clusterEndpoint + ":5432/postgres?sslmode=verify-full&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory";

        DsqlUtilities utilities = DsqlUtilities.builder()
                .region(Region.of(region))
                .credentialsProvider(DefaultCredentialsProvider.create())
                .build();

        String password = utilities.generateDbConnectAdminAuthToken(builder -> builder.hostname(clusterEndpoint)
                .region(Region.of(region)));

        props.setProperty("user", "admin");
        props.setProperty("password", password);
        return DriverManager.getConnection(url, props);
    }

    public static void main(String[] args) {
        // Replace the cluster endpoint with your own
        String clusterEndpoint = "foo0bar1baz2quux3quuux4.dsql.us-east-1.on.aws";
        String region = "us-east-1";
        try (Connection conn = Example.getConnection(clusterEndpoint, region)) {

            // Create a new table named owner
            Statement create = conn.createStatement();
            create.executeUpdate("CREATE TABLE IF NOT EXISTS owner (id UUID PRIMARY KEY, name VARCHAR(255), city VARCHAR(255), telephone VARCHAR(255))");
            create.close();

            // Insert some data
            UUID uuid = UUID.randomUUID();
            String insertSql = String.format("INSERT INTO owner (id, name, city, telephone) VALUES ('%s', 'John Doe', 'Anytown', '555-555-1999')", uuid);
            Statement insert = conn.createStatement();
            insert.executeUpdate(insertSql);
            insert.close();

            // Read back the data and assert they are present
            String selectSQL = "SELECT * FROM owner";
            Statement read = conn.createStatement();
            ResultSet rs = read.executeQuery(selectSQL);
            while (rs.next()) {
                assert rs.getString("id") != null;
                assert rs.getString("name").equals("John Doe");
                assert rs.getString("city").equals("Anytown");
                assert rs.getString("telephone").equals("555-555-1999");
            }

            // Delete some data
            String deleteSql = String.format("DELETE FROM owner where name='John Doe'");
            Statement delete = conn.createStatement();
            delete.executeUpdate(deleteSql);
            delete.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}