Oracle8i JDBC Developer's Guide and Reference Release 2 (8.1.6) A81354-01 |
|
An Oracle collection--either a variable array (VARRAY) or a nested table in the database--maps to an array in Java. JDBC 2.0 arrays are used to materialize Oracle collections in Java. The terms "collection" and "array" are sometimes used interchangeably, although "collection" is more appropriate on the database side, and "array" is more appropriate on the JDBC application side.
Oracle supports only named collections, where you specify a SQL type name to describe a type of collection.
JDBC lets you use arrays as any of the following:
The rest of this section discusses creating and materializing collections.
The remainder of the chapter describes how to access and update collection data through Java arrays. For a complete code example of creating a table with a collection column and then manipulating and printing the contents, see "Weakly Typed Arrays--ArrayExample.java".
In your application, you have the choice of materializing a collection as an instance of the oracle.sql.ARRAY
class, which is weakly typed, or materializing it as an instance of a custom Java class that you have created in advance, which is strongly typed. Custom Java classes used for collections are referred to as custom collection classes in this manual. A custom collection class must implement the Oracle oracle.sql.CustomDatum
interface. In addition, the custom class or a companion class must implement oracle.sql.CustomDatumFactory
. (The standard java.sql.SQLData
interface is for mapping SQL object types only.)
The oracle.sql.ARRAY
class implements the standard java.sql.Array
interface (oracle.jdbc2.Array
under JDK 1.1.x).
The ARRAY
class includes functionality to retrieve the array as a whole, retrieve a subset of the array elements, and retrieve the SQL base type name of the array elements. You cannot write to the array, however, as there are no setter methods.
Custom collection classes, as with the ARRAY
class, allow you to retrieve all or part of the array and get the SQL base type name. They also have the advantage of being strongly typed, which can help you find coding errors during compilation that might not otherwise be discovered until runtime.
Furthermore, custom collection classes produced by JPublisher offer the feature of being writable, with individually accessible elements. (This is also something you could implement in a custom collection class yourself.)
For more information about custom collection classes, see "Custom Collection Classes with JPublisher".
This section presents background information about creating Oracle collections.
Because Oracle supports only named collections, you must declare a particular VARRAY
type name or nested table type name. "VARRAY" and "nested table" are not types themselves, but categories of types.
A SQL type name is assigned to a collection when you create it, as in the following SQL syntax:
CREATE TYPE <sql_type_name> AS <datatype>;
A VARRAY is an array of varying size. It has an ordered set of data elements, and all the elements are of the same datatype. Each element has an index, which is a number corresponding to the element's position in the VARRAY. The number of elements in a VARRAY is the "size" of the VARRAY. You must specify a maximum size when you declare the VARRAY
type. For example:
CREATE TYPE myNumType AS VARRAY(10) OF NUMBER;
This statement defines myNumType
as a SQL type name that describes a VARRAY of NUMBER
values that can contain no more than 10-elements.
A nested table is an unordered set of data elements, all of the same datatype. The database stores a nested table in a separate table which has a single column, and the type of that column is a built-in type or an object type. If the table is an object type, it can also be viewed as a multi-column table, with a column for each attribute of the object type. Create a nested table with this SQL syntax:
CREATE TYPE myNumList AS TABLE OF integer;
This statement identifies myNumList
as a SQL type name that defines the table type used for the nested tables of the type INTEGER
.