Using object types and collections with Java v42.7.3.1

Suggest edits

The SQL CREATE TYPE command is used to create a user-defined object type, which is stored in the EDB Postgres Advanced Server database. The CREATE TYPE command is also used to create a collection, commonly referred to as an array, which is also stored in the EDB Postgres Advanced Server database.

These user-defined types can then be referenced in SPL procedures, SPL functions, and Java programs.

The basic object type is created with the CREATE TYPE AS OBJECT command along with optional usage of the CREATE TYPE BODY command.

A nested table type collection is created using the CREATE TYPE AS TABLE OF command. A varray type collection is created with the CREATE TYPE VARRAY command.

The following shows a Java method used by both upcoming examples to establish the connection to the EDB Postgres Advanced Server database.

public static Connection getEDBConnection() throws
  ClassNotFoundException, SQLException {
  String url = "jdbc:edb://localhost:5444/test";
  String user = "enterprisedb";
  String password = "edb";
  Connection conn = DriverManager.getConnection(url, user, password);
  return conn;
}

Using an object type

Create the object types in the EDB Postgres Advanced Server database. Object type addr_object_type defines the attributes of an address:

CREATE OR REPLACE TYPE addr_object_type AS OBJECT
(
    street          VARCHAR2(30),
    city            VARCHAR2(20),
    state           CHAR(2),
    zip             NUMBER(5)
);

Object type emp_obj_typ defines the attributes of an employee. One of these attributes is object type ADDR_OBJECT_TYPE. The object type body contains a method that displays the employee information:

CREATE OR REPLACE TYPE emp_obj_typ AS OBJECT
(
    empno           NUMBER(4),
    ename           VARCHAR2(20),
    addr            ADDR_OBJECT_TYPE,
    MEMBER PROCEDURE display_emp(SELF IN OUT emp_obj_typ)
);

CREATE OR REPLACE TYPE BODY emp_obj_typ AS
  MEMBER PROCEDURE display_emp (SELF IN OUT emp_obj_typ)
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Employee No   : ' || SELF.empno);
    DBMS_OUTPUT.PUT_LINE('Name          : ' || SELF.ename);
    DBMS_OUTPUT.PUT_LINE('Street        : ' || SELF.addr.street);
    DBMS_OUTPUT.PUT_LINE('City/State/Zip: ' || SELF.addr.city || ', ' ||
      SELF.addr.state || ' ' || LPAD(SELF.addr.zip,5,'0'));
  END;
END;

The following is a Java method that includes these user-defined object types:

public static void testUDT() throws SQLException {
  Connection conn = null;
  try {
    conn = getEDBConnection();
    String commandText = "{call emp_obj_typ.display_emp(?)}";
    CallableStatement stmt = conn.prepareCall(commandText);

    // initialize emp_obj_typ structure
    // create addr_object_type structure
    Struct address = conn.createStruct("addr_object_type",
      new Object[]{"123 MAIN STREET","EDISON","NJ",8817});
    Struct emp     = conn.createStruct("emp_obj_typ",
      new Object[]{9001,"JONES", address});

    // set emp_obj_typ type param
    stmt.registerOutParameter(1, Types.STRUCT, "emp_obj_typ");
    stmt.setObject(1, emp);
    stmt.execute();

    // extract emp_obj_typ object
    emp = (Struct)stmt.getObject(1);
    Object[] attrEmp = emp.getAttributes();
    System.out.println("empno: " + attrEmp[0]);
    System.out.println("ename: " + attrEmp[1]);

    // extract addr_object_type attributes
    address = (Struct) attrEmp[2];
    Object[] attrAddress = address.getAttributes();
    System.out.println("street: " + attrAddress[0]);
    System.out.println("city: " + attrAddress[1]);
    System.out.println("state: " + attrAddress[2]);
    System.out.println("zip: " + attrAddress[3]);
  } catch (ClassNotFoundException cnfe) {
    System.err.println("Error: " + cnfe.getMessage());
  } finally {
    if (conn != null) {
      conn.close();
    }
  }
}

A CallableStatement object is prepared based on the display_emp() method of the emp_obj_typ object type:

String commandText = "{call emp_obj_typ.display_emp(?)}";
CallableStatement stmt = conn.prepareCall(commandText);

createStruct() initializes and creates instances of object types addr_object_type and emp_obj_typ named address and emp, respectively:

Struct address = conn.createStruct("addr_object_type",
  new Object[]{"123 MAIN STREET","EDISON","NJ",8817});
Struct emp     = conn.createStruct("emp_obj_typ",
  new Object[]{9001,"JONES", address});

The call to registerOutParameter() registers the parameter type (Types.STRUCT) of emp_obj_typ:

stmt.registerOutParameter(1, Types.STRUCT, "emp_obj_typ");

The setObject() method binds the object instance emp to the IN OUT placeholder.

stmt.setObject(1, emp);

A call to stmt.execute() executes the call to the display_emp() method:

stmt.execute();

