JdbcTemplate.queryForList returns oracle.sql.TIMESTAMP ?

[SuperMario]

Hello,

I use the JdbcTemplate for executing SELECTs on my database.

JdbcTemplate jt = new JdbcTemplate(ds);
List values = jt.queryForList("SELECT accountdate FROM acount");
Map firstRow = (Map) values.get(0);

java.sql.Timestamp accountdate = (java.sql.Timestamp)firstRow.get("accountdate");

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 ???

Best Regards,
Mario

[SuperMario]

Does nobody has an idea ?

[Juergen Hoeller]

Can you reproduce this, Thomas? Does Oracle return its own timestamp class here, which is not assignable to java.sql.Timestamp?

Juergen

[SuperMario]

For information:
I use Oracle 9.2.0.1.0
And if I write this:

Object obj = firstRow.get("accountdate");
System.out.println("Classname: " + obj.getClass().getName());

Then it really prints out "Classname: oracle.sql.TIMESTAMP".

[gcosgrave]

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]

[trisberg]

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:

    JdbcTemplate jt = new JdbcTemplate(dataSource);
    final String sql = "select 'Spring' as name, sysdate, systimestamp from dual";
    List results = jt.query(
      new PreparedStatementCreator() {
          public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
              PreparedStatement ps = con.prepareStatement(sql);
              return ps;
          }
		}, 
      new ResultReader() {
          List results = new ArrayList();

          public void processRow(ResultSet rs) throws SQLException {
          	Map rowData = new LinkedHashMap(3);
          	rowData.put("name", rs.getString(1));
          	rowData.put("sysdate", rs.getTimestamp(2));
          	rowData.put("systimestamp", rs.getTimestamp(3));
          	results.add(rowData);
          }
          public List getResults() {
          	return results;
          }
        }
      );
	  
	  return results;

	}

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.

[Juergen Hoeller]

Actually, Thomas' code example can alternatively be written in a more concise fashion, using appropriate overloaded query methods:

JdbcTemplate jt = new JdbcTemplate(dataSource); 
List results = jt.query(
    "select 'Spring' as name, sysdate, systimestamp from dual",
    new RowMapper() {
      public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
        Map rowData = new LinkedHashMap(3); 
        rowData.put("name", rs.getString(1)); 
        rowData.put("sysdate", rs.getTimestamp(2)); 
        rowData.put("systimestamp", rs.getTimestamp(3)); 
        return rowData;
      }
    });

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

[SuperMario]

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

[SuperMario]

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:

for &#40;int i = 1; i <= numberOfColumns; i++&#41; &#123;
	switch&#40;rsmd.getColumnType&#40;i&#41;&#41; &#123;
		case java.sql.Types.TIMESTAMP&#58;
			mapOfColValues.put&#40;rsmd.getColumnName&#40;i&#41;, rs.getTimestamp&#40;i&#41;&#41;;
			break;
		default&#58;
			mapOfColValues.put&#40;rsmd.getColumnName&#40;i&#41;, rs.getObject&#40;i&#41;&#41;;
	&#125;
&#125;

Best Regards,
Mario

[SuperMario]

Hello Juergen,

I noticed there is a new release 1.1.1 of springframework.
Is there a solution for my described problem in this release ?

In the changlog there is this text:
"JdbcTemplate uses Commons Collections LinkedMap (if available, on JDK <= 1.4) for mapping result rows in lists
"

Can you post some code, if this "Commons Collections LinkedMap" is the solution for me ?

Best Regards,
Mario