JDBC Data Types

The JDBC driver converts Java data types to appropriate JDBC types before sending them to the database. It uses default mappings for most data types. For example, Java int is converted to SQL INTEGER. Default mappings have been created to provide consistency between drivers.

When calling the setXXX() method of the PreparedStatement or CallableStatement object or the ResultSet.updateXXX() method, the following table summarizes the default JDBC data types converted from Java data types.

JDBC 3Enhanced support for BLOB, CLOB, ARRAY, and REF data types. The ResultSet object now has updateBLOB(), updateCLOB(), updateArray(), and updateRef() methods, allowing you to directly operate on the corresponding data on the server.

Using setXXX() and updateXXX() methods, specific Java types can be converted to specific JDBC data types. Using setObject() and updateObject() methods, almost all Java types can be mapped to JDBC data types.

The ResultSet object provides corresponding getXXX() methods for each data type to retrieve column values. Each method can be used with the column name or its ordinal position.

Date and Time Data Type

The java.sql.Date class maps to the SQL DATE type, while the java.sql.Time and java.sql.Timestamp classes map to the SQL TIME and SQL TIMESTAMP data types, respectively.

The following example shows how the Date and Time classes format standard Java date and time values to match SQL data type requirements.

import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.*;
public class SqlDateTime {
　　　public static void main(String[] args) {
　　　　　　//Get standard date and time
　　　　　　java.util.Date javaDate = new java.util.Date();
　　　　　　long javaTime = javaDate.getTime();
　　　　　　System.out.println("The Java Date is:");　+　
　　　　　　　　　　　　　javaDate.toString());
　　　　　　//Get and display the SQL DATE
　　　　　　java.sql.Date sqlDate = new java.sql.Date(javaTime);
　　　　　　System.out.println("The SQL DATE is: ");　+　
　　　　　　　　　　　　　sqlDate.toString());
　　　　　　//Get and display the SQL TIME
　　　　　　java.sql.Time sqlTime = new java.sql.Time(javaTime);
　　　　　　System.out.println("The SQL TIME is: ");　+　
　　　　　　　　　　　　　sqlTime.toString());
　　　　　　//Get and display the SQL TIMESTAMP
　　　　　　java.sql.Timestamp sqlTimestamp =
　　　　　　new java.sql.Timestamp(javaTime);
　　　　　　System.out.println("The SQL TIMESTAMP is: ");　+　
　　　　　　　　　　　　　sqlTimestamp.toString());
　　　　　}//End main
}//End SqlDateTime

Now let's compile the above example as follows:

C:\>javac SqlDateTime.java
C:\>

at runtimeJDBCExampleIt will produce the following results-

C:\>java SqlDateTime
The Java Date is:Tue Aug　18　13:46:02　GMT+04:00　2009
The SQL DATE is:　2009-08-18
The SQL TIME is:　13:46:02
The SQL TIMESTAMP is:　2009-08-18　13:46:02.828
C:\>

Handling NULL Values

The use of NULL values in SQL and the use of null in Java are different concepts. Therefore, to handle SQL NULL values in Java, you can use three strategies-

• Avoid using methods that return the original data type from getXXX().

• Use wrapper classes for primitive data types and use the wasNull() method of the ResultSet object to test whether the wrapper class variable received from the getXXX() method should be set to null.

• Use primitive data types and the wasNull() method of the ResultSet object to test whether the original variable received from the getXXX() method should be set to the acceptable value you have chosen to represent NULL.

This is an example of handling NULL values-

Statement stmt = conn.createStatement();
String sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt.executeQuery(sql);
int id = rs.getInt(1);
if( rs.wasNull() ) {
　　　id = 0;
}