SQL Datatypes and Java Datatypes

  Return to the Java Programming Corner.


Overview

Support for different datatypes in SQL2 is poor. Since Java is an object-oriented language, support is extremely rich. Therefore a huge disconnect exists between what sits in the database adn the way you want it represented in your Java application. The SQL concept of a variable width, single-byte character array, for example, is the VARCHAR datatype. Java actually has no concept of a variable width, single-byte character array: Java doesn't even have a single-byte character type. The closest thing is the String class.

To make matters worse, many database engines internally support their own datatypes and loosely translate them to a SQL2 type. All Oracle numeric types, for example, map to the SQL NUMERIC type. JDBC, fortunately, lets you retrieve data in their Java forms defined by a JDBC-specified datatype mapping. You do not need to worry that a SQL LONG has a different representation in Sybase than it does in Oracle. You just call the ResultSet getLong() method to retrieve numbers you wish to treat as Java longs.

You do need to be somewhat concerned when designing the database, however. If you pull a 64-bit number into a Java application via getInt(), you risk getting bad data. Similarly, if you save a Java float into a numeric field with a scale of 0, you will lose data. The important rule of thumb for Java programming, however, is think and work in Java and use the database to support the Java application. Do not let the database drive Java.

These mappings are simply the JDBC specification for direct type mappings and not a law prescribing the format you must use in Java for your SQL data. In other words, you can retrieve an INTEGER column into Java as a long or put a Java Date object in a TIMESTAMP field. Some conversions are, nevertheless, nonsensical. You cannot save a Java boolean into a database DATE field.

JDBC Specification SQL to Java Datatype Mappings (SQL3 Types in Italic)

SQL Type (from java.sql.Types) Java Type
BIT boolean
TINYINT byte
SMALLINT short
INTEGER int
BIGINT long
REAL float
FLOAT double
DOUBLE double
DECIMAL java.math.BigDecimal
NUMERIC java.math.BigDecimal
CHAR java.lang.String
VARCHAR java.lang.String
LONGVARCHAR java.lang.String
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp
BINARY byte[]
VARBINARY byte[]
LONGVARBINARY byte[]
BLOB java.sql.Blob
CLOB java.sql.Clob
ARRAY java.sql.Array
REF java.sql.Ref
STRUCT java.sql.Struct

JDBC Specification Java to SQL Datatype Mappings

Java Type SQL Type (from java.sql.Types)
boolean BIT
byte TINYINT
short SMALLINT
int INTEGER
long BIGINT
float REAL
double DOUBLE
java.math.BigDecimal NUMERIC
java.lang.String VARCHAR or LONGVARCHAR
byte[] VARBINARY or LONGVARBINARY
java.sql.Date DATE
java.sql.Time TIME
java.sql.Timestamp TIMESTAMP
java.sql.Blob BLOB
java.sql.Clob CLOB
java.sql.Array ARRAY
java.sql.Ref REF
java.sql.Struct STRUCT