Mapping between SQL and Java data types

We saw that when reading from a result set via JDBC, we pull out the column values as Java primitives and objects. So this leads to the question: for a given SQL data type, what is the corresponding type of Java primitive or object that you will obtain from the ResultSet? The answer can be complex, but in practice things aren't so bad thanks to automatic conversion in many cases:

there are standard mappings between standard SQL and Java data types for standard data types;
if you use a non-standard SQL data type, the Java type will depend on the JDBC driver;
luckily, the getXXX() methods on ResultSet perform automatic conversion in many cases.

Standard SQL-Java data type mappings

The table opposite shows standard mappings between SQL data types and Java primitive and object types. In practice, many databases provide these standard types plus some subtly non-standard variants. You might find that on your column you are using the subtly non-standard variant!

If you are not sure of the type, you can always retrieve getObject() and then find out what type it is (via getClass()). Luckily, JDBC will generally come to the rescue by converting between the type returned by the JDBC driver and the type requested.

Data type conversion

The ResultSet.getXXX() methods are generally slightly "lenient". If you ask for a different type to that returned by the JDBC driver (e.g. if you ask for an int when the actual data type was a short), then JDBC will automatically convert the value to the requested type. This conversion works:

between all numeric types (though you could lose precision in the conversion);
between most types and String.

Correspondence between SQL and Java types
SQL data type	Java data type
BIT	`boolean`
TINYINT	`byte`
SMALLINT	`short`
INTEGER	`int`
BIGINT	`long`
REAL	`float`
FLOAT, DOUBLE	`double`
DATE TIME TIMESTAMP	`java.sql.Date` `java.sql.Time` `java.sql.Timestamp`
VARCHAR	`String`
BLOB	`java.sql.Blob`

The types that can't be converted automatically to a String include Blob and other types that aren't "simple" pieces of data.

1. In principle, there are standard ANSI SQL data types; most database systems then support a number of additional data types. In practice, I think most programmers don't worry about whether the types they are using are standard or not, and just choose among those available for the specific database system they're using.
2. Sun's guide to getting started with JDBC includes full tables showing which conversions are performed.