The following table 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 12c Release 1 (12.1). The JDBC Type Codes column lists data type codes supported by the JDBC standard and defined in the java.sql.Types class or by Oracle in the oracle.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 the oracle.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 an oracle.sql.* Java type.

This section provides further details regarding mappings for NUMBER and user-defined types.

NUMBER Types

For the different type codes that an Oracle NUMBER value can correspond to, call the getter routine that is appropriate for the size of the data for mapping to work properly. For example, call getByte to get a Java tinyint value for an item x, 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 as java.sql.Struct, but alternatively can map to strongly typed custom Java classes. Custom Java classes can implement one of two interfaces:

• The standard java.sql.SQLData

• The Oracle-specific oracle.jdbc.OracleData

The Oracle data types in oracle.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. Starting from Oracle Database 10g, the JDBC drivers were substantially updated. As a result, in most cases the standard Java types are preferred to the data types in oracle.sql.*. In particular, java.lang.String is much more efficient than oracle.sql.CHAR.

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

• Use the oracle.jdbc.OracleData rather than the java.sql.SqlData if the OracleData functionality better suits your needs.

• Use oracle.sql.NUMBER rather than java.lang.Double if you need to retain the exact values of floating point numbers. Oracle NUMBER is a decimal representation and Java Double and Float 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.

  Use oracle.sql.NUMBER rather than java.math.BigDecimal when performance is critical and you are not manipulating the values, just reading and writing them.

• Use oracle.sql.DATE or oracle.sql.TIMESTAMP if you are using a JDK version earlier than JDK 6. Use java.sql.Date or java.sql.Timestamp if you are using JDK 6 or a later version.

  Note:

  Due to a bug in all versions of Java prior to JDK 6, construction of java.lang.Date and java.lang.Timestamp objects is slow, especially in multithreaded environments. This bug is fixed in JDK 6.

• Use oracle.sql.CHAR only when you have data from some external source, which has been represented in an Oracle character set encoding. In all other cases, you should use java.lang.String.

• STRUCT, ARRAY, BLOB, CLOB, REF, and ROWID are all the implementation classes of the corresponding JDBC standard interface types. So, there is no benefit of using the Oracle extension types as they are identical to the JDBC standard types.

• BFILE, TIMESTAMPTZ, and TIMESTAMPLTZ have no representation in the JDBC standard. You must use these Oracle extensions.

• In all other cases, you should use the standard JDBC type rather than the Oracle extensions.

The standard 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 data types that are not Oracle-specific, the getObject method 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 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. The getObject(parameter_index) method uses the default type map of the connection. 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.OracleStruct object.

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

The return type of getOracleObject is oracle.sql.Datum. The actual returned object is an instance of the appropriate oracle.sql.* class. 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.

Example: Using getOracleObject with a Result Set

The following example creates a table that contains a column of CHAR data and a column containing a BFILE locator. A SELECT statement retrieves the contents of the table as 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 return values 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 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

The following example prepares a call to the procedure myGetDate, which associates a character string with a date. The program passes "HR" to the prepared call and registers the DATE type as an output parameter. After the call is run, getOracleObject retrieves the date associated with "HR". Note that because getOracleObject returns a Datum object, the results are cast to DATE.

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

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

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

The following table lists the underlying return types for the getObject and getOracleObject methods for each Oracle SQL type.

Keep in mind the following when you use these methods:

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

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.

In addition, the OracleResultSet and OracleCallableStatement interfaces 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. 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

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

Example of Casting Return Values

This example assumes that you have fetched data of the NUMBER type as the first column of a result set. Because you want to manipulate the NUMBER data without losing precision, cast your result set to OracleResultSet and use getOracleObject to return the NUMBER data in oracle.sql.* format. If you do not cast your result set, then you have to use getObject, which returns your numeric data into a Java Float and loses some of the precision of your SQL data.

The getOracleObject method returns an oracle.sql.NUMBER object into an oracle.sql.Datum return variable unless you cast the output. Cast the getOracleObject output to oracle.sql.NUMBER if you want to use a NUMBER return variable and any of the special functionality of that class.

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

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. However, the method has been implemented so that you can enter instances of the oracle.sql.* classes that correspond to the following JDBC standard types: Blob, Clob, Struct, Ref, and Array.

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.

Example of Using setObject and setOracleObject

For a prepared statement, 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 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);

As with the 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 type code 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 type code is java.sql.Types.REF, ARRAY, or STRUCT. If the type code 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)

Binding Oracle-specific types using the appropriate setXXX 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