How to Connect to Greenplum Database via JDBC

Products

• VMware Tanzu Greenplum
• Pivotal Data Suite Non Production Edition
• Greenplum

Issue/Introduction

1. Download and Set Up the JDBC Driver

• Download the official Greenplum JDBC driver from the official Greenplum website.
• Export the CLASSPATH environment variable to include the path to the JDBC driver JAR file. For example:

export CLASSPATH=".:/root/jdbc/PROGRESS_DATADIRECT_JDBC_DRIVER_PIVOTAL_GREENPLUM_6.0.0+109.jar"

2. Connection Parameters

To connect to the Greenplum database, you need to configure the following parameters:

• URL: The JDBC URL, e.g: String url = "jdbc:pivotal:greenplum://support14:5436;DatabaseName=gpadmin";
• User: The database username, e.g: gpadmin
• Password: The password for the database user.

3. Sample Java Code:

Below is an example of Java code to connect to the Greenplum database and truncate sales table:

import java.sql.*;

public class CheckHost {
    public static void main(String[] args) {
        String url = "jdbc:pivotal:greenplum://support14:5436;DatabaseName=gpadmin";
        String user = "xxxx";
        String password = "xxxx";

        try {
            // Load the Greenplum JDBC driver
            Class.forName("com.pivotal.jdbc.GreenplumDriver");

            // Establish the connection
            try (Connection conn = DriverManager.getConnection(url, user, password)) {
                System.out.println("Connected successfully to the database.");

                // Create a statement
                try (Statement stmt = conn.createStatement()) {
                    // Verify initial row count
                    String initialCountSQL = "SELECT COUNT(*) FROM sales";
                    try (ResultSet rs = stmt.executeQuery(initialCountSQL)) {
                        if (rs.next()) {
                            int count = rs.getInt(1);
                            System.out.println("Initial number of rows in 'sales' table: " + count);
                        }
                    }

                    // Sleep for 10 seconds using pg_sleep()
                    System.out.println("Sleeping for 10 seconds on the database...");
                    stmt.execute("SELECT pg_sleep(10)");
                    System.out.println("Sleep completed.");

                    // Truncate the table
                    String sql = "TRUNCATE TABLE sales";
                    stmt.executeUpdate(sql);
                    System.out.println("Table 'sales' has been truncated successfully.");
                }
            }
        } catch (ClassNotFoundException e) {
            System.out.println("Greenplum JDBC driver not found.");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("Database error occurred.");
            e.printStackTrace();
        }
    }
}

Resolution

4. Compile and Run

To compile the Java code:

javac -g CheckHost.java

To run the compiled program:

[root@support14 jdbc]# java checkhost
Connected successfully to the database.
Initial number of rows in 'sales' table: 0
Sleeping for 10 seconds on the database...
Sleep completed.
Table 'sales' has been truncated successfully.
Number of rows in 'sales' table after truncation: 0

5. Debugging

To debug the program, we can run the jdb command:

[root@support14 jdbc]# jdb checkhost
Initializing jdb ...
> stop at checkhost:39     <-------------------- Set break point
Deferring breakpoint checkhost:39.
It will be set after the class is loaded.
> run            <----------------------- Run the program
run checkhost
Set uncaught java.lang.Throwable
Set deferred uncaught java.lang.Throwable
>
VM Started: Set deferred breakpoint checkhost:39
Connected successfully to the database.
Initial number of rows in 'sales' table: 0
Sleeping for 10 seconds on the database...
Sleep completed.

Breakpoint hit: "thread=main", checkhost.main(), line=39 bci=451
39                    try (PreparedStatement pstmt = conn.prepareStatement(truncateSQL)) {

main[1] cont   <--------------------------- Continue
> Table 'sales' has been truncated successfully.
Number of rows in 'sales' table after truncation: 0

The application exited