Using JDBC to Retrieve Cursor Data in GBase 8c

GBase 8c is a multi-modal, distributed database that supports various storage modes such as row storage, column storage, and memory storage, as well as multiple deployment forms including standalone, master-slave, and distributed. GBase 8c boasts high performance, high availability, elastic scalability, and high security, and can be deployed on physical machines, virtual machines, containers, private clouds, and public clouds. It provides secure, stable, and reliable data storage and management services for key industry core systems, internet business systems, and government and enterprise business systems.

How can you retrieve cursor data from a GBase 8c database using JDBC? This process is explained through the following example (Note: Steps 1 and 2 below are executed on the remote client of GBase 8c V5 3.0.0 version, while steps 3 and 4 are executed in the Java environment).

Prerequisites: Correctly install GBase 8c and configure remote connections; the JDBC driver can be downloaded from the official website or obtained by consulting GBase technical staff. Official website: GBase Download

Steps:

1. Create a Custom Function

First, create a custom function to achieve a specific effect, for example, to get the username where the cursor is located:

CREATE OR REPLACE FUNCTION get_users()
RETURNS refcursor AS $$
DECLARE
   result_cursor refcursor;
BEGIN
   OPEN result_cursor FOR
       SELECT * FROM users;
   RETURN result_cursor;
END;
$$ LANGUAGE plpgsql;

2. Create a Test Table

create table users(id int, name varchar(10));
insert into users values(1, 'John Doe'), (2, 'Jane Smith'), (3, 'Alex Brown');

3. Call the Cursor Data in Java

public class jdbccallproc {
    public static void main(String[] args) {
        String jdbcURL = "jdbc:gbase8c://172.16.5.102:5432/postgres?loggerLevel=warning";
        String username = "regress";
        String password = "gbase;234";

        try {
            // 1. Register GBase8c JDBC Driver
            Class.forName("com.gbase8c.Driver");

            // 2. Establish Connection
            Connection connection = DriverManager.getConnection(jdbcURL, username, password);

            // 3. Call Stored Procedure
            String call = "{ ? = call get_users() }";
            connection.setAutoCommit(false);
            CallableStatement stmt = connection.prepareCall(call);

            // 4. Register Output Parameter Type
            //stmt.registerOutParameter(1, OracleTypes.CURSOR);
            //stmt.registerOutParameter(1, 1111);
            stmt.registerOutParameter(1, Types.REF_CURSOR);

            // 5. Execute Query
            stmt.execute();

            // 6. Retrieve Result Set
            ResultSet rs = (ResultSet) stmt.getObject(1);

            // 7. Process Result Set
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                // Process other fields as needed

                System.out.println("ID: " + id + ", Name: " + name);
            }

            // 8. Close Connection
            rs.close();
            stmt.close();
            connection.close();

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}

Note:

• setAutoCommit(false) is required to prevent exceptions.
• The registerOutParameter type supports Oracle's OracleTypes.CURSOR, 1111, and Types.REF_CURSOR. The example comments out the first two types; feel free to experiment with them.

4. Expected Output

ID: 1, Name: John Doe
ID: 2, Name: Jane Smith
ID: 3, Name: Alex Brown

This example serves as an introduction, and we welcome technical exchanges~