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