Oracle9i JDBC Developer's Guide and Reference Release 1 (9.0.1) Part Number A90211-01 |
|
This chapter describes Oracle extensions to standard JDBC that let you access and manipulate Oracle collections, which map to Java arrays, and their data. The following topics are discussed:
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:
IN
or OUT
bind variables
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.ORAData
interface. In addition, the custom class or a companion class must implement oracle.sql.ORADataFactory
. (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
.
The most common way to create a new multi-level collection type in JDBC is to pass the SQL CREATE TYPE
statement to the execute()
method of the java.sql.Statement
class. The following code creates a one-level, nested table first_level
and a two levels nested table second_level
using the execute()
method:
Connection conn = .... // make a database // connection Statement stmt = conn.createStatement(); // open a database // cursor stmt.execute("CREATE TYPE first_level AS TABLE OF NUMBER"); // create a nested // table of number stmt.execute("CREATE second_level AS TABLE OF first_level"); // create a two // levels nested // table ... // other operations // here stmt.close(); // release the // resource conn.close(); // close the // database // connection
Once the multi-level collection types have been created, they can be used as both columns of a base table as well as attributes of a object type.
See the Oracle9i Application Developer's Guide - Object-Relational Features for the SQL syntax to create multi-level collections types and how to specify the storage tables for inner collections.
You can obtain collection data in an array instance through a result set or callable statement and pass it back as a bind variable in a prepared statement or callable statement.
The oracle.sql.ARRAY
class, which implements the standard java.sql.Array
interface (oracle.jdbc2.Array
interface under JDK 1.1.x), provides the necessary functionality to access and update the data of an Oracle collection (either a VARRAY or nested table).
This section discusses the following:
ARRAY
descriptors and ARRAY
class methods
Remember that you can use custom collection classes instead of the ARRAY
class. See "Custom Collection Classes with JPublisher".
Use the following result set, callable statement, and prepared statement methods to retrieve and pass collections as Java arrays. Code examples are provided later in the chapter.
The OracleResultSet
and OracleCallableStatement
classes support getARRAY()
and getArray()
methods to retrieve ARRAY
objects as output parameters--either as oracle.sql.ARRAY
instances or java.sql.Array
instances (oracle.jdbc2.Array
under JDK 1.1.x). You can also use the getObject()
method. These methods take as input a String
column name or int
column index.
The OraclePreparedStatement
and OracleCallableStatement
classes support setARRAY()
and setArray()
methods to take updated ARRAY
objects as bind variables and pass them to the database. You can also use the setObject()
method. These methods take as input a String
parameter name or int
parameter index as well as, respectively, an oracle.sql.ARRAY
instance or a java.sql.Array
instance (oracle.jdbc2.Array
under JDK 1.1.x).
The section introduces ARRAY
descriptors and lists methods of the ARRAY
class to provide an overview of its functionality.
Creating and using an ARRAY
object requires the existence of a descriptor--an instance of the oracle.sql.ArrayDescriptor
class--to exist for the SQL type of the collection being materialized in the array. You need only one ArrayDescriptor
object for any number of ARRAY
objects that correspond to the same SQL type.
ARRAY
descriptors are further discussed in "Creating ARRAY Objects and Descriptors".
The oracle.sql.ARRAY
class includes the following methods:
getDescriptor()
: Returns the ArrayDescriptor
object that describes the array type.
getArray()
: Retrieves the contents of the array in "default" JDBC types. If it retrieves an array of objects, then getArray()
uses the default type map of the database connection object to determine the types.
getOracleArray()
: Identical to getArray()
, but retrieves the elements in oracle.sql.*
format.
getBaseType()
: Returns the SQL typecode for the array elements (see "Class oracle.jdbc.OracleTypes" for information about typecodes).
getBaseTypeName()
: Returns the SQL type name of the elements of this array.
getSQLTypeName()
(Oracle extension): Returns the fully qualified SQL type name of the array as a whole.
getResultSet()
: Materializes the array elements as a result set.
getJavaSQLConnection()
: Returns the connection instance (java.sql.Connection
) associated with this array.
length()
: Returns the number of elements in the array.
This section discusses the following topics:
The oracle.sql.ARRAY
class contains methods that return array elements as Java primitive types. These methods allow you to access collection elements more efficiently than accessing them as Datum
instances and then converting each Datum
instance to its Java primitive value.
Here are the methods:
public int[] getIntArray()throws SQLException
public int[] getIntArray(long index, int count)
throws SQLException
public long[] getLongArray()throws SQLException
public long[] getLongArray(long index, int count)
throws SQLException
public float[] getFloatArray()throws SQLException
public float[] getFloatArray(long index, int count)
throws SQLException
public double[] getDoubleArray()throws SQLException
public double[] getDoubleArray(long index, int count)
throws SQLException
public short[] getShortArray()throws SQLException
public short[] getShortArray(long index, int count)
throws SQLException
Each method using the first signature returns collection elements as an XXX[]
, where XXX is a Java primitive type. Each method using the second signature returns a slice of the collection containing the number of elements specified by count
, starting at the index
location.
The Oracle JDBC driver provides public methods to enable and disable buffering of ARRAY
contents. (See "STRUCT Automatic Attribute Buffering" for a discussion of how to buffer STRUCT
attributes.)
The following methods are included with the oracle.sql.ARRAY
class:
The setAutoBuffering()
method enables or disables auto-buffering. The getAutoBuffering()
method returns the current auto-buffering mode. By default, auto-buffering is disabled.
It is advisable to enable auto-buffering in a JDBC application when the ARRAY
elements will be accessed more than once by the getAttributes()
and getArray()
methods (presuming the ARRAY
data is able to fit into the JVM memory without overflow).
When you enable auto-buffering, the oracle.sql.ARRAY
object keeps a local copy of all the converted elements. This data is retained so that a second access of this information does not require going through the data format conversion process.
If an array is in auto-indexing mode, the array object maintains an index table to hasten array element access.
The oracle.sql.ARRAY
class contains the following methods to support automatic array-indexing:
public synchronized void setAutoIndexing
(boolean enable, int direction)
throws SQLException
public synchronized void setAutoIndexing
(boolean enable)
throws SQLException
The setAutoIndexing()
method sets the auto-indexing mode for the oracle.sql.ARRAY
object. The direction
parameter gives the array object a hint: specify this parameter to help the JDBC driver determine the best indexing scheme. The following are the values you can specify for the direction
parameter:
The setAutoIndexing(boolean)
method signature sets the access direction as ARRAY.ACCESS_UNKNOWN
by default.
By default, auto-indexing is not enabled. For a JDBC application, enable auto-indexing for ARRAY
objects if random access of array elements may occur through the getArray()
and getResultSet()
methods.
This section discusses how to create array objects and how to retrieve and pass collections as array objects, including the following topics.
This section describes how to create ARRAY
objects and descriptors and lists useful methods of the ArrayDescriptor
class.
This section describes how to construct an oracle.sql.ARRAY
object. To do this, you must:
ArrayDescriptor
object (if one does not already exist) for the array.
ArrayDescriptor
object to construct the oracle.sql.ARRAY
object for the array you want to pass.
An ArrayDescriptor
is an object of the oracle.sql.ArrayDescriptor
class and describes the SQL type of an array. Only one array descriptor is necessary for any one SQL type. The driver caches ArrayDescriptor
objects to avoid recreating them if the SQL type has already been encountered. You can reuse the same descriptor object to create multiple instances of an oracle.sql.ARRAY
object for the same array type.
Collections are strongly typed. Oracle supports only named collections, that is, a collection given a SQL type name. For example, when you create a collection with the CREATE TYPE
statement:
CREATE TYPE num_varray AS varray(22) OF NUMBER(5,2);
Where NUM_VARRAY
is the SQL type name for the collection type.
Before you can construct an Array
object, an ArrayDescriptor
must first exist for the given SQL type of the array. If an ArrayDescriptor
does not exist, then you must construct one by passing the SQL type name of the collection type and your Connection
object (which JDBC uses to go to the database to gather meta data) to the constructor.
ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor (sql_type_name
,connection
);
Where sql_type_name
is the type name of the array and connection
is your Connection
object.
Once you have your ArrayDescriptor
object for the SQL type of the array, you can construct the ARRAY
object. To do this, pass in the array descriptor, your connection object, and a Java object containing the individual elements you want the array to contain.
ARRAY array = new ARRAY(arraydesc
,connection
,elements
);
Where arraydesc
is the array descriptor created previously, connection
is your connection object, and elements
is a Java array. The two possibilities for the contents of elements
are:
int[]
xxx
[]
where xxx
is the name of a Java class--for example, Integer[]
As with single-level collections, the JDBC application can create an oracle.sql.ARRAY
instance to represent a multi-level collection, and then send the instance to the database. The oracle.sql.ARRAY
constructor is defined as follows:
public ARRAY(ArrayDescriptor type, Connection conn, Object elements) throws SQLException
The first argument is an oracle.sql.ArrayDescriptor
object that describes the multi-level collection type. The second argument is the current database connection. And the third argument is a java.lang.Object
that holds the multi-level collection elements. This is the same constructor used to create single-level collections, but in Oracle9i, this constructor is enhanced to create multi-level collections as well. The elements parameter can now be either a one dimension array or a nested Java array.
To create a single-level collection, the elements are a one dimensional Java array. To create a multi-level collection, the elements can be either an array of oracle.sql.ARRAY[]
elements or a nested Java array or the combinations.
The following code shows how to create collection types with a nested Java array:
Connection conn = ...; // make a JDBC connection // create the collection types Statement stmt = conn.createStatement (); stmt.execute ("CREATE TYPE varray1 AS VARRAY(10) OF NUMBER(12, 2)"); // one // layer stmt.execute ("CREATE TYPE varray2 AS VARRAY(10) OF varray1"); // two layers stmt.execute ("CREATE TYPE varray3 AS VARRAY(10) OF varray2"); // three layers stmt.execute ("CREATE TABLE tab2 (col1 index, col2 value)"); stmt.close (); // obtain a type descriptor of "SCOTT.VARRAY3" ArrayDescriptor desc = ArrayDescriptor.createDescriptor("SCOTT.VARRAY3", conn); // prepare the multi level collection elements as a nested Java array int[][][] elems = { {{1}, {1, 2}}, {{2}, {2, 3}}, {{3}, {3, 4}} }; // create the ARRAY by calling the constructor ARRAY array3 = new ARRAY (desc, conn, elems); // some operations ... // close the database connection conn.close();
In the above example, another implementation is to prepare the elems
as a Java array of oracle.sql.ARRAY[]
elements, and each oracle.sql.ARRAY[]
element represents a SCOTT.VARRAY3
.
An ARRAY
descriptor can be referred to as a type object. It has information about the SQL name of the underlying collection, the typecode of the array's elements, and, if it is an array of structured objects, the SQL name of the elements. The descriptor also contains the information on about to convert to and from the given type. You need only one descriptor object for any one type, then you can use that descriptor to create as many arrays of that type as you want.
The ArrayDescriptor
class has the following methods for retrieving an element's typecode and type name:
createDescriptor()
: This is a factory for ArrayDescriptor
instances; looks up the name in the database and determine the characteristics of the array.
getBaseType()
: Returns the integer typecode associated with this ARRAY
descriptor (according to integer constants defined in the OracleTypes
class, which "Package oracle.jdbc" describes).
getBaseName()
: Returns a string with the type name associated with this array element if it is a STRUCT
or REF
.
getArrayType()
: Returns an integer indicating whether the array is a VARRAY or nested table. ArrayDescriptor.TYPE_VARRAY
and ArrayDescriptor.TYPE_NESTED_TABLE
are the possible return values.
getMaxLength()
: Returns the maximum number of elements for this array type.
getJavaSQLConnection()
: Returns the connection instance (java.sql.Connection
) that was used in creating the ARRAY
descriptor (a new descriptor must be created for each connection instance).
As "Steps in Creating ArrayDescriptor and ARRAY Objects" discusses, when you create an ARRAY
object, you first must create an ArrayDescriptor
object. Create the ArrayDescriptor
object by calling the ArrayDescriptor.createDescriptor()
method. The oracle.sql.ArrayDescriptor
class is serializable, meaning that you can write the state of an ArrayDescriptor
object to an output stream for later use. Recreate the ArrayDescriptor
object by reading its serialized state from an input stream. This is referred to as deserializing. With the ArrayDescriptor
object serialized, you do not need to call the createDescriptor()
method--simply deserialize the ArrayDescriptor
object.
It is advisable to serialize an ArrayDescriptor
object when the object type is complex but not changed often.
If you create an ArrayDescriptor
object through deserialization, you must provide the appropriate database connection instance for the ArrayDescriptor
object using the setConnection()
method.
The following code furnishes the connection instance for an ArrayDescriptor
object:
public void setConnection (Connection conn) throws SQLException
This section first discusses how to retrieve an ARRAY
instance as a whole from a result set, and then how to retrieve the elements from the ARRAY
instance.
You can retrieve a SQL array from a result set by casting the result set to an OracleResultSet
object and using the getARRAY()
method, which returns an oracle.sql.ARRAY
object. If you want to avoid casting the result set, then you can get the data with the standard getObject()
method specified by the java.sql.ResultSet
interface, and cast the output to an oracle.sql.ARRAY
object.
Once you have the array in an ARRAY
object, you can retrieve the data using one of these three overloaded methods of the oracle.sql.ARRAY
class:
Oracle also provides methods that enable you to retrieve all the elements of an array, or a subset.
The getOracleArray()
method is an Oracle-specific extension that is not specified in the standard Array
interface (java.sql.Array
under JDK 1.2.x or oracle.jdbc2.Array
under JDK 1.1.x). The getOracleArray()
method retrieves the element values of the array into a Datum[]
array. The elements are of the oracle.sql.*
datatype corresponding to the SQL type of the data in the original array.
For an array of structured objects, this method will use oracle.sql.STRUCT
instances for the elements.
Oracle also provides a getOracleArray(
index
,
count
)
method
to get a subset of the array elements.
The getResultSet()
method returns a result set that contains elements of the array designated by the ARRAY
object. The result set contains one row for each array element, with two columns in each row. The first column stores the index into the array for that element, and the second column stores the element value. In the case of VARRAYs, the index represents the position of the element in the array. In the case of nested tables, which are by definition unordered, the index reflects only the return order of the elements in the particular query.
Oracle recommends using getResultSet()
when getting data from nested tables. Nested tables can have an unlimited number of elements. The ResultSet
object returned by the method initially points at the first row of data. You get the contents of the nested table by using the next()
method and the appropriate getXXX()
method. In contrast, getArray()
returns the entire contents of the nested table at one time.
The getResultSet()
method uses the connection's default type map to determine the mapping between the SQL type of the Oracle object and its corresponding Java datatype. If you do not want to use the connection's default type map, another version of the method, getResultSet(
map
)
, enables you to specify an alternate type map.
Oracle also provides the getResultSet(
index
,
count
)
and getResultSet(
index
,
count
,
map
)
methods to retrieve a subset of the array elements.
The getArray()
method is a standard JDBC method that returns the array elements into a java.lang.Object
instance that you can cast as appropriate (see "Comparing the Data Retrieval Methods"). The elements are converted to the Java types corresponding to the SQL type of the data in the original array.
Oracle also provides a getArray(
index
,
count
)
method to retrieve a subset of the array elements.
If you use getOracleArray()
to return the array elements, the use by that method of oracle.sql.Datum
instances avoids the expense of data conversion from SQL to Java. The data inside a Datum
(or subclass) instance remains in raw SQL format.
If you use getResultSet()
to return an array of primitive datatypes, then the JDBC driver returns a ResultSet
object that contains, for each element, the index into the array for the element and the element value. For example:
ResultSet rset = intArray.getResultSet();
In this case, the result set contains one row for each array element, with two columns in each row. The first column stores the index into the array; the second column stores the element value.
If you use getArray()
to retrieve an array of primitive datatypes, then a java.lang.Object
that contains the element values is returned. The elements of this array are of the Java type corresponding to the SQL type of the elements. For example:
BigDecimal[] values = (BigDecimal[]) intArray.getArray();
Where intArray
is an oracle.sql.ARRAY
, corresponding to a VARRAY of type NUMBER
. The values
array contains an array of elements of type java.math.BigDecimal
, because the SQL NUMBER
datatype maps to Java BigDecimal
by default, according to the Oracle JDBC drivers.
By default, if you are working with an array whose elements are structured objects, and you use getArray()
or getResultSet()
, then the Oracle objects in the array will be mapped to their corresponding Java datatypes according to the default mapping. This is because these methods use the connection's default type map to determine the mapping.
However, if you do not want default behavior, then you can use the getArray(
map
)
or getResultSet(
map
)
method to specify a type map that contains alternate mappings. If there are entries in the type map corresponding to the Oracle objects in the array, then each object in the array is mapped to the corresponding Java type specified in the type map. For example:
Object[] object = (Object[])objArray.getArray(map);
Where objArray
is an oracle.sql.ARRAY
object and map
is a java.util.Map
object.
If the type map does not contain an entry for a particular Oracle object, then the element is returned as an oracle.sql.STRUCT
object.
The getResultSet(
map
)
method behaves similarly to the getArray(
map
)
method.
For more information on using type maps with arrays, see "Using a Type Map to Map Array Elements".
If you do not want to retrieve the entire contents of an array, then you can use signatures of getArray()
, getResultSet()
, and getOracleArray()
that let you retrieve a subset. To retrieve a subset of the array, pass in an index and a count to indicate where in the array you want to start and how many elements you want to retrieve. As described above, you can specify a type map or use the default type map for your connection to convert to Java types. For example:
Object object = arr.getArray(index
,count
,map
); Object object = arr.getArray(index
,count
);
Similar examples using getResultSet()
are:
ResultSet rset = arr.getResultSet(index
,count
,map
); ResultSet rset = arr.getResultSet(index
,count
);
A similar example using getOracleArray()
is:
Datum arr = arr.getOracleArray(index
,count
);
Where arr
is an oracle.sql.ARRAY
object, index
is type long
, count
is type int
, and map
is a java.util.Map
object.
Use getOracleArray()
to return an oracle.sql.Datum[]
array. The elements of the returned array will be of the oracle.sql.*
type that correspond to the SQL datatype of the elements of the original array. For example:
Datum arraydata[] = arr.getOracleArray();
Where arr
is an oracle.sql.ARRAY
object. For an example of retrieving an array and its contents, see "Weakly Typed Arrays--ArrayExample.java".
The following example assumes that a connection object conn
and a statement object stmt
have already been created. In the example, an array with the SQL type name NUM_ARRAY
is created to store a VARRAY of NUMBER
data. The NUM_ARRAY
is in turn stored in a table VARRAY_TABLE
.
A query selects the contents of the VARRAY_TABLE
. The result set is cast to an OracleResultSet
object; getARRAY()
is applied to it to retrieve the array data into my_array
, which is an oracle.sql.ARRAY
object.
Because my_array
is of type oracle.sql.ARRAY
, you can apply the methods getSQLTypeName()
and getBaseType()
to it to return the name of the SQL type of each element in the array and its integer code.
The program then prints the contents of the array. Because the contents of my_array
are of the SQL datatype NUMBER
, it must first be cast to the BigDecimal
datatype. In the for
loop, the individual values of the array are cast to BigDecimal
and printed to standard output.
stmt.execute ("CREATE TYPE num_varray AS VARRAY(10) OF NUMBER(12, 2)"); stmt.execute ("CREATE TABLE varray_table (col1 num_varray)"); stmt.execute ("INSERT INTO varray_table VALUES (num_varray(100, 200))"); ResultSet rs = stmt.executeQuery("SELECT * FROM varray_table"); ARRAY my_array = ((OracleResultSet)rs).getARRAY(1); // return the SQL type names, integer codes, // and lengths of the columns System.out.println ("Array is of type " + array.getSQLTypeName()); System.out.println ("Array element is of typecode " + array.getBaseType()); System.out.println ("Array is of length " + array.length()); // get Array elements BigDecimal[] values = (BigDecimal[]) my_array.getArray(); for (int i=0; i<values.length; i++) { BigDecimal out_value = (BigDecimal) values[i]; System.out.println(">> index " + i + " = " + out_value.intValue()); }
Note that if you use getResultSet()
to obtain the array, you would first get the result set object, then use the next()
method to iterate through it. Notice the use of the parameter indexes in the getInt()
method to retrieve the element index and the element value.
ResultSet rset = my_array.getResultSet(); while (rset.next()) { // The first column contains the element index and the // second column contains the element value System.out.println(">> index " + rset.getInt(1)+" = " + rset.getInt(2)); }
The oracle.sql.ARRAY
class provides three methods (which can be overloaded) to access collection elements. For Oracle9i JDBC drivers, these methods are extended to support multi-level collections. The three methods are the following:
getArray()
method : JDBC standard
getOracleArray()
method : Oracle extension
getResultSet()
method : JDBC standard
The getArray()
method returns a Java array that holds the collection elements. The array element type is determined by the collection element type and the JDBC default conversion matrix.
For example, the getArray()
method returns a java.math.BigDecimal
array for collection of SQL NUMBER
. The getOracleArray()
method returns a Datum array that holds the collection elements in Datum format. For multi-level collections, the getArray()
and getOracleArray()
methods both return a Java array of oracle.sql.ARRAY
elements.
The getResultSet()
method returns a ResultSet
object that wraps the multi-level collection elements. For multi-level collections, the JDBC applications use the getObject()
, getARRAY()
, or getArray()
method of the ResultSet
class to access the collection elements as instances of oracle.sql.ARRAY
.
The following code shows how to use the getOracleArray()
, getArray()
, and getResultSet()
methods:
Connection conn = ...; // make a JDBC connection Statement stmt = conn.createStatement (); ResultSet rset = stmt.executeQuery ("select col2 from tab2 where idx=1"); while (rset.next()) { ARRAY varray3 = (ARRAY) rset.getObject (1); Object varrayElems = varray3.getArray (1); // access array elements of "varray3" Datum[] varray3Elems = (Datum[]) varrayElems; for (int i=0; i<varray3Elems.length; i++) { ARRAY varray2 = (ARRAY) varray3Elems[i]; Datum[] varray2Elems = varray2.getOracleArray(); // access array elements of "varray2" for (int j=0; j<varray2Elems.length; j++) { ARRAY varray1 = (ARRAY) varray2Elems[j]; ResultSet varray1Elems = varray1.getResultSet(); // access array elements of "varray1" while (varray1Elems.next()) System.out.println ("idx="+varray1Elems.getInt(1)+" value="+varray1Elems.getInt(2)); } } } rset.close (); stmt.close (); conn.close ();
This section discusses how to pass arrays to prepared statement objects or callable statement objects.
Pass an array to a prepared statement as follows (use similar steps to pass an array to a callable statement). Note that you can use arrays as either IN
or OUT
bind variables.
ArrayDescriptor
object for the SQL type that the array will contain (unless one has already been created for this SQL type). See "Steps in Creating ArrayDescriptor and ARRAY Objects" for information about creating ArrayDescriptor
objects.
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor (sql_type_name
,connection
);
Where sql_type_name
is a Java string specifying the user-defined SQL type name of the array, and connection
is your Connection
object. See "Oracle Extensions for Collections (Arrays)" for information about SQL typenames.
oracle.sql.ARRAY
object.
ARRAY array = new ARRAY(descriptor, connection, elements);
Where descriptor
is the ArrayDescriptor
object previously constructed and elements
is a java.lang.Object
containing a Java array of the elements.
java.sql.PreparedStatement
object containing the SQL statement to execute.
OraclePreparedStatement
and use the setARRAY()
method of the OraclePreparedStatement
object to pass the array to the prepared statement.
(OraclePreparedStatement)stmt.setARRAY(parameterIndex
,array
);
Where parameterIndex
is the parameter index, and array
is the oracle.sql.ARRAY
object you constructed previously.
To retrieve a collection as an OUT
parameter in PL/SQL blocks, execute the following to register the bind type for your OUT
parameter.
OracleCallableStatement
:
OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("{? = call func()}");
OUT
parameter with this form of the regsiterOutParameter()
method:
ocs.registerOutParameter (intparam_index
, intsql_type
, stringsql_type_name
);
Where param_index
is the parameter index, sql_type
is the SQL typecode, and sql_type_name
is the name of the array type. In this case, the sql_type
is OracleTypes.ARRAY
.
ocs.execute();
oracle.sql.ARRAY array = ocs.getARRAY(1);
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]; ... }
This chapter primarily describes the functionality of the oracle.sql.ARRAY
class, but it is also possible to access Oracle collections through custom Java classes or, more specifically, custom collection classes.
You can create custom collection classes yourself, but the most convenient way is to use the Oracle JPublisher utility. Custom collection classes generated by JPublisher offer all the functionality described earlier in this chapter, as well as the following advantages (it is also possible to implement such functionality yourself):
ARRAY
class, allow you to get and set individual elements using the getElement()
and setElement()
methods. (This is also something you could implement in a custom collection class yourself.)
A custom collection class must satisfy three requirements:
oracle.sql.ORAData
interface described under "Creating and Using Custom Object Classes for Oracle Objects". Note that the standard JDBC SQLData
interface, which is an alternative for custom object classes, is not intended for custom collection classes.
oracle.sql.ORADataFactory
interface, for creating instances of the custom collection class.
oracle.sql.ARRAY
attribute for this purpose (this is the case with a JPublisher-produced custom collection class).
A JPublisher-generated custom collection class implements ORAData
and ORADataFactory
and indirectly includes an oracle.sql.ARRAY
attribute. The custom collection class will have an oracle.jpub.runtime.MutableArray
attribute. The MutableArray
class has an oracle.sql.ARRAY
attribute.
As an example of custom collection classes being strongly typed, if you define an Oracle collection MYVARRAY
, then JPublisher can generate a MyVarray
custom collection class. Using MyVarray
instances, instead of generic oracle.sql.ARRAY
instances, makes it easier to catch errors during compilation instead of at runtime--for example, if you accidentally assign some other kind of array into a MyVarray
variable.
If you do not use custom collection classes, then you would use standard java.sql.Array
instances (or oracle.sql.ARRAY
instances) to map to your collections.
For more information about JPublisher, see "Using JPublisher to Create Custom Object Classes", or refer to the Oracle9i JPublisher User's Guide.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|