JDBC - Data Types

Advertisements


The JDBC driver converts the Java data type to the appropriate JDBC type before sending it to the database. It uses a default mapping for most data types. For example, a Java int is converted to an SQL INTEGER. Default mappings were created to provide consistency between drivers.

The following table summarizes the default JDBC data type that the Java data type is converted to when you call the setXXX() method of the PreparedStatement or CallableStatement object or the ResultSet.updateXXX() method.

SQLJDBC/JavasetXXXupdateXXX
VARCHARjava.lang.StringsetStringupdateString
CHARjava.lang.StringsetStringupdateString
LONGVARCHARjava.lang.StringsetStringupdateString
BITbooleansetBooleanupdateBoolean
NUMERICjava.math.BigDecimalsetBigDecimalupdateBigDecimal
TINYINTbytesetByteupdateByte
SMALLINTshortsetShortupdateShort
INTEGERintsetIntupdateInt
BIGINTlongsetLongupdateLong
REALfloatsetFloatupdateFloat
FLOATfloatsetFloatupdateFloat
DOUBLEdoublesetDoubleupdateDouble
VARBINARYbyte[ ]setBytesupdateBytes
BINARYbyte[ ]setBytesupdateBytes
DATEjava.sql.DatesetDateupdateDate
TIMEjava.sql.TimesetTimeupdateTime
TIMESTAMPjava.sql.TimestampsetTimestampupdateTimestamp
CLOBjava.sql.ClobsetClobupdateClob
BLOBjava.sql.BlobsetBlobupdateBlob
ARRAYjava.sql.ArraysetARRAYupdateARRAY
REFjava.sql.RefSetRefupdateRef
STRUCTjava.sql.StructSetStructupdateStruct

JDBC 3.0 has enhanced support for BLOB, CLOB, ARRAY, and REF data types. The ResultSet object now has updateBLOB(), updateCLOB(), updateArray(), and updateRef() methods that enable you to directly manipulate the respective data on the server.

The setXXX() and updateXXX() methods enable you to convert specific Java types to specific JDBC data types. The methods, setObject() and updateObject(), enable you to map almost any Java type to a JDBC data type.

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

SQLJDBC/JavasetXXXgetXXX
VARCHARjava.lang.StringsetStringgetString
CHARjava.lang.StringsetStringgetString
LONGVARCHARjava.lang.StringsetStringgetString
BITbooleansetBooleangetBoolean
NUMERICjava.math.BigDecimalsetBigDecimalgetBigDecimal
TINYINTbytesetBytegetByte
SMALLINTshortsetShortgetShort
INTEGERintsetIntgetInt
BIGINTlongsetLonggetLong
REALfloatsetFloatgetFloat
FLOATfloatsetFloatgetFloat
DOUBLEdoublesetDoublegetDouble
VARBINARYbyte[ ]setBytesgetBytes
BINARYbyte[ ]setBytesgetBytes
DATEjava.sql.DatesetDategetDate
TIMEjava.sql.TimesetTimegetTime
TIMESTAMPjava.sql.TimestampsetTimestampgetTimestamp
CLOBjava.sql.ClobsetClobgetClob
BLOBjava.sql.BlobsetBlobgetBlob
ARRAYjava.sql.ArraysetARRAYgetARRAY
REFjava.sql.RefSetRefgetRef
STRUCTjava.sql.StructSetStructgetStruct

Date & Time Data Types:

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

Following examples shows how the Date and Time classes format standard Java date and time values to match the 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 SQL DATE
      java.sql.Date sqlDate = new java.sql.Date(javaTime);
      System.out.println("The SQL DATE is: " + 
             sqlDate.toString());

      //Get and display SQL TIME
      java.sql.Time sqlTime = new java.sql.Time(javaTime);
      System.out.println("The SQL TIME is: " + 
             sqlTime.toString());
      //Get and display 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 us compile above example as follows:

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

When you run JDBCExample, it produces following result:

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:

SQL's use of NULL values and Java's use of null are different concepts. So how do you handle SQL NULL values in Java? There are three tactics you can use:

  • Avoid using getXXX( ) methods that return primitive data types.

  • Use wrapper classes for primitive data types, and use the ResultSet object's wasNull( ) method to test whether the wrapper class variable that received the value returned by the getXXX( ) method should be set to null.

  • Use primitive data types and the ResultSet object's wasNull( ) method to test whether the primitive variable that received the value returned by the getXXX( ) method should be set to an acceptable value that you've chosen to represent a NULL.

Here is one example to handle a NULL value:

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;
}


Advertisements
Advertisements