9.2 Mapping SQL Types to Java Types

Unfortunately there are significant variations between the SQL types supported by different database products. Even when different databases support SQL types with the same semantics, they may give those types different names. For example, most of the major databases support an SQL data type for large binary values, but Oracle calls this type LONG RAW, Sybase calls it IMAGE, Informix calls it BYTE, and DB2 calls it LONG VARCHAR FOR BIT DATA.
Fortunately, JDBC programmers will normally not need to concern themselves with the actual SQL type names used by a target database. Most of the time JDBC programmers will be programming against existing database tables, and they need not concern themselves with the exact SQL type names that were used to create these tables.
The JDBC API defines a set of generic SQL type identifiers in the class java.sql.Types. These types have been designed to represent the most commonly used SQL types. In programming with the JDBC API, programmers will normally be able to use these JDBC types to reference generic SQL types, without having to be concerned about the exact SQL type name used by the target database. These JDBC types are fully described in the next section.

The one major place where programmers may need to use SQL type names is in the SQL CREATE TABLE statement when they are creating a new database table. In this case programmers must take care to use SQL type names that are supported by their target databases. The table "JDBC Types Mapped to Database-specific SQL Types" on page 134 provides some suggestions for suitable SQL type names to be used for JDBC types for some of the major databases. We recommend that you consult your database documentation if you need exact definitions of the behavior of the various SQL types on a particular database.

If you want to be able to write portable JDBC programs that can create tables on a variety of different databases, you have two main choices. First, you can restrict yourself to using only very widely accepted SQL type names such as INTEGER, NUMERIC, or VARCHAR, which are likely to work for all databases. Or second, you can use the java.sql.DatabaseMetaData.getTypeInfo method to discover which SQL types are actually supported by a given database and select a database-specific SQL type name that matches a given JDBC type.
JDBC defines a standard mapping from the JDBC database types to Java types. For example, a JDBC INTEGER is normally mapped to a Java int. This supports a simple interface for reading and writing JDBC values as simple Java types.
The Java types do not need to be exactly isomorphic to the JDBC types; they just need to be able to represent them with enough type information to correctly store and retrieve parameters and recover results from SQL statements. For example, a Java String object does not precisely match any of the JDBC CHAR types, but it gives enough type information to represent CHAR, VARCHAR, or LONGVARCHAR successfully.