oracle.sql.timestamp cannot be cast to java.util.date when trying to convert timestamp field

Question

I have LATEST_MODIFICATION_DATE field in my database table which is timestamp datatype. I am using plane jdbcTemplate for database execute queries.

Below is part of my code which is working fine. But I see an issue on the line

latestModDate = rowSet.getString("LATEST_MODIFICATION_DATE");

Because I am trying to treat LATEST_MODIFICATION_DATE timestamp datatype field as a string it might create problem in my code in future.

I tried to update the line below and then declare LATEST_MODIFICATION_DATE as timestamp datatype but getting error as oracle.sql.timestamp cannot be cast to java.util.date.

latestModDate = rowSet.getTimestamp("LATEST_MODIFICATION_DATE");

I really dont know how to treat LATEST_MODIFICATION_DATE as timestamp. I really dont want to make change in system property like below as i want to handle this issue in code:

java -Doracle.jdbc.J2EE13Compliant=true YourApplication
or
System.getProperties().setProperty("oracle.jdbc.J2EE13Compliant", "true")

Below is part of my code:

            String orderID = null, extOrdId = null, latestModDate = null;                

            while (rowSet.next()) {
                latestModDate = rowSet.getString("LATEST_MODIFICATION_DATE");
            }

            

Answer 1

You've defined latestModDate as a String but as you've said it's a timestamp in the database, if you change the type to something like java.util.Date and then use ResultSet.getDate() this should fix your problem:

Date latestModDate = rowSet.getDate("LATEST_MODIFICATION_DATE");

Answer 2

1. Declare latestModDate of type java.sql.Timestamp.

2. Get java.time.Instant from latestModDate as follows:

   Instant instant = latestModDate.toInstant();
   

Once you have the object of Instant, you can convert it to any other modern date-time object as per your requirement e.g.

import java.time.Instant;
import java.time.LocalDateTime;
import java.time.OffsetDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;

public class Main {
    public static void main(String[] args) {
        Instant instant = Instant.now();
        ZonedDateTime zdt = instant.atZone(ZoneId.of("Europe/London"));
        OffsetDateTime odt = zdt.toOffsetDateTime();
        LocalDateTime ldt = zdt.toLocalDateTime();
        System.out.println(instant);
        System.out.println(zdt);
        System.out.println(odt);
        System.out.println(ldt);
    }
}

Output:

2020-06-27T08:55:12.317754Z
2020-06-27T09:55:12.317754+01:00[Europe/London]
2020-06-27T09:55:12.317754+01:00
2020-06-27T09:55:12.317754

Some additional notes:

3. Do not use outdated and error-prone java.util date-time API. Use the modern date-time API.
4. LocalDateTime drops off information like Zone Offset and Zone ID which may be required in your business logic. Therefore, choose the appropriate class as per the table shown below (Ref):

[Update]

The changes that you need to make in your code:

java.sql.Timestamp latestModDate;

if (rowSet.next()) {
    latestModDate = ((oracle.sql.TIMESTAMP) rowSet.getObject("LATEST_MODIFICATION_DATE")).timestampValue();
}

// If the record exist, update accordingly, else insert a new one.
if (latestModDate != null) {
    String sqlUpdate = "update " + tableName + " set LATEST_MODIFICATION_DATE = sysdate, SOURCES = '" + fileName + "' where (ORDERID = '" + orderID + "' and EXTORDID = '" + extOrdId + "') or (ORDERID = '" + orderID + "' and EXTORDID is null)";
    jdbcTemplate.update(sqlUpdate);
    //...

Note: The function oracle.sql.TIMESTAMP#timestampValue returns a value in java.sql.Timestamp.

Answer 3

Don't use java.sql.Date or java.util.Date. Use java.time.LocalDateTime. java.util.Date is a hack and java.sql.Date is a hack on top of a hack.

LocalDateTime latestModDate;

while (rowSet.next()) {
    latestModDate = rowSet.getObject("LATEST_MODIFICATION_DATE", LocalDateTime.class);
}