Oracle8i SQLJ Developer's Guide and Reference Release 8.1.5 A64684-01 |
|
This section has these subsections:
The oracle.sql.ARRAY
class enables you to access and manipulate arrays and their data within a JDBC program. The oracle.sql.ARRAY
class implements the oracle.jdbc2.Array
interface.
JDBC provides support for arrays as any of the following:
Arrays include varray
s (variable-length arrays) and nested tables. The methods in the oracle.sql.ARRAY
class enable you to access and manipulate the array and its data even if it is a varray
or nested table. That is, you do not have to add any special code when you are accessing a varray
or nested table. The methods can determine if they are being applied to a varray
or nested table, and respond by taking the appropriate actions.
Oracle supports only named arrays, where you specify a SQL type name to describe a type of array. The SQL type name is assigned to the array when you create it, as in the following SQL syntax:
CREATE TYPE <sql_type_name> AS <datatype>
The array can be either a nested table or a varray
.
A varray
is an array of varying size, thus the name "varray
". A varray
has an ordered set of data elements. All elements of a given varray
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 array 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
s that can contain no more than 10-elements.
A nested table is an unordered set of data elements, all of the same datatype. It 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 remainder of this section describes how to access and update array data. For general information about the oracle.sql.ARRAY
class, including how to manually create array objects, see "Class oracle.sql.ARRAY". For a complete code example of creating a table with an array column, then manipulating and printing the contents, see "Array Sample".
When you retrieve an array you get an oracle.sql.ARRAY
object where each array element can be returned as a materialized Java array object or as a result set object.
You can retrieve a SQL array that has been selected into 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, you can get the data with the getObject()
method of the oracle.sql.ResultSet
class, then cast the output to oracle.sql.ARRAY
.
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 provides versions of these methods that enable you to specify a type map so you can choose how you want your SQL datatypes to map to Java datatypes. Oracle also provides methods that enable you to retrieve all of an array's elements or a subset of the array (but note, there is no performance advantage in retrieving a subset of an array as opposed to retrieving the entire array).
The getArray()
method retrieves the element values of the array into a java.lang.Object[]
array. The elements are converted to the Java types corresponding to the SQL type of the data in the original array.
The getArray()
materializes the data as an array of oracle.sql.*
objects and does not use a type map. Oracle also provides a getArray(map)
method to let you specify a type map and a getArray(index,count)
method to retrieve a subset of the array.
The getOracleArray()
method retrieves the element values of the array into a Datum[]
array. The elements are converted to the oracle.sql.*
datatype corresponding to the SQL type of the data in the original array.
The getOracleArray()
method materializes the data as an array of oracle.sql.*
objects and does not use the type map. Oracle also provides the getOracleArray(index,count)
.
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 varray
s, 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 that you use 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.
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.
Similarly, if you use getResultSet()
to return an array of primitive datatypes, then the JDBC drivers return 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 BigDecimal
element value.
By default, if 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
.
The getResultSet(map)
method behaves in a similar manner to getArray(map)
.
For more information on using type maps with arrays, see "Using a Type Map to Map Array Elements".
To retrieve a subset of the array, you can 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 SQL array elements. 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 "Array Sample".
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 object of type oracle.sql.ARRAY
.
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 type code " + 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)); };
Pass an array to a prepared statement as follows (use similar steps to pass an array to a callable statement):
ArrayDescriptor
object for the SQL type that the array will contain (unless one has already been created for this SQL type). See "Class oracle.sql.ARRAY" 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 "Working with Arrays" for information about SQL typenames.
oracle.sql.ARRAY
object.
ARRAY array = new ARRAY(descriptor, elements);
where descriptor
is the ArrayDescriptor
object previously constructed and elements
is a java.lang.Object
containing a Java array of the elements. These objects are converted to raw bytes of the appropriate SQL type.
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, do 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(int param_index, int sql_type, string sql_type_name);
where param_index
is the parameter index, sql_type
is the SQL type code, and sql_type_name
is the name of the array type. In this case, the sql_type
is OracleTypes.ARRAY
.
ocs.executeQuery()
If your array contains Oracle objects, then you can use a type map to associate each object in the array with its 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 the element is returned as an oracle.sql.STRUCT
.
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 them to the type map if they are not already there. For instructions on how to add entries to an existing type map or how to create a new type map, see "Understanding Type Maps".
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 select all of the employees belonging to the SALES department as the custom Java object EmployeeObj
, then you must create a mapping in the type map between the EMPLOYEE
SQL type and the EmployeeObj
custom Java 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 that the getARRAY()
method can retrieve the EMPLOYEE_LIST
object into the employeeArray
object.
Note:
The |
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" Dictionary map = conn.getTypeMap(); map.put("EMPLOYEE", Class.forName("EmployeeObj"));
Retrieve the SQL EMPLOYEE
objects from the EMPLOYEE_LIST
. To do this, apply the getArray()
method of the oracle.jdbc2.Array
class to employeeArray
. 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]; ... }