Using the Array Type

This section describes how to use the array type of a JDBC driver.

Prerequisites for code running:

1. Add the gaussdbjdbc.jar package as required. For example, if you use an IDE to run code, you need to add the gaussdbjdbc.jar package to the local IDE.
2. An A-compatible database is connected, and the database version must be 503.0 or later.

Example 1: The JDBC driver uses the table of type in the kernel.

// Writing the username and password used for authentication directly into code may cause security risks. It is recommended that you store them in the configuration file or environment variables (the password must be stored in ciphertext and decrypted when being used) to ensure security.
// In this example, the username and password are stored in environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV in the local environment (variable names are subject to the actual situation).
// Change the values of $ip, $port, and database.

import com.huawei.gaussdb.jdbc.jdbc.ArrayDescriptor;
import com.huawei.gaussdb.jdbc.jdbc.GaussArray;

import java.sql.*;

public class ArrayTest1 {
    // Establish a database connection in non-encrypted mode.
    public static Connection getConnection() {
        String username = System.getenv("EXAMPLE_USERNAME_ENV");
        String passwd = System.getenv("EXAMPLE_PASSWORD_ENV");
        String driver = "com.huawei.gaussdb.jdbc.Driver";
        String sourceURL = "jdbc:gaussdb://$ip:$port/database?enableGaussArrayAndStruct=true";
        Connection conn = null;
        try {
            // Load the database driver.
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }

        try {
            // Create a database connection.
            conn = DriverManager.getConnection(sourceURL, username, passwd);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }

        return conn;
    }

    /**
     * Create a frontend database object.
     *
     * @param conn conn
     * @throws SQLException An exception occurred while executing the statement
     */
    public static void prepareTestObject(Connection conn) throws SQLException {
        Statement stmt = conn.createStatement();
        stmt.execute("create type test_array is table of int");
        stmt.execute("create or replace function test_func return test_array is\n" +
                "begin\n" +
                "     return test_array(123, 456, 789);\n" +
                "end;");

        stmt.execute("create or replace procedure test_proc(v1 in test_array, v2 out test_array) is\n" +
                "begin\n" +
                "     v2 := v1;\n" +
                "     v2(3) := 456;\n" +
                "end;");
    }

    /**
     * Clear the database object.
     *
     * @param conn conn
     * @throws SQLException if an exception occurred while executing the statement
     */
    public static void cleanTestObject(Connection conn) throws SQLException {
        Statement stmt = conn.createStatement();
        stmt.execute("drop procedure test_proc");
        stmt.execute("drop function test_func");
        stmt.execute("drop type test_array");
    }

    /**
     * Use array object APIs.
     *
     * @param array instance of Array
     * @throws SQLException if used array failed
     */
    public static void testIntArray(Array array) throws SQLException {
        // Traverse and print an element array.
        Integer[] elements = (Integer[]) array.getArray();
        for (Integer element : elements) {
            System.out.println(element);
        }

        // Traverse and print some elements.
        Integer[] someElements = (Integer[]) array.getArray(2, 2);
        for (Integer element : someElements) {
            System.out.println(element);
        }

        // Print the element type name.
        System.out.println(array.getBaseTypeName());

        // Print whether sqlType of an element is Types.INTEGER.
        System.out.println(array.getBaseType() == Types.INTEGER);
    }

    /**
     * Construct an array object and use related APIs.
     *
     * @param conn conn
     * @throws SQLException if create array failed
     */
    public static void testConstructArray(Connection conn) throws SQLException {
        System.out.println("===========  testConstructArray  ===========");
        String typeName = "test_array";
        ArrayDescriptor typeDesc = ArrayDescriptor.getDescriptor(typeName, conn);
        GaussArray array = new GaussArray(typeDesc, new Object[]{1, 2, 3});
        testIntArray(array);
    }

    /**
     * Obtain the array object returned by a function.
     *
     * @param conn conn
     * @throws SQLException if an exception occurred
     */
    public static void testReturnArrayParam(Connection conn) throws SQLException {
        System.out.println("===========  testReturnArrayParam  ===========");

       // Run a stored procedure, obtain the array object returned by a function, and call array APIs.
        PreparedStatement stmt = conn.prepareStatement("select test_func()");
        ResultSet rs = stmt.executeQuery();
        rs.next();
        Array array = rs.getArray(1);
        testIntArray(array);
    }

