Mapping SQL and Java Types

Mapping SQL and Java Types

Overview

The JDBC API provides three sets of methods for transfering data between a database and and Java apps.

Class Description

ResultSet Retrieve SQL SELECT results as Java types

PreparedStatement Send Java types as SQL statement parameters

CallableStatement class Retrieve SQL OUT parameters as Java types

JDBC defines a set of generic SQL type identifiers in the class java.sql.Types which can be used to reference generic SQL types. Be careful when running SQL CREATE TABLE statements.
There are two ways to write portable JDBC programs:

Use only widely accepted SQL types such as INTEGER, NUMERIC, or VARCHAR.
Use java.sql.DatabaseMetaData.getTypeInfo to discover which SQL types are supported for a given database.

CHAR, VARCHAR, and LONGVARCHAR

SQL CHAR represents a small, fixed-length character string, and corresponds to JDBC CHAR. It takes a parameter that specifies the string length. Thus CHAR(10) defines a 10-character string. Max CHAR lengths is generally 254 characters.
SQL VARCHAR represents a small, variable-length character string, and corresponds to JDBC VARCHAR. It takes a parameter that specifies the maximum length of the string. When a string value is assigned to a VARCHAR variable, the database remembers the length of the assigned string and on a SELECT, it will return the exact original string.
JDBC LONGVARCHAR represents a large, variable-length character string type. All the major databases support some kind of very large variable-length string supporting up to at least a gigabyte of data, but the SQL type names vary.
Java programmers do not need to distinguish among the three types of JDBC strings, CHAR, VARCHAR, and LONGVARCHAR. Each can be expressed as a Java String, and it is possible to read and write an SQL statement correctly without knowing the exact data type that was expected.
When a CHAR(n) field is retrieved from the database, the driver will convert it to a Java String object of length n, which may include some padding spaces at the end. Conversely, when a String object is sent to a CHAR(n) field, the driver and/or the database will add any necessary padding spaces to the end of the string to bring it up to length n.
The method ResultSet.getString is recommended for retrieving data from CHAR, VARCHAR, and LONGVARCHAR fields. You can use ResultSet.getAsciiStream and ResultSet.getCharacterStream to deliver data stored in a LONGVARCHAR column as a stream of ASCII or Unicode characters.
The SQL3 CLOB data type provides an alternate means of representing large amounts of character data.

BINARY, VARBINARY, and LONGVARBINARY

The JDBC types BINARY, VARBINARY, and LONGVARBINARY are closely related. BINARY represents a small, fixed-length binary value, VARBINARY represents a small, variable-length binary value, and LONGVARBINARY represents a large, variable-length binary value.
Unfortunately, the use of these various BINARY types has not been standardized and support varies considerably among the major databases.
The SQL BINARY type corresponding to JDBC BINARY is a nonstandard SQL extension and is only implemented on some databases. It takes a parameter that specifies the number of binary bytes. Thus BINARY(12) defines a 12-byte binary type. Typically, BINARY values are limited to 254 bytes.
The SQL VARBINARY type corresponding to JDBC VARBINARY is a nonstandard SQL extension and is only implemented on some databases. It takes a parameter that specifies the maximum number of binary bytes. Thus VARBINARY(12) defines a binary type whose length may be up to 12 bytes. Typically, VARBINARY values are limited to 254 bytes. When a binary value is assigned to a VARBINARY variable, the database remembers the length of the assigned value and on a SELECT, it will return the exact original value.
Regrettably, there is no consistent SQL type name corresponding to the JDBC LONGVARBINARY type. All the major databases support some kind of very large variable length binary type supporting up to at least a gigabyte of data, but the SQL type names vary.
BINARY, VARBINARY, and LONGVARBINARY can all be expressed identically as byte arrays in the Java programming language. Since it is possible to read and write SQL statements correctly without knowing the exact BINARY data type that was expected, there is no need for programmers writing code in the Java programming language to distinguish among them.
The method recommended for retrieving BINARY and VARBINARY values is ResultSet.getBytes. If a column of type JDBC LONGVARBINARY stores a byte array that is many megabytes long, however, the method getBinaryStream is recommended. Similar to the situation with LONGVARCHAR, this method allows a programmer to retrieve a LONGVARBINARY value as a Java input stream that can be read later in smaller chunks.
The SQL3 BLOB data type, which is described later in this chapter, provides an alternate means of representing large amounts of binary data.

