Table of Mappings

Table 11-1 shows the default mappings between SQL data types, JDBC type codes, standard Java types, and Oracle extended types.

The SQL Data Types column lists the SQL types that exist in Oracle Database 11g. The JDBC Type Codes column lists data type codes supported by the JDBC standard and defined in thejava.sql.Types class or by Oracle in theoracle.jdbc.OracleTypes class. For standard type codes, the codes are identical in these two classes.

The Standard Java Types column lists standard types defined in the Java language. The Oracle Extension Java Types column lists theoracle.sql.* Java types that correspond to each SQL data type in the database. These are Oracle extensions that let you retrieve all SQL data in the form of aoracle.sql.* Java type. Manipulating SQL data asoracle.sql.* data types minimizes conversions, improving performance and eliminating conversion losses.

Notes Regarding Mappings

This section provides further detail regarding mappings forNUMBER and user-defined types.

NUMBER Types

For the different type codes that an OracleNUMBER value can correspond to, call the getter routine that is appropriate for the size of the data for mapping to work properly. For example, callgetByte to get a Javatinyint value for an itemx, where -128 <x < 128.

User-Defined Types

User-defined types, such as objects, object references, and collections, map by default to weak Java types, such asjava.sql.Struct, but alternatively can map to strongly typed custom Java classes. Custom Java classes can implement one of two interfaces:

• The standardjava.sql.SQLData

• The Oracle-specificoracle.sql.ORAData

Standard Types Versus Oracle Types

The Oracle data types inoracle.sql store data in the same bit format as used by the database. In versions of the Oracle JDBC drivers prior to Oracle Database 10g, the Oracle data types were generally more efficient. The Oracle Database 10g JDBC drivers were substantially updated. As a result, in most cases the standard Java types are preferred to the data types inoracle.sql. In particular,java.lang.String is much more efficient thanoracle.sql.CHAR.

In general, Oracle recommends that you use the Java standard types. The exceptions to this are:

• Use theoracle.sql.OraData rather than thejava.sql.SqlData if the OraData functionality better suits your needs.

• Useoracle.sql.NUMBER rather thanjava.lang.Double if you need to retain the exact values of floating point numbers. OracleNUMBER is a decimal representation and JavaDouble andFloat are binary representations. Conversion from one format to the other can result in slight variations in the actual value represented. Additionally, the range of values that can be represented using the two formats is different.

• Useoracle.sql.DATE ororacle.sql.TIMESTAMP rather thanjava.sql.Date orjava.sql.Timestamp if you are using JDK 1.5 or earlier versions or require maximum performance. You may also use theoracle.sql data type if you want to read many date values or compute or display only a small percentage. Due to a bug in all versions of Java prior to JDK 1.6, construction ofjava.lang.Date andjava.lang.Timestamp objects is slow, especially in multithreaded environments. This bug is fixed in JDK 1.6.

Converting SQL NULL Data

Java represents a SQLNULL datum by the Java valuenull. Java data types fall into two categories: primitive types, such asbyte,int, andfloat, and object types, such as class instances. The primitive types cannot representnull. Instead, they storenull 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 representnull. The Java language defines an object container type corresponding to every primitive type that can representnull. The object container types must be used as the targets for SQL data to detect SQLNULL without ambiguity.

Testing for NULLs

You cannot use a relational operator to compareNULL values with each other or withother values. For example, the followingSELECT statement does not return any row even if theCOMM column contains one or moreNULL values.

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

Thenext example shows how to compare values for equality when some return values might beNULL. The following code returns all theENAMES from theEMP table that areNULL, if there is no value of 100 forCOMM.

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

Note:

ResultSet rset = stmt.executeQuery("SELECT emp.deptno, dept.deptno FROM emp, dept");rset.getInt("emp.deptno");

ThegetInt method in the preceding code will throw an exception. To uniquely identify the columns in thegetXXX method, you can either use column index or specify column aliases in the query and use these aliases in thegetXXX method.

Standard getObject Method

The standardgetObject method of a result set or callable statement has a return type ofjava.lang.Object. The class of the object returned is based on its SQL type, as follows:

• For SQL data types that are not Oracle-specific,getObject returns the default Java type corresponding to the SQL type of the column, following the mapping in the JDBC specification.

• For Oracle-specific data types,getObject returns an object of the appropriateoracle.sql.* class, such asoracle.sql.ROWID.