    /**
     * Construct an array object and input and output parameters.
     *
     * @param conn conn
     * @throws SQLException if an exception occurred
     */
    public static void testInputOutputArrayParam(Connection conn) throws SQLException {
        System.out.println("===========  testInputOutputArrayParam  ===========");
        String typeName = "test_array";
        ArrayDescriptor typeDesc = ArrayDescriptor.getDescriptor(typeName, conn);
        GaussArray inArray = new GaussArray(typeDesc, new Object[]{1, 2, 3});

        // To output parameters, enable the proc_outparam_override parameter for the database.
        Statement stmt = conn.createStatement();
        stmt.execute("set behavior_compat_options = 'proc_outparam_override'");

        // Run a stored procedure.
        CallableStatement cstmt = conn.prepareCall("{call test_proc(?, ?)}");
        cstmt.setArray(1, inArray);
        cstmt.registerOutParameter(2, Types.ARRAY, typeName);
        cstmt.execute();

        // Obtain output parameters and call array APIs.
        Array outArray = cstmt.getArray(2);
        testIntArray(outArray);
    }

    /**
    * Main program. Call static methods one by one.
     *
     * @param args args
     */
    public static void main(String[] args) throws SQLException {
       // Create a database connection.
        Connection conn = getConnection();

        // Create a frontend test object.
        prepareTestObject(conn);

        // Construct an array object.
        testConstructArray(conn);

        // Return the array.
        testReturnArrayParam(conn);

        // Input and output array parameters.
        testInputOutputArrayParam(conn);

        // Delete the test object.
        cleanTestObject(conn);
    }
}

The execution result of the preceding example is as follows:

===========  testConstructArray  ===========
1
2
3
2
3
int4
true
===========  testReturnArrayParam  ===========
123
456
789
456
789
int4
true
===========  testInputOutputArrayParam  ===========
1
2
456
2
456
int4
true

Example 2: Array object APIs are used.

// Writing the username and password used for authentication directly into code may cause security risks. It is recommended that you store them in the configuration file or environment variables (the password must be stored in ciphertext and decrypted when being used) to ensure security.
// In this example, the username and password are stored in environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV in the local environment (variable names are subject to the actual situation).
// Change the values of $ip, $port, and database.

import com.huawei.gaussdb.jdbc.jdbc.ArrayDescriptor;
import com.huawei.gaussdb.jdbc.jdbc.GaussArray;

import java.sql.*;

public class ArrayTest2 {
    // Establish a database connection in non-encrypted mode.
    public static Connection getConnection() {
        String username = System.getenv("EXAMPLE_USERNAME_ENV");
        String passwd = System.getenv("EXAMPLE_PASSWORD_ENV");
        String driver = "com.huawei.gaussdb.jdbc.Driver";
        String sourceURL = "jdbc:gaussdb://$ip:$port/database?enableGaussArrayAndStruct=true";
        Connection conn = null;
        try {
            // Load the database driver.
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }

        try {
            // Create a database connection.
            conn = DriverManager.getConnection(sourceURL, username, passwd);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }

        return conn;
    }

    /**
     * Create a frontend database object.
     *
     * @param conn conn
     * @throws SQLException An exception occurred while executing the statement
     */
    public static void prepareTestObject(Connection conn) throws SQLException {
        Statement stmt = conn.createStatement();
        stmt.execute("create type test_array is table of int");
    }

    /**
     * Clear the database object.
     *
     * @param conn conn
     * @throws SQLException if an exception occurred while executing the statement
     */
    public static void cleanTestObject(Connection conn) throws SQLException {
        Statement stmt = conn.createStatement();
        stmt.execute("drop type test_array");
    }

