11 Accessing and Manipulating Oracle Data

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

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

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

11.3.1 Standard getObject() Method

The standard JDBC getObject() method of a result set or callable statement has a return type of java.lang.Object. The class of the object returned is based on its SQL 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 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 database objects, getObject() returns a Java object of the class specified in your type map. Type maps specify a mapping from database named types to Java classes; they 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, "getObject() and getOracleObject() Return Types".

11.3.2 Oracle getOracleObject() Method

If you want to retrieve data from a result set or callable statement as an oracle.sql.* object, you must follow a special process. For a ResultSet, you must cast the result set itself to oracle.sql.OracleResultSet and then invoke getOracleObject() instead of getObject(). The same applies to CallableStatement and oracle.sql.OracleCallableStatement.

The return type of getOracleObject() is oracle.sql.Datum. The actual returned object is an instance of the appropriate oracle.sql.* class (the oracle.sql.* classes extend Datum). The method signature is:

public oracle.sql.Datum getOracleObject(int parameter_index)

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

For more information on getOracleObject() return values, see Table 11-1, "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);
...

11.3.3 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 "Datatypes For Returned Objects from getObject and getXXX").

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

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

There is no performance advantage in using the specific getXXX() methods; they do save you the trouble of casting, because the return type is specific to the object being returned.

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

(int columnIndex) or (String columnName)

11.3.5 Datatypes For Returned Objects from getObject and getXXX

As described in "Standard getObject() Method", the return type 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, and the class of the returned value is a subclass of oracle.sql.Datum. You normally cast the returned object to the appropriate class to 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, the return type of getCLOB() is oracle.sql.CLOB, as opposed to java.lang.Object.

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

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

11.3.6 The setObject() and setOracleObject() Methods

Just as there is a standard getObject() and Oracle-specific getOracleObject() in result sets and callable statements, there are also standard setObject() and Oracle-specific setOracleObject() methods in OraclePreparedStatement and OracleCallableStatement. 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. REVIEWED TO HERE

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

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

11.3.7 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;
 }