BIT

The JDBC type BIT represents a single bit value that can be zero or one.
SQL-92 defines an SQL BIT type. However, unlike the JDBC BIT type, this SQL-92 BIT type can be used as a parameterized type to define a fixed-length binary string. Fortunately, SQL-92 also permits the use of the simple non-parameterized BIT type to represent a single binary digit, and this usage corresponds to the JDBC BIT type. Unfortunately, the SQL-92 BIT type is only required in "full" SQL-92 and is currently supported by only a subset of the major databases. Portable code may therefore prefer to use the JDBC SMALLINT type, which is widely supported.
The recommended Java mapping for the JDBC BIT type is as a Java boolean.

TINYINT

The JDBC type TINYINT represents an 8-bit integer value between 0 and 255 that may be signed or unsigned.
The corresponding SQL type, TINYINT, is currently supported by only a subset of the major databases. Portable code may therefore prefer to use the JDBC SMALLINT type, which is widely supported.
The recommended Java mapping for the JDBC TINYINT type is as either a Java byte or a Java short. The 8-bit Java byte type represents a signed value from -128 to 127, so it may not always be appropriate for larger TINYINT values, whereas the 16-bit Java short will always be able to hold all TINYINT values.

SMALLINT

The JDBC type SMALLINT represents a 16-bit signed integer value between -32768 and 32767.
The corresponding SQL type, SMALLINT, is defined in SQL-92 and is supported by all the major databases. The SQL-92 standard leaves the precision of SMALLINT up to the implementation, but in practice, all the major databases support at least 16 bits.
The recommended Java mapping for the JDBC SMALLINT type is as a Java short.

INTEGER

The JDBC type INTEGER represents a 32-bit signed integer value ranging between -2147483648 and 2147483647.
The corresponding SQL type, INTEGER, is defined in SQL-92 and is widely supported by all the major databases. The SQL-92 standard leaves the precision of INTEGER up to the implementation, but in practice all the major databases support at least 32 bits.
The recommended Java mapping for the INTEGER type is as a Java int.

BIGINT

The JDBC type BIGINT represents a 64-bit signed integer value between -9223372036854775808 and 9223372036854775807.
The corresponding SQL type BIGINT is a nonstandard extension to SQL. In practice the SQL BIGINT type is not yet currently implemented by any of the major databases, and we recommend that its use be avoided in code that is intended to be portable.
The recommended Java mapping for the BIGINT type is as a Java long.

REAL

The JDBC type REAL represents a "single precision" floating point number that supports seven digits of mantissa.
The corresponding SQL type REAL is defined in SQL-92 and is widely, though not universally, supported by the major databases. The SQL-92 standard leaves the precision of REAL up to the implementation, but in practice all the major databases supporting REAL support a mantissa precision of at least seven digits.
The recommended Java mapping for the REAL type is as a Java float.

DOUBLE

The JDBC type DOUBLE represents a "double precision" floating point number that supports 15 digits of mantissa.
The corresponding SQL type is DOUBLE PRECISION, which is defined in SQL-92 and is widely supported by the major databases. The SQL-92 standard leaves the precision of DOUBLE PRECISION up to the implementation, but in practice all the major databases supporting DOUBLE PRECISION support a mantissa precision of at least 15 digits.
The recommended Java mapping for the DOUBLE type is as a Java double.

FLOAT

The JDBC type FLOAT is basically equivalent to the JDBC type DOUBLE. We provided both FLOAT and DOUBLE in a possibly misguided attempt at consistency with previous database APIs. FLOAT represents a "double precision" floating point number that supports 15 digits of mantissa.
The corresponding SQL type FLOAT is defined in SQL-92. The SQL-92 standard leaves the precision of FLOAT up to the implementation, but in practice all the major databases supporting FLOAT support a mantissa precision of at least 15 digits.
The recommended Java mapping for the FLOAT type is as a Java double. However, because of the potential confusion between the double precision SQL FLOAT and the single precision Java float, we recommend that JDBC programmers should normally use the JDBC DOUBLE type in preference to FLOAT.

DECIMAL and NUMERIC

