Using object types and collections with Java v42.7.3.2
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());
- On this page
- Using an object type
- Using a collection