3

I am getting the below given error for the following code snippets:

  try {
        cRows = new CachedRowSetImpl();
        while(cRows.next()) 
        {
        MyClass myClass = new MyClass();
        myClass.setPrevDate(cRows.getDate("PREV_DATE")); // In debug mode, the error was throwing when I press Resume from here.
        }
      }

Error:

Caused by: java.lang.ClassCastException: java.sql.Timestamp cannot be cast to java.sql.Date

In the database, the datatype for the column is DATE only. I am not able to figure out where the Timestamp is coming here.

Arnab Nandy
  • 6,472
  • 5
  • 44
  • 50
Nidheesh
  • 4,390
  • 29
  • 87
  • 150

2 Answers2

5

Obsolete:

Use java.util.Date for the field. java.sql.Timestamp is a direct subclass of it. As is java.sql.Date - that strips the time part. Why the java database driver takes DATE to be Timestamp is a bit weird. What is the database vendor? Did you specify a length or so? Are indeed only dates stored?

Researched:

I looked into CachedRowSetImpl.java, and Oracle's docs and Oracle does everything fine (java.sql.Date, java.sql.Time, java.sql.Timestamp convertible). The CachedRowSetImpl does simply cast the DATE's Object (and getObject is likely to return the high resolution Timestamp - with time) to java.sql.Date, and that's wrong. So override or substitute this sun's class.

      /*
       * The object coming back from the db could be
       * a date, a timestamp, or a char field variety.
       * If it's a date type return it, a timestamp
       * we turn into a long and then into a date,
       * char strings we try to parse. Yuck.
       */
       switch (RowSetMD.getColumnType(columnIndex)) {
           case java.sql.Types.DATE: {
               long sec = ((java.sql.Date)value).getTime();
               return new java.sql.Date(sec);
       }
Joop Eggen
  • 107,315
  • 7
  • 83
  • 138
  • DB is `Oracle 11g`. Apart from this, String , double etc are using, which are fine. – Nidheesh Sep 17 '14 at 14:34
  • Let me check this. So we can't expect a `DATE` object to be returned from a db column even if the column type is `DATE` (as per the commented lines)? – Nidheesh Sep 17 '14 at 15:10
  • Also if I put `new java.util.Date()` instead of `cRows.getDate("PREV_DATE")` the error has gone. So we can confirm the object coming back is not `DATE`... right? – Nidheesh Sep 17 '14 at 15:13
  • If calling `ResultSet.getDate("PREV_DATE")` fine, `Timestamp ts = rs.getTimestamp("PREV_DATE")` should be possible, and I guess that `rs.getObject("PREV_DATE")` yields a Timestamp. The culprit is CachedRowImpl. – Joop Eggen Sep 17 '14 at 15:15
  • 1
    You can override `getDate` from CachedRowImpl and do the cast to java.util.Date. – Joop Eggen Sep 17 '14 at 15:18
  • +1 for your valuable comments and help. I have posted my answer below. Please share your thoughts. – Nidheesh Sep 18 '14 at 14:26
5

I have done a research on this issue and found some useful links. I found this confusion between DATE and TIMESTAMP is JDBC Driver specific. And most of the links suggest the use of -Doracle.jdbc.V8Compatible=true. For my JBoss I have set this in run.bat and the issue got resolved.

  1. https://community.oracle.com/thread/68918?start=0&tstart=0

  2. http://www.coderanch.com/t/90891/JBoss/oracle-jdbc-Compatible-true

  3. https://community.oracle.com/message/3613155

The oracle doc shares different solutions:

  • Alter your tables to use TIMESTAMP instead of DATE. This is probably rarely possible, but it is the best solution when it is.

  • Alter your application to use defineColumnType to define the columns as TIMESTAMP rather than DATE. There are problems with this because you really don't want to use defineColumnType unless you have to (see What is defineColumnType and when should I use it? ).

  • Alter you application to use getTimestamp rather than getObject. This is a good solution when possible, however many applications contain generic code that relies on getObject, so it isn't always possible.

  • Set the V8Compatible connection property. This tells the JDBC drivers to use the old mapping rather than the new one. You can set this flag either as a connection property or a system property. You set the connection property by adding it to the java.util.Properties object passed to DriverManager.getConnection or to OracleDataSource.setConnectionProperties. You set the system property by including a -D option in your java command line.

    java -Doracle.jdbc.V8Compatible="true" MyApp

Here is the link: http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#08_00

Nidheesh
  • 4,390
  • 29
  • 87
  • 150