The JDBC types DECIMAL and NUMERIC are very similar. They both represent fixed-precision decimal values. The corresponding SQL types DECIMAL and NUMERIC are defined in SQL-92 and are very widely implemented. These SQL types take precision and scale parameters. The precision is the total number of decimal digits supported, and the scale is the number of decimal digits after the decimal point. For most DBMSs, the scale is less than or equal to the precision. So for example, the value "12.345" has a precision of 5 and a scale of 3, and the value ".11" has a precision of 2 and a scale of 2. JDBC requires that all DECIMAL and NUMERIC types support both a precision and a scale of at least 15.
The sole distinction between DECIMAL and NUMERIC is that the SQL-92 specification requires that NUMERIC types be represented with exactly the specified precision, whereas for DECIMAL types, it allows an implementation to add additional precision beyond that specified when the type was created. Thus a column created with type NUMERIC(12,4) will always be represented with exactly 12 digits, whereas a column created with type DECIMAL(12,4) might be represented by some larger number of digits.
The recommended Java mapping for the DECIMAL and NUMERIC types is java.math.BigDecimal. The java.math.BigDecimal type provides math operations to allow BigDecimal types to be added, subtracted, multiplied, and divided with other BigDecimal types, with integer types, and with floating point types.
The method recommended for retrieving DECIMAL and NUMERIC values is ResultSet.getBigDecimal. JDBC also allows access to these SQL types as simple Strings or arrays of char. Thus, Java programmers can use getString to receive a DECIMAL or NUMERIC result. However, this makes the common case where DECIMAL or NUMERIC are used for currency values rather awkward, since it means that application writers have to perform math on strings. It is also possible to retrieve these SQL types as any of the Java numeric types.

DATE, TIME, and TIMESTAMP

There are three JDBC types relating to time:

The JDBC DATE type represents a date consisting of day, month, and year. The corresponding SQL DATE type is defined in SQL-92, but it is implemented by only a subset of the major databases. Some databases offer alternative SQL types that support similar semantics.
The JDBC TIME type represents a time consisting of hours, minutes, and seconds. The corresponding SQL TIME type is defined in SQL-92, but it is implemented by only a subset of the major databases. As with DATE, some databases offer alternative SQL types that support similar semantics.
The JDBC TIMESTAMP type represents DATE plus TIME plus a nanosecond field. The corresponding SQL TIMESTAMP type is defined in SQL-92, but it is implemented by only a very small number of databases.

Because the standard Java class java.util.Date does not match any of these three JDBC date/time types exactly (it includes both DATE and TIME information but has no nanoseconds), JDBC defines three subclasses of java.util.Date to correspond to the SQL types. They are:

java.sql.Date for SQL DATE information. The hour, minute, second, and millisecond fields of the java.util.Date base class should be set to zero. If the number of milliseconds supplied to the java.sql.Date constructor is negative, the driver will compute the date as the number of milliseconds before January 1, 1970. Otherwise, the date is computed as the specified number of milliseconds after January 1, 1970.

java.sql.Time for SQL TIME information. The year, month, and day fields of the java.util.Date base class are set to 1970, January, and 1. This is the "zero" date in the Java epoch.

java.sql.Timestamp for SQL TIMESTAMP information. This class extends java.util.Date by adding a nanoseconds field.

All three of the JDBC time-related classes are subclasses of java.util.Date, and as such, they can be used where a java.util.Date is expected. For example, internationalization methods take a java.util.Date object as an argument, so they can be passed instances of any of the JDBC time-related classes.
A JDBC Timestamp object has its parent's date and time components and also a separate nanoseconds component. If a java.sql.Timestamp object is used where a java.util.Date object is expected, the nanoseconds component is lost. However, since a java.util.Date object is stored with a precision of one millisecond, it is possible to maintain this degree of precision when converting a java.sql.Timestamp object to a java.util.Date object. This is done by converting the nanoseconds in the nanoseconds component to whole milliseconds (by dividing the number of nanoseconds by 1,000,000) and then adding the result to the java.util.Date object. Up to 999,999 nanoseconds may be lost in this conversion, but the resulting java.util.Date object will be accurate to within one millisecond.
The following code fragment is an example of converting a java.sql.Timestamp object to a java.util.Date object that is accurate to within one millisecond.
Timestamp t = new Timestamp(98724573287540L);
java.util.Date d;
d = new java.util.Date(t.getTime() + (t.getNanos() / 1000000));
New methods in the JDBC 2.0 core API make it possible for the driver to take a specified time zone into account when calculating a date, time, or timestamp. The time zone information is included in a java.util.Calendar object that is passed to new versions of the methods for getting and setting Date, Time, and Timestamp values. When no time zone is specified, the driver uses the time zone of the virtual machine running the application when it calculates a date, time, or timestamp.