    /**
     * Main program and main process
     *
     * @param args args
     */
    public static void main(String[] args) throws SQLException {
       // Create a database connection.
        Connection conn = getConnection();

        // Create a frontend test object.
        prepareTestObject(conn);

        String typeName = conn.getSchema() + ".test_array";
        // Obtain the type descriptor of the test_array type.
        ArrayDescriptor desc = ArrayDescriptor.getDescriptor(typeName, conn);

        // Create a GaussArray object based on the type descriptor and element data.
        GaussArray array = new GaussArray(desc, new Object[]{1, 2, 3});

        // Obtain the array type descriptor.
        desc = array.getDescriptor();

        // Use type descriptor APIs (non-standard).
        // Print whether the type name is $currentSchema.test_array.
        System.out.println((conn.getSchema() + ".test_array").equals(desc.getSQLTypeName()));
        // Print whether sqlType of the type is Types.ARRAY.
        System.out.println(desc.getSQLType() == Types.ARRAY);

        // Use array APIs.
        // Obtain and traverse the element arrays.
        Integer[] elements = (Integer[]) array.getArray();
        for (Integer element : elements) {
            System.out.println(element);
        }

        // Obtain and traverse some element arrays.
        elements = (Integer[]) array.getArray(1, 2);
        for (Integer element : elements) {
            System.out.println(element);
        }

        // Print the element type name.
        System.out.println(array.getBaseTypeName());

        // Print whether sqlType of an element is Types.INTEGER.
        System.out.println(array.getBaseType() == Types.INTEGER);

        // Delete the test object.
        cleanTestObject(conn);
    }
}

The execution result of the preceding example is as follows:

true
true
1
2
3
1
2
int4
true

Example 3: An empty element of the string type is converted to null when it is passed to the database.

// Writing the username and password used for authentication directly into code may cause security risks. It is recommended that you store them in the configuration file or environment variables (the password must be stored in ciphertext and decrypted when being used) to ensure security.
// In this example, the username and password are stored in environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV in the local environment (variable names are subject to the actual situation).
// Change the values of $ip, $port, and database.

import com.huawei.gaussdb.jdbc.jdbc.ArrayDescriptor;
import com.huawei.gaussdb.jdbc.jdbc.GaussArray;

import java.sql.*;

public class ArrayTest3 {
    // Establish a database connection in non-encrypted mode.
    public static Connection getConnection() {
        String username = System.getenv("EXAMPLE_USERNAME_ENV");
        String passwd = System.getenv("EXAMPLE_PASSWORD_ENV");
        String driver = "com.huawei.gaussdb.jdbc.Driver";
        String sourceURL = "jdbc:gaussdb://$ip:$port/database?enableGaussArrayAndStruct=true";
        Connection conn = null;
        try {
            // Load the database driver.
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }

        try {
            // Create a database connection.
            conn = DriverManager.getConnection(sourceURL, username, passwd);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }

        return conn;
    }

    /**
     * Create a frontend database object.
     *
     * @param conn conn
     * @throws SQLException An exception occurred while executing the statement
     */
    public static void prepareTestObject(Connection conn) throws SQLException {
        Statement stmt = conn.createStatement();
        stmt.execute("create type test_array is table of varchar(50)");
        stmt.execute("create table test_tab(c1 varchar(50))");
        stmt.execute("create or replace procedure test_proc(v1 in test_array) is\n" +
                "begin\n" +
                "     for i in 1..v1.count loop\n" +
                "         insert into test_tab values(v1(i));\n" +
                "     end loop;\n" +
                "end;");
    }

    /**
     * Clear the database object.
     *
     * @param conn conn
     * @throws SQLException if an exception occurred while executing the statement
     */
    public static void cleanTestObject(Connection conn) throws SQLException {
        Statement stmt = conn.createStatement();
        stmt.execute("drop procedure test_proc");
        stmt.execute("drop table test_tab");
        stmt.execute("drop type test_array");
    }

    /**
     * Transfer an array with empty elements to the database.
     *
     * @param conn conn
     * @throws SQLException if an exception occurred while executing the statement
     */
    public static void inParamWithEmptyElement(Connection conn) throws SQLException {
        String typeName = "test_array";
        // Obtain the type descriptor of the test_array type.
        ArrayDescriptor desc = ArrayDescriptor.getDescriptor(typeName, conn);
        // Create a GaussArray object based on the type descriptor and element data.
        GaussArray array = new GaussArray(desc, new String[]{"", "", null});
        // Traverse and print elements.
        for (String s : (String[]) array.getArray()) {
            if (s == null) {
                System.out.println("s is null");
            } else {
                if (s.isEmpty()) {
                    System.out.println("s is empty");
                }
            }
        }

        // Input parameters and run the stored procedure.
        CallableStatement cstmt = conn.prepareCall("{call test_proc(?)}");
        cstmt.setObject(1, array, Types.ARRAY);
        cstmt.execute();

        // Query results.
        PreparedStatement stmt = conn.prepareStatement("select * from test_tab");
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            String s = rs.getString(1);
            if (s == null) {
                System.out.println("s is null");
            } else {
                if (s.isEmpty()) {
                    System.out.println("s is empty");
                }
            }
        }
    }

    /**
    * Main program. Call static methods one by one.
     *
     * @param args args
     */
    public static void main(String[] args) throws SQLException {
       // Create a database connection.
        Connection conn = getConnection();

        // Create a frontend test object.
        prepareTestObject(conn);

        // Transfer an array with empty elements to the database as input parameters.
        inParamWithEmptyElement(conn);

        // Delete the test object.
        cleanTestObject(conn);
    }
}

