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
- between most types and String.
Correspondence between SQL and Java types
|SQL data type||Java data type|
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.