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 want to 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