8 Mapping SQL data types into Java

8.1     Constraints

However, there is no particular reason that the Java data type needs to be exactly isomorphic to the SQL data type. For example, since Java has no fixed length arrays, we can represent both fixed length and variable length SQL arrays as variable length Java arrays. We also felt free to use Java Strings even though they don't precisely match any of the SQL CHAR types.

Table 2 shows the default Java mapping for various common SQL data types. Not all of these types will necessarily be supported by all databases. The various mappings are described more fully in the following sections.

Table 2: Standard mapping from SQL types to Java types.

SQL type	Java Type
CHAR	String
VARCHAR	String
LONGVARCHAR	String
NUMERIC	java.math.BigDecimal
DECIMAL	java.math.BigDecimal
BIT	boolean
TINYINT	byte
SMALLINT	short
INTEGER	int
BIGINT	long
REAL	float
FLOAT	double
DOUBLE	double
BINARY	byte[]
VARBINARY	byte[]
LONGVARBINARY	byte[]
DATE	java.sql.Date
TIME	java.sql.Time
TIMESTAMP	java.sql.Timestamp

Similarly table 3 shows the reverse mapping from Java types to SQL types.

Table 3: Standard mapping from Java types to SQL types.

The mapping for String will normally be VARCHAR but will turn into LONGVARCHAR if the given value exceeds the drivers limit on VARCHAR values. Similarly for byte[] and VARBINARY and LONGVARBINARY.

Java Type	SQL type
String	VARCHAR or LONGVARCHAR
java.math.BigDecimal	NUMERIC
boolean	BIT
byte	TINYINT
short	SMALLINT
int	INTEGER
long	BIGINT
float	REAL
double	DOUBLE
byte[]	VARBINARY or LONGVARBINARY
java.sql.Date	DATE
java.sql.Time	TIME
java.sql.Timestamp	TIMESTAMP

8.2     Dynamic data access

8.3     CHAR, VARCHAR, and LONGVARCHAR

These types could be mapped to either String or char[]. After considerable discussion we decided to use String, as this seemed the more appropriate type for normal use. Note that the Java String class provides a method for converting a String to a char[] and a constructor for turning a char[] into a String.

For fixed length SQL strings of type CHAR(n), the JDBC drivers will perform appropriate padding with spaces. Thus when a CHAR(n) field is retrieved from the database the resulting String will always be of length "n" and may include some padding spaces at the end. When a String is sent to a CHAR(n) field, the driver and/or the database will add any necessary padding spaces to the end of the String to bring it up to length "n".

The ResultSet.getString method allocates and returns a new String. This is suitable for retrieving normal data, but the LONGVARCHAR SQL type can be used to store multi-megabyte strings. We therefore needed to provide a way for Java programmers to retrieve a LONGVARCHAR value in chunks. We handle this by allowing programmers to retrieve a LONGVARCHAR as a Java input stream from which they can subsequently read data in whatever chunks they prefer. Java streams can be used for either Unicode or Ascii data, so the programmer may chose to use either getAsciiStream or getUnicodeStream.

8.4     DECIMAL and NUMERIC

These two types can be expressed identically in Java. The most convenient mapping uses the java.math.BigDecimal extended precision number type provided in JDK1.1

We also allow access to DECIMAL and NUMERIC as simple Strings and arrays of chars. Thus Java programmers can use getString to receive a NUMERIC or DECIMAL result.

8.5     BINARY, VARBINARY, and LONGVARBINARY

As with the LONGVARCHAR SQL type, the LONGVARBINARY SQL type can sometimes be used to return multi-megabyte data values. We therefore allow a LONGVARBINARY value to be retrieved as a Java input stream, from which programmers can subsequently read data in whatever chunks they prefer.

8.6     BIT

8.7     TINYINT, SMALLINT, INTEGER, and BIGINT

8.8     REAL, FLOAT, and DOUBLE

We map REAL to Java float, and FLOAT and DOUBLE to Java double.

REAL is required to support 7 digits of mantissa precision. FLOAT and DOUBLE are required to support 15 digits of mantissa precision.

8.9     DATE, TIME, and TIMESTAMP

There is a standard Java class java.util.Date that provides date and time information. However, this class doesn't perfectly match any of the three SQL types, as it includes both DATE and TIME information, but lacks the nanosecond granularity required for TIMESTAMP.

We therefore define three subclasses of java.util.Date. These are:

• java.sql.Date for SQL DATE information
  
  
• java.sql.Time for SQL TIME information
  
  
• java.sql.Timestamp for SQL TIMESTAMP information
  
  

In the case of java.sql.Time the year, month, and day fields of the java.util.Date base class are set to 1970, January, and 1, respectively. This is the "zero" date in the Java epoch.

The java.sql.Timestamp class extends java.util.Date by adding a nanosecond field.