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

框架及JSP页面设计 专栏收录该内容
17 篇文章 0 订阅

某次在做项目时用到了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();
 }
}

还是英文资料全啊,呵呵!

  • 0
    点赞
  • 2
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值