SQL Data Type Conversions | JDBC 3: Java Database Connectivity

Java data types aren’t exactly isomorphic to SQL data types. However, mapping SQL data types into Java enables users to store and retrieve data without losing information.

Mapping data types to Java

When types are known at compile time, the mappings shown in this section are used. Note that JDBC also provides a mechanism to fully support dynamically typed data access when result and parameter types aren’t known at compile time (see 9).

Character strings

There are three SQL data types for SQL strings:

CHAR
VARCHAR
LONGVARCHAR

In Java, the String and char[] types hold these SQL data types. String is used as the default mapping because it is possible to convert a String to a char[] and a char[] to a String. Thus reading and writing data can be achieved without knowing the exact data type expected.

Space padding is automatically done whenever dealing with fixed-length types such as SQL CHAR(n). Spaces are added to the end of a string to set its length to n when a SQL CHAR(n) must be sent to the database. When a SQL CHAR(n) field is retrieved, additional padding is performed to get a string of length n.

Note that a LONGVARCHAR may overflow a string when retrieved using ResultSet.getString(). In this case, I advise retrieving the LONGVARCHAR field in small chunks by using a Java input stream. Overflow may occur when retrieving BLOBs (binary large objects) from the database.

Numbers

SQL integer types allow for 1- (TINYINT), 2- (SMALLINT), 4- (INTEGER), and 8-byte-wide values (BIGINT). They can, therefore, be mapped to Java types such as Java byte, short, int, and long, respectively.

Fixed-point numbers can be expressed as SQL DECIMAL and SQL NUMERIC, where absolute precision is preserved. They can be mapped to java.math.BigDecimal without losing precision. SQL DECIMAL and SQL NUMERIC can be used to perform addition, subtraction, multiplication, and division. Note that these SQL types can also be accessed as Java strings, although it is difficult to perform math on strings.

Floating-point numbers are mapped as follows: SQL REAL, which requires 7 digits of mantissa precision, to Java float and SQL FLOAT and SQL DOUBLE, which require 15 digits of mantissa precision, to Java double. Note that any character or numeric type data can be retrieved from the result set as String and then converted to the required numeric type.

XREF

ResultSetMetaData in Chapter 9 provides ways for finding out the data types of fields at runtime.

Binary types

SQL types BINARY, VARBINARY, and LONGVARBINARY can be expressed as byte arrays in Java. As with LONGVARCHAR fields, LONGVARBINARY fields can be retrieved using Java streams for multimegabyte data values.

The SQL BIT data type can be mapped to the Java boolean type.

Time-related types

The SQL DATE, TIME, and TIMESTAMP data types are time-related types. They can be expressed as java.sql.Date (yyyy-mm-dd), java.sql.Time (hh:mm:ss), and java.sql.Timestamp (yyyy-mm-dd hh:mm:ss.nanosecond), respectively.

Note that java.sql.Date, java.sql.Time, and java.sql.Timestamp are three subclasses of java.util.date. Depending on which one is in use, different java.util.date members are affected. These subclasses include the following:

java.sql.Date sets the java.util.date.hour, .minute, .second, and .milliseconds fields to zero.
java.sql.Time sets the java.util.date.year, .month, and .day fields according to 1970, January 1st.
java.sql.Timestamp has a similar behavior as java.sql.Time but also sets a nanosecond field.

BLOB and CLOB

You can use the java.sql.BLOB and java.sql.CLOB values to set and retrieve large objects containing bytes and characters, respectively. Objects of these types can be passed as parameter of prepared statements. Refer to Chapter 8’s coverage of Dynamic SQL for more information on how to use prepared statements. ResultSet.getBlob(int columnIndex) can be used to retrieve a BLOB value; ResultSet.getClob(int columnIndex) can be used to retrieve a CLOB value from a result set.

ARRAY

You can use the java.sql.Array value to set and retrieve SQL ARRAY values. You can pass objects of these types as parameter of prepared statements. Refer to Chapter 8’s coverage of Dynamic SQL for more information on how to use prepared statements. ResultSet.getArray(int columnIndex) can be used to retrieve a SQL ARRAY value.

REF

You can use the java.sql.Ref type to set and retrieve referenced values. In this case, the reference is similar to a pointer to an object. Ref ref = ResultSet.getRef(int columnIndex) returns the reference, and (Customer) ref.getValue() returns the actual object referenced by ref (in this case, an instance of Customer). Structured types referenced by a Ref object are set using maps, as explained in the section "Type mapping tables," later in this chapter.

DISTINCT

SQL DISTINCT values must be set and retrieved using the appropriate setXXX() and getXXX() method for the type on which the DISTINCT type is based. For example, a custom type called MONEY and defined as NUMERIC(10, 2) can be accessed as a java.math.BigDecimal.

STRUCT

A java.sql.Struct value must be set and retrieved using setObject() and getObject() methods. The Struct interface provides a getAttributes() method to access the attributes of a structured type as an array of java.lang.Object values.

DATALINK

SQL DATALINK types refer to data residing outside the database. The value for a DATALINK is a filename, for example. With JDBC, DATALINK values are represented as URLs using java.net.URL; therefore the getURL() and setURL() methods are used to access this type of value. Note that getString() or getObject() can be used as well.

Type mapping tables

Table 7-1 shows the standard mapping from SQL data types to Java types. Although they are common SQL data types, some databases may not support them.

