This forum is now a read-only archive. All commenting, posting, registration services have been turned off. Those needing community support and/or wanting to ask questions should refer to the Tag/Forum map, and to http://spring.io/questions for a curated list of stackoverflow tags that Pivotal engineers, and the community, monitor.

The field accountdate is of type TIMESTAMP in the database.
This code above works fine if I specify my MySQL Database as DataSource. But if I switch to my Oracle Database, this code throws a ClassCastException, because the queryForList()/get() returns an Object of type oracle.sql.TIMESTAMP.
Our application should work for more than one Database type. So I choose the Springframework, because I thought it wraps all this database type dependency. But now with this exception above I cannot write common code, but have to use a lot of switches (Oracle or MySQL or SQLServer ...).
I hope there is also a better solution, where the queryForList()/get() returns the common sql type java.sql.Timestamp also in case of Oracle.
Is there a better solution ???

Comment

this appears to be a "standard" feature of oracle (which I have recreated).
I don't tend to use "queryForList" much but I have an issue with PreparedStatement.setTimestamp() trunctating milliseconds.
I work around this by wrapping the Oracle DataSource, Connection, Statement, PreparedStatement and ResultSet with my custom versions and intercept setTimestamp & setObject to workaround this issue. A similar approach could work for you where your ResultSet.getObject() could check for a oracle.sql.TIMESTAMP and convert it.

If wrapping the datasource is not an option I would use a JdbcTemplate.query(java.lang.String sql, RowCallbackHandler rch) using a generic RowCallbackHandler which would know the types of the result set and call the appropriate getter (ResultSet.getTimestamp() does return a java.sql.Timestamp for oracle)

Gordon.[/code]

Comment

Just wanted to second what Gordon said. It's an Oracle issue that they don't seem to worry about. The QueryForList and QueryForObject methods rely on the object type returned by rs.getObject and it varies between databases/jdbc drivers. To stay in control you are better off using the 'query' method and handle the getXxx based on what you want returned. Here is a brief example:

I am going to try to fix the Oracle date issues in queyForList though. We should be able to detect this Oracle class and do a getTimestamp instead. I'll post something when I have some code comitted - probably 1.1.2 release.

Two conveniences are leveraged in the above: Firstly, passing in a SQL string rather than coding a PreparedStatementCreator; secondly, using a RowMapper callback rather than a RowCallbackHandler/ResultReader. The latter allow to build any kind of result, while the former assumes one object per row, to be returned as List. So if you want to build a List with one object per row, I recommend to use a RowMapper as callback.

Juergen

Comment

Thanks for your helpful responses.
I like to use queryForList, because it's so easy to use and the code is so small.
So, Juergen, if you commit any correction for this issue in queryForList then let me know here. In the meanwhile I will use this construct with the query().

Best Regards,
Mario

Comment

In JdbcTemplate.java in class ListResultSetExtractor, method extractData, there is a for-loop, which fills the map. Do you think it would be a possible solution for my problem, if I replace the for-loop with this for-loop:

Comment

I am going to try to fix the Oracle date issues in queyForList though. We should be able to detect this Oracle class and do a getTimestamp instead. I'll post something when I have some code comitted - probably 1.1.2 release.