The execution result of the preceding example is as follows:

s is empty
s is empty
s is null
s is null
s is null
s is null

Example 4: When the registered varray type is different from that of the output parameter of the stored procedure, the actual instance type returned by the output parameter is subject to the output parameter type of the stored procedure.

// Writing the username and password used for authentication directly into code may cause security risks. It is recommended that you store them in the configuration file or environment variables (the password must be stored in ciphertext and decrypted when being used) to ensure security.
// In this example, the username and password are stored in environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV in the local environment (variable names are subject to the actual situation).
// Change the values of $ip, $port, and database.

import com.huawei.gaussdb.jdbc.jdbc.GaussArray;

import java.sql.*;

public class ArrayTest4 {
    // Establish a database connection in non-encrypted mode.
    public static Connection getConnection() {
        String username = System.getenv("EXAMPLE_USERNAME_ENV");
        String passwd = System.getenv("EXAMPLE_PASSWORD_ENV");
        String driver = "com.huawei.gaussdb.jdbc.Driver";
        String sourceURL = "jdbc:gaussdb://$ip:$port/database?enableGaussArrayAndStruct=true";
        Connection conn = null;
        try {
            // Load the database driver.
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }

        try {
            // Create a database connection.
            conn = DriverManager.getConnection(sourceURL, username, passwd);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }

        return conn;
    }

    /**
     * Create a frontend database object.
     *
     * @param conn conn
     * @throws SQLException An exception occurred while executing the statement
     */
    public static void prepareTestObject(Connection conn) throws SQLException {
        Statement stmt = conn.createStatement();
        stmt.execute("create or replace package test_pkg is\n" +
                "    type t1 is varray(20) of int;\n" +
                "    type t2 is varray(20) of varchar(50);\n" +
                "    procedure proc_out_t1(v out t1);\n" +
                "end test_pkg;");

        stmt.execute("create or replace package body test_pkg is\n" +
                "    procedure proc_out_t1(v out t1) is\n" +
                "    begin\n" +
                "        v := t1(1, 2, 3, 4, 5);\n" +
                "    end;\n" +
                "end test_pkg;");
    }

    /**
     * Clear the database object.
     *
     * @param conn conn
     * @throws SQLException if an exception occurred while executing the statement
     */
    public static void cleanTestObject(Connection conn) throws SQLException {
        Statement stmt = conn.createStatement();
        stmt.execute("drop package test_pkg");
    }

    /**
     * Register an overloading type.
     *
     * @param conn conn
     * @throws SQLException if an exception occurred while executing statement
     */
    public static void testRegisterDiffType(Connection conn) throws SQLException {
        // To output parameters, enable the proc_outparam_override parameter for the database.
        Statement stmt = conn.createStatement();
        stmt.execute("set behavior_compat_options = 'proc_outparam_override'");
        CallableStatement cstmt = conn.prepareCall("{call test_pkg.proc_out_t1(?)}");

        // Register the t2 type.
        cstmt.registerOutParameter(1, Types.ARRAY, "test_pkg.t2");
        cstmt.execute();

        // Obtain the t1 type with the return type of getArray being Integer[].
        GaussArray array = (GaussArray)cstmt.getArray(1);
        // Traverse elements.
        for (Integer element : (Integer[]) array.getArray()) {
            System.out.println(element);
        }
    }

    /**
    * Main program. Call static methods one by one.
     *
     * @param args args
     */
    public static void main(String[] args) throws SQLException {
       // Create a database connection.
        Connection conn = getConnection();

        // Create a frontend test object.
        prepareTestObject(conn);

        // Register a different type.
        testRegisterDiffType(conn);

        // Delete the test object.
        cleanTestObject(conn);
    }
}

The execution result of the preceding example is as follows:

1
2
3
4
5