SQL3

ISO and IEC have defined new data types that are commonly referred to as SQL3 types. These new data types include BLOB, CLOB, ARRAY, REF, JAVA_OBJECT, and DISTINCT.
These new types, with the exception of DISTINCT, are mapped to JDBC 2.0 core interfaces. Because DISTINCT types are mapped to a built-in type, it has no separate interface.

BLOB

The JDBC type BLOB represents an SQL3 BLOB (Binary Large Object).
A JDBC BLOB value is mapped to an instance of the Blob interface in the Java programming language. If a driver follows the standard implementation, a Blob object logically points to the BLOB value on the server rather than containing its binary data, greatly improving efficiency. The Blob interface provides methods for materializing the BLOB data on the client when that is desired.

CLOB

The JDBC type CLOB represents the SQL3 type CLOB (Character Large Object).
A JDBC CLOB value is mapped to an instance of the Clob interface in the Java programming language. If a driver follows the standard implementation, a Clob object logically points to the CLOB value on the server rather than containing its character data, greatly improving efficiency. Two of the methods on the Clob interface materialize the data of a CLOB object on the client.

ARRAY

The JDBC type ARRAY represents the SQL3 type ARRAY.
An ARRAY value is mapped to an instance of the Array interface in the Java programming language. If a driver follows the standard implementation, an Array object logically points to an ARRAY value on the server rather than containing the elements of the ARRAY object, which can greatly increase efficiency. The Array interface contains methods for materializing the elements of the ARRAY object on the client in the form of either an array or a ResultSet object.

DISTINCT

The JDBC type DISTINCT field (Types)DISTINCT represents the SQL3 type DISTINCT.
The standard mapping for a DISTINCT type is to the Java type to which the base type of a DISTINCT object would be mapped. For example, a DISTINCT type based on a CHAR would be mapped to a String object, and a DISTINCT type based on an SQL INTEGER would be mapped to an int.
The DISTINCT type may optionally have a custom mapping to a class in the Java programming language. A custom mapping consists of a class that implements the interface SQLData and an entry in a java.util.Map object.

STRUCT

The JDBC type STRUCT represents the SQL3 structured type. An SQL structured type, which is defined by a user with a CREATE TYPE statement, consists of one or more attributes. These attributes may be any SQL data type, built-in or user-defined.
The standard mapping for the SQL type STRUCT is to a Struct object in the Java programming language. A Struct object contains a value for each attribute of the STRUCT value it represents.
A STRUCT value may optionally be custom mapped to a class in the Java programming language, and each attribute in the STRUCT may be mapped to a field in the class. A custom mapping consists of a class that implements the interface SQLData and an entry in a java.util.Map object.

REF

The JDBC type REF represents an SQL3 type REF<structured type>. An SQL REF references (logically points to) an instance of an SQL structured type, which the REF persistently and uniquely identifies. In the Java programming language, the interface Ref represents an SQL REF.
If an application wants to point to an instance of an SQL structured type in the database rather than having its attribute values materialized on the client, it can use the type REF<structured type>, a reference to that SQL structured type.
A REF value is a unique identifier created specifically for a particular instance of an SQL structured type. It is persistently stored with the instance it references in a special table on the server. An application can select the REF value from its special table and use it in place of the structured type instance it identifies.

JAVA_OBJECT

