Oracle8i SQLJ Developer's Guide and Reference
Release 8.1.5

A64684-01

Library

Product

Contents

Index

Prev  Chap Top Next

Data Access and Manipulation: Oracle Types vs. Java Types

This section contains the following subsections:

This section describes data access in oracle.sql.* formats as opposed to Java formats. As discussed in the introduction to this chapter, the oracle.sql.* formats are a key factor of the Oracle JDBC extensions, offering significant advantages in efficiency and precision in manipulating SQL data.

Using oracle.sql.* formats involves casting your result sets and statements to OracleResultSet, OracleStatement, OraclePreparedStatement, and OracleCallableStatement objects as appropriate, and using the getOracleObject(), setOracleObject(), getXXX(), and setXXX() (where XXX corresponds to the types in the oracle.sql package) methods of these classes. Refer to the Javadoc for additional information about these classes and methods.

Data Conversion Considerations

When JDBC programs retrieve SQL data into Java variables, the SQL data is converted to the Java datatypes of those variables. The Java datatypes can be represented as members of the oracle.sql package instead of as members of the java.lang or java.sql.Types packages. In processing speed and effort, the oracle.sql.* classes 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 or perform any character-set conversions (aside from the usual network conversions) on it. The data remains in SQL format; therefore, no information is lost. For SQL primitive types (such as NUMBER, and CHAR), the oracle.sql.* classes simply wrap the SQL data. For SQL structured types (such as objects and arrays), the classes provide additional information such as conversion methods and structure details.

If you are moving data within the database, then you will probably want to keep your data in oracle.sql.* format. If you are displaying the data, or performing calculations on it in a Java application running outside of the database, then you will probably want to represent the data as a member of java.sql.Types.* or java.lang.*. Similarly, if you are using a parser that expects the data to be in Java format, you must represent the data in one of the Java formats instead of as an oracle.sql.*.

Converting SQL NULL Data

Java represents a SQL NULL datum by the Java value null. Java datatypes fall into two categories: the fixed set of scalar types (such as byte, int, float) and object types (such as objects and arrays). The Java scalar 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 scalar 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.

Using Result Set and Statement Extensions

The JDBC Statement object returns an OracleResultSet object, typed as a java.sql.ResultSet. If you want to apply only standard JDBC methods to the object, keep it as a ResultSet type. However, if you want to use the Oracle extensions on the object, you must cast it to an OracleResultSet type. The object is unchanged. The type by which the Java compiler will identify the object is changed.

When you execute a SELECT statement in a Java application using a standard JDBC Statement object, Oracle's JDBC drivers return a java.sql.ResultSet object. You can use this standard ResultSet object if all you need are standard JDBC ResultSet methods, but to use Oracle extensions you must cast the result set to an OracleResultSet object.

For example, assuming you have a standard Statement object stmt, do the following if you want to use only standard JDBC ResultSet methods:

ResultSet rs = stmt.executeQuery("SELECT * FROM emp");

If you need the extended functionality provided by the Oracle extensions to JDBC, you can select the results into a standard ResultSet object, as above, and then cast that object into an OracleResultSet object later.

Similarly, when you want to execute a stored procedure using a callable statement, the JDBC drivers will return an OracleCallableStatement object typed as a java.sql.CallableStatement. If you want to apply only standard JDBC methods to the object, then keep it as a CallableStatement type. However, if you want to use the Oracle extensions on the object, you must cast it to an OracleCallableStatement type. The object is unchanged. The type by which the Java compiler identifies the object is changed.

You use the standard JDBC java.sql.Connection.prepareStatement() method to create a PreparedStatement object. If you want to apply only standard JDBC methods to the object, keep it as a PreparedStatement type. However, if you want to use the Oracle extensions on the object, you must cast it to an OraclePreparedStatement type. The object is unchanged. The type by which the Java compiler identifies the object is changed.

Key extensions to the result set and statement classes include getOracleObject() and setOracleObject() methods that you can use to access and manipulate data in oracle.sql.* formats instead of standard Java formats. For more information see the next section: "Comparing get and set Methods for oracle.sql.* Format with Java Format".

Comparing get and set Methods for oracle.sql.* Format with Java Format

This section describes get and set methods, particularly the JDBC standard getObject() and setObject() methods and the Oracle-specific getOracleObject() and setOracleObject() methods, and how to access data in oracle.sql.* format compared with Java format.