getObject() retrieves the emp_obj_typ object type. The attributes of the emp and address object instances are then retrieved and displayed:

emp = (Struct)stmt.getObject(1);
Object[] attrEmp = emp.getAttributes();
System.out.println("empno: " + attrEmp[0]);
System.out.println("ename: " + attrEmp[1]);

address = (Struct) attrEmp[2];
Object[] attrAddress = address.getAttributes();
System.out.println("street: " + attrAddress[0]);
System.out.println("city: " + attrAddress[1]);
System.out.println("state: " + attrAddress[2]);
System.out.println("zip: " + attrAddress[3]);

Using a collection

Create collections types NUMBER_ARRAY and CHAR_ARRAY in the EDB Postgres Advanced Server database:

CREATE OR REPLACE TYPE NUMBER_ARRAY  AS TABLE OF NUMBER;
CREATE OR REPLACE TYPE CHAR_ARRAY  AS TABLE OF VARCHAR(50);

The following is an SPL function that uses collection types NUMBER_ARRAY and CHAR_ARRAY as IN parameters and CHAR_ARRAY as the OUT parameter.

The function concatenates the employee ID from the NUMBER_ARRAY IN parameter with the employee name in the corresponding row from the CHAR_ARRAY IN parameter. The resulting concatenated entries are returned in the CHAR_ARRAY OUT parameter.

CREATE OR REPLACE FUNCTION concatEmpIdName
(
    arrEmpIds       NUMBER_ARRAY,
    arrEmpNames     CHAR_ARRAY
) RETURN CHAR_ARRAY
AS
DECLARE
    i               INTEGER := 0;
    arrEmpIdNames   CHAR_ARRAY;
BEGIN
  arrEmpIdNames := CHAR_ARRAY(NULL,NULL);
  FOR i IN arrEmpIds.FIRST..arrEmpIds.LAST LOOP
    arrEmpIdNames(i) := arrEmpIds(i) || '  ' || arrEmpNames(i);
  END LOOP;
  RETURN arrEmpIdNames;
END;

The following is a Java method that calls the previous function, passing and retrieving the collection types:

public static void testTableOfAsInOutParams() throws SQLException {
  Connection conn = null;
  try {
    conn = getEDBConnection();
    String commandText = "{? = call concatEmpIdName(?,?)}";
    CallableStatement stmt = conn.prepareCall(commandText);

    // create collections to specify employee id and name values
    Array empIdArray = conn.createArrayOf("integer",
      new Integer[]{7900, 7902});
    Array empNameArray = conn.createArrayOf("varchar",
      new String[]{"JAMES", "FORD"});

    // set TABLE OF VARCHAR as OUT param
    stmt.registerOutParameter(1, Types.ARRAY);

    // set TABLE OF INTEGER as IN param
    stmt.setObject(2, empIdArray, Types.OTHER);

    // set TABLE OF VARCHAR as IN param
    stmt.setObject(3, empNameArray, Types.OTHER);
    stmt.execute();
    java.sql.Array empIdNameArray = stmt.getArray(1);
    String[] emps = (String[]) empIdNameArray.getArray();

    System.out.println("items length: " + emps.length);
    System.out.println("items[0]: " + emps[0].toString());
    System.out.println("items[1]: " + emps[1].toString());

  } catch (ClassNotFoundException cnfe) {
    System.err.println("Error: " + cnfe.getMessage());
  } finally {
    if (conn != null) {
      conn.close();
    }
  }
}

A CallableStatement object is prepared to invoke the concatEmpIdName() function:

String commandText = "{? = call concatEmpIdName(?,?)}";
CallableStatement stmt = conn.prepareCall(commandText);

createArrayOf() initializes and creates collections named empIdArray and empNameArray:

Array empIdArray = conn.createArrayOf("integer",
  new Integer[]{7900, 7902});
Array empNameArray = conn.createArrayOf("varchar",
  new String[]{"JAMES", "FORD"});

The call to registerOutParameter() registers the parameter type (Types.ARRAY) of the OUT parameter:

stmt.registerOutParameter(1, Types.ARRAY);

The setObject() method binds the collections empIdArray and empNameArray to the IN placeholders:

stmt.setObject(2, empIdArray, Types.OTHER);
stmt.setObject(3, empNameArray, Types.OTHER);

A call to stmt.execute() invokes the concatEmpIdName() function:

stmt.execute();

getArray() retrieves the collection returned by the function. The first two rows consisting of the concatenated employee IDs and names are displayed:

java.sql.Array empIdNameArray = stmt.getArray(1);
String[] emps = (String[]) empIdNameArray.getArray();
System.out.println("items length: " + emps.length);
System.out.println("items[0]: " + emps[0].toString());
System.out.println("items[1]: " + emps[1].toString());

← Prev

Using BYTEA data with Java

↑ Up

Advanced JDBC Connector functionality

Asynchronous notification handling with NoticeListener

Using object types and collections with Java v42.7.3.1

Using an object type

Using a collection

← Prev

↑ Up

Next →