The JDBC type JAVA_OBJECT, added in the JDBC 2.0 core API, makes it easier to use objects in the Java programming language as values in a database. JAVA_OBJECT is simply a type code for an instance of a class defined in the Java programming language that is stored as a database object. The type JAVA_OBJECT is used by a database whose type system has been extended so that it can store Java objects directly. The JAVA_OBJECT value may be stored as a serialized Java object, or it may be stored in some vendor-specific format.
The type JAVA_OBJECT is one of the possible values for the column DATA_TYPE in the ResultSet objects returned by various DatabaseMetaData methods, including getTypeInfo, getColumns, and getUDTs. The method getUDTs, part of the new JDBC 2.0 core API, will return information about the Java objects contained in a particular schema when it is given the appropriate parameters. Having this information available facilitates using a Java class as a database type.
For DBMSs that support them, values of type JAVA_OBJECT are stored in a database table using the method PreparedStatement.setObject. They are retrieved with the methods ResultSet.getObject or CallableStatement.getObject and updated with the ResultSet.updateObject method.
For example, assuming that instances of the class Engineer are stored in the column ENGINEERS in the table PERSONNEL, the following code fragment, in which stmt is a Statement object, prints out the names of all of the engineers.
ResultSet rs = stmt.executeQuery("SELECT ENGINEERS FROM PERSONNEL");
while (rs.next()) {
    Engineer eng = (Engineer)rs.getObject("ENGINEERS");
    System.out.println(eng.lastName + ", " + eng.firstName);
}
After the query returns the ResultSet object rs, which contains all of the instances of Engineer, the code retrieves each instance in turn with the method getObject. The value returned by getObject is an Object type, so it must be narrowed to the more specific type Engineer before being assigned to the variable eng.

Examples of Mapping

In any situation where a program written in the Java programming language retrieves data from a database, there has to be some form of mapping and data conversion. In most cases, programmers using the JDBC API will be programming with knowledge of their target database's schema. In other words, they know, for example, what tables the database contains and the data type for each column in those tables. They can therefore use the strongly typed access methods in the interfaces ResultSet, PreparedStatement, and CallableStatement. This section presents three different scenarios, describing the data mapping and conversion required in each case.

Simple SQL Statement

In the most common case, a user executes a simple SQL statement and gets back a ResultSet object with the results. Each value returned by the database and stored in a ResultSet column will have a JDBC data type. A call to a ResultSet.getXXX method will retrieve that value as a Java data type. For example, if a ResultSet column contains a JDBC FLOAT value, the method getDouble will retrieve that value as a Java double.

SQL Statement with IN Parameters

In another possible scenario, the user sends an SQL query that takes input parameters. In this case, the user calls the PreparedStatement.setXXX methods to assign a value to each input parameter. For example, PreparedStatement.setLong(1, 2345678) will assign the value 2345678 to the first parameter as a Java long. The driver will convert 2345678 to a JDBC BIGINT in order to send it to the database. Which JDBC type the driver sends to the database is determined by the standard mapping from Java types to JDBC types.

SQL Statement with INOUT Parameters

