SQL CHAR types are fixed-length character data of maximum 2,000 bytes or characters and minimum 1 byte or character. If you insert a value that is shorter than the specified column length, Oracle will blank-pad to match the length. The Oracle JDBC furnishes the oracle.sql.CHAR wrapper class and the corresponding getCHAR(), setCHAR(), setCHARAtName, and updateCHAR() methods in OraclePreparedStatement, OracleCallableStatement, and OracleResultSet classes. The oracle.sql.CHAR class furnishes getString(), toString(), and getStringWithReplacement() methods for converting character data into Java strings.

As Table 3.2 indicates, a CHAR column can also be mapped to and from the following standard Java types using the corresponding setxxx(), updatexxx(), and getxxx() methods defined in PreparedStatement, CallableStatement, and ResultSet interfaces: java.lang.String, java.sql.Date, java.sql.Time, java.sql.Timestamp, java.lang.Byte, java.lang.Short, java.lang.Integer, java.lang.Long, java.sql.Float, java.lang.Double, java.math.BigDecimal, byte, short, int, long, float, and double.

The following code snippet excerpted from the complete code sample in Chapter 3 illustrates retrieving and updating CHAR columns:

  /*
   * Update SQL CHAR column with oracle.sql.CHAR
   */
    OraclePreparedStatement ops = (OraclePreparedStatement)
      conn.prepareStatement("UPDATE TypesTab SET xchar = ?");
    ops.setCHAR (1, c);
    ops.execute();
    ops.close();


  /*
   * Retrieve SQL CHAR column as oracle.sq.CHAR
   */
    OracleStatement ostmt = (OracleStatement)