Although there are specific getXXX() methods for all of the Oracle SQL types (as described in "Other getXXX() Methods"), you can use the general get methods for convenience or simplicity, or if you are not certain in advance what type of data you will receive.

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 more information on getObject() return types, see Table 4-3, "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 Datum object. The prototype 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 4-3, "Summary of getObject() and getOracleObject() Return Types".

Example: Using getOracleObject() with a ResultSet

The following example creates a table that contains a column of character data (in this case, a row number) and a column containing a BFILE locator. A SELECT statement gets the contents of the table into a result set. The getOracleObject() then retrieves the CHAR data into the char_datum variable and the BFILE locator into the bfile_datum variable. Note that because getOracleObject() returns a Datum object, the results must be cast to CHAR and BFILE respectively.

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 string_table");
  while (rset.next ())
   {
    CHAR char_datum = (CHAR) ((OracleResultSet)rset).getOracleObject (1);
    BFILE bfile_datum = (BFILE) ((OracleResultSet)rset).getOracleObject (2);
    ...
   }     
Example: Using getOracleObject() in a Callable Statement

The following example prepares a call to the procedure myGetDate(), which associates a character string (in this case a name) with a date. The program passes the string SCOTT to the prepared call, and registers the DATE type as an output parameter. After the call is executed, getOracleObject() retrieves the date associated with the name SCOTT. Note that since getOracleObject() returns a Datum object, the results are cast to a DATE object.

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 4-3 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:

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

Table 4-3 Summary of getObject() and getOracleObject() Return Types
Oracle SQL Type  getObject()
Underlying Return Type
 
getOracleObject()
Underlying Return Type
 

CHAR  

String  

oracle.sql.CHAR  

VARCHAR2  

String  

oracle.sql.CHAR  

LONG  

String  

oracle.sql.CHAR  

NUMBER  

java.math.BigDecimal  

oracle.sql.NUMBER  

RAW  

byte[]  

oracle.sql.RAW  

LONGRAW  

byte[]  

oracle.sql.RAW  

DATE  

java.sql.Timestamp  

oracle.sql.DATE  

ROWID  

oracle.sql.ROWID  

oracle.sql.ROWID  

REF CURSOR  

java.sql.ResultSet  

(not supported)  

BLOB  

oracle.sql.BLOB  

oracle.sql.BLOB  

CLOB  

oracle.sql.CLOB  

oracle.sql.CLOB  

BFILE  

oracle.sql.BFILE  

oracle.sql.BFILE  

Oracle object  

class specified in type map

OR

oracle.sql.STRUCT
(if no type map entry)  

oracle.sql.STRUCT  

Oracle object reference  

oracle.sql.REF  

oracle.sql.REF  

collection (varray or nested table)  

oracle.sql.ARRAY  

oracle.sql.ARRAY  

For information on type compatibility between all SQL and Java types, see Table 8-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 of the oracle.sql.* types. Each getXXX() method returns an oracle.sql.XXX. For example, getROWID() returns an oracle.sql.ROWID.

Some of these extensions are taken from the JDBC 2.0 specification. They return objects of type oracle.jdbc2.* instead of oracle.sql.*. For example, compare the prototypes:

oracle.jdbc2.Blob getBlob(int parameter_index) 

which returns an oracle.jdbc2 type for BLOBs, in contrast to:

oracle.sql.BLOB getBLOB(int parameter_index)  

which returns an oracle.sql type for BLOBs.

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.

Table 4-4 summarizes the underlying return types and the signature types for each getXXX() method. You must cast to an OracleResultSet or OracleCallableStatement to use methods that are Oracle-specific.

Table 4-4 Summary of getXXX() Return Types
Method  Underlying Return Type  Signature Type  Oracle Specific? 

getArray()  

oracle.sql.ARRAY  

oracle.jdbc2.Array  

Yes  

getARRAY()  

oracle.sql.ARRAY  

oracle.sql.ARRAY  

Yes  

getBfile()  

oracle.sql.BFILE  

oracle.sql.BFILE  

Yes  

getBFILE()  

oracle.sql.BFILE  

oracle.sql.BFILE  

Yes  

getBigDecimal()  

BigDecimal  

BigDecimal  

No  

getBlob()  

oracle.sql.BLOB  

oracle.jdbc2.Blob  

Yes  

getBLOB  

oracle.sql.BLOB  

oracle.sql.BLOB  

Yes  

getBoolean()  

boolean  

