Sri Sri - 4 months ago 8
Java Question

Incorrect year in the date when trying to insert in database using jdbc, SimpleDateFormat

I've been trying to insert a date into the Oracle database using JDBC, SimpleDateFormat API.
When I passed the date as "2016-07-24", it is inserting as "24-JUL-24" which is wrong (year is wrong), it should suppose to be "24-JUL-16". It's happening once in a nth time. I was unable to debug the code, although I tried repeatedly inserting the data.
Could anyone please refer the below code, db schema and date format and let me know if I'm wrong anywhere. Thank you.

DAO Program

package com.oog.big.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import com.oog.big.database.DBConnection;
import java.text.SimpleDateFormat;

public class ItemDetailsDAO {
// format for date
public static final SimpleDateFormat transactionDateFormat = new SimpleDateFormat(
"yyyy-MM-dd");
public static transient final SimpleDateFormat requestDateTimeFormat = new SimpleDateFormat(
"yyyy-MM-dd'T'HH:mm:ss.SSSXXX");
private static final String insertTableSQL = "INSERT INTO ITEM_DETAILS ("
+ " ITEM_ID ,"
+ " SRV_NAME ,"
+ " INS_TYPE ,"
+ " REQ_CONTEXT ,"
+ " STATUS_FLAG ,"
+ " CHL ,"
+ " TRANSACTION_DATE ,"
+ " REQ_DATETIME ,"
+ " FLAG )" + "values(?,?,?,?,?,?,?,?,?)";

public static void save() throws Exception {

Connection con = null;
PreparedStatement stmt = null;
try {
con = DBConnection.getConnection();
con.setAutoCommit(true);

stmt = con.prepareStatement(insertTableSQL);
stmt.setInt(1, 1256);
stmt.setString(2, "DD");
stmt.setString(3, "OL");
stmt.setString(4, "DP");
stmt.setString(5, "");
stmt.setString(6, "FF");
/** Incorrect Date **/
stmt.setDate(
7,
new java.sql.Date(transactionDateFormat.parse(
"2016-07-24").getTime()));
stmt.setDate(
8,
new java.sql.Date(requestDateTimeFormat.parse(
"2016-08-02T16:56:01.764-04:00").getTime()));
stmt.setString(9, "N");
stmt.execute();
stmt.close();
System.out.println("Completed!!");

} catch (Exception e) {
System.out.println("DB Error: "+e);
}
}
}


Schema for
ITEM_DETAILS


CREATE TABLE OYD.ITEM_DETAILS
(
ITEM_ID INTEGER NOT NULL,
SRV_NAME VARCHAR2(5) DEFAULT NULL,
INS_TYPE VARCHAR2(3) DEFAULT NULL,
REQ_CONTEXT VARCHAR2(3) DEFAULT NULL,
REQ_DATETIME TIMESTAMP DEFAULT NULL,
STATUS_FLAG VARCHAR2(2) DEFAULT NULL,
CHL VARCHAR2(2) DEFAULT NULL,
TRANSACTION_DATE DATE DEFAULT NULL,
FLAG VARCHAR2(1) DEFAULT NULL,
LAST_UPDATE_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP,
CONSTRAINT PK_ITEM_DETAILS PRIMARY KEY (ITEM_ID)
)
PARTITION BY RANGE (LAST_UPDATE_TIMESTAMP) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(
PARTITION P_ITEM_DETAILS VALUES LESS THAN (TO_DATE('07-MAR-2016','DD-MON-YYYY'))
)PARALLEL;


Specifications:




  • JAVA SE8

  • Oracle 12c database

  • JDBC Connector: ojdbc7-12.1.0.1.0.jar


Answer

Instead of formatting date using SimpleDateFormat, you can use Oracle's date function in your SQL.

private static final String insertTableSQL = "INSERT INTO ITEM_DETAILS ("
        + "    ITEM_ID,"
        + "    SRV_NAME,"
        + "    INS_TYPE,"
        + "    REQ_CONTEXT,"
        + "    STATUS_FLAG,"
        + "    CHL,"
        + "    TRANSACTION_DATE,"
        + "    REQ_DATETIME,"
        + "    FLAG)" + "values(?,?,?,?,?,?,TO_DATE(?,'DD-MON-RR'),TO_DATE(?,'DD-MON-RR'),?)";

public static void save(){
//.........
smtp.setString(7,"24-07-2016");
smtp.setString(8,"02-8-2016");
//.........

You can even format time(hh:mm:ss) in TO_DATE() function to store timestamp.