conn.createStatement();
    String ochar = null;
    OracleResultSet ors =
     (OracleResultSet) ostmt.executeQuery("SELECT xchar FROM
TypesTab");
    while (ors.next())
    {
      ochar = ors.getString(1);
    }
    ostmt.close();
    // construct an oracle.sql.CHAR object
    return new CHAR(ochar, null);   

Because of the automatic blank-padding of CHAR columns, setCHAR() might not give the right result when comparing a CHAR column with character data in a WHERE clause; use the setFixedCHAR() method of the OraclePreparedStatement object instead.

The SQL VARCHAR2 data type defines a variable-length (nonblank-padded) character data type. The size, which is between 1 and 4,000, must be specified. There is no oracle.sql.VARCHAR2 class; as Table 3.2 indicates, the Oracle JDBC maps SQL VARCHAR2 to and from oracle.sql.CHAR, java.lang.String, java.sql.Date, java.sql.Time, java.sql.Timestamp, java.lang.Byte, java.lang.Short, java.lang.Integer, java.lang.Long, java.sql.Float, java.lang.Double, java.math.BigDecimal, byte, short, int, long, float, and double.

Apart from setFixedCHAR, the same methods as CHAR (i.e., setString, getString, etc.) apply for manipulating VARCHAR.

VARCHAR2 (as well as CHAR) columns can also be streamed using getAsciiStream by redefining it as a LONGVARCHAR using defineColumnType of OracleStatement. In fact, the data is fetched into a staging buffer, from which getAsciiStream returns an InputStream.

Assume a database with a UTF-8 character set, a VARCHAR2(4000) column, and the following code snippet:

char[] ch = new char[4000];
// fill up ch[1..4000] = 'a'
setString (1, new String (ch));

NCHAR specifies a fixed-length Unicode data type; the actual maximum column length (2,000 bytes) is determined by the width (in bytes) of the national character set, defined at database creation.

NVARCHAR specifies a variable-length Unicode data type; the maximum size (less or equal to 4,000 bytes) must be specified.

JDBC does not currently offer standard APIs to deal with NCHAR/NVARCHAR data types in a way different from CHAR. The application must unwrap the connection to get an Oracle Connection (if not already) in order to map to and from oracle.sql.NCHAR.

The OracleConnection provides an isNCHAR method for checking if the column is of type NCHAR, and the OraclePreparedStatement object furnishes the setFormOfUse method to direct JDBC to treat the column as either SQL NCHAR or SQL CHAR.

pstmt.setFormOfUse(2, FORM_NCHAR); // NCHAR column
pstmt.setString(2, UnicodeString); 

If the system property oracle.jdbc.defaultNChar or the connection property defaultNChar is set to true (default is false ), then there is no need to specify setFormOfUse. JDBC treats all character columns as being national language (the database will convert all CHAR data into NCHAR/ NVARCHAR2).

Notes:

The upcoming JDBC 4.0 specification is said to provide standard support for NCHAR, NVARCHAR, LONGNVARCHAR, and NCLOB data types.

SQL NUMBER columns store positive and negative fixed numbers with absolute values from 1.0 × 10-130 to 1.0 × 10126 (but not including 1.0 ×10126 itself). A SQL NUMBER data types represent numbers with precision p between 1 and 38 and scale s between -84 and 127.

Examples:

The Oracle JDBC furnishes the oracle.sql.NUMBER wrapper class and the corresponding getNUMBER(), setNUMBER(), setNUMBERAtName, and updateNUMBER() methods in OraclePreparedStatement, OracleCallableStatement, and OracleResultSet classes. In fact, the oracle.sql.NUMBER is a wrapper class for all ANSI SQL92 data types, including INTEGER, SMALLINT, NUMERIC, DECIMAL, FLOAT, DOUBLE, and REAL.

Depending on the Java type that you use to receive the data, you may lose the precision information, oracle.sql.NUMBER preserves the precision.

Per Table 3.2, a SQL NUMBER can also be mapped to and from the following standard Java types using the corresponding setxxx(), updatexxx(), and getxxx() methods defined in PreparedStatement, CallableStatement, and ResultSet interfaces: java.lang.Byte, java.lang.Short, java.lang.Integer, java.lang.Long, java.sql.Float, java.lang.Double, java.math.BigDecimal, byte, short, int, long, float, and double.

Here is a code snippet excerpted from Listing 3.5:

  /*
   * Update SQL NUMBER column with java.lang.Integer
   */
     PreparedStatement ps =
        conn.prepareStatement("UPDATE TypesTab SET num = ?");
     int n = x.intValue();
     ps.setInt (1, n);
     ps.execute();
     ps.close();

  /*
   * Retrieve SQL NUMBER column as int
   */
     Statement stmt = conn.createStatement();
     int n = 0;
    ResultSet rs = stmt.executeQuery("SELECT num FROM
TypesTab");
    while (rs.next())
    {
      n = rs.getInt(1);
    }
    stmt.close();
    return n;

Here is a code snippet excerpted from Listing 3.6:

/*
 * Map oracle.sql.NUMBER to SQL NUMBER
 */
   OraclePreparedStatement ops = (OraclePreparedStatement)
       conn.prepareStatement("UPDATE TypesTab SET num = ?");
   ops.setNUMBER (1, n);
   ops.execute();
   ops.close();


 /*
  * Map SQL NUMBER column to oracle.sq.NUMBER
  */
    OracleStatement ostmt = (OracleStatement)
conn.createStatement();
    NUMBER onb = new NUMBER();
    OracleResultSet ors =
     (OracleResultSet) ostmt.executeQuery("SELECT num FROM
TypesTab");
    while (ors.next())
    {
      onb = ors.getNUMBER(1);
    }
    ostmt.close();
    return onb;

A SQL BINARY_FLOAT is a 32-bit, single-precision, floating-point number data type; its values vary between 1.17549E-38F and 3.40282E+38F. A SQL BINARY_DOUBLE is a 64-bit, double-precision, floating-point number data type; its values vary between 2.22507485850720E-308 and 1.79769313486231E+308.

Since Oracle Database 10g Release 1, the JDBC drivers support the SQL BINARY_FLOAT and SQL BINARY_DOUBLE data types, respectively, through the oracle.sql.BINARY_FLOAT and oracle.sql.BINARY_DOUBLE classes. The JDBC mapping is compatible with IEEE754 float and IEEE754 double, but with the following restrictions:

The following methods have been added to the OraclePreparedStatement and OracleCallableStatement objects: setBinary-Float(int, float), setBinaryDouble(int, double), setBinaryFloat(String, float), and setBinaryDouble(String, double).

BINARY_FLOAT bf = new BINARY_FLOAT((float)789.669);
BINARY_DOUBLE bd = new BINARY_DOUBLE((double)897.9999);

OraclePreparedStatement opstmt = (OraclePreparedStatement)
  oconn.prepareStatement("insert into binary_tab
values(?,?)");

pstmt.setBinaryFloat (1, bf);
pstmt.setBinaryDouble (2, bd);
pstmt.executeUpdate();

pstmt.registerReturnParameter(1, OracleTypes.BINARY_FLOAT);
pstmt.registerReturnParameter(2, OracleTypes.BINARY_DOUBLE);

OraclePreparedStatement pstmt = (OraclePreparedStatement)
oconn.prepareStatement("SELECT bfcol, bdcol FROM binary_tab
where ...);
ResultSet rs = pstmt.executeQuery ();

float bfcol= 0;
double bdcol = 0;
while (rs.next()) {
col2 = rs.getFloat (2);
col3 = rs.getDouble (3);

The SQL RAW data type stores raw binary data such as graphics, sound, documents, or arrays of binary data. The variable size, between 1 and 2,000, must be specified. Oracle recommends to use SQL BLOB instead (you can view the RAW data type as a small-sized BLOB but without the locator). The Oracle JDBC maps SQL RAW to and from oracle.sql.RAW and Java bytes[]. SQL RAW can be manipulated using the setRAW, setRAWAtName, getRAW, and updateRAW methods of OraclePreparedStatement, OracleCallableStatement, and OracleResultSet classes. The oracle.sql.RAW class furnishes the newRAW method for creating instances of RAW data type compatible with Oracle Database 10g, and oldRAW constructors for creating instances of RAW data type compatible with pre-10 g releases. The following code snippet excerpted from Listing 3.8 illustrates the mapping of a RAW column to oracle.sql.RAW:

/*
 * Update a SQL RAW column with oracle.sql.RAW data
 */
   byte[] bytearr = new byte[600]; // Max size of SQL RAW data
    RAW oraw = new RAW(bytearr);
   OraclePreparedStatement ops = (OraclePreparedStatement)
       conn.prepareStatement("UPDATE TypesTab SET xraw = ?");
    ops.setRAW (1, oraw);
    ops.execute();
    ops.close();

/*
 * Retrieve a SQL RAW column as oracle.sq.RAW
 */
    OracleStatement ostmt = (OracleStatement) conn.createStatement();
    // construct an oracle.sql.RAW object

    OracleResultSet ors =
     (OracleResultSet) ostmt.executeQuery("SELECT xraw FROM
TypesTab");
    while (ors.next())
    {
     oraw = ors.getRAW(1);
    }
    ostmt.close();
   return oraw;

RAW columns can also be redefined as LONGVARBINARY using defineColumnType of OracleStatement, then streamed using getBinaryStream. In fact, the data is fetched into a staging buffer, from which getBinaryStream returns an InputStream.

LONG columns store variable-length character strings containing up to 2 gigabytes –1, (i.e., 231–1) bytes. LONG columns have many of the characteristics of VARCHAR2 columns. A LONG RAW data type is a variable-length raw binary data, up to 2 gigabytes data, and the storage is allocated dynamically.

LONG and LONG RAW data types are maintained for backward compatibility only; Oracle recommends to use VARCHAR2 or LOBs instead. Still, the Oracle JDBC maps LONG to oracle.sql.RAW and java.lang.String, while LONG RAW data is mapped to oracle.sql.RAW and byte[].

LONG and LONG RAW columns can be manipulated using one of the following three approaches:

// Insert  a LONG column
File if = new File("Ascii.dat");
InputStream is = new FileInputStream(if);
PreparedStatement pstmt =
      conn.prepareStatement("INSERT INTO LONGTAB VALUES (?)");
pstmt.setAsciiStream(1, is, (int)if.length());
pstmt.execute();
// retrieve a LONG Column
ResultSet rs =
      stmt.executeQuery("SELECT LongCol FROM TAB");
 if (rs.next())
 {
   InputStream longdata = rs.getAsciiStream(1);
   FileOutputStream fos = new FileOutputStream("Long.log");
   // Read from the stream and write to log file
   int count;
   while ((count = longdata.read()) != -1) fos.write(c);
    ...
   fos.close ();
  }

// Insert a LONG RAW column
OraclePreparedStatement ps = (OraclePreparedStatement)
      conn.prepareStatement ("insert into LONGRAWTAB values (?)");
byte[] bytearr = new byte[200]; // byte array
InputStream is = new ByteArrayInputStream (bytearr);
ps.setBinaryStream (1, is, 200);
pstmt.executeUpdate ();
is.close ();

// Retrieve  a LONG RAW column
ResultSet rs =
  stmt.executeQuery ("SELECT LongRawCol FROM Tab");
if (rs.next ())
 {
   InputStream in = ((OracleResultSet)rset).getBinaryStream (1);
   //process input stream as above (retrieve LONG)
    ...
 }

In Oracle JDBC 10g Release 2, for data smaller than 32,766 bytes, the getXXXStream methods switch automatically to setString or setBytes. You may also explicitly disable the automatic streaming of LONG and LONG RAW columns by redefining these respectively as VARCHAR2 and VARBINARY using the defineColumnType() method of OracleStatement, and then manipulating it using setString/setBytes.

The SQL DATE data type stores date and time information, including century, year, month, date, hour, minute, and second; it is equivalent to SQL99 TIMESTAMP[0], meaning without the fractional part of second. The Oracle JDBC supports SQL DATE through the oracle.sql.DATE wrapper class. SQL DATE data types can be manipulated using the setDATE, setDATEAtName, getDATE, and updateDATE methods of OraclePreparedStatement, OracleCallableStatement, and OracleResultSet classes. In addition, DATE data types can also be mapped to standard java.sql.Date, java.sql.Time, java.sql.Timestamp, and java.lang.String; however, doing so may lead to precision loss (i.e., the time component of java.sql.Date is zeroed, and the behavior differs throughout releases), as explained later.

The following code snippets, excerpted from Listing 3.2, illustrates the mapping of SQL DATE to java.sql.Date, and vice versa:

/*
 * Update SQL DATE column with java.sql.Date
 */
   PreparedStatement ps =
       conn.prepareStatement("UPDATE TypesTab SET xdat = ?");
    ps.setDate (1, x);
    ps.execute();
    ps.close();

/*
 * Retrieve SQL DATE column as java.sql.Date
 */
    Statement stmt = conn.createStatement();
    Date dat = null;
    ResultSet rs = stmt.executeQuery("SELECT xdat FROM
TypesTab");
    while (rs.next())
    {
      dat = rs.getDate(1);
    }
    stmt.close();
    return dat.toString();
  

The following code snippet, excerpted from Listing 3.3, shows mapping oracle.sql.DATE to SQL DATE:

/*
 * Update SQL DATE with oracle.sql.DATE
 */
   OraclePreparedStatement ops = (OraclePreparedStatement)
       conn.prepareStatement("UPDATE TypesTab SET xdat = ?");
    ops.setDATE (1, x);
    ops.execute();
    ops.close();
 /*
  * Retrieve SQL DATE column as oracle.sq.DATE
  */
    OracleStatement ostmt = (OracleStatement)
conn.createStatement();
    DATE dat = new DATE();
    OracleResultSet ors =
     (OracleResultSet) ostmt.executeQuery("SELECT xdat FROM
TypesTab");
    while (ors.next())
    {
      dat = ors.getDATE(1);
    }
    ostmt.close();
    return dat; 

Starting with Oracle 9i Release 2, the Oracle database furnishes the SQL99 TIMESTAMP data type (i.e., precision up to nanosecond). The SQL TIMESTAMP data type is an extension of the DATE data type, since it stores year, month, day, hour, minute, second, and fractional seconds; these fractional seconds are not stored by the DATE data type.

TIMESTAMP'1997-01-31 09:26:50.124'

The Oracle JDBC supports SQL TIMESTAMP through the oracle.sql.TIMESTAMP class and can be manipulated using the setTIMESTAMP, setTIMESTAMPAtName, getTIMESTAMP, and updateTIMESTAMP methods of OraclePreparedStatement, OracleCallableStatement, and OracleResultSet classes. In addition, the TIMESTAMP data can also be mapped to oracle.sql.DATE, java.sql.Date, java.sql.Time, java.sql.Timestamp, java.lang.String, and byte[]. In these cases, the corresponding setxxx, getxxx, and updatexxx methods of the PreparedStatement, CallableStatement, and ResultSet interfaces can be used.

The TIMESTAMPTZ data type (i.e., TIMESTAMP WITH TIME ZONE data type ()) is a TIMESTAMP data type “with a time zone offset, which is the difference (in hours and minutes) between the local time and the UTC (Coordinated Universal Time.)”

TIMESTAMP '1997-01-31 09:26:56.66 +02:00'
TIMESTAMP '1999-04-15 8:00:00 -8:00'
TIMESTAMP '1999-04-15 8:00:00 US/Pacific'

The Oracle JDBC supports SQL TIMESTAMPTZ through the oracle.sql.TIMESTAMPTZ class and corresponding setTIMESTAMPTZ, setTIMESTAMPTZATName, getTIMESTAMPTZ, updateTIMESTAMPTZ in OraclePreparedStatement, OracleCallableStatement, and OracleResultSet classes. Similar to TIMESTAMP, it can also be mapped to oracle.sql.DATE, java.sql.Date, java.sql.Time, java.sql.Timestamp, java.lang.String, and byte[], with the corresponding manipulation methods.

The TIMESTAMPLTZ data type is a TIMESTAMP WITH TIME ZONE data type; however, “the data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data.”

The Oracle JDBC supports TIMESTAMPLTZ through the oracle.sql.TIMESTAMPLTZ class and corresponding setTIMESTAMPLTZ, set-TIMESTAMPLTZATName, getTIMESTAMPLTZ, and updateTIMESTAMPLTZ in OraclePreparedStatement, OracleCallableStatement, and OracleResultSet classes..

Similar to TIMESTAMPTZ, it can also be mapped to oracle.sql.DATE, java.sql.Date, java.sql.Time, java.sql.Timestamp, java.lang.String, and byte[].

// Assume a TIMESTAMP_TAB with the various Timestamp types
// as columns
String my_date = "2005-12-04 12:23:47.66";
oracle.sql.TIMESTAMPLTZ      my_tsltz = null;
GregorianCalendar            my_gcal  = null;
Timestamp my_tss = Timestamp.valueOf(my_date);

TimeZone my_tz = TimeZone.getDefault();
my_tz.setID("US/Pacific");
my_gcal = new GregorianCalendar(my_tz);
my_tsltz = new oracle.sql.TIMESTAMPLTZ(conn, my_tss, my_cal);
my_tstz = new oracle.sql.TIMESTAMPTZ(conn, my_tss, my_cal);
my_ts = new oracle.sql.TIMESTAMP(my_tss);
String query = "update timestamp_tab set c1 = ?, c2 = ?, c3 =
?";
OraclePreparedStatement ops =
 (OraclePreparedStatement)  con.prepareStatement(sql);
ops.setTIMESTAMPLTZ(1, my_ts);
ops.setTIMESTAMPLTZ(2, my_tstz);
ops.setTIMESTAMPLTZ(3, my_tsltz);

ops.executeUpdate ();

The SQL INTERVALYM data type (i.e., INTERVAL YEAR TO MONTH) stores a period of time in YEAR and MONTH; it is used to measure time differences. The Oracle JDBC supports this data type through the oracle.sql.INTERVALYM class and the setINTERVALYM, setINTERVALYMATName, getINTERVALYM, and updateINTERVALYM methods.

The SQL INTERVALDS (i.e., INTERVAL DAY TO SECOND) stores a period of time in days, hours, minutes, and seconds; it is used to measure time differences. The Oracle JDBC supports this data type through the oracle.sql.INTERVALDS class, and the setINTERVALDS, setINTERVALDSATName, getINTERVALDS, and updateINTERVALDS methods.

INTERVALDS ids = new INTERVALDS ("15 08:12:42.0");
OraclePreparedStatement ops = (OraclePreparedStatement)
    oconn.prepareStatement("INSERT INTO idstab VALUES (?)");
ops.setINTERVALDS (1, ds);
...
opst.executeUpdate ();
ops = (OraclePreparedStatement) conn.prepareStatement
        ("SELECT ids1 FROM idstab");
OracleResultSet ors = pstmt.executeQuery ();
ids = new INTERVALDS;
while (rs.next ()) {
   col2 = rs.getINTERVALDS (1);
}

Large Object data types (LOBs) is an umbrella name for the Binary LOB (BLOB), the Character LOB (CLOB), the Unicode LOB (NCLOB), and the Binary File (BFILE) data types. These are used for storing large and unstructured data such as text, image, video, and spatial data. The BLOB, CLOB, and NCLOB data types can store unlimited data size (the physical storage is the limit). CLOB data is in the database character set, which may be single or multibytes, while NCLOB data is in the national language character set. LOB data types larger than 4 K are not stored within the table column; in all cases, the column contains a locator, which points to the actual LOB data (inside or outside of the database). See the Oracle Database SQL Reference documentation for more details on the features and restrictions of SQL LOB data types. How does JDBC support LOB manipulation?

Temporary LOBs (BLOB, CLOB, NCLOB) are created either implicitly or explicitly in temporary tablespace (as opposed to regular tablespace) for storing transient data. In Oracle JDC 10g Release 2, the setBytesForBlob and setStringForClob methods implicitly create temporary LOBs, which are automatically freed when the statement is executed or closed before execution.

You can explicitly create a temporary LOB with the createTemporary(Connection, boolean, int) method, which is defined in both the oracle.sql.BLOB and oracle.sql.CLOB classes.

The isTemporary method returns true if the LOB was created by calling the createTemporary method.

The SQL OPAQUE type acts as a black box, which hosts custom binary data types. The oracle.sql.OPAQUE class wraps the SQL OPAQUE data type, and the drivers retrieve the bytes from the server, but the consumer must turn the bytes into a meaningful type (e.g., XMLType). It is used mainly internally by Oracle to support complex built-in types.

The oracle.sql.OPAQUE extends the oracle.sql.DatumWithConnection class and furnishes the following methods: getBytesValue(), getDescriptor(), getJavaSqlConnection(), getMap(), getSQLTypeName(), getValue(), isConvertibleTo(), and toJdbc(). TheOraclePreparedStatement, OracleResultSet furnish thesetOPAQUE(), setOPAQUEAtNAme(), and getOPAQUE() methods.

See the following XMLType code fragments that use the OPAQUE type.

The oracle.sql.OpaqueDescriptor class furnishes the following methods for describing the OPAQUE data type: createDescriptor(), desc-Type(), getMaxLength(), getTypeCode(), hasFixedSize(), hasUnboundedSize(), isModeledInC(), and so on.

See the Oracle JDBC javadoc for more details on the signatures.

The Oracle XML Database (also known as XDB) furnishes a native XML-Type data type for managing XML documents directly in the database. As well explained in the Oracle XML DB Developer’s Guide and white paper,^[4]XDB stores XML in XMLType columns and/or tables, using either a structured or an unstructured format. In summary:

XDB furnishes a comprehensive Java API, including a Java DOM API and JDBC support for XMLType. Full coverage of XML DB will require an entire book, but the use case in Chapter 17 is an example of a complete open-source framework leveraging XML DB.

The upcoming JDBC 4.0 specification is expected to define a standard XMLType data type.

JDBC supports the SQL XMLType data type through the oracle.xdb.XML-Type wrapper class in xdb.jar (located under $ORACLE_HOME/rdbms/ jlib). It extends oracle.sql.OPAQUE and furnishes a constructor XML-Type(), which can build an XMLType object from BLOB, CLOB, String, OPAQUE Descriptor,^[5]InputStream, andDOM documents, using the corresponding signature. It also furnishes the following APIs: createXML(), close(), createContext(), existsNode(), extract(), getClobVal(), getBlobVal(), getConnType(), getDocument(), getDocumentFragment(), getDOM(), getErrorHandle(), getInputStream(), getNameSpace(), getNumberVal(), getRootElement(), getSChemaURL(), getServiceHandle(), getStringVal(), isFragment(), isSchema-Based(), isSChemaValid(), transform(), andWriteToOutput-Stream().

XMLType is based on the OPAQUE type. All Oracle JDBC driver types are capable of transporting OPAQUE data types (hence XMLType) to and from the database; however, because most or all SQL XMLTypes operations are implemented in C and available in the C libraries, only JDBC-OCI can access the C layer of the Oracle client, similarly the server-side type 2 driver can access the C layer of the RDBMS, and understand XMLType and its various storage formats. See Part IV for a driver-independent workaround.

Code fragments:

See more code fragments and DOM document manipulation techniques in the online XDB documentation.^[6]

As we have seen, built-in SQL types are mapped to Java through the classes furnished by the oracle.sql.* package. For user-defined SQL types, a type-map object (java.util.Map) allows defining the mapping between a SQL user-defined type and the associated Java class. A type-map object is a class (i.e., java.util.Hashtable) that implements a java.util.Map interface and is associated with each Connection object.

The getTypeMap() andsetTypeMap() methods of the java.sql.Connection and oracle.jdbc.OracleConnection interfaces allow “installing” and retrieving the TypeMap object associated with a Connection object.

java.util.Map map = con.getTypeMap();

The following code snippet maps the SQL type Address_t to the class AddressObj:

map.put(”Address_t”,Class.forName(”AddressObj”));
oconn.setTypeMap((Map)map);

When retrieving data from the result set, you use the default type-map of the connection object, unless you specify a type-map, as follows:

rs.getObject(int columnIndex, Map map);
rs.getObject(String colName, Map map);

A type-map is required for the getObject(), getAttribute(), getARRAY(), andgetValue() methods ofthe ResultSet, CallableStatement, Struct, java.sql.Array, andoracle.sql.REF interfaces. However, when there is none specified for the method in question, the default connection type-map is checked, and, if undefined, the JDBC driver materializes the Oracle object as an instance of oracle.sql.STRUCT.

The java.sql.Struct interface defines methods for custom mapping and processing of a user-defined type (i.e., Object type) and its attributes. It furnishes the following methods:

The oracle.sql.STRUCT class is Oracle’s implementation of java.sql.Struct and extends the oracle.sql.Datum. It wraps the “raw bytes” of an instance of an Oracle Object type and contains its SQL type name and an array of oracle.sql.Datum objects that hold the value of each attribute in SQL format. This is the default mapping of Oracle objects when a custom mapping through SQLData or ORAData interfaces is not provided.

In addition to the java.sql.Struct methods discussed previously, it furnishes the following methods:

// retrieving the object as a java.sql.Stuct
ResultSet rset = stmt.executeQuery("SELECT * from
AddressTab");
Struct struct = (Struct)rset.getObject(1);

// retrieving the object as an oracle.sql.STRUCT
STRUCT struct =(oracle.sql.STRUCT) rset.getObject(1);
or
STRUCT struct = rset.getSTRUCT(1);

To retrieve the attributes, the JDBC application invokes either getAttributes() or getOracleAttributes().

// using getAttributes()
Struct struct = (Struct)rset.getObject(1);
Object[] attrarr = struct.getAttributes();

// using getOracleAttriutes()
oracle.sql.STRUCT strct =(oracle.sql.STRUCT)
rset.getObject(1);
System.out.println("SQL Object type: " +
strct.getSQLTypeName());
Object[] attrarr = strct.getOracleAttributes();

To bind the oracle.sql.STRUCT or the java.sql.Struct instance to an IN parameter, use the setObject (), setSTRUCT(), setSTRUCTAtName(), setObject(), setOBjectAtName(), setOracleObject(), andsetOracleObjectATName() methods of OraclePreparedStatement, OracleCalableStatement, andOracleResultSet.

STRUCT strct new STRUCT (...);
pstmt.setObject(<index>, strct, Types.STRUCT);

or:

((OralePreparedStatement)pstmt).setOracleObject(<index>,
strct);

The updateSTRUCT(), updateOracleObject() methods of OracleResultSet can be used to update a STRUCT object.

create type ADTTYP2 as object (n1 number, n2 varchar2(30), n3
date)
/
create table ADTTAB2 (id number, adtcol ADTTYP2)
/
insert into ADTTAB2 values (1, ADTTYP2(101, 'Row One', '01-
JAN-2001'));
commit;

 // inserts an oracle.sql.STRUCT object into an Object Type
table
 //
     OraclePreparedStatement ps =
        (OraclePreparedStatement) conn.prepareStatement
             ("INSERT INTO ADTTAB2 (ID, ADTCOL) VALUES(?, ?)");

     ps.setNUMBER (1, id);
     ps.setSTRUCT (2, adt);
     ps.execute ();
     ps.close();


//retrieves a user-defined type as oracle.sql.STRUCT
//
    OraclePreparedStatement ps =
       (OraclePreparedStatement) conn.prepareStatement
            ("SELECT ADTCOL FROM ADTTAB2 WHERE ID = ?");

     ps.setNUMBER (1, id);
     OracleResultSet rs = (OracleResultSet) ps.executeQuery();

     STRUCT st = null;
     while (rs.next())
     {
      st = (STRUCT) rs.getObject(1);
     }
     ps.close();
     return st;

The SQLJ Object type is a special Object type specified by ANSI SQLJ Part II. It allows constructing user-defined SQL types, using Java classes that implement the SQLData or ORAData interfaces.

CREATE TYPE <SQLJ object>  AS OBJECT
 [ EXTERNAL NAME '<Class name>'  LANGUAGE JAVA ]
  USING SQLData|ORAdata (...);

The attributes, the setters, and the getters of the Java class can be mapped to SQL through the EXTERNAL NAME clause; more specifically, public class methods (i.e., public static ) map to STATIC FUNCTION or STATIC PROCEDURE, and public instance methods (i.e., public ) map to MEMBER FUNCTION orMEMBER PROCEDURE.

The SQLJ object type may also be created as a usual SQL object type (i.e., without a base Java class), but the “externalized” methods must be implemented by Java methods.

Here are the required steps for creating a SQLJ Obect type, as illustrated by Listing 8.3:

import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.*;
import java.math.*;
public class Paymaster implements SQLData {
  // Implement the attributes and operations for this type.
  private BigDecimal empno;
  private String ename;
  private String job;
  private BigDecimal mgr;
  private Date hiredate;
  private BigDecimal sal;
  private BigDecimal comm;
  private Ref dept;

  public BigDecimal wages() {
    BigDecimal pay = sal;
    if (comm != null) pay = pay.add(comm);
    return pay;
  }
  public void raiseSal(BigDecimal amount) {
    //
    sal = sal.add(amount);
    System.out.println(sal);
  }


  // Implement SQLData interface.
  String sql_type;

  public String getSQLTypeName() throws SQLException {
    return sql_type;
  }

  public void readSQL(SQLInput stream, String typeName)
    throws SQLException {
    sql_type = typeName;
    empno = stream.readBigDecimal();
    ename = stream.readString();
    job = stream.readString();
    mgr = stream.readBigDecimal();
    hiredate = stream.readDate();
    sal = stream.readBigDecimal();
    comm = stream.readBigDecimal();
    dept = stream.readRef();
  }

  public void writeSQL(SQLOutput stream) throws SQLException
{
    stream.writeBigDecimal(empno);
    stream.writeString(ename);
    stream.writeString(job);
    stream.writeBigDecimal(mgr);
    stream.writeDate(hiredate);
    stream.writeBigDecimal(sal);
    stream.writeBigDecimal(comm);
    stream.writeRef(dept);
  }
}

$ javac Paymaster.java
$ loadjava -u user/pass Paymaster.class

Paymaster.sql

----------------
drop table employee_tab;
drop table dept_tab;
drop type employee;
drop type department;

CREATE TYPE Department AS OBJECT (
  deptno NUMBER(2),
  dname  VARCHAR2(14),
  loc    VARCHAR2(13)
);
/
CREATE or replace TYPE Employee AS OBJECT (
  empno    NUMBER(4),
  ename    VARCHAR2(10),
  job       VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal       NUMBER(7,2),
  comm      NUMBER(7,2),
  deptno   REF Department,
  MEMBER FUNCTION wages RETURN NUMBER
    AS LANGUAGE JAVA
    NAME 'Paymaster.wages() return java.math.BigDecimal',

 MEMBER PROCEDURE raise_sal (r NUMBER)
    AS LANGUAGE JAVA
    NAME 'Paymaster.raiseSal(java.math.BigDecimal)'

);
/
create table employee_tab of employee;
create table dept_tab of department;
insert into dept_tab values (department(1, 'physics',
'sample'));
insert into employee_tab values (employee(1, 'kuassi', 'sw',
10, '13-dec-2005', 1000, 1000, (select ref(d) from dept_tab
d)));

select value(a).wages() from employee_tab a;
$ sqlplus user/pass @Paymaster

The SQL REF type represents references (pointer) to objects of a specified type such as REF of ADT types, REF of X. In SQL and PL/SQL, REF of X can be created in SQL by querying the X object and applying the REF operator. Similarly, to access the object referenced by a REF, you dereference the REF, using the Oracle-supplied DEREF operator.

The Oracle JDBC oracle.sql.REF class wraps the SQL REF data type and implements the java.sql.Ref interface. The java.sql.Ref interface furnishes the following methods:

The OraclePreparedStatement, OracleCallableStatement, and OracleResultSet furnish the getREF, getRef, setREF, setRef, setREFAtName, setRefType, setRefTypeAtName, updateREF, getObject, andsetObject methods. Unlike SQL, in JDBC, application codes cannot create an instance of oracle.sql.REF, only the driver may.

Assume a user-defined object type ADTtyp, a table ADTtab of ADTtyp objects, and a table of REF of ADTtyp:

create type ADTtyp as OBJECT (a1 number, a2 varchar2(20), a3
date)
/
create table ADTtab of ADTtyp
/
create table REFtab (id number, refcol REF ADTtyp)
/

Prepopulated as follows:

insert into ADTtab values (ADTtyp(1, 'One', '01-JAN-2001'));
insert into REFtab select 1, REF(R2) from ADTtab R2 where
R2.a1 = 1;
commit;

The following code snippet inserts anoracle.sql.REF into the REFtab table:

OraclePreparedStatement ps = (OraclePreparedStatement)
    conn.prepareStatement("INSERT INTO REFTAB (ID, REFCOL)
VALUES(?, ?)");

    ps.setNUMBER (1, id);
    ps.setREF (2, rf);
    ps.execute ();    

The following code snippet retrieves and returns an instance ofREF ADTTyp as oracle.sql.REF:

   OraclePreparedStatement ps = (OraclePreparedStatement)
      conn.prepareStatement ("SELECT REFCOL FROM REFTAB WHERE
ID = ?");

    ps.setNUMBER (1, id);
    OracleResultSet rs = (OracleResultSet) ps.executeQuery();

    REF r = null;
    while (rs.next())
    {
      r = (REF) rs.getObject(1);
    }
    ps.close();
    return r;

Cursors contain query results and metadata. A REF Cursor (or cursor variable) data type contains a reference to a cursor. It can be passed between the RDBMS and the client, or between PL/SQL and Java in the database; it can also be returned from a query or a stored procedure. The Oracle JDBC furnishes the getCursor and setCursorAtName methods for manipulating Ref Cursor as ResultSet and OracleResultSet. Setting setCreateStatementAsRefCursor() to true turns any statements created from this connection into a REF CURSOR.

Returning a REF Cursor as a java.sql.ResultSet:

...

 ((OracleConnection)conn).setCreateStatementAsRefCursor(true);
     Statement stmt = conn.createStatement();
     ((OracleStatement)stmt).setRowPrefetch(1);

     ResultSet rset =
        stmt.executeQuery("select * from EMP order by empno");
        rset = ((OracleResultSet)rset).getCursor(i);
     return rset;

For CallableStatement, the RefCursor must be declared as an OUT parameter with the CURSOR typecode:

cstmt.registerOutParameter(1,OracleTypes.CURSOR);
...

See a complete example in Chapter 3.

The Oracle database furnishes only variable-length arrays (i.e., VARRAYs) as part of the user-defined SQL collection types. They represent an ordered set of elements of the same type (scalar or complex types) and are used to map array data types in other languages. VARRAY data are stored in line in table columns; however, when their size is larger than 4 K, or when a storage clause is specified, these are stored outline, as BLOB data. The maximum number of elements in a VARRAY must be specified at creation time.

The Oracle JDBC drivers support the SQL VARRAY through the oracle.sql.ARRAY wrapperclass (which implements the standard java.sql.Array interface) and the oracle.sql.ArrayDescriptor class.

The oracle.sql.ARRAY furnishes the following methods: getArray() with various signatures (see the Oracle JDBC javadoc), getBaseType(), getBaseTypeName(), getDescriptor(), getDoubleArray(), getFloatArray(), getIntArray(), getLongArray(), getResultSet(), getJavaSQLConnection(), toJdbc(), and so on

The oracle.sql.ArrayDescriptor furnishes the following methods: createDescriptor(), dscType(), getArrayType(), getBaseName(), getBaseType(), getTypeCode(), getMaxLength(), getArrayType() (i.e., TYPE_VARRAY and TYPE_NESTED_TABLE), toResultSet(), and so on.

The OraclePreparedStatement, OracleCallableStatement, and OracleResultSet furnishthe setArray, setArrayAtName, setARRAY, setARRAYAtName, getArray, getARRAY, updateArray, and updateARRAY methods.These methods can be used directly to manipulate arrays of scalar (built-in) SQL types. However,if the elements of the SQL Array are user-defined types, the java.sql.SQLData or oracle.sql.ORAData classes and a type-map must be used to manipulate the elements of the Java array, similar to SQL Object types, covered earlier.

The following snippets excerpted from Chapter 3 illustrate the manipulation of VARRAY of scalar types, including VARRAY of NUMBER, VARRAY of VARCHAR2, and VARRAY of DATE.

create or replace type NVARRAY as VARRAY(10) of number;
 NVARRAY(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

// The following code snippet inserts a java.sql.Array into
// VarrayTab table as NVARRAY

    Statement stmt = conn.createStatement();

    OraclePreparedStatement ps = (OraclePreparedStatement)
    conn.prepareStatement("INSERT INTO VarrayTab (ID, NVA)
VALUES(?, ?)");
    ps.setNUMBER (1, id[0]);
    ps.setARRAY (2, (ARRAY)va[0]);
    ps.execute ();

// The following code snippet retrieves and returns an NVARRAY
// as a java.sql.Array

    OraclePreparedStatement ps = (OraclePreparedStatement)
    conn.prepareStatement ("SELECT NVA FROM VarrayTab WHERE ID
= ? ");
     ps.setNUMBER (1, id[0]);
     OracleResultSet rs = (OracleResultSet) ps.executeQuery();
     Array a = null;
     while (rs.next())
     {
       a = (Array) rs.getObject(1);
     }
     ps.close();
     return a;

create or replace type VC2VARRAY as VARRAY(10) of
varchar2(30);
VC2VARRAY('Thirty One', 'Thirty Two', 'Thirty Three',
              'Thirty Four', 'Thirty Five', 'Thirty Six',
              'Thirty Seven', 'Thirty Eight')

// The following code snippet inserts a java.sql.Array row
// into VC2VARRAY as a java.sql.Array

    Statement stmt = conn.createStatement();

    OraclePreparedStatement ps = (OraclePreparedStatement)
    conn.prepareStatement("INSERT INTO VarrayTab(ID, VC2VA)
VALUES(?, ?)");

    ps.setNUMBER (1, id[0]);
    ps.setARRAY (2, (ARRAY)va[0]);
    ps.execute ();
    ps.close();


// The following code snippet retrieves and returns a
// VC2VARRAY as a java.sql.Array

    OraclePreparedStatement ps = (OraclePreparedStatement)
    conn.prepareStatement ("SELECT VC2VA FROM VarrayTab WHERE
ID = ? ");
    ps.setNUMBER (1, id[0]);
    OracleResultSet rs = (OracleResultSet) ps.executeQuery();
    Array a = null;
    while (rs.next())
    {
      a = (Array) rs.getObject(1);
    }
    ps.close();
    return a;

create or replace type DATVARRAY as VARRAY(10) of date;
DATVARRAY('01-JAN-2005', '02-JAN-2005', '03-JAN-2005', '04-
JAN-2005',
              '05-JAN-2005', '06-JAN-2005', '07-JAN-2005',
              '08-JAN-2005','09-JAN-2005', '10-JAN-2005')

// The following code snippet inserts a java.sql.Array
// row into VarrayTab table as DATVARRAY

    Statement stmt = conn.createStatement();

    OraclePreparedStatement ps = (OraclePreparedStatement)
       conn.prepareStatement ("INSERT INTO VarrayTab (ID,
DATVA)
         VALUES(?, ?)");
    ps.setNUMBER (1, id[0]);
    ps.setARRAY (2, (ARRAY)va[0]);
    ps.execute ();
    ps.close();
    conn.commit();

// The following code snippet retrieves and returns
// a DATVARRAY as a java.sql.Array

    OraclePreparedStatement ps = (OraclePreparedStatement)
    conn.prepareStatement ("SELECT DATVA FROM VarrayTab WHERE
ID = ? ");

     ps.setNUMBER (1, id[0]);
     OracleResultSet rs = (OracleResultSet) ps.executeQuery();
     Array a = null;
     while (rs.next())
     {
       a = (Array) rs.getObject(1);
     }
     ps.close();
     return a;

Nested Tables, or tables within a table, are part of the user-defined SQL collection types. They define a type, which represents an unordered set of elements of the same type and are used to map sets and bag data types in other languages. The nested table columns of a table are stored out of line from the rows of the parent table, using the “store as” clause (see the Oracle SQL Reference Guide). Unlike VARRAYs, which have fixed boundaries, Nested Tables are more flexible because the size can dynamically grow or shrink; however, there is no such thing as a free lunch, so the size change will be at the expense of more storage. If the elements of the Nested Tables are user-defined types, the java.sql.SQLData or oracle.sql.ORAData classes and a type-map must be used to manipulate the elements of the Java arrays, similar to SQL Object types, covered earlier.

The following snippets excerpted from Chapter 3 illustrate the manipulation of NESTED TABLE of scalar types, including NESTED TABLE of NUMBER, NESTED TABLE of VARCHAR2, andNESTED TABLE of DATE.

NTab_Num(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

// The following code snippet inserts a java.sql.Array row
// into NSTableTab as NTab_Num

    Statement stmt = conn.createStatement();
    OraclePreparedStatement ps = (OraclePreparedStatement)
       conn.prepareStatement ("INSERT INTO NSTableTab (ID,
NUMNT)
         VALUES(?, ?)");

    ps.setNUMBER (1, id[0]);
    ps.setARRAY (2, (ARRAY) nt[0]);
    ps.execute ();
    ps.close();

// The following code snippet retrieves and returns a NTab_Num
// as a java.sql.Array

    OraclePreparedStatement ps = (OraclePreparedStatement)
       conn.prepareStatement ("SELECT NUMNT FROM NSTableTab
                 WHERE ID = ?");
    ps.setNUMBER (1, id[0]);
    OracleResultSet rs = (OracleResultSet) ps.executeQuery();
    Array a = null;
    while (rs.next())
    {
      a = (Array) rs.getObject(1);
    }
    ps.close();
    return a;

NTab_Vc2('One', 'Two', 'Three', 'Four', 'Five', 'Six',
'Seven',
              'Eight', 'Nine', 'Ten')

// The following code snippet inserts a java.sql.Array row
// into NSTableTab as NTab_Vc2

Statement stmt = conn.createStatement();

 OraclePreparedStatement ps = (OraclePreparedStatement)
 conn.prepareStatement("INSERT INTO NSTableTab(ID,VC2NT)
VALUES(?, ?)");

    ps.setNUMBER (1, id[0]);
    ps.setARRAY (2, (ARRAY) nt[0]);
    ps.execute ();
    ps.close();

 // The following code snippet retrieves and returns a
 // NTab_Vc2 as a java.sql.Array

    OraclePreparedStatement ps = (OraclePreparedStatement)
       conn.prepareStatement ("SELECT VC2NT FROM NSTableTab
WHERE ID = ?");

     ps.setNUMBER (1, id[0]);
     OracleResultSet rs = (OracleResultSet) ps.executeQuery();
     Array a = null;
     while (rs.next())
     {
       a = (Array) rs.getObject(1);
     }
     ps.close();
     return a;

NTab_Dat('01-JAN-2003', '02-JAN-2003', '03-JAN-2003', '04-
JAN-2003',
           '05-JAN-2003', '06-JAN-2003', '07-JAN-2003', '08-
JAN-2003',
           '09-JAN-2003', '10-JAN-2003')

// The following code snippet inserts a java.sql.Array row
// into NSTableTab as NTab_Dat

 Statement stmt = conn.createStatement();

 OraclePreparedStatement ps = (OraclePreparedStatement)
 conn.prepareStatement("INSERT INTO NSTableTab(ID,DATNT)
VALUES(?, ?)");
    ps.setNUMBER (1, id[0]);
    ps.setARRAY (2, (ARRAY) nt[0]);
    ps.execute ();
    ps.close();

    id[0] = new NUMBER(id[0].intValue() + 1000);
    nt[0] = nt2;
  }

// The following code snippet retrieves and returns a NTab_Dat
// as a java.sql.Array

  Statement stmt = conn.createStatement();

  OraclePreparedStatement ps = (OraclePreparedStatement)
    conn.prepareStatement ("SELECT DATNT FROM NSTableTab WHERE
ID = ?");

    ps.setNUMBER (1, id[0]);
    OracleResultSet rs = (OracleResultSet) ps.executeQuery();

    Array a = null;
    while (rs.next())
    {
      a = (Array) rs.getObject(1);
    }
    ps.close();
    id[0] = new NUMBER(id[0].intValue() + 1000);
    nt[0] = nt2;

    return a;

The PL/SQL Associative Array type (previously PL/SQL Index-by Table) is a hash table of key/value pairs, which models unordered lists, trees, maps, dictionaries, and lookup tables found in other languages (e.g., a phone book of name/phone numbers, a dictionary of words, definition).

PL/SQL Associative Arrays are similar to Nested Tables in terms of their structure (i.e., unbound, single dimension, all elements must be of the same type), but unlike nested tables, the former can only be used in PL/SQL, are sparse, have no storage clause, and are easily extensible. They are constructed in memory each time the corresponding package is initialized or at the invocation of corresponding procedure(s).

The following PL/SQL methods are available: COUNT, DELETE, EXISTS(n), FIRST, LAST, PRIOR(n), andNEXT(n). See the PL/SQL Users Guide for more details.

The Oracle JDBC allows applications to bind PL/SQL Associative Array types as IN, OUT, andIN/OUT parameters. Prior to Oracle JDBC 10g Release 2, PL/SQL Associate Arrays were supported by JDBC-OCI only, but with 10g Release 2, both driver types support these.

The Oracle JDBC supports PL/SQL Associative Array through the setPlsqlIndexTable(), registerIndexTableOutParameter(), getOraclePlsqlIndexTable(), and getPlsqlIndexTable() methods in OraclePreparedStatement and OraleCallableStatement.

In order to use PL/SQL associative arrays, the following prerequisite steps must be performed either in a separate SQL session or directly within JDBC (see following example):

Step 1. Create a PL/SQL package and the PL/SQL associative array of the scalar type:

TYPE <index-by-table-type> IS TABLE OF <scalar>
                 INDEX BY BINARY_INTEGER

  CREATE OR REPLACE PACKAGE mypkg AS TYPE plsqldxtyp IS
        TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
  END;

Step 2. Create PL/SQL procedures and/or functions, which take the new type as IN and/or OUT and/or IN/OUT parameter(s) and manipulate the associative array (e.g., update the elements):

CREATE TABLE plsqlidxtab (col VARCHAR2(30));

create or replace procedure proc1
(feedback OUT VARCHAR2(30),  p1 in mypkg.plsqlidxtyp) is
    begin
        for i in p1.FIRST..p1.LAST loop
        insert into plsqlidxtab (plsqlidxtyp(i));
        end loop;
       feedback := 'PLSQL ASSOC ARRAY ROCKS';
    end;          

Listing 8.4 illustrates PL/SQL Associative Array.

import java.sql.*;
import java.util.*;
import java.io.*;
import oracle.jdbc.*;
import oracle.sql.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.pool.OracleDataSource;

public class myPLSQLIndexTab
{
  public static void main(String[] args) throws SQLException
  {
   //Create an OracleDataSource
   OracleDataSource ods = new OracleDataSource();
   // Set the URL, using TNS Alias with JDBC-Thin
   String url = "jdbc:oracle:thin:scott/tiger@inst1";
   ods.setURL(url);
   // Retrieve a connection
   OracleConnection conn = (OracleConnection)ods.getConnection();
   Statement stmt = conn.createStatement ();
   try {

    // define PL/SQL Index by table
    stmt.execute ("create or replace package mypkg as type plsidxtyp "+
                  " is table of varchar2(20) index by binary_integer; "+
                  "end;");

    // create procedure p1
    stmt.execute ("create or replace procedure proc1 "+
              "(p1 in out mypkg.plsidxtyp) is " +
              "begin "+
              " for i in p1.FIRST..p1.LAST loop "+
              " p1(i) := p1(i)|| ' out'; "+
              " end loop; "+
              "end;");
   stmt.close();
    // set the table values
    String [] plsqlidxarr1 =
      { "element1","element2","element3", "element4" };

    // prepare the invocation of the stored procedue
    OracleCallableStatement cstmt = (OracleCallableStatement)
      conn.prepareCall ("begin proc1 (?); end;");

    // actual size of the index-by table bind value
    int currentLen = plsqlidxarr1.length;
    int maxLen = currentLen;

    // index-by table element type
    int elemSqlType =  OracleTypes.VARCHAR;

    // index-by table element length
    int elemMaxLen = 20;

     // Bind the IN parameter
     cstmt.setPlsqlIndexTable (1, plsqlidxarr1,
                         maxLen, currentLen,
                         elemSqlType, elemMaxLen);
     // Register OUT parameter
     // cstmt.registerOutParameter (1, Types.CHAR);
    // register the OUT parameter
    cstmt.registerIndexTableOutParameter (1, maxLen,
                      elemSqlType, elemMaxLen);
    // execute the call
    cstmt.execute ();

     // retrieve the elements using JDBC default mapping
     String[] elements =
       (String[]) cstmt.getPlsqlIndexTable (1);

    // print the elements
    for (int i=0; i<elements.length; i++)
     System.out.println(" " + elements[i]);

     cstmt.close();
    } catch (SQLException ea) {
      ea.printStackTrace();
    }
  }
}
$ javac myPLSQLIndexTab.java
$ java -Doracle.net.tns_admin=$TNS_ADMIN myPLSQLIndexTab
  element1 out
  element2 out
  element3 out
  element4 out
$

These data types are not currently supported by Oracle JDBC; for example, you cannot call a PL/SQL procedure that has a Record parameter. However, using JPublisher (covered later), a Java wrapper is generated that calls the procedure in question.

Since its introduction in JDBC 1.22, result sets have evolved into various types with additional capabilities. As discussed in the overview of the JDBC specifications in Chapter 6:

Methods for navigation and row manipulation include: absolute, afterLast, beforeFirst, getFetchDirection, isAfterLast, isBeforeFirst, isFirst, isLast, last, moveToCurrentRow, moveToInsertRow, next, previous, refreshRow, relative, rowDeleted, rowInserted, rowUpdated, getRow, cancelRowUpdates, clearWarnings, close, deleteRow, findColumn, first, setFetchDirection, setFetchSize.

Methods for retrieving and updating Oracle database SQL types to and from Java types (i.e., java.sql.*) include: getArray, getAsciiStream, getBigDecimal, getBinaryStream, getBlob, getBoolean, getBytes, getCharacterStream, getClob, getConcurrency, getCursorName, getDate, getDouble, getFetchSize, getFloat, getFloat, getInt, getInt, getLong, getLong, getMetaData, getObject, getRef, getShort, getShort, getStatement, getString, getString, getTime, getTime, getTime, getTime, getTimestamp, getTimestamp, getTimestamp, getTimestamp, getType, getUnicodeStream, getUnicodeStream, getURL, updateArray, updateArray, updateAsciiStream, updateBig-Decimal, updateBinaryStream, updateBlob, updateBoolean, updateByte, updateBytes, updateCharacterStream, updateClob, updateDate, updateDouble, updateFloat, updateInt, updateLong, updateNull, updateObject, updateRef, updateRow, updateShort, updateShort, updateString, updateTime, updateTimestamp, wasNull.

Depending on its requirement, a JDBC application must specify the scrollability of the result set (i.e., forward-only vs. scrollable), the sensitivity of the result set to underlying data changes (i.e., scroll insensitive vs. scroll sensitive), and the updatability or concurrency mode (i.e., read-only vs. updatable). Table 8.5 summarizes the six result set types.

The result set type and concurrency mode are specified on statement objects during the invocation of CreateStatement(), preparedStatement(), andprepareCall() methods using the following ResultSet static constants:

Examples:

Statement stmt =
con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
     ResultSet.CONCUR_READ_ONLY);

PreparedStatement pstmt = conn.preparedStatement ("SELECT
ename, empno, sal, job FROM emp WHERE ename = ?",
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
CallableStatement cs = con.prepareCall("{ ? = call
stproc.queyr1()}",
     ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);

The result set type and result set concurrency can be obtained by invoking the following methods:

void setFetchDirection (int) throws SQLException
int getFetchDirection()throws SQLException

ResultSet rs = null;
Statement stmt =

con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
rs.setFetchDirection(ResultSet.FETCH_FORWARD);
rs = stmt.executeQuery("SELECT empn, sal, job FROM emp");

void setFetchSize (int) throws SQLException
int getFetchSize() throws SQLException

ResultSet rs = null;
Statement stmt = con.createStatement();
// set fetch size on the Statemnt object
stmt.setFetchSize(15);
rs = stmt.executeQuery("SELECT empn, sal, job FROM emp");

In Chapter 6, the historical trail of JDBC specifications support by Oracle JDBC drivers was briefly outlined. Result set support started with forward-only/read-only result set support in Oracle 8i release 8.1.5, which implements JDBC 1.22 API. Then, in Oracle 8i Release 8.1.6, a “Beta-quality”^[7]support was made available for scrollable cursors; the “production quality” support was delivered in Oracle 8i Release 8.1.7. Result set holdability was finally supported in Oracle database 10g Release 2 (i.e., ResultSet.HOLD_CURSORS_OVER_COMMIT is the default and only holdability is supported). As mentioned in Chapter 6, the Oracle database does not allow returning multiple result sets; therefore, the OracleDatabaseMetaData.supportsMultipleOpenResults will return false.

Forward-only result sets (i.e., Forward-Only/Read-Only and Forward-Only/Updatable) reside within the RDBMS, while the rows and columns that make up scrollable result sets are cached on the client side within the driver. The next section describes the implementation and navigation within scrollable result sets, updating the result sets, inserting in the result sets, sensitivity and insensitivity to changes, and refetching rows.

The following restrictions apply to Oracle JDBC implementation of scrollable result sets:

Scrolling the Result Sets

In order to navigate within the scrollable result set, applications must position the cursor by invoking its positioning methods: beforeFirst(), first(), next(), previous(), last(), afterLast(), absolute(int row), and relative(int row). At the creation of the result set object, the cursor is positioned beforeFirst, as illustrated by Figure 8.2, and then moves forward to the first row upon the invocation of the next() method. Conversely, when the last row has been retrieved, the cursor is positioned afterLast; it then may move backward using either previous() or relative() methods. Listing 8.5 illustrates this navigation.

Figure 8.2. Navigating the Result Set Rows

Example 8.5. NavigResSet

import java.sql.*;
import oracle.jdbc.*;
import oracle.sql.*;
import oracle.jdbc.pool.OracleDataSource;
import java.io.*;
/*
 *Exercise the various Result Set Navigation Methods
 *
 */

 public class NavigResSet
 {
  public static void main (String args [])throws SQLException
  {
   // Create an OracleDataSource
   OracleDataSource ods = new OracleDataSource();

   // Set the URL, using TNS Alias with JDBC-Thin
   String url = "jdbc:oracle:thin:scott/tiger@inst1";
   ods.setURL(url);
   // Retrieve a connection
   Connection conn = ods.getConnection();
   // Create a Statement
   Statement stmt = conn.createStatement
          (ResultSet.TYPE_SCROLL_INSENSITIVE,
                   ResultSet.CONCUR_READ_ONLY);
   // Set the statement fetch size to 1
   stmt.setFetchSize (1);

   // Query EMP Table
    ResultSet rset =
       stmt.executeQuery ("select EMPNO, ENAME, SAL from EMP");

    /*
     *  rs.next()
     */
     RsNext(rset);

    /*
     *  rs.previous()
     */
     RsPrevious(rset);

    /*
     *  rs.absolute()
     */
     RsAbsolute(rset);

    /*
     *  rs.relative()
     */
     RsRelative(rset);

    // Close RseultSet, statement and Connection
     rset.close();
     stmt.close();
     conn.close();
  }


  private static void RsNext(ResultSet rset) throws SQLException
  {
     System.out.println
         ("**** Forward Navig using ResultSet.next():");

     // Ensure the cursor is before the first row
     if (!rset.isBeforeFirst()) rset.beforeFirst ();

    // Iterate through the rows using next()
    while (rset.next())
       System.out.println ("Employee Id: " + rset.getString (1));
    }

   private static void RsPrevious(ResultSet rset) throws SQLException
   {
     System.out.println
          ("**** Backward Navig using ResultSet.previous():");
     // Ensure the cursor is after the last row
     if (!rset.isAfterLast()) rset.afterLast();

     // Iterate through the rows using previous()
     while (rset.previous())
        System.out.println ("Employee Id: " + rset.getString (1));
     }

     private static void RsAbsolute(ResultSet rset) throws SQLException
     {
     System.out.println
           ("**** Forward Navig using  ResultSet.absolute():");

     // Place the cursor at the first row
     int pos = 1;
     // Loop through rows
     while (rset.absolute(pos))
     {
       System.out.println ("Employee Id: " + rset.getString (1));
       pos ++;
     }
    }
   private static void RsRelative(ResultSet rset) throws SQLException
   {
    System.out.println
           ("**** Backward Navig using ResultSet.relative():");

    // Make sure the cursor is on the last row
    if (rset.getRow () == 0 || !rset.isLast())
          rset.last ();

    // relative(-1) is similar to previous()
    do
    {
      System.out.println ("Employee Id: " + rset.getString (1));
     }
     while (rset.relative (-1));
    }
}
$ javac NavigResSet.java
$ java -Doracle.net.tns_admin=$TNS_ADMIN NavigResSet

For various reasons, you may want to bypass the default (i.e., Oracle-supplied) result set cache. The Oracle JDBC driver furnishes the OracleResultSetCache interface, which allows you to implement your own result set cache.

/*
 *  OracleResultSetCache interface.
 */
public interface OracleResultSetCache
{

  /*
   * Store the data in row r and column c
   */
  public void put(int r, int c, Object value) throws IOException;


  /*
   * Retrieve the data in row r and column c.
   */
  public Object get(int row, int column) throws IOException;

  /*
   * Remove row r.
   */
  public void remove(int row) throws IOException;

  /*
   * Remove the data in row r and column c.
   */
  public void remove(int row, int column) throws IOException;

  /*
   * Clear/Purge the cache (remove all cached data).
   */
  public void clear() throws IOException;

  /*
   * Close the cache.
   */
  public void close() throws IOException;
}


import oracle.jdbc.*;
...
public class MyOracleResultSetCache implements OracleResultSetCache
{
 // OracleResultSetCache methods implementation

}

Once you have implemented your own cache, you can use the setResultSetCache() method to instruct OracleStatement, OraclePreparedStatement, or OracleCallableStatement objects—before the execution of these statements—to use the specified instance of your cache object.

public void
setResultSetCache(oracle.jdbc.OracleResultSetCache cache)
    throws SQLException;

Code Snippet:

============

OracleStatement stmt = conn.createStatement
          (ResultSet.TYPE_SCROLL_INSENSITIVE,
                   ResultSet.CONCUR_READ_ONLY);

// Instantiate the cache and assign the instance
// to the statement
      MyOracleResultSetCacheImpl rscache = new
      MyOracleResultSetCache();
      stmt.setResultSetCache(rscache);

// execute the statement
OracleResultSet rs = stmt.executeQuery(" SELECT col1, col2
FROM table1)

At this stage, you can navigate the result set from within the user-supplied cache, exactly as you would do with the Oracle-supplied cache.

The following restrictions apply to Oracle JDBC implementation of updatable result sets:

Performing an UPDATE operation in a result set requires the following four steps:

import java.sql.*;
import oracle.jdbc.*;
import oracle.sql.*;
import oracle.jdbc.pool.OracleDataSource;
import java.io.*;
/*
 * Result Set Update Test.
 */
public class UpdateRSet
{
  public static void main (String args [])throws SQLException
  {
   // Create an OracleDataSource
   OracleDataSource ods = new OracleDataSource();

   // Set the URL, using TNS Alias with JDBC-Thin
    String url = "jdbc:oracle:thin:scott/tiger@inst1";
    ods.setURL(url);
    Connection conn = ods.getConnection(); // Retrieve a connection
    conn.setAutoCommit(false); // Disable Auto Commit
    Statement stmt = null;
    try
    {
     stmt = conn.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE,
     ResultSet.CONCUR_UPDATABLE );

     String sql =
      "SELECT deptno, dname, loc FROM dept WHERE deptno = 10 FOR
            UPDATE";
     ResultSet rs = stmt.executeQuery(sql);
     while (rs.next())
         System.out.println("** Result Set " + rs.getString(1) + " " +
            rs.getString(2) + " " + rs.getString(3) );

     // The result set has only 1 row
     rs.first();
     System.out.println("*** updating LOC column in row #1 of result
           set ...");
     rs.updateString(3, "LAS VEGAS " ); // change this the next time
     System.out.println("*** saving changes to the table and
            commiting...");
     rs.updateRow();
     conn.commit();
     rs.close();
     System.out.println("Display the table  After Update");
     rs = stmt.executeQuery("select deptno, dname, loc from dept");
     while (rs.next ())
     System.out.println (rs.getString(1) + " "
                   + rs.getString(2) + " "+ rs.getString(3));
     } catch (SQLException e) {
        System.out.println( " Exception = " + e.getMessage() +
                       " SQLState = " + e.getSQLState() +
                        " ErrorCode = " + e.getErrorCode() );
      }
  }
 }
$ javac UpdateRSet.java
$ java -Doracle.net.tns_admin=$TNS_ADMIN UpdateRSet

** Result Set 10 ACCOUNTING LOS ANGELES
*** updating LOC column in row #1 of result set ...
*** saving changes to the table and commiting...
Display the table  After Update
10 ACCOUNTING LAS VEGAS
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
$

Performing a DELETE operation in a result set requires the following three steps:

Performing an INSERT operation in a result set requires the following five steps:

import java.sql.*;
import oracle.jdbc.*;
import oracle.sql.*;
import oracle.jdbc.pool.OracleDataSource;
import java.io.*;
/*
 * Result Set Insert
 */
public class InsertRSet
{
  public static void main (String args [])throws SQLException
  {
  // Create an OracleDataSource
  OracleDataSource ods = new OracleDataSource();

  // Set the URL, using TNS Alias with JDBC-Thin
  String url = "jdbc:oracle:thin:scott/tiger@inst1";
  ods.setURL(url);
  Connection conn = ods.getConnection(); // Retrieve a connection
  conn.setAutoCommit(false); // Disable Auto Commit
  Statement stmt = null;
  try
  {
    stmt = conn.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE,
    ResultSet.CONCUR_UPDATABLE );
    ResultSet rs = stmt.executeQuery("SELECT deptno, dname, loc FROM
dept");

    // Print the result set before the insert
    System.out.println("*** Printing Result Set *** ");
     while (rs.next())
        System.out.println(rs.getString(1) + " " +
           rs.getString(2) + " " + rs.getString(3) );

     System.out.println("*** Insert new row");
     // Step#1 save cursor position and move to insert-row
     rs.moveToInsertRow();

     // Step#2 populate the columns
     rs.updateString(1, "50");
     rs.updateString("dname", "MARKETING");
     rs.updateString(3, "San Diego" );



     // Step#3 save changes to the table
     System.out.println("*** saving changes to the table and
              commiting...");
     rs.insertRow();

     // Step#4 restore cursor position
     rs.moveToCurrentRow();

     // Step#5 Commit
     conn.commit();
     rs.close();

     System.out.println("*** Printing the table  After Insert");
     rs = stmt.executeQuery("select deptno, dname, loc from dept");
     while (rs.next ())
     System.out.println (rs.getString(1) + " "
                     + rs.getString(2) + " "+ rs.getString(3));
     } catch (SQLException e) {
       System.out.println( " Exception = " + e.getMessage() +
                       " SQLState = " + e.getSQLState() +
                        " ErrorCode = " + e.getErrorCode() );
     }
 }
}

$ vi InsertRSet.java
$ javac InsertRSet.java
$ java -Doracle.net.tns_admin=$TNS_ADMIN InsertRSet
*** Printing Result Set ***
10 ACCOUNTING LAS VEGAS
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
*** Insert Result Set
*** saving changes to the table and commiting...
*** Printing the table  After Insert
10 ACCOUNTING LAS VEGAS
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 MARKETING San Diego
$ 

Row prefetching consists of anticipating forward fetching by fetching multiple rows at a time. This improves performance, because the application can consume the prefetched rows without a database roundtrip. This is obviously a common requirement, but there is no standard API yet. The Oracle JDBC furnishes the following proprietary API for retrieving a window of rows, starting with the current row. The window size or fetch size can be set at statement or connection levels.

The optimal Row Prefetching value is 10. Starting with JDBC 10g Release 1, the driver allocates a fixed amount of memory to hold all column values of each row; therefore, setting Row Prefetching to a value larger than 50 will result in allocating a too large amount of memory for minimal to no additional peformance (compared with Row Prefetching value of 10). You need to trade/tune the number of columns versus the number of rows.

When retrieving data (typically LONG, LONG RAW, orLOBs data types or other data types redefined using defineColumnType(nn, Types.LONG-VARXXX)) via the LONG streaming approach (i.e.,the data is fetched into a staging buffer, from which getXXXStream returns an InputStream), the driver sets the window size to 1 (i.e., disable row prefetching).

Code Snippet
============

Connection conn = ds.getConnection();
//Connection level row-prefetch  -- default for all statements
((OracleConnection)conn).setDefaultRowPrefetch(10);

Statement stmt = conn.createStatement();

ResultSet rset = stmt.executeQuery("SELECT dname FROM dept");
System.out.println( rset.next () );
while( rset.next () )
System.out.println( rset.getString (1) );

//Statement level row-prefetch overrides the default value

( (OracleStatement)stmt ).setRowPrefetch (5);

ResultSet rset = stmt.executeQuery("SELECT ename FROM emp");
System.out.println( rset.next () );
while( rset.next() )
System.out.println( rset.getString (1) );
stmt.close();
...

During the processing of a result set, changes may have occurred to rows that are part of the result set in question. The values of columns in the ResultSet set by the updateXXX() calls may not be in sync with the state/ values of the columns in the database table (e.g., a database trigger may have modified the column values). Auto-Refresh consists of automatically refetching the column values during updateRow(). The JDBC API specified the refreshRow() method on result set objects for obtaining the latest copies of the result rows from the RDBMS tables.

The Oracle JDBC implementation of refreshRow() only supports result sets that are implemented with a ROWID pseudo column: namely, the following result set types: Scroll-Insensitive/Updatable, Scroll-Sensitive/Read-Only, andScroll-Sensitive/Updatable. However, auto-refetch incurs a database roundtrip during the updateRow(). Disabling auto-refresh avoids such a roundtrip, resulting in performance improvement.

Change visibility refers to the ability to see a new value of the same data when calling ResultSet.getXXX() following a (previous) call toResultSet.updateXXX(). Internal changes refer to changes made by the result set itself through DML (i.e.,INSERT, UPDATE, DELETE) and are summarized in Table 8.6.

The DatabaseMetaData (andOracleDatabaseMetaData) API furnish the following methods for checking the response:

public boolean ownUpdatesAreVisible(int type)
                         throws java.sql.SQLException
public boolean ownDeletesAreVisible(int type)
                         throws java.sql.SQLException
public boolean ownInsertsAreVisible(int type)
                         throws java.sql.SQLException

Where type stands for the result set type (i.e., ResultSet.TYPE_XXX).

OracleDatabaseMetaData odmd;
...
if (odmd.ownUpdatesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE))
{
// changes are visible
}

External changes refer to committed changes (the Oracle database does not allow seeing uncommitted changes, anyway) made by external sources in other database sessions, or changes incurred by a trigger as a result of internal change. There is a nuance between the visibility of external changes and the awareness that the changes happened (changes detection). The OracleDatabaseMetaData implements the deletesAreDetected(), updatesAreDetected(), andinsertsAreDetected(); however, these methods will always return false because the Oracle JDBC does not detect row change.

Table 8.7 summarizes external changes visibility:

The DatabaseMetaData (andOracleDatabaseMetaData) API furnish the following methods for checking the response:

public boolean othersUpdatesAreVisible(int type)
                              throws java.sql.SQLException
public boolean othersDeletesAreVisible(int type)
                              throws java.sql.SQLException
public boolean othersInsertsAreVisible(int type)
                              throws java.sql.SQLException

Where type stands for the result set type (i.e., ResultSet.TYPE_XXX).

OracleDatabaseMetaData odmd;
...
if (odmd.othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE))
{
// changes are visible
}

An instance of CachedRowSet object is created by invoking a constructor (i.e., new CachedRowSet() ornew OracleCachedRowSet()). It is then populated (i.e., gets its data) primarily from a result set, using either the populate() method, as illustrated by Listing 8.9, or the execute() method, discussed later.

import oracle.jdbc.rowset.*;
import javax.sql.*;
import java.sql.*;
import oracle.sql.*;
import java.io.*;
import oracle.jdbc.pool.OracleDataSource;
public class myCachedRowSet
{
 public static void main (String[] args) throws SQLException
 {
    OracleCachedRowSet crset = new OracleCachedRowSet ();
    try
    {
      // Get a connection from data source
      OracleDataSource ods = new OracleDataSource();
      String url = "jdbc:oracle:thin:scott/tiger@inst1";
      String sql = "SELECT empno, ename, sal, hiredate FROM emp";
      ods.setURL(url);
      Connection conn = ods.getConnection();
      Statement stmt = conn.createStatement ();

      // Execute the statement and produce a result set
      ResultSet rs = stmt.executeQuery ( sql);
      // set cached row set properties
      crset.setUrl(url);
      crset.setCommand (sql);
      crset.setUsername("scott");
      crset.setPassword("tiger");
      // populate the CachedRowSet with the data in the result set
      crset.populate (rs);

      // release the result set, the statement and the connection
      rs.close ();
      stmt.close ();
      conn.close ();
      System.out.println ("*** Connection  Closed *** ");

      // loop through the cached rowset and retrieve data
      for (int i = 0; crset.next () ; ++i)
        System.out.println ("*** Cached RowSet(" +(i+1) +") "
           + crset.getInt (1) +" : " + crset.getString (2)
            +" : " + crset.getFloat (3) +" : " + crset.getDate(4));
        crset.close ();

      } catch (SQLException sqle) {
        System.out.println( " SQL Exception = " + sqle.getMessage() +
                       " SQLState = " + sqle.getSQLState() +
                       " ErrorCode = " + sqle.getErrorCode() );
      }
    }
}

$ javac myCachedRowSet.java
$ java -Doracle.net.tns_admin=$TNS_ADMIN myCachedRowSet

*** Connection  Closed ***

*** Cached RowSet(1) 7369 : SMITH : 800.0 : 1980-12-17
*** Cached RowSet(2) 7499 : ALLEN : 1600.0 : 1981-02-20
*** Cached RowSet(3) 7521 : WARD : 1250.0 : 1981-02-22
*** Cached RowSet(4) 7566 : JONES : 2975.0 : 1981-04-02
*** Cached RowSet(5) 7654 : MARTIN : 1250.0 : 1981-09-28
*** Cached RowSet(6) 7698 : BLAKE : 2850.0 : 1981-05-01
*** Cached RowSet(7) 7782 : CLARK : 2450.0 : 1981-06-09
*** Cached RowSet(8) 7788 : SCOTT : 3000.0 : 1987-04-19
*** Cached RowSet(9) 7839 : KING : 5000.0 : 1981-11-17
*** Cached RowSet(10) 7844 : TURNER : 1500.0 : 1981-09-08
*** Cached RowSet(11) 7876 : ADAMS : 1100.0 : 1987-05-23
*** Cached RowSet(12) 7900 : JAMES : 950.0 : 1981-12-03
*** Cached RowSet(13) 7902 : FORD : 3000.0 : 1981-12-03
*** Cached RowSet(14) 7934 : MILLER : 1300.0 : 1982-01-23

In this example, the cached rowset is populated through the invocation of the populate() method, which takes a result set as parameter; this approach populates the cached rowset, starting with the first row of the result set. Another form of populate() takes an additional parameter representing the starting row within the result set from which to populate the cached rowset; this approach is used for chunking large result sets data to the cached rowset.

OracleCachedRowSet ocrs = new OracleCachedRowSet();
ocrs.setPageSize(8); // chunk size
ocrs.populate(rset, 2);  // populate cached row set, 8 rows at a time
                         // starting with row #2
// consume
ocrs.populate(rset, 10); // populate cached row set, 8 rows at a time
                         // starting with row #10 

The nextPage() method on the cached rowset, combined with setPageSize(), also allows you to populate and consume the result sets, iteratively, in chunks, as cached rowset.

ocrs.setPageSize(8);
while (ocrs.nextPage())
{
 //consume
}

A CachedRowset may also be populated using the following methods:

The parameterless form uses the connection defined in the rowset properties, while the second form uses the connection object furnished in parameter and then closes it afterward.

A CachedRowSet object may also get its data from any tabular data-source such as flat files or spreadsheets. The reference implementation sup ports only getting data from a ResultSet object, and so does OracleCachedRowSet (the OracleCachedRowSetReader class simply implements javax.sql.RowSetReader), but Java developers can associate an alternative SyncProvider implementation, which can furnish access to other forms of tabular data sources:

OracleCachedRowSetImpl ocrs =
      new
OracleCachedRowSetImpl("com.foo.xxx.ExcelProvider");

CachedRowsets make a result set object serializable and, therefore, movable across JVM, movable across the Web, and shipable to thin clients and handheld devices or PDAs, which are not always connected and do not have a full-fledged JDBC driver stack (e.g., Java Micro Edition stack). As a result, the cached rowset can be populated with chunks (pages) of data from the result set, updated or changed, and then synchronized back to the datasource. How does it work?

The SyncProvider furnishes a RowSetReader interface (implemented by the OracleCachedRowSetReader class) for reading from a datasource to populate the CachedRowSet, and a RowSetWriter interface (implemented by the OracleCachedRowSetWriter class) to synchronize the changes back to the datasource. During the propagation of changes, and depending on the implementation, the SyncProvider can detect conflicts between changes in the rowset and changes to the datasource. The CachedRowSet keeps track of the original state of the underlying datasource and the states in the rowset. In case of conflict, it invokes the restoreOriginal() method to resore the datasource to its state before the changes. Depending on the SyncProvider, the CacheRowSet performs an optimistic concurrency control (i.e., no lock used) or a pessimistic concurrency control (i.e., use locks). The Reference Implementation furnishes an optimistic concurrency control (RIOptimisticProvider), so does Oracle’s implementation.

// set cached row set properties
ocrs.setUrl(url);
ocrs.setCommand (sql);
ocrs.setUsername("scott");
ocrs.setPassword("tiger");

ocrs.beforeFirst();  // Navigate
ocrs..next();

pid = ocrs.getInt(1); // Get Value

// Make the rowset updatable
rowset.setReadOnly(false);
ocrs.updateInt(1,1721); // Update columns
ocrs.updateShort(2, 35);
ocrs.updateString(2, "Making Changes");

...
ocrs.updateRow();  // apply changes to the Cached Row

// Batch multiple rowset updates and
// optimize the invocation of accepChanges
...

// Assume another JDBC thread/client makes changes to the
// underlying rows at this stage    conflict
//

// Done with changes, connect to the data source ...
conn = ods.getConnection();

try
{
 // ... and propagate the changes to the data source
 ccrs.setTableName("<table>"); // the originating table
 ccrs.acceptChanges(conn);
} catch (SyncProviderException spe) {
  // Oops, conflict, restore the data source to its original state
  ccrs.restoreOriginal);
} 

Similar to other rowsets, CachedRowSet objects may have consumers (i.e., displaying the data in the rowset) that want to be notified when a change happens, so as to refresh their own state (i.e., display). The interested parties must implement the javax.sql.RowSetListener interface and then register as a listener for the cached rowset in question, using the addRowSetListener() method.

ocrs.addRowSetListener(consumer1);
...
ocrs.addRowSetListener(consumern);

The interested parties will be notified when any CachedRowSet method moves the cursor (cursorMoved), changes rows(rowChanged), or when the entire rowset has changed (rowSetChanged).

Let’s now look at the other members of the RowSet family, which are more complex disconnected rowsets built on top of CachedRowSet and include: the WebRowSets, the JoinRowSets, and the FilteredRowSets.