boolean  

No  

getByte()  

byte  

byte  

No  

getBytes()  

byte[]  

byte[]  

No  

getCHAR()  

oracle.sql.CHAR  

oracle.sql.CHAR  

Yes  

getClob()  

oracle.sql.CLOB  

oracle.jdbc2.Clob  

Yes  

getCLOB()  

oracle.sql.CLOB  

oracle.sql.CLOB  

Yes  

getDate()  

java.sql.Date  

java.sql.Date  

No  

getDATE()  

oracle.sql.DATE  

oracle.sql.DATE  

Yes  

getDouble()  

double  

double  

No  

getFloat()  

float  

float  

No  

getInt()  

int  

int  

No  

getLong()  

long  

long  

No  

getNUMBER()  

oracle.sql.NUMBER  

oracle.sql.NUMBER  

Yes  

getRAW()  

oracle.sql.RAW  

oracle.sql.RAW  

Yes  

getRef()  

oracle.sql.REF  

oracle.jdbc2.Ref  

Yes  

getREF()  

oracle.sql.REF  

oracle.sql.REF  

Yes  

getROWID()  

oracle.sql.ROWID  

oracle.sql.ROWID  

Yes  

getShort()  

short  

short  

No  

getString()  

String  

String  

No  

getSTRUCT()  

oracle.sql.STRUCT.  

oracle.sql.STRUCT  

Yes  

getTime()  

java.sql.Time  

java.sql.Time  

No  

getTimestamp  

java.sql.Timestamp  

java.sql.Timestamp  

No  

Casting Your get Method Return Values

As described in "Standard getObject() Method", Oracle's implementation of getObject() always returns a java.lang.Object and getOracleObject() always returns an oracle.sql.Datum. Usually, you would 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 as opposed to a java.lang.Object.

Example: Casting Return Values

This example assumes that you have fetched data of type CHAR into a result set (where it is in column 1). Because you want to manipulate the CHAR data without losing precision, cast your result set to an OracleResultSet ors and use getOracleObject() to return the CHAR data. (If you do not cast your result set, you have to use getObject(), which returns your character data into a Java String and loses some of the precision of your SQL data.) By casting the result set, you can use getOracleObject() and return data in oracle.sql.* format.

The getOracleObject() method returns an oracle.sql.CHAR object into an oracle.sql.Datum return variable unless you cast the output. Cast the getOracleObject() output to oracle.sql.CHAR if you want to use a CHAR return variable and later use any special functionality of that class (such as the getCharacterSet() method that returns the character set used to represent the characters).

CHAR char = (CHAR)ors.getOracleObject(1);
CharacterSet cs = char.getCharacterSet();

Alternatively, return into a generic oracle.sql.Datum return variable and cast this object later whenever you must use the CHAR getCharacterSet() method.

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

This uses the getCharacterSet() method of oracle.sql.CHAR. The getCharacterSet() method is not defined on oracle.sql.Datum and would not be reachable without the cast.

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.

You can use the setObject() method to bind standard Java types to a prepared statement or callable statement; it takes a java.lang.Object as input. You can use the setOracleObject() method to bind oracle.sql.* types; it takes an oracle.sql.Datum (or any subclass) as input. The setObject() method supports some oracle.sql.* types--see note below. For other oracle.sql.* types, you must use setOracleObject().

To use setOracleObject(), you must cast your prepared statement or callable statement to an OraclePreparedStatement or OracleCallableStatement object.


Note:

The setObject() method 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.  


Example: Using setObject() and setOracleObject() in a Prepared Statement

This example assumes that you have fetched character data into a standard result set (where it is in column 1), and you want to cast the results to an OracleResultSet so that you can use Oracle-specific formats and methods. Since you want to use the data as oracle.sql.CHAR format, cast the results of the getOracleObject() (which returns type oracle.sql.Datum) to CHAR. Similarly, since you want to manipulate the data in column 2 as strings, cast the data to a Java String type (since getObject() returns data of type Object). In this example, rs represents the result set, charVal represents the data from column 1 in oracle.sql.CHAR format, and strVal represents the data from column 2 in Java String format.

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

For some prepared statement ps, the setOracleObject() method binds the oracle.sql.CHAR data represented by the charVal variable to the prepared statement. To bind the oracle.sql.* data, the prepared statement must be cast to an OraclePreparedStatement. Similarly, the setObject() method binds the Java String data represented by the variable strVal.

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.

