Use JDBC driver to access ADB from JVM-based applications

Anton Vasilev

JDBC (Java Database Connectivity) is the standard Java API that defines how a JVM-based application can connect to and interact with a database. To connect to ADB, you can use the PostgreSQL JDBC Driver.

To use the driver, download the corresponding JAR file or specify the dependency using your build tool.

  • Maven

  • Gradle (Kotlin)

  • Gradle (Groovy)

  • Download JAR

Add the dependency to the pom.xml file:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.10</version>
</dependency>

Add the dependency to the build.gradle.kts file:

dependencies {
    implementation("org.postgresql:postgresql:42.7.10")
}

Add the dependency to the build.gradle file:

dependencies {
    implementation 'org.postgresql:postgresql:42.7.10'
}

Download the driver JAR:

$ curl -O https://jdbc.postgresql.org/download/postgresql-42.7.10.jar

Then add it to the classpath:

$ export CLASSPATH=".:postgresql-42.7.10.jar"

  1. Write Java code that passes the connection parameters to the DriverManager.getConnection() method. For example, the following sample application collects statistics on the segments and their statuses on a host provided by a user:

    import java.sql.*;
import java.util.Scanner;

public class TestApp {

    public static void main(String[] args) {
        String url = "jdbc:postgresql://192.0.2.123:5432/adb?user=gpadmin";

        try (Scanner scanner = new Scanner(System.in)) {
            System.out.print("Enter a hostname: ");
            String hostname = scanner.nextLine();

            String sql = """
                SELECT
                    COUNT(*) AS total,
                    COUNT(CASE WHEN status = 'u' THEN 1 END) AS up,
                    COUNT(CASE WHEN status = 'd' THEN 1 END) AS down
                FROM gp_segment_configuration
                WHERE hostname = ?
            """;

            try (Connection conn = DriverManager.getConnection(url);
                 PreparedStatement pstmt = conn.prepareStatement(sql)) {

                pstmt.setString(1, hostname);

                try (ResultSet rs = pstmt.executeQuery()) {
                    if (rs.next()) {
                        int total = rs.getInt("total");
                        int up = rs.getInt("up");
                        int down = rs.getInt("down");

                        System.out.println("\nSegments on host " + hostname);
                        System.out.println("========================================");
                        System.out.printf("Total number of segments: %d%n", total);
                        System.out.printf("  ✅ Up:   %d%n", up);
                        System.out.printf("  ❌ Down: %d%n", down);
                        if (total > 0 && up == total) {
                            System.out.println("\n🎉 Good news! All segments on this host are up");
                        }
                    } else {
                        System.out.println("No data for host " + hostname);
                    }
                }

            } catch (SQLException e) {
                System.err.println("DB error: " + e.getMessage());
            }
        }
    }
}

    This code connects to the ADB master host as the default gpadmin user without a password and queries the gp_segment_configuration table.

    NOTE

    Make sure the master accepts connections from the host where you run the code. If necessary, add the IP address of your client host to the pg_hba.conf file as described in Remote connection.

  2. Compile and run the project. You will be prompted to enter a hostname from which you want to collect segment statistics.

  3. Enter a hostname. If the connection is established successfully and the hostname is correct, the application returns statistics on the segments on this host, similarly to the following:

Segments on host val-sdw2
========================================
Total number of segments: 8
  ✅ Up:   8
  ❌ Down: 0

🎉 Good news! All segments on this host are up

For more information on JDBC driver syntax, refer to the PostgreSQL JDBC Driver documentation.

Found a mistake? Seleсt text and press Ctrl+Enter to report it