11 Accessing and Manipulating Oracle Data

Standard Types Versus Oracle Types

In processing speed and effort, the oracle.sql.* classes usually provide the most efficient way of representing SQL data. These classes store the usual representations of SQL data as byte arrays. They do not reformat the data.

In Oracle 10g, the implementation of the JDBC drivers has been changed in order to improve overall performance. As a result, all character data is converted to Java chars, which are in the UCS2 character set. A result of this is that oracle.sql.CHAR is no longer the most efficient way to access character data. In order to construct a CHAR, JDBC must convert the Java chars to bytes encoded in the appropriate character set. This additional conversion causes a reduction in performance. At this release, unlike earlier versions, the most efficient way to access character data in JDBC is through the Java type 3. It is worth noting that the NUMBER, DATE and other conversions are much faster in 10g Release 1 (10.1) and the performance advantage of using the oracle.sql types is correspondingly less.

Converting SQL NULL Data

Java represents a SQL NULL datum by the Java value null. Java datatypes fall into two categories: primitive types (such as byte, int, float) and object types (class instances). The primitive types cannot represent null. Instead, they store the null as the value zero (as defined by the JDBC specification). This can lead to ambiguity when you try to interpret your results.

In contrast, Java object types can represent null. The Java language defines an object wrapper type corresponding to every primitive type (for example, Integer for int, Float for float) that can represent null. The object wrapper types must be used as the targets for SQL data to detect SQL NULL without ambiguity.

Testing for NULLs

You cannot use a relational operator to compare NULL values with each other or with other values. For example, the following SELECT statement fails if the COMM column contains one or more NULLs. This SELECT does not return any rows.

PreparedStatement pstmt = conn.prepareStatement(
  "SELECT * FROM EMP WHERE COMM = ?"); 
pstmt.setNull(1, java.sql.Types.VARCHAR); 

The next example shows how to compare values for equality when some return values might be NULL. The following code returns all the ENAMES from the EMP table that are NULL, if there is no value of 100 for COMM.

