Oracle: convert java date to Oracle date in java program

Question

I have a java program that reads the date as string from an input file and updates the Oracle table, where this date is part of an index fields.

I tried the following options:

a) Reading the data from csv file delimited by ; as String

109920541;2013-01-17 11:48:09;EDWARD ANTHONY;

b) Using SimpleDateFormat convert String to Date in a routine:

  java.text.SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  java.util.Date dt = formatter.parse("2013-01-17 11:48:09");

c) Use this date to prepare my sql stmt:

String update_query = "UPDATE "+ tableName +" SET NAME = ? WHERE ID = ? AND DATE_TIME = ?";
java.sql.Connection conn = java.sql.DriverManager.getConnection(dbUrl, dbUser, dbPwd);  
java.sql.PreparedStatement pstmt = conn.prepareStatement(update_query);
if (dt not null) {
  pstmt.setObject(1, new java.sql.Timestamp(dt.getTime()),java.sql.Types.DATE);
}
pstmt.executeBatch();

d) This does not find the date which is a part of key and so the data is not updated.

In sql developer I use the below query to fetch row:

SELECT * FROM table
WHERE ID = '1099205410'
AND DATE_TIME = TO_DATE('2013-01-17 11:48:09', 'YYYY-MM-DD HH24:MI:SS');

How to convert a java String to Oracle date?

Thanks.

Joop Eggen · Accepted Answer · 2019-05-22 13:00:35Z

1

For a single line:

pstmt.setString(1, name);
pstmt.setInt(2 id);
pstmt.setTimeStamp(3, ew java.sql.Timestamp(dt.getTime()));
pstmt.executeUpdate();

If the DATE_TIME value is not always present in the CSV data, simply use a second PreparedStatement; with just 2 parameters.

If you are using a loop reading the lines, do

for ... {
    ...
    pstmt.clearParameters();
    pstmt.setString(1, name);
    pstmt.setInt(2 id);
    pstmt.setTimeStamp(3, new java.sql.Timestamp(dt.getTime()));
    pstmt.addBatch();
}
pstmt.executeBatch();

You might consider using the new time APIs.

(UPDATE is for existing records.)

edited May 22, 2019 at 13:00

answered May 22, 2019 at 12:55

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle: convert java date to Oracle date in java program

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related