• For Oracle database objects,getObject returns a Java object of the class specified in yourtype map. Type maps specify a mapping from database named types to Java classes. ThegetObject(parameter_index) method uses the default type map of the connection. ThegetObject(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, thengetObject returns anoracle.sql.STRUCT object.

Oracle getOracleObject Method

If you want to retrieve data from a result set or callable statement as anoracle.sql.* object, then you must follow a special process. For a Result Set, you must cast the Result Set itself tooracle.jdbc.OracleResultSet and then callgetOracleObject instead ofgetObject. The same applies toCallableStatement andoracle.jdbc.OracleCallableStatement.

The return type ofgetOracleObject isoracle.sql.Datum. The actual returned object is an instance of the appropriateoracle.sql.* class. The method signature is:

public oracle.sql.Datum getOracleObject(intparameter_index)

When you retrieve data into aDatum variable, you can use the standard Javainstanceof operator to determine whichoracle.sql.* type it really is.

Example: Using getOracleObject with a Result Set

Thefollowing example creates a table that contains a column ofCHAR data and a column containing aBFILE locator. ASELECT statement retrieves the contents of the table as a result set. ThegetOracleObject then retrieves theCHAR data into thechar_datum variable and theBFILE locator into thebfile_datum variable. Note that becausegetOracleObject returns aDatum object, the return values must be cast toCHAR andBFILE, 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 bfile_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

Thefollowing example prepares a call to the proceduremyGetDate, which associates a character string with a date. The program passes "SCOTT" to the prepared call and registers theDATE type as an output parameter. After the call is run,getOracleObject retrieves the date associated with"SCOTT". Note that becausegetOracleObject returns aDatum object, the results are cast toDATE.

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-2 lists the underlying return types for thegetObject andgetOracleObject methods for each Oracle SQL type.

Keep in mind the following when you use these methods:

• getObjectalways returns data into ajava.lang.Object instance

• getOracleObject always returns data into anoracle.sql.Datum instance

You must cast the returned object to use any special functionality.

^Footnote 1 ResultSet.getObject returnsjava.sql.Timestamp only if theoracle.jdbc.J2EE13Compliant connection property is set toTRUE, else the method returnsoracle.sql.TIMESTAMP.

Other getXXX Methods

Standard JDBC provides agetXXX for each standard Java type, such asgetByte,getInt,getFloat, and so on. Each of these returns exactly what the method name implies.

In addition, theOracleResultSet andOracleCallableStatement classes provide a full complement ofgetXXX methods corresponding to all theoracle.sql.* types. EachgetXXX method returns anoracle.sql.XXX object. For example,getROWID returns anoracle.sql.ROWID object.

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

This section covers the following topics:

• Return Types of getXXX Methods

• Special Notes about getXXX Methods

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

(int columnIndex) or (String columnName)

Data Types For Returned Objects from getObject and getXXX

The return type ofgetObject isjava.lang.Object. The returned value is an instance of a subclass ofjava.lang.Object. Similarly, the return type ofgetOracleObject isoracle.sql.Datum, and the class of the returned value is a subclass oforacle.sql.Datum. You typically 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 specificgetXXX method instead of the genericgetObject orgetOracleObject methods. ThegetXXX methods enable you to avoid casting, because the return type ofgetXXX corresponds to the type of object returned. For example, the return type ofgetCLOB isoracle.sql.CLOB, as opposed tojava.lang.Object.

Example of Casting Return Values

This example assumes that you have fetched data of theNUMBER type as the first column of a result set. Because you want to manipulate theNUMBER data without losing precision, cast your result set toOracleResultSet and usegetOracleObject to return theNUMBER data inoracle.sql.* format. If you do not cast your result set, then you have to usegetObject, which returns your numeric data into a JavaFloat and loses some of the precision of your SQL data.

ThegetOracleObject method returns anoracle.sql.NUMBER object into anoracle.sql.Datum return variable unless you cast the output. Cast thegetOracleObject output tooracle.sql.NUMBER if you want to use aNUMBER return variable and any of the special functionality of that class.

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

The setObject and setOracleObject Methods

Just as there is a standardgetObject and Oracle-specificgetOracleObject in result sets and callable statements, there are also standardsetObject and Oracle-specificsetOracleObject methods inOraclePreparedStatement andOracleCallableStatement. ThesetOracleObject methods takeoracle.sql.* input parameters.

To bind standard Java types to a prepared statement or callable statement, use thesetObject method, which takes ajava.lang.Object as input. ThesetObject method does support a few of theoracle.sql.* types. However, the method has been implemented so that you can enter instances of theoracle.sql.* classes that correspond to the following JDBC standard types:Blob,Clob,Struct,Ref, andArray.

To bindoracle.sql.* types to a prepared statement or callable statement, use thesetOracleObject method, which takes a subclass oforacle.sql.Datum as input. To usesetOracleObject, you must cast your prepared statement or callable statement toOraclePreparedStatement orOracleCallableStatement.

Example of Using setObject and setOracleObject

For a prepared statement, thesetOracleObject method binds theoracle.sql.CHAR data represented by thecharVal variable to the prepared statement. To bind theoracle.sql.* data, the prepared statement must be cast toOraclePreparedStatement. Similarly, thesetObject method binds the JavaString data represented by the variablestrVal.

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

Other setXXX Methods

As with thegetXXX methods, there are several specificsetXXX methods. StandardsetXXX methods are provided for binding standard Java types, and Oracle-specificsetXXX methods are provided for binding Oracle-specific types.

Similarly, there are two forms of thesetNull method:

• voidsetNull(intparameterIndex,intsqlType)

  This is specified in the standardjava.sql.PreparedStatement interface. This signature takes a parameter index and a SQL type code defined by thejava.sql.Types ororacle.jdbc.OracleTypes class. Use this signature to set an object other than aREF,ARRAY, orSTRUCT toNULL.

• void setNull(intparameterIndex,intsqlType, Stringsql_type_name)

  With JDBC 2.0, this signature is also specified in the standardjava.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 type code isjava.sql.Types.REF,ARRAY, orSTRUCT. If the type code is other thanREF,ARRAY, orSTRUCT, then the given SQL type name is ignored.

Similarly, theregisterOutParameter method has a signature for use withREF,ARRAY, orSTRUCT data:

void registerOutParameter            (intparameterIndex, intsqlType, Stringsql_type_name)

Binding Oracle-specific types using the appropriatesetXXX methods, instead of the methods used for binding standard Java types, may offer some performance advantage.

This section covers the following topics:

• Input Data Binding

• Method setFixedCHAR for Binding CHAR Data into WHERE Clauses

/* 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; }

Note: