JDBC Tutorial - JDBC Data Types

• « Previous
• Next »

Java has a data type system, for example, int, long, float, double, string.

Database systems also have a type system, such as int, char, varchar, text, blob, clob.

The JDBC driver can convert the Java data type to the appropriate database type back and forth.

The following table lists the default database data type and the Java data type mapping.

The mapping is used when calling the setXXX() method from the PreparedStatement or CallableStatement object or the ResultSet.updateXXX()/getXXX() method.

Example

The following examples shows how to convert Java Date and Time classes to match the SQL data type.

public class Main {
  public static void main(String[] args) {
    java.util.Date javaDate = new java.util.Date();
    long javaTime = javaDate.getTime();
    System.out.println("The Java Date is:" + javaDate.toString());
//  w w w . j  a v  a  2 s.c om
    // SQL DATE
    java.sql.Date sqlDate = new java.sql.Date(javaTime);
    System.out.println("The SQL DATE is: " + sqlDate.toString());

    // SQL TIME
    java.sql.Time sqlTime = new java.sql.Time(javaTime);
    System.out.println("The SQL TIME is: " + sqlTime.toString());
    
    // SQL TIMESTAMP
    java.sql.Timestamp sqlTimestamp = new java.sql.Timestamp(javaTime);
    System.out.println("The SQL TIMESTAMP is: " + sqlTimestamp.toString());
  }
}

The code above generates the following result.

Handling NULL Values

SQL uses NULL values to indicate empty while Java null means no memory has been allocated.

To handle NULL value from database properly we should avoid using getXXX() methods that return primitive data types. Since the JDBC driver may convert the NULL value to 0 and we may have 0 value in the same column.

Or we can use the wasNull() method from the ResultSet to check if the value was null.

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; // or -1
}

SQL Timestamp

A simple table script in Oracle database.

CREATE TABLE Person ( 
  USER_ID       NUMBER (5)    NOT NULL, 
  USERNAME      VARCHAR2 (20)  NOT NULL, 
  CREATED_BY    VARCHAR2 (20)  NOT NULL, 
  CREATED_DATE  DATE          NOT NULL, 
  PRIMARY KEY ( USER_ID ) 
 )

Use the setTimestamp from PreparedStatement to insert Timestamp to database.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
/*from   w w w. j av  a 2s. c o  m*/
public class Main {
  private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
  private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:YourDatabase";
  private static final String DB_USER = "user";
  private static final String DB_PASSWORD = "password";

  public static void main(String[] argv) throws Exception {
    Class.forName(DB_DRIVER);
    Connection dbConnection = DriverManager.getConnection(DB_CONNECTION,
        DB_USER, DB_PASSWORD);
    PreparedStatement preparedStatement = null;
    java.util.Date today = new java.util.Date();
    String insertTableSQL = "INSERT INTO DBUSER"
        + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
        + "(?,?,?,?)";
    preparedStatement = dbConnection.prepareStatement(insertTableSQL);
    preparedStatement.setTimestamp(4,new java.sql.Timestamp(today.getTime()));


    dbConnection.commit();
    dbConnection.close();
  }
}

Or we can use setDate method.

java.util.Date today = new java.util.Date();

preparedStatement.setDate(4, new java.sql.Date(today.getTime()));

• Next »
• « Previous