In addition, for compatibility with the JDBC 2.0 standard, OraclePreparedStatement and OracleCallableStatement classes provide setXXX() methods that take oracle.jdbc2 input parameters for BLOBs, CLOBs, object references, and arrays. For example, there is a setBlob() method that takes an oracle.jdbc2.Blob input parameter, and a setBLOB() method that takes an oracle.sql.BLOB input parameter.

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

Similarly, the OracleCallableStatement.registerOutParameter() method also has an overloaded method that you use when working with REFs, ARRAYs, or STRUCTs.

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

There is no particular performance advantage in using the specific setXXX() methods for binding Oracle-specific types over the methods for binding standard Java types.

Table 4-5 summarizes the input types for all of the setXXX() methods. To use methods that are Oracle-specific, you must cast your statement to an OraclePreparedStatement or OracleCallableStatement.

Table 4-5 Summary of setXXX() Input Parameter Types
Method  Input Parameter Type  Oracle Specific? 

setArray()  

oracle.jdbc2.Array  

Yes  

setARRAY()  

oracle.sql.ARRAY  

Yes  

setBfile()  

oracle.sql.BFILE  

Yes  

setBFILE()  

oracle.sql.BFILE  

Yes  

setBigDecimal()  

BigDecimal  

No  

setBlob()  

oracle.jdbc2.Blob  

Yes  

setBLOB()  

oracle.sql.BLOB  

Yes  

setBoolean()  

boolean  

No  

setByte()  

byte  

No  

setBytes()  

byte[]  

No  

setCHAR()  

oracle.sql.CHAR  

Yes  

setClob()  

oracle.jdbc2.Clob  

Yes  

setCLOB()  

oracle.sql.CLOB  

Yes  

setDate()  

java.sql.Date  

No  

setDATE()  

oracle.sql.DATE  

Yes  

setDouble()  

double  

No  

setFloat()  

float  

No  

setInt()  

int  

No  

setLong()  

long  

No  

setNUMBER()  

oracle.sql.NUMBER  

Yes  

setRAW()  

oracle.sql.RAW  

Yes  

setRef()  

oracle.jdbc2.Ref  

Yes  

setREF()  

oracle.sql.REF  

Yes  

setROWID()  

oracle.sql.ROWID  

Yes  

setShort()  

short  

No  

setString()  

String  

No  

setSTRUCT()  

oracle.sql.STRUCT  

Yes  

setTime()  

java.sql.Time  

No  

setTimestamp()  

java.sql.Timestamp  

No  

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

Using Result Set Meta Data Extensions

Although the oracle.jdbc.driver.OracleResultSetMetaData class does not implement the full JDBC 2.0 API for retrieving result set meta data, it does provide many methods to retrieve information about an Oracle result set.

The getColumnTypeName() method takes a column number and returns the SQL type name for columns of type REF, STRUCT, or ARRAY. In contrast, the getColumnType() method takes a column number and returns the SQL type. If the column stores an Oracle object or collection, then it returns an OracleTypes.STRUCT or an OracleTypes.ARRAY. For a list of the key methods provided by OracleResultSetMetadata, see "Class oracle.jdbc.driver.OracleResultSetMetaData".

The following example uses several of the methods in the OracleResultSetMetadata class to retrieve the number of columns from the EMP table, and each column's numerical type and SQL type name.

DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rset = dbmd.getTables("", "SCOTT", "EMP", null);

 while (rset.next())
 {
   OracleResultSetMetaData orsmd = ((OracleResultSet)rset).getMetaData();
   int numColumns = orsmd.getColumnCount();
   System.out.println("Num of columns = " + numColumns);

   for (int i=0; i<numColumns; i++)
   {
     System.out.print ("Column Name=" + orsmd.getColumnName (i+1));
     System.out.print (" Type=" + orsmd.getColumnType (i + 1) );
     System.out.println (" Type Name=" + orsmd.getColumnTypeName (i + 1));
  }
}

The program returns the following output:

Num of columns = 5
Column Name=TABLE_CAT Type=12 Type Name=VARCHAR2
Column Name=TABLE_SCHEM Type=12 Type Name=VARCHAR2
Column Name=TABLE_NAME Type=12 Type Name=VARCHAR2
Column Name=TABLE_TYPE Type=12 Type Name=VARCHAR2
Column Name=TABLE_REMARKS Type=12 Type Name=VARCHAR2



Prev

Top

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index