使用jdbcTemplate.queryForRowSet()遇到UncategorizedSQLException：Invalid precision value. Cannot be less than zero解决办法

某次在做项目时用到了spring2.5的jdbcTemplate.queryForRowSet()去连接Oracle 11g，运行时出现如下异常：

 

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT tu.user_id,user_name,firstname,lastname,email,description,item_id,item_title FROM tb_user tu LEFT JOIN tb_user_setting ts ON tu.user_id=ts.user_id LEFT JOIN (SELECT description,item_id,item_title FROM (SELECT t.description,tf.item_id,ti.item_title FROM tb_bulk_buying_campaign t,tb_offer tf,tb_item ti WHERE tf.offer_id=t.offer_id AND tf.revision=t.offer_revision AND t.begin_date>SYSDATE AND t.status=1 AND tf.item_id=ti.item_id ORDER BY t.end_date ASC) WHERE ROWNUM<=1) s ON 1=1 WHERE tu.account_sts=1 AND receive_news_letter=1 AND tu.user_id IN (SELECT DISTINCT user_id FROM tb_request WHERE status=0 AND item_id=s.item_id) ]; SQL state [null]; error code [0]; Invalid precision value. Cannot be less than zero; nested exception is java.sql.SQLException: Invalid precision value. Cannot be less than zero
 at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:124)
 at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
 at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:604)
 at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:638)
 at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:667)
 at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:675)
 at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:781)
 at au.com.iwanttobuy.offerme.batch.dao.impl.MailDaoJdbcImpl.getReceivers(MailDaoJdbcImpl.java:32)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
 at java.lang.reflect.Method.invoke(Unknown Source)

 

因为我这句sql本身没有任何问题，所以不得不求助于google。最后发现，it was caused by Oracle's drivers not correctly supporting the CachedRowSet interface.

 

解决办法是creating an ResultSetExctractor class that implements ResultSetExtractor and used that to get an OracleCachedRowSet implementation of SqlRowSet. To use this you will need to include the oracle driver jar in your project (used ojdbc5.jar).

 

下面是我创建的SqlRowSetOracleResultSetExtractor源代码：

 

import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.rowset.CachedRowSet;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.rowset.ResultSetWrappingSqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import oracle.jdbc.rowset.OracleCachedRowSet;

 

/**

 * SqlRowSetOracleResultSetExtractor

 */

public class SqlRowSetOracleResultSetExtractor implements ResultSetExtractor {
 public Object extractData(ResultSet rs) throws SQLException {
  return createSqlRowSet(rs);
 }

 

 /**
  * Create a SqlRowSet that wraps the given ResultSet,
  * representing its data in a disconnected fashion.
  * <p>This implementation creates a Spring ResultSetWrappingSqlRowSet
  * instance that wraps a standard JDBC CachedRowSet instance.
  * Can be overridden to use a different implementation.
  * @param rs the original ResultSet (connected)
  * @return the disconnected SqlRowSet
  * @throws SQLException if thrown by JDBC methods
  * @see #newCachedRowSet
  * @see org.springframework.jdbc.support.rowset.ResultSetW rappingSqlRowSet
  */
 protected SqlRowSet createSqlRowSet(ResultSet rs) throws SQLException {
  CachedRowSet rowSet = newCachedRowSet();
  rowSet.populate(rs);
  return new ResultSetWrappingSqlRowSet(rowSet);
 }

 

 /**
  * Create a new CachedRowSet instance, to be populated by
  * the <code>createSqlRowSet</code> implementation.
  * <p>This implementation creates a new instance of
  * Oracle's <code>oracle.jdbc.rowset.OracleCachedRowSet</code> class,
  * which is their implementation of the Java 1.5 CachedRowSet interface.
  * @return a new CachedRowSet instance
  * @throws SQLException if thrown by JDBC methods
  * @see #createSqlRowSet
  * @see oracle.jdbc.rowset.OracleCachedRowSet
  */
 protected CachedRowSet newCachedRowSet() throws SQLException {
  return new OracleCachedRowSet();
 }
}

还是英文资料全啊，呵呵！