Connect to AnalyticDB using JDBC - AnalyticDB for PostgreSQL

AnalyticDB for PostgreSQL supports the PostgreSQL and Greenplum Java Database Connectivity (JDBC) drivers. This guide shows you how to connect to an AnalyticDB for PostgreSQL instance using JDBC.

Prerequisites

Before you begin, ensure that you have:

The internal or public endpoint of an AnalyticDB for PostgreSQL instance
- Internal endpoint: use when your client runs on an Elastic Compute Service (ECS) instance in the same region and the same network type as the AnalyticDB for PostgreSQL instance. Find the endpoint in the Database Connection Information section of the Basic Information page in the AnalyticDB for PostgreSQL console.
- Public endpoint: use when your client is in a different region, uses a different network type, or runs outside Alibaba Cloud. Apply for a public endpoint first. For details, see Manage public endpoints.
The client IP address added to an IP address whitelist of the instance. For details, see Configure an IP address whitelist.

Connect to an instance using JDBC

Step 1: Add the JDBC driver

Download the PostgreSQL JDBC driver from the PostgreSQL official website and add the driver to your environment variable.

AnalyticDB for PostgreSQL V7.0 requires JDBC V42.2.0 or later.

Step 2: Connect and run a query

The following example connects to an AnalyticDB for PostgreSQL instance, runs a query against gp_segment_configuration, and prints the results. Credentials are read from environment variables to avoid hardcoding sensitive values.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class GpConn {
    public static void main(String[] args) {
        // Read credentials from environment variables
        String host     = System.getenv("ADB_PG_HOST");      // e.g. mygpdbpub.gpdb.rds.aliyuncs.com
        String port     = System.getenv("ADB_PG_PORT");      // e.g. 5432
        String database = System.getenv("ADB_PG_DATABASE");  // e.g. postgres
        String username = System.getenv("ADB_PG_USERNAME");
        String password = System.getenv("ADB_PG_PASSWORD");

        String url = String.format("jdbc:postgresql://%s:%s/%s", host, port, database);

        try {
            Class.forName("org.postgresql.Driver");
            Connection conn = DriverManager.getConnection(url, username, password);
            Statement  stmt = conn.createStatement();
            ResultSet  rs   = stmt.executeQuery("SELECT * FROM gp_segment_configuration;");

            while (rs.next()) {
                for (int i = 1; i <= 11; i++) {
                    System.out.print(rs.getString(i));
                    if (i < 11) System.out.print("    |    ");
                }
                System.out.println();
            }
            rs.close();
            stmt.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Set the following environment variables before running the program:

Variable	Description	Example
`ADB_PG_HOST`	Endpoint of the instance	`mygpdbpub.gpdb.rds.aliyuncs.com`
`ADB_PG_PORT`	Port the instance listens on	`5432`
`ADB_PG_DATABASE`	Database name	`postgres`
`ADB_PG_USERNAME`	Database account name	`myusername`
`ADB_PG_PASSWORD`	Database account password	—

The JDBC URL format is:

jdbc:postgresql://<host>:<port>/<database>

For the full list of supported connection parameters, see the PostgreSQL JDBC driver documentation.

What's next

Greenplum also provides a tools package that includes JDBC, ODBC, and libpq drivers. For details, see the Greenplum documentation.