Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## Using a Type Map to Map Array Elements

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];
       ...
    }

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index