PreparedStatement pstmt = conn.prepareStatement("SELECT ENAME FROM EMP 
  WHERE COMM =? OR  ((COMM IS NULL) AND (? IS NULL))"); 
pstmt.setBigDecimal(1, new BigDecimal(100)); 
pstmt.setNull(2, java.sql.Types.VARCHAR); 

Standard getObject() Method

The standard JDBC getObject() method of a result set or callable statement returns data into a java.lang.Object object. The format of the data returned is based on its original type, as follows:

• For SQL datatypes that are not Oracle-specific, getObject() returns the default Java type corresponding to the column's SQL type, following the mapping specified in the JDBC specification.

• For Oracle-specific datatypes (such as ROWID, discussed in "Oracle ROWID Type"), getObject() returns an object of the appropriate oracle.sql.* class (such as oracle.sql.ROWID).

• For Oracle objects, getObject() returns an object of the Java class specified in your type map. (Type maps specify the correlation between Java classes and database SQL types and are discussed in "Understanding Type Maps for SQLData Implementations".) The getObject(parameter_index) method uses the connection's default type map. The getObject(parameter_index, map) enables you to pass in a type map. If the type map does not provide a mapping for a particular Oracle object, then getObject() returns an oracle.sql.STRUCT object.

For more information on getObject() return types, see Table 11-1, "Summary of getObject() and getOracleObject() Return Types ".

Oracle getOracleObject() Method

If you want to retrieve data from a result set or callable statement into an oracle.sql.* object, then cast your result set to an OracleResultSet type or your callable statement to an OracleCallableStatement type, and use the getOracleObject() method.

When you use getOracleObject(), the data will be of the appropriate oracle.sql.* type and is returned into an oracle.sql.Datum object (the oracle.sql type classes extend Datum). The signature for the method is:

public oracle.sql.Datum getOracleObject(int parameter_index)

When you have retrieved data into a Datum object, you can use the standard Java instanceof operator to determine which oracle.sql.* type it really is.

For more information on getOracleObject() return types, see Table 11-1, "Summary of getObject() and getOracleObject() Return Types ".

stmt.execute ("CREATE TABLE bfile_table (x varchar2 (30), b bfile)");
stmt.execute 
    ("INSERT INTO bfile_table VALUES ('one', bfilename ('TEST_DIR', 'file1'))");

ResultSet rset = stmt.executeQuery ("SELECT * FROM bfile_table");
while (rset.next ())
{
   CHAR char_datum = (CHAR) ((OracleResultSet)rset).getOracleObject (1);
   BFILE bfile_datum = (BFILE) ((OracleResultSet)rset).getOracleObject (2);
   ...
} 

OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall
                                   ("begin myGetDate (?, ?); end;");

cstmt.setString (1, "SCOTT");
cstmt.registerOutParameter (2, Types.DATE);
cstmt.execute ();

DATE date = (DATE) ((OracleCallableStatement)cstmt).getOracleObject (2);
...

Summary of getObject() and getOracleObject() Return Types

Table 11-1 summarizes the information in the preceding sections, "Standard getObject() Method" and "Oracle getOracleObject() Method".

This table lists the underlying return types for each method for each Oracle SQL type, but keep in mind the signatures of the methods when you write your code:

• getObject(): Always returns data into a java.lang.Object instance.

• getOracleObject(): Always returns data into an oracle.sql.Datum instance.

You must cast the returned object to use any special functionality (see "Casting Your get Method Return Values").

For information on type compatibility between all SQL and Java types, see Table 24-1, "Valid SQL Datatype-Java Class Mappings ".

Other getXXX() Methods

Standard JDBC provides a getXXX() for each standard Java type, such as getByte(), getInt(), getFloat(), and so on. Each of these returns exactly what the method name implies (a byte, an int, a float, and so on).

In addition, the OracleResultSet and OracleCallableStatement classes provide a full complement of getXXX() methods corresponding to all the oracle.sql.* types. Each getXXX() method returns an oracle.sql.XXX object. For example, getROWID() returns an oracle.sql.ROWID object.

Some of these extensions are taken from the JDBC 2.0 specification. They return objects of type java.sql.* instead of oracle.sql.*. For example, compare the following method names and return types:

java.sql.Blob getBlob(int parameter_index) 

oracle.sql.BLOB getBLOB(int parameter_index)  

Although there is no particular performance advantage in using the specific getXXX() methods, they can save you the trouble of casting, because they return specific object types.

(int columnIndex, int scale) or (String columnName, int scale)

(int columnIndex) or (String columnName)

Casting Your get Method Return Values

As described in "Standard getObject() Method", the return type of the Oracle implementation of getObject() is java.lang.Object. The returned value is an instance of a subclass of java.lang.Object. Similarly, the return type of getOracleObject() is oracle.sql.Datum. You normally cast the returned object to the appropriate class so that you could use particular methods and functionality of that class.

In addition, you have the option of using a specific getXXX() method instead of the generic getObject() or getOracleObject() methods. The getXXX() methods enable you to avoid casting, because the return type of getXXX() corresponds to the type of object returned. For example, getCLOB() returns an oracle.sql.CLOB instance, as opposed to a java.lang.Object instance.

NUMBER x = (NUMBER)ors.getOracleObject(1);

Datum rawdatum = ors.getOracleObject(1);
...
CharacterSet cs = ((NUMBER)rawdatum).getCharacterSet();

Standard setObject() and Oracle setOracleObject() Methods

Just as there is a standard getObject() and Oracle-specific getOracleObject() in result sets and callable statements for retrieving data, there is also a standard setObject() and an Oracle-specific setOracleObject() in Oracle prepared statements and callable statements for updating data. The setOracleObject() methods take oracle.sql.* input parameters.

To bind standard Java types to a prepared statement or callable statement, use the setObject() method, which takes a java.lang.Object as input. The setObject() method does support a few of the oracle.sql.* types—it has been implemented so that you can also input instances of the oracle.sql.* classes that correspond to JDBC 2.0-compliant Oracle extensions: BLOB, CLOB, BFILE, STRUCT, REF, and ARRAY.

To bind oracle.sql.* types to a prepared statement or callable statement, use the setOracleObject() method, which takes an oracle.sql.Datum (or any subclass) as input. To use setOracleObject(), you must cast your prepared statement or callable statement to an OraclePreparedStatement or OracleCallableStatement object.

CHAR charVal=(CHAR)((OracleResultSet)rs).getOracleObject(1);
String strVal=(String)rs.getObject(2);
...

PreparedStatement ps= conn.prepareStatement("text_of_prepared_statement");
((OraclePreparedStatement)ps).setOracleObject(1,charVal);
ps.setObject(2,strVal);

Other setXXX() Methods

As with getXXX() methods, there are several specific setXXX() methods. Standard setXXX() methods are provided for binding standard Java types, and Oracle-specific setXXX() methods are provided for binding Oracle-specific types.

Similarly, there are two forms of the setNull() method:

• void setNull(int parameterIndex, int sqlType)

  This is specified in the standard java.sql.PreparedStatement interface. This signature takes a parameter index and a SQL typecode defined by the java.sql.Types or oracle.jdbc.OracleTypes class. Use this signature to set an object other than a REF, ARRAY, or STRUCT to NULL.

• void setNull(int parameterIndex, int sqlType, String sql_type_name)

  With JDBC 2.0, this signature is also specified in the standard java.sql.PreparedStatement interface. This method takes a SQL type name in addition to a parameter index and a SQL type code. Use this method when the SQL typecode is java.sql.Types.REF, ARRAY, or STRUCT. (If the typecode is other than REF, ARRAY, or STRUCT, then the given SQL type name is ignored.)

Similarly, the registerOutParameter() method has a signature for use with REF, ARRAY, or STRUCT data:

void registerOutParameter
            (int parameterIndex, int sqlType, String sql_type_name)

For binding Oracle-specific types, using the appropriate specific setXXX() methods instead of methods for binding standard Java types may offer some performance advantage.

/* Schema is :
 create table my_table (col1 char(10));
 insert into my_table values ('JDBC');
*/
 PreparedStatement pstmt = conn.prepareStatement 
                    ("select count(*) from my_table where col1 = ?");

 pstmt.setString (1, "JDBC");  // Set the Bind Value
 runQuery (pstmt);             // This will print " No of rows are 0"

 CHAR ch = new CHAR("JDBC      ", null);
 ((OraclePreparedStatement)pstmt).setCHAR(1, ch); // Pad it to 10 bytes
 runQuery (pstmt);             // This will print "No of rows are 1"

 ((OraclePreparedStatement)pstmt).setFixedCHAR(1, "JDBC");
  runQuery (pstmt);            // This will print "No of rows are 1"
 
 void runQuery (PreparedStatement ps)
 {    
   // Run the Query
   ResultSet rs = pstmt.executeQuery ();

   while (rs.next())
     System.out.println("No of rows are " + rs.getInt(1));
   
   rs.close();
   rs = null;
 }