Basic Features

Datatype Mappings

The Oracle JDBC drivers support standard JDBC 1.0 and 2.0 types as well as Oracle-specific BFILE and ROWID datatypes and types of the REF CURSOR category.

This section documents standard and Oracle-specific SQL-Java default type mappings.

Table of Mappings

For reference, Table 3-2 shows the default mappings between SQL datatypes, JDBC typecodes, standard Java types, and Oracle extended types.

The SQL Datatypes column lists the SQL types that exist in the database.

The JDBC Typecodes column lists data typecodes supported by the JDBC standard and defined in the java.sql.Types class, or by Oracle in the oracle.jdbc.driver.OracleTypes class. For standard typecodes, 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 datatype in the database. These are Oracle extensions that let you retrieve all SQL data in the form of a oracle.sql.* Java type. Mapping SQL datatypes into the oracle.sql datatypes lets you store and retrieve data without losing information. Refer to "Package oracle.sql" for more information on the oracle.sql.* package.

Table 3-2 Default Mappings Between SQL Types and Java Types

SQL Datatypes	JDBC Typecodes	Standard Java Types	Oracle Extension Java Types
	STANDARD JDBC 1.0 TYPES:
CHAR	java.sql.Types.CHAR	java.lang.String	oracle.sql.CHAR
VARCHAR2	java.sql.Types.VARCHAR	java.lang.String	oracle.sql.CHAR
LONG	java.sql.Types.LONGVARCHAR	java.lang.String	oracle.sql.CHAR
NUMBER	java.sql.Types.NUMERIC	java.math.BigDecimal	oracle.sql.NUMBER
NUMBER	java.sql.Types.DECIMAL	java.math.BigDecimal	oracle.sql.NUMBER
NUMBER	java.sql.Types.BIT	boolean	oracle.sql.NUMBER
NUMBER	java.sql.Types.TINYINT	byte	oracle.sql.NUMBER
NUMBER	java.sql.Types.SMALLINT	short	oracle.sql.NUMBER
NUMBER	java.sql.Types.INTEGER	int	oracle.sql.NUMBER
NUMBER	java.sql.Types.BIGINT	long	oracle.sql.NUMBER
NUMBER	java.sql.Types.REAL	float	oracle.sql.NUMBER
NUMBER	java.sql.Types.FLOAT	double	oracle.sql.NUMBER
NUMBER	java.sql.Types.DOUBLE	double	oracle.sql.NUMBER
RAW	java.sql.Types.BINARY	byte[]	oracle.sql.RAW
RAW	java.sql.Types.VARBINARY	byte[]	oracle.sql.RAW
LONGRAW	java.sql.Types.LONGVARBINARY	byte[]	oracle.sql.RAW
DATE	java.sql.Types.DATE	java.sql.Date	oracle.sql.DATE
DATE	java.sql.Types.TIME	java.sql.Time	oracle.sql.DATE
DATE	java.sql.Types.TIMESTAMP	javal.sql.Timestamp	oracle.sql.DATE
	STANDARD JDBC 2.0 TYPES:
BLOB	java.sql.Types.BLOB	java.sql.Blob	oracle.sql.BLOB
CLOB	java.sql.Types.CLOB	java.sql.Clob	oracle.sql.CLOB
user-defined object	java.sql.Types.STRUCT	java.sql.Struct	oracle.sql.STRUCT
user-defined reference	java.sql.Types.REF	java.sql.Ref	oracle.sql.REF
user-defined collection	java.sql.Types.ARRAY	java.sql.Array	oracle.sql.ARRAY
	ORACLE EXTENSIONS:
BFILE	oracle.jdbc.driver.OracleTypes.BFILE	n/a	oracle.sql.BFILE
ROWID	oracle.jdbc.driver.OracleTypes.ROWID	n/a	oracle.sql.ROWID
REF CURSOR type	oracle.jdbc.driver.OracleTypes.CURSOR	java.sql.ResultSet	oracle.jdbc.driver.OracleResultSet

Note:
Under JDK 1.1.x, the Oracle package oracle.jdbc2 is required to support JDBC 2.0 types. (Under JDK 1.2.x they are supported by the standard javal.sql package.)

For a list of all the Java datatypes to which you can validly map a SQL datatype, see "Valid SQL-JDBC Datatype Mappings".

See Chapter 6, "Overview of Oracle Extensions", for more information on type mappings. In Chapter 6 you can also find more information on the following:

packages oracle.sql, oracle.jdbc.driver, and oracle.jdbc2
type extensions for the Oracle BFILE and ROWID datatypes and user-defined types of the REF CURSOR category

Notes Regarding Mappings

This section goes into further detail regarding mappings for NUMBER and user-defined types.

Regarding 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 (for user-defined objects only)
the Oracle-specific oracle.sql.CustomDatum (primarily for user-defined objects, object references, and collections, but able to map from any SQL type where you want customized processing of any kind)

For information about custom Java classes and the SQLData and CustomDatum interfaces, see "Mapping Oracle Objects" and "Creating and Using Custom Object Classes for Oracle Objects". (Although these sections focus on custom Java classes for user-defined objects, there is some general information about other kinds of custom Java classes as well.)

Regarding NUMBER Types

For the different typecodes 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.