Oracle8i JDBC Developer's Guide and Reference
Release 2 (8.1.6)
A81354-01

Library
Product
Contents
Index

Prev Chap Top Next

Oracle JDBC Packages and Classes

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.

Package oracle.sql

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.

Classes of the oracle.sql Package

Table 5-1 lists the oracle.sql datatype classes and their corresponding Oracle SQL types.

Table 5-1 Oracle Datatype Classes
Java Class  Oracle SQL Type and Interfaces Implemented if for JDBC 2.0 

oracle.sql.STRUCT  

STRUCT (objects) (JDBC 2.0)
implements java.sql.Struct (oracle.jdbc2.Struct under JDK 1.1.x)  

oracle.sql.REF  

REF (object references) (JDBC 2.0)
implements java.sql.Ref (oracle.jdbc2.Ref under JDK 1.1.x)  

oracle.sql.ARRAY  

VARRAY or nested table (collections) (JDBC 2.0)
implements java.sql.Array (oracle.jdbc2.Array under JDK 1.1.x)  

oracle.sql.BLOB  

BLOB (binary large objects) (JDBC 2.0)
implements java.sql.Blob (oracle.jdbc2.Blob under JDK 1.1.x)  

oracle.sql.CLOB  

CLOB (character large objects) (JDBC 2.0)
implements java.sql.Clob (oracle.jdbc2.Clob under JDK 1.1.x)  

oracle.sql.BFILE  

BFILE (external files)  

oracle.sql.CHAR  

CHAR, VARCHAR2  

oracle.sql.DATE  

DATE  

oracle.sql.NUMBER  

NUMBER  

oracle.sql.RAW  

RAW  

oracle.sql.ROWID  

ROWID (row identifiers)  

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:

In addition to the datatype classes, the oracle.sql package includes the following support classes and interfaces, primarily for use with objects and collections:

General oracle.sql.* Datatype Support

Each of the Oracle datatype classes provides, among other things, the following:

Refer to the Oracle JDBC Javadoc for additional information about these classes.

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:

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".

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.

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".

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:

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".

Class oracle.sql.CHAR

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.

CHAR Objects and Character Sets

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.

Constructing a CHAR

Follow these general steps to construct a CHAR object:

  1. Create a 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.

    Note:

    If you enter an invalid ID, an exception will not be thrown. Instead, when you try to use the character set, you will receive unpredictable results.  

    For more information on character sets and character set IDs, see the Oracle8i National Language Support Guide.

  2. Construct a 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.

    Notes:

    • The CharacterSet object cannot be null.

    • The CharacterSet class is an abstract class, therefore it has no constructor. The only way to create instances is to use the make() method.

    • The server recognizes the special value CharacterSet.DEFAULT_CHARSET as the database character set. For the client, this value is not meaningful.

    • Oracle does not intend or recommend that users extend the CharacterSet class.

     

CHAR Conversion Methods

The CHAR class provides these methods for translating character data to strings:

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.

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.

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 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".

Package oracle.jdbc.driver

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.

Table 5-2 Connection, Statement, and Result Set Classes
Class  Key Functionality  

OracleDriver  

implements java.sql.Driver  

OracleConnection  

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  

methods to set Oracle performance extensions for individual statement; superclass of OraclePreparedStatement and OracleCallableStatement (implements java.sql.Statement)  

OraclePreparedStatement  

set methods to bind oracle.sql.* types into a prepared statement (implements java.sql.PreparedStatement; extends OracleStatement; superclass of OracleCallableStatement)  

OracleCallableStatement  

get methods to retrieve data in oracle.sql format; set methods to bind oracle.sql.* types into a callable statement (implements java.sql.CallableStatement; extends OraclePreparedStatement)  

OracleResultSet  

get methods to retrieve data in oracle.sql format (implements java.sql.ResultSet)  

OracleResultSetMetaData  

methods to get meta information about Oracle result sets, such as column names and datatypes (implements java.sql.ResultSetMetaData)  

OracleDatabaseMetaData  

methods to get meta information about the database, such as database product name/version, table information, and default transaction isolation level (implements java.sql.DatabaseMetaData)  

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".

Class oracle.jdbc.driver.OracleDriver

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".

Class oracle.jdbc.driver.OracleConnection

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:

These oracle.jdbc.driver.OracleConnection methods are Oracle-defined extensions:

Class oracle.jdbc.driver.OracleStatement

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:

These oracle.jdbc.driver.OracleStatement methods are Oracle-defined extensions:

Class oracle.jdbc.driver.OraclePreparedStatement

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:

Class oracle.jdbc.driver.OracleCallableStatement

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:

Class oracle.jdbc.driver.OracleResultSet

This class extends standard JDBC result set functionality, implementing get methods for retrieving data into oracle.sql.* objects.

Key methods include:

Class oracle.jdbc.driver.OracleResultSetMetaData

This class extends standard JDBC result set metadata functionality to retrieve information about Oracle result set objects.

Key methods include the following:

Class oracle.jdbc.driver.OracleTypes

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.

OracleTypes and Registering Output Parameters

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(int index, int sqlType);

cs.registerOutParameter(int index, int sqlType, String sql_name);

cs.registerOutParameter(int index, int sqlType, int scale);

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).

Note:

The second signature is standard under JDBC 2.0 in a JDK 1.2.x environment, but is an Oracle extension under JDK 1.1.x.  

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);
OracleTypes and the setNull() Method

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(int index, int sqlType);

ps.setNull(int index, int sqlType, String sql_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.

Note:

The second signature is standard under JDBC 2.0 in a JDK 1.2.x environment, but is an Oracle extension under JDK 1.1.x.  

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 ();

Package oracle.jdbc2 (for JDK 1.1.x only)

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.

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:

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.


Prev
Top
Next
Oracle
Copyright © 1999 Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index