Oracle8i JDBC Developer's Guide and Reference Release 2 (8.1.6) A81354-01 |
|
This section describes the Java packages that support the Oracle JDBC extensions and the key classes that are included in these packages:
You can refer to the Oracle JDBC Javadoc for more information about all the classes mentioned in this section.
The oracle.sql
package supports direct access to data in SQL format. This package consists primarily of classes that provide Java mappings to SQL datatypes.
Essentially, the classes act as Java wrappers for the raw SQL data. Because data in an oracle.sql.*
object remains in SQL format, no information is lost. For SQL primitive types, these classes simply wrap the SQL data. For SQL structured types (objects and arrays), they provide additional information such as conversion methods and details of structure.
Each of the oracle.sql.*
datatype classes extends oracle.sql.Datum
, a superclass that encapsulates functionality common to all the datatypes. Some of the classes are for JDBC 2.0-compliant datatypes. These classes, as Table 5-1 indicates, implement standard JDBC 2.0 interfaces in the java.sql
package (oracle.jdbc2
for JDK 1.1.x), as well as extending the oracle.sql.Datum
class.
Table 5-1 lists the oracle.sql
datatype classes and their corresponding Oracle SQL types.
You can find more detailed information about each of these classes later in this chapter. Additional details about use of the Oracle extended types (STRUCT
, REF
, ARRAY
, BLOB
, CLOB
, BFILE
, and ROWID
) are described in the following locations:
Notes:
oracle.sql.*
types, as opposed to Java types, see Chapter 6, "Accessing and Manipulating Oracle Data".
LONG
and LONG
RAW
SQL types and REF
CURSOR
type category have no oracle.sql.*
classes. Use standard JDBC functionality for these types. For example, retrieve LONG
or LONG
RAW
data as input streams using the standard JDBC result set and callable statement methods getAsciStream()
, getBinaryStream()
, getUnicodeStream()
, and getCharacterStream()
. Use getCursor()
for REF
CURSOR
types.
In addition to the datatype classes, the oracle.sql
package includes the following support classes and interfaces, primarily for use with objects and collections:
oracle.sql.ArrayDescriptor
class: Used in constructing oracle.sql.ARRAY
objects; describes the SQL type of the array. (For information, see "Creating ARRAY Objects and Descriptors".)
oracle.sql.StructDescriptor
class: Used in constructing oracle.sql.STRUCT
objects, which you can use as a default mapping to Oracle objects in the database. (For information, see "Creating STRUCT Objects and Descriptors".)
oracle.sql.CustomDatum
and oracle.sql.CustomDatumFactory
interfaces: Used in Java classes implementing the Oracle CustomDatum
scenario of Oracle object support. (The other possible scenario is the JDBC-standard SQLData
implementation.) See "Understanding the CustomDatum Interface" for more information on CustomDatum
.
Each of the Oracle datatype classes provides, among other things, the following:
getBytes()
method, which returns the SQL data as a byte array (in the raw format in which JDBC received the data from the database)
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
.
xxxValue()
methods to convert SQL data to Java typed--for example: stringValue()
, intValue()
, booleanValue()
, dateValue()
, bigDecimalValue()
get
, and set
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.
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:
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.
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".
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.
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".
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".
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:
oracle.sql.BLOB
class.
oracle.sql.CLOB
class.
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".
The oracle.sql.CHAR
class is used by Oracle JDBC in handling and converting string and character data. JDBC constructs and populates CHAR
objects once character data has been read from the database.
CHAR
objects that the driver constructs and returns can be in the database character set, UTF-8
, or ISO-Latin-1 (WE8ISO8859P1
). CHAR
objects that are Oracle8 object attributes are returned in the database character set.
A JDBC application will rarely need to construct CHAR
objects directly, because the JDBC driver creates CHAR
objects automatically as character data items are obtained from the database. There may be circumstances, however, where constructing CHAR
objects directly is more efficient--for example, to repeatedly pass the same character data to one or more prepared statements without the overhead of converting from Java strings each time.
The CHAR
class has special functionality for NLS conversion of character data. A key attribute of the CHAR
class, and a parameter always passed in when a CHAR
object is constructed, is the NLS character set used in presenting the character data. Without the character set being known, the bytes of data in the CHAR
object are meaningless.
The oracle.sql.CharacterSet
class is instantiated to represent character sets. When you construct a CHAR
object, you must provide character set information to the CHAR
object by way of an instance of the CharacterSet
class. Each instance of this class represents one of the NLS character sets that Oracle supports. A CharacterSet
instance encapsulates methods and attributes of the character set, mainly involving functionality to convert to or from other character sets. You can find a complete list of the character sets that Oracle supports in the Oracle8i National Language Support Guide.
Follow these general steps to construct a CHAR
object:
CharacterSet
object by calling the static CharacterSet.make()
method. This method is a factory for the character set class. The make()
method takes as input an integer Oracle ID that corresponds to a character set that Oracle supports. For example:
int oracleId = CharacterSet.JA16SJIS_CHARSET; // this is character set 832 ... CharacterSet mycharset = CharacterSet.make(oracleId);
Each character set that Oracle supports has a unique predefined Oracle ID.
For more information on character sets and character set IDs, see the Oracle8i National Language Support Guide.
CHAR
object. Pass to the constructor a string (or the bytes that represent the string) and the CharacterSet
object that indicates how to interpret the bytes based on the character set. For example:
String mystring = "teststring"; ... CHAR mychar = new CHAR(teststring, mycharset);
The CHAR
class has multiple constructors--they can take a string, a byte array, or an object as input along with the CharacterSet
object. In the case of a string, the string is converted to the character set indicated by the CharacterSet
object before being placed into the CHAR
object.
Refer to the CHAR
class Javadoc for more information.
The CHAR
class provides these methods for translating character data to strings:
getString()
: Converts the sequence of characters represented by the CHAR
object to a string, returning a Java String
object. If the character set is not recognized (that is, if you entered an invalid OracleID
), then getString()
throws a SQLException
.
toString()
: Identical to getString()
, but if the character set is not recognized (that is, if you entered an invalid OracleID
), then toString()
returns a hexadecimal representation of the CHAR
data and does not throw a SQLException
.
getStringWithReplacement()
: Identical to getString()
, except a default replacement character replaces characters that have no Unicode representation in the character set of this CHAR
object. This default character varies from character set to character set, but is often a question mark.
The server (database) and the client (or application running on the client) can use different character sets. When you use the methods of this class to transfer data between the server and the client, the JDBC drivers must convert the data from the server character set to the client character set (or the reverse). To convert the data, the drivers use Oracle's National Language Support (NLS). For more information on how the JDBC drivers convert between character sets, see "JDBC and NLS". For more information on NLS, see the Oracle8i National Language Support Guide.
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.
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 Oracle database updates them automatically as appropriate.
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".
The oracle.jdbc.driver
package includes classes that add extended features to enable data access in oracle.sql
format. In addition, these classes provide Oracle-specific extensions to allow access to raw SQL format data by using oracle.sql.*
objects.
Table 5-2 lists key classes in this package for connections, statements, and result sets.
The oracle.jdbc.driver
package additionally includes the OracleTypes
class, which defines integer constants used to identify SQL types. For standard types, it uses the same values as the standard java.sql.Types
class. In addition, it adds constants for Oracle extended types.
The remainder of this section describes the classes of the oracle.jdbc.driver
package. For more information about using these classes to access Oracle type extensions, see Chapter 6, "Accessing and Manipulating Oracle Data".
Use this class to register the Oracle JDBC drivers for use by your application. You can input a new instance of this class to the static registerDriver()
method of the java.sql.DriverManager
class so that your application can access and use the Oracle drivers. The registerDriver()
method takes as input a "driver" class, that is, a class that implements the java.sql.Driver
interface, as is the case with OracleDriver
.
Once you register the Oracle JDBC drivers, you can create your connection using the DriverManager
class. For more information on registering drivers and writing a connection string, see "First Steps in JDBC".
This class extends standard JDBC connection functionality to create and return Oracle statement objects, set flags and options for Oracle performance extensions, and support type maps for Oracle objects.
"Additional Oracle Performance Extensions" describes the performance extensions, including row prefetching, update batching, and metadata TABLE_REMARKS
reporting.
Key methods include:
createStatement()
: Allocates a new OracleStatement
object.
prepareStatement()
: Allocates a new OraclePreparedStatement
object.
prepareCall()
: Allocates a new OracleCallableStatement
object.
getTypeMap()
: Retrieves the type map for this connection (for use in mapping Oracle object types to Java classes).
setTypeMap()
: Initializes or updates the type map for this connection (for use in mapping Oracle object types to Java classes).
getTransactionIsolation()
: Gets this connection's current isolation mode.
setTransactionIsolation()
: Changes the transaction isolation level using one of the TRANSACTION_*
values.
These oracle.jdbc.driver.OracleConnection
methods are Oracle-defined extensions:
getDefaultExecuteBatch()
: Retrieves the default update-batching value for this connection.
setDefaultExecuteBatch()
: Sets the default update-batching value for this connection.
getDefaultRowPrefetch()
: Retrieves the default row-prefetch value for this connection.
setDefaultRowPrefetch()
: Sets the default row-prefetch value for this connection.
getRemarksReporting()
: Returns true if TABLE_REMARKS
reporting is enabled.
setRemarksReporting()
: Enables or disables TABLE_REMARKS
reporting.
This class extends standard JDBC statement functionality and is the superclass of the OraclePreparedStatement
and OracleCallableStatement
classes. Extended functionality includes support for setting flags and options for Oracle performance extensions on a statement-by-statement basis, as opposed to the OracleConnection
class that sets these on a connection-wide basis.
"Additional Oracle Performance Extensions" describes the performance extensions, including row prefetching and column type definitions.
Key methods include:
executeQuery()
: Executes a database query and returns an OracleResultSet
object.
getResultSet()
: Retrieves an OracleResultSet
object.
close()
: Closes the current statement.
These oracle.jdbc.driver.OracleStatement
methods are Oracle-defined extensions:
defineColumnType()
: Defines the type you will use to retrieve data from a particular database table column.
getRowPrefetch()
: Retrieves the row-prefetch value for this statement.
setRowPrefetch()
: Sets the row-prefetch value for this statement.
This class extends standard JDBC prepared statement functionality, is a subclass of the OracleStatement
class, and is the superclass of the OracleCallableStatement
class. Extended functionality consists of set
methods for binding oracle.sql.*
types and objects into prepared statements, and methods to support Oracle performance extensions on a statement-by-statement basis.
"Additional Oracle Performance Extensions" describes the performance extensions, including database update batching.
Key methods include:
getExecuteBatch()
: Retrieves the update-batching value for this statement.
setExecuteBatch()
: Sets the update-batching value for this statement.
setOracleObject()
: This is a generic set
method for binding oracle.sql.*
data into a prepared statement as an oracle.sql.Datum
object.
setXXX()
: These methods, such as setBLOB()
, are for binding specific oracle.sql.*
types into prepared statements.
setCustomDatum()
: Binds a CustomDatum
object (for use in mapping Oracle object types to Java) into a prepared statement.
setNull()
: Sets the value of the object specified by its SQL type name to NULL
. For setNull(
param_index
, type_code
, sql_type_name
), if type_code
is REF
, ARRAY
, or STRUCT
, then sql_type_name
is the fully qualified name (schema.sql_type_name
) of the SQL type.
close()
: Closes the current statement.
This class extends standard JDBC callable statement functionality and is a subclass of the OracleStatement
and OraclePreparedStatement
classes. Extended functionality includes set
methods for binding structured objects and oracle.sql
.* objects into prepared statements, and get
methods for retrieving data into oracle.sql.*
objects.
Key methods include:
getOracleObject()
: This is a generic get
method for retrieving data into an oracle.sql.Datum
object, which can be cast to the specific oracle.sql.*
type as necessary.
getXXX()
: These methods, such as getCLOB()
, are for retrieving data into specific oracle.sql.*
objects.
setOracleObject()
: This is a generic set
method for binding oracle.sql.*
data into a callable statement as an oracle.sql.Datum
object.
setXXX()
: These methods, such as setBLOB()
, are inherited from OraclePreparedStatement
for binding specific oracle.sql.*
objects into callable statements.
setNull()
: Sets the value of the object specified by its SQL type name to NULL
. For setNull(
param_index
, type_code
, sql_type_name
), if type_code
is REF
, ARRAY
, or STRUCT
, then sql_type_name
is the fully qualified (schema.type
) name of the SQL type.
registerOutParameter()
: Registers the SQL typecode of the statement's output parameter. JDBC requires this for any callable statement with an OUT
parameter. It takes an integer parameter index (the position of the output variable in the statement, relative to the other parameters) and an integer SQL type (the type constant defined in oracle.jdbc.driver.OracleTypes
).
This is an overloaded method. One version of this method is for named types only--when the SQL typecode is OracleTypes.REF
, STRUCT
, or ARRAY
. In this case, in addition to a parameter index and SQL type, the method also takes a String
SQL type name (the name of the Oracle user-defined type in the database, such as EMPLOYEE
).
close()
: Closes the current result set, if any, and the current statement.
This class extends standard JDBC result set functionality, implementing get
methods for retrieving data into oracle.sql.*
objects.
Key methods include:
getOracleObject()
: This is a generic get
method for retrieving data into an oracle.sql.Datum
object. It can be cast to the specific oracle.sql.*
type as necessary.
getXXX()
: These methods, such as getCLOB()
, are for retrieving data into oracle.sql.*
objects.
This class extends standard JDBC result set metadata functionality to retrieve information about Oracle result set objects.
Key methods include the following:
getColumnCount()
: Returns the number of columns in an Oracle result set.
getColumnName()
: Returns the name of a specified column in an Oracle result set.
getColumnType()
: Returns the SQL type of a specified column in an Oracle result set. If the column stores an Oracle object or collection, then this method returns OracleTypes.STRUCT
or OracleTypes.ARRAY
respectively.
getColumnTypeName()
: Returns the SQL type name of the data stored in the column. If the column stores an array or collection, then this method returns its SQL type name. If the column stores REF
data, then this method returns the SQL type name of the objects to which the object reference points.
getTableName()
: Returns the name of the table from which an Oracle result set column was selected.
The OracleTypes
class defines constants that JDBC uses to identify SQL types. Each variable in this class has a constant integer value. The oracle.jdbc.driver.OracleTypes
class duplicates the typecode definitions of the standard Java java.sql.Types
class and contains these additional typecodes for Oracle extensions:
As in java.sql.Types
, all the variable names are in all-caps.
JDBC uses the SQL types identified by the elements of the OracleTypes
class in two main areas: registering output parameters, and in the setNull()
method of the PreparedStatement
class.
The typecodes in java.sql.Types
or oracle.jdbc.driver.OracleTypes
identify the SQL types of the output parameters in the registerOutParameter()
method of the java.sql.CallableStatement
interface and oracle.jdbc.driver.OracleCallableStatement
class.
These are the forms that registerOutputParameter()
can take for CallableStatement
and OracleCallableStatement
(assume a standard callable statement object cs
):
cs.registerOutParameter(intindex
, intsqlType
); cs.registerOutParameter(intindex
, intsqlType
, Stringsql_name
); cs.registerOutParameter(intindex
, intsqlType
, intscale
);
In these signatures, index
represents the parameter index, sqlType
is the typecode for the SQL datatype, sql_name
is the name given to the datatype (for user-defined types, when sqlType
is a STRUCT
, REF
, or ARRAY
typecode), and scale
represents the number of digits to the right of the decimal point (when sqlType
is a NUMERIC
or DECIMAL
typecode).
The following example uses a CallableStatement
to call a procedure named charout
, which returns a CHAR
datatype. Note the use of the OracleTypes.CHAR
typecode in the registerOutParameter()
method (although java.sql.Types.CHAR
could have been used as well).
CallableStatement cs = conn.prepareCall ("BEGIN charout (?); END;"); cs.registerOutParameter (1, OracleTypes.CHAR); cs.execute (); System.out.println ("Out argument is: " + cs.getString (1));
The next example uses a CallableStatement
to call structout
, which returns a STRUCT
datatype. The form of registerOutParameter()
requires you to specify the typecode (Types.STRUCT
or OracleTypes.STRUCT
), as well as the SQL name (EMPLOYEE
).
The example assumes that no type mapping has been declared for the EMPLOYEE
type, so it is retrieved into a STRUCT
datatype. To retrieve the value of EMPLOYEE
as an oracle.sql.STRUCT
object, the statement object cs
is cast to an OracleCallableStatement
and the Oracle extension getSTRUCT()
method is invoked.
CallableStatement cs = conn.prepareCall ("BEGIN structout (?); END;"); cs.registerOutParameter (1, OracleTypes.STRUCT, "EMPLOYEE"); cs.execute (); // get the value into a STRUCT because it // is assumed that no type map has been defined STRUCT emp = ((OracleCallableStatement)cs).getSTRUCT (1);
The typecodes in Types
and OracleTypes
identify the SQL type of the data item, which the setNull()
method sets to NULL
. The setNull()
method can be found in the java.sql.PreparedStatement
interface and the oracle.jdbc.driver.OraclePreparedStatement
class.
These are the forms that setNull()
can take for PreparedStatement
and OraclePreparedStatement
objects (assume a standard prepared statement object ps
):
ps.setNull(intindex
, intsqlType
); ps.setNull(intindex
, intsqlType
, Stringsql_name
);
In these signatures, index
represents the parameter index, sqlType
is the typecode for the SQL datatype, and sql_name
is the name given to the datatype (for user-defined types, when sqlType
is a STRUCT
, REF
, or ARRAY
typecode). If you enter an invalid sqlType
, a Parameter Type Conflict
exception is thrown.
The following example uses a PreparedStatement
to insert a NULL
numeric value into the database. Note the use of OracleTypes.NUMERIC
to identify the numeric object set to NULL
(although Types.NUMERIC
could have been used as well).
PreparedStatement pstmt = conn.prepareStatement ("INSERT INTO num_table VALUES (?)"); pstmt.setNull (1, OracleTypes.NUMERIC); pstmt.execute ();
In this example, the prepared statement inserts a NULL
STRUCT
object of type EMPLOYEE
into the database.
PreparedStatement pstmt = conn.prepareStatement ("INSERT INTO employee_table VALUES (?)"); pstmt.setNull (1, OracleTypes.STRUCT, "EMPLOYEE"); pstmt.execute ();
The oracle.jdbc2
package is an Oracle implementation for use with JDK 1.1.x, containing classes and interfaces that mimic JDBC 2.0 classes and interfaces (which exist in the JDK 1.2 version of the standard java.sql
package).
The following interfaces are implemented by oracle.sql.*
type classes for JDBC 2.0-compliant Oracle type extensions under JDK 1.1.x.
oracle.jdbc2.Array
is implemented by oracle.sql.ARRAY
oracle.jdbc2.Struct
is implemented by oracle.sql.STRUCT
oracle.jdbc2.Ref
is implemented by oracle.sql.REF
oracle.jdbc2.Clob
is implemented by oracle.sql.CLOB
oracle.jdbc2.Blob
is implemented by oracle.sql.BLOB
In addition, the oracle.jdbc2
package includes the following interfaces for users employing the JDBC-standard SQLData
interface to create Java classes that map to Oracle objects. Again, these interfaces mimic java.sql
interfaces available with JDK 1.2:
oracle.jdbc2.SQLData
is implemented by classes that map to Oracle objects; users must provide this implementation
oracle.jdbc2.SQLInput
is implemented by classes that read object data; Oracle provides a SQLInput
class that the JDBC drivers use
oracle.jdbc2.SQLOutput
is implemented by classes that write object data; Oracle provides a SQLOutput
class that the JDBC drivers use
The SQLData
interface is one of the two facilities you can use to support Oracle objects in Java. The other choice is the Oracle CustomDatum
interface, included in the oracle.sql
package. See "Understanding the SQLData Interface" for more information about SQLData
, SQLInput
, and SQLOutput
.