1

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");
            }
Improve this question
0

3 Answers 3

Reset to default
1

  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:

  1. Do not use outdated and error-prone java.util date-time API. Use the modern date-time API.
  2. 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): enter image description here

[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.

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

11 Comments

sorry i am bit confused with the changes i have to do actually ...will be really helpful if you can please mentioned the changes i need to do thanks...specially this part i dont understand: Once you have the object of Instant, you can convert it to any other modern date-time object as per your requirement e.g.
@Symonds - I've added an update in my answer. I hope, it helps. Feel free to comment in case of any doubt/issue.
but are we still using rowSet.getString in your changes ?
sorry still geting same error oracle.sql.timestamp cannot be cast to java.util.date
@Symonds - Please post the error as a comment below this comment.
|
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");
Improve this answer

5 Comments

i have changed the latestModDate type to java.util.Date latestModDate and then did the change you mentioned but still getting same error oracle.sql.TIMESTAMP cannot be cast to java.sql.Timestamp
@Symonds this is a problem with the Oracle JDBC driver. Have a look at the discussion here: stackoverflow.com/questions/13269564/…
If i do like this then its working now: oracle.sql.TIMESTAMP latestModDate = null; latestModDate = (TIMESTAMP) rowSet.getObject("LATEST_MODIFICATION_DATE");
so declaration normally should not be java.util.Date but oracle.sql.TIMESTAMP
@Symonds yes as per the discussion on that thread that is one solution. Adding a setting to the jvm means you could use my solution. (tbh I didn't realise this was a problem, I've always done it this way, someone must have added those settings to our project when it was first started 15 years ago).
0

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);
}
Improve this answer

3 Comments

i am using java 8 and i am getting the exception with the changes: org.springframework.jdbc.InvalidResultSetAccessException: Not supported yet.; nested exception is java.sql.SQLFeatureNotSupportedException: Not supported yet.
As per @Symonds comment ResultSet doesn't support java.time.* classes.
If i do like this then its working now: oracle.sql.TIMESTAMP latestModDate = null; latestModDate = (TIMESTAMP) rowSet.getObject("LATEST_MODIFICATION_DATE");

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.