Each of the Oracle datatype classes provides, among other things, the following:
■ one or more constructors, typically with a constructor that uses raw bytes as input and a constructor that takes a Java type as input
Notes:
■ For information about retrieving data from a result set or callable statement object into oracle.sql.* types, as opposed to Java types, see Chapter 6, "Accessing and Manipulating Oracle Data".
■ The LONG and LONGRAW SQL types and REFCURSOR type category have no oracle.sql.* classes. Use standard JDBC functionality for these types. For example, retrieve LONG or LONGRAW data as input streams using the standard JDBC result set and callable statement methods getBinaryStream() and getCharacterStream(). Use the getCursor() method for REFCURSOR types.
Oracle JDBC Packages and Classes
■ data storage as Java byte arrays for SQL data
■ a getBytes() method, which returns the SQL data as a byte array (in the raw format in which JDBC received the data from the database)
■ a toJdbc() method that converts the data into an object of a corresponding Java class as defined in the JDBC specification
The JDBC driver does not convert Oracle-specific datatypes that are not part of the JDBC specification, such as ROWID; the driver returns the object in the corresponding oracle.sql.* format. For example, it returns an Oracle ROWID as an oracle.sql.ROWID.
■ appropriate xxxValue() methods to convert SQL data to Java typed—for example: stringValue(), intValue(), booleanValue(), dateValue(), bigDecimalValue()
■ additional conversion, getXXX() and setXXX() methods as appropriate for the functionality of the datatype (such as methods in the LOB classes that get the data as a stream, and methods in the REF class that get and set object data through the object reference)
Refer to the Oracle JDBC Javadoc for additional information about these classes. See
"Class oracle.sql.CHAR" on page 5-29 to learn how the oracle.sql.CHAR class supports character data.
Overview of Class oracle.sql.STRUCT
For any given Oracle object type, it is usually desirable to define a custom mapping between SQL and Java. (If you use a SQLData custom Java class, the mapping must be defined in a type map.)
If you choose not to define a mapping, however, then data from the object type will be materialized in Java in an instance of the oracle.sql.STRUCT class.
The STRUCT class implements the standard JDBC 2.0 java.sql.Struct interface (oracle.jdbc2.Struct under JDK 1.1.x) and extends the oracle.sql.Datum class.
In the database, Oracle stores the raw bytes of object data in a linearized form. A STRUCT object is a wrapper for the raw bytes of an Oracle object. It contains the SQL type name of the Oracle object and a "values" array of oracle.sql.Datum objects that hold the attribute values in SQL format.
You can materialize a STRUCT’s attributes as oracle.sql.Datum[] objects if you use the getOracleAttributes() method, or as java.lang.Object[] objects
if you use the getAttributes() method. Materializing the attributes as
oracle.sql.* objects gives you all the advantages of the oracle.sql.* format:
■ Materializing oracle.sql.STRUCT data in oracle.sql.* format
completely preserves data by maintaining it in SQL format. No translation is performed. This is useful if you want to access data but not necessarily display it.
■ It allows complete flexibility in how your Java application unpacks data.
In some cases, you might want to manually create a STRUCT object and pass it to a prepared statement or callable statement. To do this, you must also create a StructDescriptor object.
For more information about working with Oracle objects using the
oracle.sql.STRUCT and StructDescriptor classes, see "Using the Default STRUCT Class for Oracle Objects" on page 8-3.
Overview of Class oracle.sql.REF
The oracle.sql.REF class is the generic class that supports Oracle object references. This class, as with all oracle.sql.* datatype classes, is a subclass of the oracle.sql.Datum class. It implements the standard JDBC 2.0
java.sql.Ref interface (oracle.jdbc2.Ref under JDK 1.1.x).
The REF class has methods to retrieve and pass object references. Be aware,
however, that selecting an object reference retrieves only a pointer to an object. This does not materialize the object itself. But the REF class also includes methods to retrieve and pass the object data.
Notes:
■ Elements of the values array, although of the generic Datum type, actually contain data associated with the relevant oracle.sql.* type appropriate for the given attribute. You can cast the element to the appropriate oracle.sql.* type as desired. For example, a CHAR data attribute within the STRUCT is materialized as oracle.sql.Datum. To use it as CHAR data, you must cast it to the oracle.sql.CHAR type.
■ Nested objects in the values array of a STRUCT object are materialized by the JDBC driver as instances of STRUCT.
Oracle JDBC Packages and Classes
You cannot create REF objects in your JDBC application—you can only retrieve existing REF objects from the database.
For more information about working with Oracle object references using the oracle.sql.REF class, see Chapter 9, "Working with Oracle Object References".
Overview of Class oracle.sql.ARRAY
The oracle.sql.ARRAY class supports Oracle collections—either VARRAYs or nested tables. If you select either a VARRAY or nested table from the database, then the JDBC driver materializes it as an object of the ARRAY class; the structure of the data is equivalent in either case. The oracle.sql.ARRAY class extends
oracle.sql.Datum and implements the standard JDBC 2.0 java.sql.Array interface (oracle.jdbc2.Array under JDK 1.1.x).
You can use the setARRAY() method of the OraclePreparedStatement or OracleCallableStatement class to pass an array as an input parameter to a prepared statement. Similarly, you might want to manually create an ARRAY object to pass it to a prepared statement or callable statement, perhaps to insert into the database. This involves the use of ArrayDescriptor objects.
For more information about working with Oracle collections using the
oracle.sql.ARRAY and ArrayDescriptor classes, see "Overview of Collection (Array) Functionality" on page 10-5.
Overview of Classes oracle.sql.BLOB, oracle.sql.CLOB, oracle.sql.BFILE
BLOBs and CLOBs (referred to collectively as "LOBs"), and BFILEs (for external files) are for data items that are too large to store directly in a database table.
Instead, the database table stores a locator that points to the location of the actual data.
The oracle.sql package supports these datatypes in several ways:
■ BLOBs point to large unstructured binary data items and are supported by the oracle.sql.BLOB class.
■ CLOBs point to large fixed-width character data items (that is, characters that require a fixed number of bytes per character) and are supported by the oracle.sql.CLOB class.
■ BFILEs point to the content of external files (operating system files) and are supported by the oracle.sql.BFILE class.
You can select a BLOB, CLOB, or BFILE locator from the database using a standard SELECT statement, but bear in mind that you are receiving only the locator, not the data itself. Additional steps are necessary to retrieve the data.
For information about how to access and manipulate locators and data for LOBs and BFILEs, see Chapter 7, "Working with LOBs and BFILEs".
Classes oracle.sql.DATE, oracle.sql.NUMBER, and oracle.sql.RAW
These classes map to primitive SQL datatypes, which are a part of standard JDBC, and supply conversions to and from the corresponding JDBC Java types. For more information, see the Javadoc.
Classes oracle.sql.TIMESTAMP, oracle.sql.TIMESTAMPTZ, and oracle.sql.TIMESTAMPLTZ
The Oracle9i JDBC drivers support the following date/time datatypes:
■ Timestamp (TS)
■ Timestamp with Time Zone (TSTZ)
■ TIMESTAMP with Local Time Zone (TSLTZ)
Oracle9i JDBC drivers allow conversions among DATE and date/time datatypes.
For example, you can access a TIMESTAMPTZ column as a DATE value.
Oracle9i JDBC drivers support the most popular time zone names used in the industry as well as most of the time zone names defined in the JDK from Sun Microsystems. Time zones are specified by using the java.util.Calendar class.
The following code shows how the TimeZone and Calendar objects are created for US_PACIFIC, which is a time zone name not defined in the JDK:
TimeZone tz = TimeZone.getDefault();
tz.setID("US_PACIFIC");
GregorianCalendar gcal = new GregorianCalendar(tz);
The following classes are for the date/time data types:
■ oracle.sql.TIMESTAMP
Note: Do not use TimeZone.getTimeZone to create a time zone object because there are more Oracle time zone names than Sun has defined in its JDK.
Oracle JDBC Packages and Classes
■ oracle.sql.TIMESTAMPTZ
■ oracle.sql.TIMESTAMPLTZ
Use the following methods from the oracle.jdbc.OraclePreparedStatement interface to set a date/time item:
■ setTIMESTAMP(int paramIdx,TIMESTAMP x)
■ setTIMESTAMPTZ(int paramIdx,TIMESTAMPTZ x)
■ setTIMESTAMPLTZ(int paramIdx,TIMESTAMPLTZ x)
Use the following methods from the oracle.jdbc.OracleCallableStatement interface to get a date/time item:
■ TIMESTAMP getTIMESTAMP (int paramIdx)
■ TIMESTAMPTZ getTIMESTAMPTZ(int paramIdx)
■ TIMESTAMPLTZ getTIMESTAMPLTZ(int paramIdx)
Use the following methods from the oracle.jdbc.OracleResultSet interface to get a date/time item:
■ TIMESTAMP getTIMESTAMP(int paramIdx)
■ TIMESTAMP getTIMESTAMP(java.lang.String colName)
■ TIMESTAMPTZ getTIMESTAMPTZ(int paramIdx)
■ TIMESTAMPTZ getTIMESTAMPTZ(java.lang.String colName)
■ TIMESTAMPLTZ getTIMESTAMPLTZ(int paramIdx)
■ TIMESTAMPLTZ getTIMESTAMPLTZ(java.lang.String colName)
■ TIMESTAMPLTZ getTIMESTAMPLTZ(int paramIdx) Before accessing TIMESTAMPLTZ data, call the
OracleConnection.setSessionTime() method to set the session time zone.
When this method is called, the JDBC driver sets the session time zone of the connection and saves the session time zone so that any TIMESTAMPLTZ data accessed through JDBC can be adjusted using the session time zone.
Overview of Class oracle.sql.ROWID
This class supports Oracle ROWIDs, which are unique identifiers for rows in database tables. You can select a ROWID as you would select any column of data from the table. Note, however, that you cannot manually update ROWIDs—the
The oracle.sql.ROWID class does not implement any noteworthy functionality beyond what is in the oracle.sql.Datum superclass. However, ROWID does provide a stringValue() method that overrides the stringValue() method in the oracle.sql.Datum class and returns the hexadecimal representation of the ROWID bytes.
For information about accessing ROWID data, see "Oracle ROWID Type" on page 5-33.
Class oracle.sql.OPAQUE
The oracle.sql.OPAQUE class gives you the name and characteristics of the OPAQUE type and any attributes. OPAQUE types provide access only to the uninterrupted bytes of the instance.
The following are the methods of the oracle.sql.OPAQUE class:
■ getBytesValue(): Returns a byte array that represents the value of the OPAQUE object, in the format used in the database.
■ public boolean isConvertibleTo(Class jClass): Determines if a Datum object can be converted to a particular class, where Class is any class and jClass is the class to convert. true is returned if conversion to jClass is permitted and false if conversion to jClass is not permitted.
■ getDescriptor(): Returns the OpaqueDescriptor object that contains the type information.
■ getJavaSqlConnection(): Returns the connection associated with the receiver. Because methods that use the oracle.jdbc.driver package are deprecated, the getConnection() method has been deprecated in favor of the getJavaSqlConnection() method.
■ getSQLTypeName(): Implements the java.sql.Struct interface function and retrieves the SQL type name of the SQL structured type that this Struct object represents. This method returns the fully-qualified type name of the SQL structured type which this STRUCT object represents.
■ getValue(): Returns a Java object that represents the value (raw bytes).
■ toJdbc(): Returns the JDBC representation of the Datum object.
Note: For Oracle9i 9.0.1, there is minimal support for OPAQUE types.
Oracle JDBC Packages and Classes
Package oracle.jdbc
The interfaces of the oracle.jdbc package provide Oracle-specific extensions to allow access to raw SQL format data by using oracle.sql.* objects.
For the oracle.jdbc package, Table 5–2 lists key interfaces and classes used for connections, statements, and result sets.
Note: The interfaces of the oracle.jdbc package replace the deprecated classes of the oracle.jdbc.driver package found in previous releases. (See "Package oracle.jdbc" on page 1-13 for more information.)
Table 5–2 Key Interfaces and Classes of the oracle.jdbc Package
Name
Interface
or Class Key Functionality
OracleDriver Class implements java.sql.Driver OracleConnection Interface methods to return Oracle statement
objects; methods to set Oracle
performance extensions for any statement executed in the current connection (implements
java.sql.Connection) OracleStatement Interface methods to set Oracle performance
extensions for individual statement;
superclass of
OraclePreparedStatement and OracleCallableStatement (implements java.sql.Statement) OraclePreparedStatement Interface setXXX() methods to bind
oracle.sql.* types into a prepared statement (implements
java.sql.PreparedStatement; extends OracleStatement; superclass of
OracleCallableStatement)
The remainder of this section describes the interfaces and classes of the
oracle.jdbc package. For more information about using these interfaces and classes to access Oracle type extensions, see Chapter 6, "Accessing and
Manipulating Oracle Data".