In yet another scenario, a user wants to call a stored procedure, assign values to its INOUT parameters, retrieve values from a ResultSet object, and retrieve values from the parameters. This case is rather uncommon and more complicated than most, but it gives a good illustration of mapping and data conversion.
In this scenario, the first thing to do is to assign values to the INOUT parameters using PreparedStatement.setXXX methods. In addition, because the parameters will also be used for output, the programmer must register each parameter with the JDBC type of the value that the database will return to it. This is done with the method CallableStatement.registerOutParameter, which takes one of the JDBC types defined in the class Types. A programmer retrieves the values stored in the output parameters with CallableStatement.getXXX methods.
The XXX type used for CallableStatement.getXXX must map to the JDBC type registered for that parameter. For example, if the database is expected to return an output value whose type is JDBC REAL, the parameter should have been registered as java.sql.Types.REAL. Then to retrieve the JDBC REAL value, the method CallableStatement.getFloat should be called. The method getFloat will return the value stored in the output parameter after converting it from a JDBC REAL to a Java float. To accommodate various databases and make an application more portable, it is recommended that values be retrieved from ResultSet objects before values are retrieved from output parameters.
The following code demonstrates calling a stored procedure named getTestData, which has two parameters that are both INOUT parameters and which also returns a normal JDBC ResultSet. First the Connection object con creates the CallableStatement object cstmt. Then the method setByte sets the first parameter to 25 as a Java byte. The driver will convert 25 to a JDBC TINYINT and send it to the database. The method setBigDecimal sets the second parameter with an input value of 83.75. The driver will convert this Java BigDecimal object to a JDBC NUMERIC value. Next the two parameters are registered as OUT parameters, the first parameter as a JDBC TINYINT and the second parameter as a JDBC NUMERIC with two digits after the decimal point. After cstmt is executed, the values are retrieved from the ResultSet object using ResultSet.getXXX methods. The method getString gets the value in the first column as a Java String object, getInt gets the value in the second column as a Java int, and the second getInt retrieves the value in the third column as a Java int.
Then CallableStatement.getXXX methods retrieve the values stored in the output parameters. The method getByte retrieves the JDBC TINYINT as a Java byte, and getBigDecimal retrieves the JDBC NUMERIC as a Java BigDecimal object with two digits after the decimal point. Note that when a parameter is both an input and an output parameter, the setXXX method uses the same Java type as the getXXX method (as in setByte and getByte). The registerOutParameter method registers it to the JDBC type that is mapped from the Java type (a Java byte maps to a JDBC TINYINT.
CallableStatement cstmt = con.prepareCall(
    "{call getTestData(?, ?)}");
cstmt.setByte(1, 25);
cstmt.setBigDecimal(2, 83.75);
// register the first parameter as a JDBC TINYINT and the second
// as a JDBC NUMERIC with two digits after the decimal point
cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
cstmt.registerOutParameter(2, java.sql.Types.NUMERIC, 2);
ResultSet rs = cstmt.executeQuery();
// retrieve and print values in result set
while (rs.next()) {     
    String name = rs.getString(1);
    int score = rs.getInt(2);
    int percentile = rs.getInt(3);
    System.out.print("name = " + name + ", score = " + score);

    System.out.println(", percentile = " + percentile);
}
// retrieve values in output parameters    
byte x = cstmt.getByte(1); 
java.math.BigDecimal n = cstmt.getBigDecimal(2); 
To generalize, the XXX in CallableStatement.getXXX and CallableStatement.setXXX methods is a Java type. For setXXX methods, the driver converts the Java type to a JDBC type before sending it to the database. For getXXX methods, the driver converts the JDBC type returned by the database to a Java type.
The method registerOutParameter always takes a JDBC type as an argument, and the method setObject may take a JDBC type as an argument.
Note that if a JDBC type is supplied in its optional third argument, the method setObject will cause an explicit conversion of the parameter value from a Java type to the JDBC type specified. If no target JDBC type is supplied to setObject, the parameter value will be converted to the JDBC type that is the standard mapping from the Java Object type. The driver will perform the explicit or implicit conversion before sending the parameter to the database.

Custom Mapping

The SQL3 user-defined types (UDTs), structured types and DISTINCT types, can be custom mapped to classes in the Java programming language. If a custom mapping has been set up, the driver will use that mapping instead of the standard mapping when it converts a UDT from a JDBC type to a Java type or vice versa.
UDTs are retrieved from the database with the methods ResultSet.getObject and CallableStatement.getObject; UDTs are sent back to the database with the method PreparedStatement.setObject. When an application calls a getObject method to retrieve a UDT, the driver will check to see if the type map associated with the connection has an entry for the UDT. If it does, the driver will use that type map to custom map the UDT; if there is no matching entry, the driver will use the standard mapping.
Almost all custom mapping is done using the connection's type map. It is possible, however, to have the driver use a different type map. The methods for which a custom mapping is possible have two versions, one that takes a type map and one that does not. The usual situation is not to supply a type map, in which case the driver uses the connection's type map by default. When a type map is supplied to a method, it supersedes the connection's type map, and the driver will use it instead of the type map associated with the connection to map a UDT. If the type map supplied has no entry for the UDT, the driver will use the standard mapping.
The setObject method does not take a type map as a parameter, so it operates a little differently. If setObject is passed an instance of a class that implements the SQLData interface, that is, an object that was custom mapped when it was retrieved, the driver will already have set up the mechanism for mapping it. The driver will convert the class instance, mapping the UDT back to its SQL type before sending it to the database. If the parameter to be set by the method setObject has not been custom mapped, the driver will use the standard mapping to convert it before sending it to the database.
The fact that only the methods getObject and setObject can be used to retrieve or store SQL structured types ensures that a custom mapping will be used if there is one. Four methods in the Array interface may be passed a type map so that if the ARRAY elements are UDTs, they can be custom mapped when the elements are materialized on the client. The Struct method getAttributes also has a version that takes a type map, which is used to custom map an SQL structured type's attributes before bringing them over to the client.

Dynamic Data Access

In most cases, the user wants to access results or parameters whose data types are known at compile time. However, some applications, such as generic browsers or query tools, are compiled with no knowledge of the database schema they will access. For this reason, JDBC provides support for fully dynamically typed data access in addition to static data type access.
Three methods facilitate accessing values whose data types are not known at compile time:

ResultSet.getObject
PreparedStatement.setObject
CallableStatement.getObject

If, for example, an application wants to be able to accept a variety of types as results in a ResultSet object, it can use the method ResultSet.getObject.
The methods ResultSet.getObject and CallableStatement.getObject retrieve a value as a Java Object. Since Object is the base class for all Java objects, an instance of any Java class can be retrieved as an instance of Object. However, the following Java types are built-in "primitive" types and are therefore not instances of the class Object: boolean, char, byte, short, int, long, float, and double. As a result, these types cannot be retrieved by getObject methods. However, each of these primitive types has a corresponding class that serves as a wrapper. Instances of these classes are objects, which means that they can be retrieved with the methods ResultSet.getObject and CallableStatement.getObject.

Storing Java Objects in a Database

Additions to the JDBC 2.0 core API make it easier to store Java objects in a database. The PreparedStatement.setObject method in the JDBC 1.0 API has always provided some support for persistent storage of objects defined in the Java programming language. With the new data type JAVA_OBJECT, in conjunction with the new method DatabaseMetaData.getUDTs, it is now easier to keep track of the Java objects stored in a database.

JDBC Types Mapped to Java Types
This table shows the conceptual correspondence between JDBC types and Java types. A programmer should write code with this mapping in mind. For example, if a value in the database is a SMALLINT, a short should be the data type used in a JDBC application.
All CallableStatement.getXXX methods except for getObject use this mapping. The getObject methods for both the CallableStatement and ResultSet interfaces use the mapping in "JDBC Types Mapped to Java Object Types" on page 107.

JDBC Type Java Type

CHAR String

VARCHAR String

LONGVARCHAR String

NUMERIC java.math.BigDecimal

DECIMAL java.math.BigDecimal

BIT boolean

TINYINT byte

SMALLINT short

INTEGER int

BIGINT long

REAL float

FLOAT double

DOUBLE double

BINARY byte[]

VARBINARY byte[]

LONGVARBINARY byte[]

DATE java.sql.Date

TIME java.sql.Time

TIMESTAMP java.sql.Timestamp

CLOB Clob

BLOB Blob

ARRAY Array

DISTINCT mapping of underlying type

STRUCT Struct

REF Ref

JAVA_OBJECT underlying Java class

This table has two purposes. First, it illustrates the general correspondence between types in the Java programming language and the SQL types. Second, it shows the mapping used by CallableStatement.getXXX methods and SQLInput.readXXX methods. (Note that the mapping used by the CallableStatement.getObject method is shown in Table 8.3.)

Java Types Mapped to JDBC Types
This table shows the mapping a driver should use for the ResultSet.updateXXX methods and for IN parameters. PreparedStatement.setXXX methods and RowSet.setXXX methods use this table for mapping an IN parameter, which is a Java type, to the JDBC type that will be sent to the database.

Java Type JDBC Type

String CHAR, VARCHAR, or LONGVARCHAR

java.math.BigDecimal NUMERIC

boolean BIT

byte TINYINT

short SMALLINT

int INTEGER

long BIGINT

float REAL

double DOUBLE

byte[] BINARY, VARBINARY, or LONGVARBINARY

java.sql.Date DATE

java.sql.Time TIME

java.sql.Timestamp TIMESTAMP

Clob CLOB

Blob BLOB

Array ARRAY

Struct STRUCT

Ref REF

Java class JAVA_OBJECT

This table shows the conversions used for IN parameters before they are sent to the DBMS, used by the PreparedStatement.setXXX and RowSet.setXXX methods. These same conversions are also used by ResultSet.updateXXX methods and SQLOutput.writeXXX methods. (Note that the PreparedStatement.setObject and RowSet.setObject methods use the mapping in Table 8.4.)
The mapping for String will normally be VARCHAR but will turn into LONGVARCHAR if the given value exceeds the driver's limit on VARCHAR values. The same is true for byte[], which may be mapped to either VARBINARY or LONGVARBINARY values, depending on the driver's limit on VARBINARY values. In most cases, the choice between CHAR and VARCHAR is not significant. In any case, drivers will just make the right choice. The same is true for the choice between BINARY and VARBINARY.

JDBC Types Mapped to Java Object Types
ResultSet.getObject and CallableStatement.getObject use the mapping shown in this table for standard mappings.

JDBC Type Java Object Type

CHAR String

VARCHAR String

LONGVARCHAR String

NUMERIC java.math.BigDecimal

DECIMAL java.math.BigDecimal

BIT Boolean

TINYINT Integer

SMALLINT Integer

INTEGER Integer

BIGINT Long

REAL Float

FLOAT Double

DOUBLE Double

BINARY byte[]

VARBINARY byte[]

LONGVARBINARY byte[]

DATE java.sql.Date

TIME java.sql.Time

TIMESTAMP java.sql.Timestamp

DISTINCT Object type of underlying type

CLOB Clob

BLOB Blob

ARRAY Array

STRUCT Struct or SQLData

REF Ref

JAVA_OBJECT underlying Java class

This table shows the mapping from JDBC types to Java object types that is used by the ResultSet.getObject and CallableStatement.getObject methods.

Java Object Types Mapped to JDBC Types
PreparedStatement.setObject and RowSet.setObject use the mapping shown in this table when no parameter specifying a target JDBC type is provided.

Java Object Type JDBC Type

String CHAR, VARCHAR, or LONGVARCHAR

java.math.BigDecimal NUMERIC

Boolean BIT

Integer INTEGER

Long BIGINT

Float REAL

Double DOUBLE

byte[] BINARY, VARBINARY, or LONGVARBINARY

java.sql.Date DATE

java.sql.Time TIME

java.sql.Timestamp TIMESTAMP

Clob CLOB

Blob BLOB

Array ARRAY

Struct STRUCT

Ref REF

Java class JAVA_OBJECT

These are the mappings used by the PreparedStatement.setObject method when no parameter specifying a target JDBC type is given. (The JDBC types that may be specified to the PreparedStatement.setObject method are shown in Table 8.5.)
Note that the mapping for String will normally be VARCHAR but will turn into LONGVARCHAR if the given value exceeds the driver's limit on VARCHAR values. The case is similar for byte[], which will be VARBINARY or LONGVARBINARY, depending on the driver's limit for VARBINARY values.

Class	Description
ResultSet	Retrieve SQL SELECT results as Java types
PreparedStatement	Send Java types as SQL statement parameters
CallableStatement class	Retrieve SQL OUT parameters as Java types

JDBC Type	Java Type
CHAR	String
VARCHAR	String
LONGVARCHAR	String
NUMERIC	java.math.BigDecimal
DECIMAL	java.math.BigDecimal
BIT	boolean
TINYINT	byte
SMALLINT	short
INTEGER	int
BIGINT	long
REAL	float
FLOAT	double
DOUBLE	double
BINARY	byte[]
VARBINARY	byte[]
LONGVARBINARY	byte[]
DATE	java.sql.Date
TIME	java.sql.Time
TIMESTAMP	java.sql.Timestamp
CLOB	Clob
BLOB	Blob
ARRAY	Array
DISTINCT	mapping of underlying type
STRUCT	Struct
REF	Ref
JAVA_OBJECT	underlying Java class

Java Type	JDBC Type
String	CHAR, VARCHAR, or LONGVARCHAR
java.math.BigDecimal	NUMERIC
boolean	BIT
byte	TINYINT
short	SMALLINT
int	INTEGER
long	BIGINT
float	REAL
double	DOUBLE
byte[]	BINARY, VARBINARY, or LONGVARBINARY
java.sql.Date	DATE
java.sql.Time	TIME
java.sql.Timestamp	TIMESTAMP
Clob	CLOB
Blob	BLOB
Array	ARRAY
Struct	STRUCT
Ref	REF
Java class	JAVA_OBJECT