Oracle8i JDBC Developer's Guide and Reference Release 2 (8.1.6) A81354-01 |
|
If your array contains Oracle objects, then you can use a type map to associate the objects in the array with the corresponding Java class. If you do not specify a type map, or if the type map does not contain an entry for a particular Oracle object, then each element is returned as an oracle.sql.STRUCT
object.
If you want the type map to determine the mapping between the Oracle objects in the array and their associated Java classes, then you must add an appropriate entry to the map. For instructions on how to add entries to an existing type map or how to create a new type map, see "Understanding Type Maps for SQLData Implementations".
The following example illustrates how you can use a type map to map the elements of an array to a custom Java object class. In this case, the array is a nested table. The example begins by defining an EMPLOYEE
object that has a name attribute and employee number attribute. EMPLOYEE_LIST
is a nested table type of EMPLOYEE
objects. Then an EMPLOYEE_TABLE
is created to store the names of departments within a corporation and the employees associated with each department. In the EMPLOYEE_TABLE
, the employees are stored in the form of EMPLOYEE_LIST
tables.
stmt.execute("CREATE TYPE EMPLOYEE AS OBJECT (EmpName VARCHAR2(50),EmpNo INTEGER))"); stmt.execute("CREATE TYPE EMPLOYEE_LIST AS TABLE OF EMPLOYEE"); stmt.execute("CREATE TABLE EMPLOYEE_TABLE (DeptName VARCHAR2(20), Employees EMPLOYEE_LIST) NESTED TABLE Employees STORE AS ntable1"); stmt.execute("INSERT INTO EMPLOYEE_TABLE VALUES ("SALES", EMPLOYEE_LIST (EMPLOYEE('Susan Smith', 123), EMPLOYEE('Scott Tiger', 124)))");
If you want to retrieve all the employees belonging to the SALES
department into an array of instances of the custom object class EmployeeObj
, then you must add an entry to the type map to specify mapping between the EMPLOYEE
SQL type and the EmployeeObj
custom object class.
To do this, first create your statement and result set objects, then select the EMPLOYEE_LIST
associated with the SALES
department into the result set. Cast the result set to OracleResultSet
so you can use the getARRAY()
method to retrieve the EMPLOYEE_LIST
into an ARRAY
object (employeeArray
in the example below).
The EmployeeObj
custom object class in this example implements the SQLData
interface. "Custom Object Class--SQLData Implementation" contains the code that creates the EmployeeObj
type.
Statement s = conn.createStatement(); OracleResultSet rs = (OracleResultSet)s.executeQuery ("SELECT Employees FROM employee_table WHERE DeptName = 'SALES'"); // get the array object ARRAY employeeArray = ((OracleResultSet)rs).getARRAY(1);
Now that you have the EMPLOYEE_LIST
object, get the existing type map and add an entry that maps the EMPLOYEE
SQL type to the EmployeeObj
Java type.
// add type map entry to map SQL type // "EMPLOYEE" to Java type "EmployeeObj" Map map = conn.getTypeMap(); map.put("EMPLOYEE", Class.forName("EmployeeObj"));
Next, retrieve the SQL EMPLOYEE
objects from the EMPLOYEE_LIST
. To do this, invoke the getArray()
method of the employeeArray
array object. This method returns an array of objects. The getArray()
method returns the EMPLOYEE
objects into the employees
object array.
// Retrieve array elements Object[] employees = (Object[]) employeeArray.getArray();
Finally, create a loop to assign each of the EMPLOYEE
SQL objects to the EmployeeObj
Java object emp
.
// Each array element is mapped to EmployeeObj object. for (int i=0; i<employees.length; i++) { EmployeeObj emp = (EmployeeObj) employees[i]; ... }