• The type name is case-sensitive and cannot contain decimal points.
• The type name does not support synonyms.
• The type name can be in the Schema.Package.Type, Package.Type, Schema.Type, or Type format. If no schema name is transferred, the current schema is used by default. If the schema or package name contains a decimal point, it must be enclosed in double quotation marks.
• To construct ArrayDescriptor using the built-in native array type, the input type name must contain the schema name (the current schema does not have the built-in native array type).
• The element supports the following basic types: int2, int4, int8, float4, float8, numeric, bool, bpchar, varchar, nvarchar2, name, text, timestamp, timestamptz, time, timetz, clob, bytea, and blob. Possible aliases of the preceding types include smallint, int, integer, bigint, number, float, boolean, char, and varchar2.
• The element supports the following user-defined types: array, collection, and record.
• After JDBC enables the URL parameter enableGaussArrayAndStruct, the output parameters and return values of the collection or array type can be obtained as array objects. To output parameters, the GUC parameter behavior_compat_options needs to be set to 'proc_outparam_override' for the database.
• The output parameter or return value cannot be a multi-dimensional array. The array start index of the output parameter or return value cannot be 1.
• JDBC relies on the database to verify type modifiers. For example:
  1. The element length cannot be verified. For example, for the table of varchar(5) type, if the length of the input string is greater than 5 when an array is constructed, JDBC will not report an error.
  2. The element precision cannot be verified. For example, for the table of numeric(3) type, if the number of input digits is greater than 3 when an array is constructed, JDBC will not report an error.
  3. The element precision cannot be converted. For example, for the varray of numeric(3, 1) type, if the input value is 2.55 when an array is constructed, JDBC will not convert it to 2.6.
  4. The varray length cannot be verified. For example, for the varray(2) of int type, if the number of input elements is greater than 2 when an array is constructed, JDBC will not report an error.

• When an array object passed to the database contains empty elements, the database converts them to null. For example, an empty element of the string type is converted to null when being passed to the database. Similarly, if the length of a string stored in an element of the CLOB type is 0, the element is converted to null when being passed to the database. In addition, if the length of a binary array stored in an element of the BLOB or BYTEA type is 0, the element is converted to null when being passed to the database.

  You are advised not to store empty elements in the constructed array object because empty elements are converted to null when they are passed to the database. For example, an empty element of the string type is converted to null. For details, see Example 3.

  When the array object sent from the database to the client contains empty elements, the client also converts these elements to null.

• Implicit conversion is supported between different array types. If the registered type is different from the output parameter type of the stored procedure, the instance type returned by the output parameter is subject to the output parameter type of the stored procedure. For details, see Example 4.
• For details about array APIs, see the description of standard APIs supported by GaussArray objects.
• ArrayDescriptor is a non-standard API. Its getSQLType API returns the fixed value java.sql.Types.ARRAY.

  For details about the type name returned by the getSQLTypeName API of ArrayDescriptor, see Data Type Mapping.

The following table lists standard array APIs.

• For details about the array types obtained by the getArray and getArray(long index, int count) APIs, see the Java Variable Type column in Data Type Mapping.

  For example, if the element type is varchar, the object type obtained through the getArray API is String[].

  Special notes: For an element of the int2 or smallint type, if the element type is changed to short when the element is accessed, the object type obtained by the getArray API is Short[].

• For details about sqlType obtained by the getBaseType API, see the JDBC Type Index column in Data Type Mapping.
• For details about the element type names obtained by getBaseTypeName, see Data Type Mapping. The type name mapping rules for the collection, array, and record types are as follows:
  1. If a type is defined in a package, its name is generally in the Schema.Package.Type format.
     If any of schema, package, and type names does not comply with the rules, the type name is in the "Schema"."Package"."Type" format.

     • Only digits, letters, and underscores (_) are allowed.
     • The first character cannot be a digit.
  2. If a type is defined in a schema, its name is generally in the Schema.Type format.
     If any of schema and type names do not comply with the rules, the type name is in the "Schema"."Type" format.

     • Only digits, letters, and underscores (_) are allowed.
     • The first character cannot be a digit.
• For the getArray(long index, int count) API:
  1. The value of index is 1 for the start element.
  2. The value of index ranges from 1 to 2147483647. If the input value is out of this range, an error is reported.
  3. The value of count ranges from 0 to 2147483647. If the input value is out of this range, an error is reported.
  4. If the value of index exceeds the number of elements, an empty array whose length is 0 is returned.
  5. If the value of count exceeds the maximum number of elements that can be obtained based on the current index, an array consisting of the remaining elements is returned.