Table 7-1: MAPPING TABLE FROM SQL TYPES TO JAVA TYPES
SQL Type	Java Type	Java Object Type
CHAR	String	String
VARCHAR	String	String
LONGVARCHAR	String	String
NUMERIC	java.math.BigDecimal	java.math.BigDecimal
DECIMAL	java.math.BigDecimal	java.math.BigDecimal
BIT	boolean	Boolean
BOOLEAN	boolean	Boolean
TINYINT	byte	Integer
SMALLINT	short	Integer
INTEGER	int	Integer
BIGINT	long	Long
REAL	float	Float
FLOAT	double	Double
DOUBLE	double	Double
BINARY	byte[]	byte[]
VARBINARY	byte[]	byte[]
LONGVARBINARY	byte[]	byte[]
DATE	java.sql.Date	java.sql.Date
TIME	java.sql.Time	java.sql.Time
TIMESTAMP	java.sql.Timestamp	java.sql.Timestamp
CLOB	Clob	Clob
BLOB	Blob	Blob
ARRAY	Array	Array
DISTINCT	Mapping of underlying type	Mapping of underlying type
STRUCT	Struct	Struct or SQLData
REF	Ref	Ref
DATALINK	java.net.URL	java.net.URL
JAVA_OBJECT	Underlying Java class	Underlying Java class

Table 7-2 shows the default mapping from Java types to SQL data types. The mapping from String is normally VARCHAR. Where the String length exceeds the driver’s limit on VARCHAR values, it becomes LONGVARCHAR. The same occurs with byte[] and BINARY, VARBINARY, and LONGVARBINARY.

Table 7-1: MAPPING TABLE FROM JAVA TYPES TO SQL TYPES
Java Type	SQL Type
String	VARCHAR or LONGVARCHAR
java.math.BigDecimal	NUMERIC
boolean	BIT or BOOLEAN
Boolean	BIT or BOOLEAN
byte	TINYINT
short	SMALLINT
int	INTEGER
Integer	INTEGER
Java Type	SQL Type
long	BIGINT
Long	BIGINT
float	REAL
Float	REAL
double	DOUBLE
Double	DOUBLE
byte[]	BINARY, VARBINARY, or LONGVARBINARY
java.sql.Date	DATE
java.sql.Time	TIME
java.sql.Timestamp	TIMESTAMP
Clob	CLOB
Blob	BLOB
Array	ARRAY
Struct	STRUCT
Ref	REF
java.net.URL	DATALINK
Java class	JAVA_OBJECT

Customizing type mapping

Type mapping is used with SQL user-defined types (UDTs). It is a technique that enables Java developers to map SQL structured and distinct types to Java classes. By default, a SQL STRUCT type is mapped to the java.sql.Struct interface, and a SQL DISTINCT type is mapped to the associated Java class. However, when dealing with custom maps, the JDBC Driver will use the custom mapping instead of the default when STRUCT or DISTINCT types are accessed with the getObject() and setObject() methods.

Defining a type map

Consider the EMPLOYEE user-defined type. In this example, I assume that EMPLOYEE is defined as the following:

ID INTEGER, FIRSTNAME VARCHAR(50), LASTNAME VARCHAR(50), DEPT INTEGER, HIREDATE DATE

To use type mapping, you must first implement a Java class that maps to the SQL user-defined type. This class must implement the java.sql.SQLData interface because the JDBC Driver looks for two specific methods to do the mapping; these methods are readSQL(SQLInput stream, String type) and writeSQL(SQLOutput stream). Listing 7-2 provides an example matching with the EMPLOYEE UDT.

Listing 7-2: Customized Type Mapping

import java.sql.*; public class Employee implements SQLData {          public int id;          public String firstname;          public String lastname;          public int dept;          public Date hireDate;          private String sql_type;          public String getSQLTypeName() { return sql_type; }          public void readSQL(SQLInput stream, String type) throws SQLException {                  sql_type = type;                  id = stream.readInt();                  firstname = stream.readString();                  lastname = stream.readString();                  dept = stream.readInt();                  hireDate = stream.readDate();          }          public void writeSQL(SQLOutput stream) throws SQLException {                  stream.writeInt(id);                  stream.writeString(firstname);                  stream.writeString(lastname);                  stream.writeInt(dept);                  stream.writeDate(hireDate);          } }

The next step after implementing the map consists of defining the map to be used when accessing data typed as SQL UDT EMPLOYEE. This is fairly simple, as shown in Listing 7-3. The Employee class is associated to the EMPLOYEE user type using a java.util.Map object.

Listing 7-3: Setting the Map

// establish connection // ... try {          java.util.Map map = conn.getTypeMap();          // associate the Employee mapping class to the EMPLOYEE table          map.put("shemaname.EMPLOYEE", Class.forName("Employee"));          conn.setTypeMap(map);          // ... } catch (ClassNotFoundException e) {          System.out.println(e.getMessage());          exit(); }

Accessing data using a type map

Accessing data using a type map is simply done with the getObject() and setObject() methods. The following example in Listing 7-4 illustrates how an EMPLOYEE user data type value is fetched with the Employee class as customized mapping.

Listing 7-4: Accessing Data Using a Type Map

// establish connection // ... try {          java.util.Map map = conn.getTypeMap();          map.put("shemaname.EMPLOYEE", Class.forName("Employee"));          conn.setTypeMap(map);          Statement st = conn.createStatement();          ResultSet rs = st.executeQuery("SELECT empl from personnel");          Employee empl;          while (rs.next()) {                  empl = (Employee) rs.getObject(1);                  // ...          } } catch (Exception e) {          System.out.println(e.getMessage());          exit(); }

SQL inheritance

Inheritance is possible for SQL structured types. For example, a user-defined type for DEVELOPER might extend EMPLOYEE and add a programming language attribute. You can access values of type DEVELOPER by using a customized type mapping, provided that the readSQL() and writeSQL() methods manage the inherited and noninherited attributes in the correct order.