JDBC - Data Types

The JDBC driver changes over the Java information type to the suitable JDBC type, before sending it to the database. It utilizes a default mapping for most information types. For instance, a Java int is changed over to a SQL INTEGER. Default mappings were made to give consistency between drivers.

The following table outlines the default JDBC information type that the Java information type is changed over to, when you call the setXXX() strategy for the PreparedStatement or CallableStatement object or the ResultSet.updateXXX() method.

SQL JDBC/Java setXXX updateXXX
VARCHAR java.lang.String setString updateString
CHAR java.lang.String setString updateString
LONGVARCHAR java.lang.String setString updateString
BIT boolean setBoolean updateBoolean
NUMERIC java.math.BigDecimal setBigDecimal updateBigDecimal
TINYINT byte setByte updateByte
SMALLINT short setShort updateShort
INTEGER int setInt updateInt
BIGINT long setLong updateLong
REAL float setFloat updateFloat
FLOAT float setFloat updateFloat
DOUBLE double setDouble updateDouble
VARBINARY byte[ ] setBytes updateBytes
BINARY byte[ ] setBytes updateBytes
DATE java.sql.Date setDate updateDate
TIME java.sql.Time setTime updateTime
TIMESTAMP java.sql.Timestamp setTimestamp updateTimestamp
CLOB java.sql.Clob setClob updateClob
BLOB java.sql.Blob setBlob updateBlob
ARRAY java.sql.Array setARRAY updateARRAY
REF java.sql.Ref SetRef updateRef
STRUCT java.sql.Struct SetStruct updateStruct

JDBC 3.0 has improved help for BLOB, CLOB, ARRAY, and REF information types. The ResultSet object presently has updateBLOB(), updateCLOB(), updateArray(), and updateRef() techniques that empower you to specifically control the particular information on the server.

The setXXX() and updateXXX() techniques empower you to change over explicit Java types to explicit JDBC information types. The strategies, setObject() and updateObject(), empower you to delineate any Java type to a JDBC information type.

ResultSet object gives comparing getXXX() technique for every datum type to recover section esteem. Every strategy can be utilized with section name or by its ordinal position.

VARCHAR java.lang.String setString getString
CHAR java.lang.String setString getString
LONGVARCHAR java.lang.String setString getString
BIT boolean setBoolean getBoolean
NUMERIC java.math.BigDecimal setBigDecimal getBigDecimal
TINYINT byte setByte getByte
SMALLINT short setShort getShort
INTEGER int setInt getInt
BIGINT long setLong getLong
REAL float setFloat getFloat
FLOAT float setFloat getFloat
DOUBLE double setDouble getDouble
VARBINARY byte[ ] setBytes getBytes
BINARY byte[ ] setBytes getBytes
DATE java.sql.Date setDate getDate
TIME java.sql.Time setTime getTime
TIMESTAMP java.sql.Timestamp setTimestamp getTimestamp
CLOB java.sql.Clob setClob getClob
BLOB java.sql.Blob setBlob getBlob
ARRAY java.sql.Array setARRAY getARRAY
REF java.sql.Ref SetRef getRef
STRUCT java.sql.Struct SetStruct getStruct

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 guide to the SQL TIME and SQL TIMESTAMP information types, respectively.

Following precedent shows how the Date and Time classes group the standard Java date and time esteems to coordinate the SQL information 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:" + 


//Get and show SQL DATE 

java.sql.Date sqlDate = new java.sql.Date(javaTime); 

System.out.println("The SQL DATE is: " + 


//Get and show SQL TIME 

java.sql.Time sqlTime = new java.sql.Time(javaTime); 

System.out.println("The SQL TIME is: " + 


//Get and show 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 gather the above model as pursues −

C:\>javac SqlDateTime.java 


When you run JDBCExample, it delivers the accompanying outcome −

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 


Handling NULL Values

SQL's utilization of NULL qualities and Java's utilization of invalid are diverse ideas. In this way, to deal with SQL NULL qualities in Java, there are three strategies you can utilize −

  • Avoid utilizing getXXX( ) techniques that arrival crude information types.

  • Use wrapper classes for crude information types, and utilize the ResultSet article's wasNull( ) technique to test whether the wrapper class variable that got the esteem returned by the getXXX( ) strategy ought to be set to null.

  • Use crude information types and the ResultSet article's wasNull( ) technique to test whether the crude variable that got the esteem returned by the getXXX( ) technique ought to be set to a worthy esteem that you've spoken to a NULL.

Here is one guide to deal with a NULL esteem −

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; 


© Javacodegeeks © 2019 - 2019
All Right Reserved and you agree to have read and accepted our term and condition.

Python 3