`

Spring中关于SqlRowSet的Invalid scale size. Cannot be less than zero异常处理

阅读更多
在使用Spring中使用JdbcTemplate.queryForRowSet()方法时,抛出了SQLException:Invalid scale size. Cannot be less than zero 异常。报这个异常情况如下:

2数据库环境为oracle而且使用了RowSet时。具体原因是由于“oracle驱动面对一个数值型的返回字段时,在得到指定的字段小数点右边的数值数量时(Gets the designated column's number of digits to right of the decimal point.这个是原文),居然会返回-127,而oracle本身的cacheRowSet实现不允许这种情况出现,于是就会报标题所说的异常。

     oracle9i和oracle10之间会存在这种差别。具体的解决办法如下:

     ①编写一个类实现org.springframework.jdbc.core.ResultSetExtractor接口

package com.*.base.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.rowset.CachedRowSet;

import oracle.jdbc.rowset.OracleCachedRowSet;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.rowset.ResultSetWrappingSqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSet;

public class SqlRowSetOracleResultSetExtractor implements ResultSetExtractor {

       public Object extractData(ResultSet rs) throws SQLException,DataAccessException {
             // TODO Auto-generated method stub
             return createSqlRowSet(rs);
       }

       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 createSqlRowSet implementation.

* This implementation creates a new instance of

* Oracle's oracle.jdbc.rowset.OracleCachedRowSet 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();

      }

}

   ②使用这个类代替JdbcTemplate.queryForRowSet():

     rs = (SqlRowSet) jdbcTemplate.query(sql, new SqlRowSetOracleResultSetExtractor());


SqlRowSet rs = (SqlRowSet)jdbcTemplate.query(sql,params,new SqlRowSetResultSetExtractor() {
protected CachedRowSet newCachedRowSet() throws SQLException {
  return new OracleWebRowSet();
}
});




这个异常源自于oracle驱动面对一个数值型的返回字段时,在得到指定的字段小数点右边的数值数量时(Gets the designated column's number of digits to right of the decimal point.这个是原文),居然会返回-127,而oracle本身的cacheRowSet实现不允许这种情况出现,于是就会报标题所说的异常。

对于一般的做法,需要修改很多地方,包括ResultSet的decorate类,还有Spring的SqlRowSetResultSetExtractor

所谓头痛医头,脚痛医脚,这里提供一种方法直接从oracle jdbc驱动入手,彻底从源头上修改掉该问题:
反编译ojdbc14.jar(Oracle 9i驱动为例)


package oracle.jdbc.driver;

public class OracleResultSetMetaData


目标方法:
public int getScale(int paramInt)
    throws SQLException
  {
    int i = getValidColumnIndex(paramInt);
    return this.statement.getDBDescription()[i].scale;
  }

使用javassist编写一段代码:
public  void crackOracleDriver() {
        ClassPool pool = ClassPool.getDefault();
        try {
            pool.insertClassPath("E:\\allproject\\bpmtrans\\lib\\ojdbc14.jar");
            CtClass cc = pool.get("oracle.jdbc.driver.OracleResultSetMetaData");
            System.out.println(cc);
            CtClass[] param = new CtClass[1] ;
            param[0]=pool.get("int");
            CtMethod a = cc.getDeclaredMethod("getScale",param);
            System.out.println(a);
            a.setBody("{int i = getValidColumnIndex($1);\n" +
                    "    int res=statement.getDBDescription()[i].scale;\n" +
                    "return res<0?0:res; }");
            cc.writeFile("c:\\");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
将生成的class置换原来的class,大功告成!

这个所谓的精度,一般来说,修改了应该没有多大问题的

I have been dealing with the same problem (SQLException - "Invalid scale size. Cannot be less than zero") and believe I have arrived at a better solution for those who wish to use the Spring API as much as possible.

The basic problem, as I understand it, is that there is an incompatibility between Oracle and the standard CachedRowSet implementation (CachedRowSetImpl) of Java 1.5. Spring uses this implementation by default when you call queryForRowSet(...). However, this does not mean that you cannot use SqlRowSet. The SqlRowSet class doesn't know anything about the implementation of the CachedRowSet interface that you're using. The class that is actually utilizing the CachedRowSetImpl class is the ResultSetExtractor... more specifically, the SqlRowSetResultSetExtractor (this is used by Spring when you call queryForRowSet).

In order to achieve the same result (returning a Spring SqlRowSet), you can pass in your own ResultSetExtractor to the query(...) methods (*not* queryForRowSet) that take a ResultSetExtractor as a parameter. What I did was just clone the SqlRowSetResultSetExtractor and instead of using the standard CachedRowSetImpl class, I replaced it with Oracle's CachedRowSet implementation. This way, when the ResultSet is mapped to a CachedRowSet, it uses Oracle's implementation to do so and thus the incompatibility is eliminated. Here is my ResultSetExtractor class that does just that...
-------------------------

Java代码
package com.xunjienet.cms.logic;

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;

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();  
}  
  





-------------------------
You can pass this to the various query methods like so:

Java代码 SqlRowSet sqlRowSet = (SqlRowSet)jdbcTemplate.query(sql,    new SqlRowSetOracleResultSetExtractor());    >>>>>>>>>>>>>>从9i到10g就出现了这个问题
查阅相关资料:数据库中有number型字段没有指定精度我也出了这个问题.我用NVL把空数字转为了0就解决了. http://forum.springsource.org/showthread.php?t=19848 java.sql.SQLException: Invalid scale size. Cannot be less than zero
数据库Oracle 10203
package com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.sql.rowset.*;
import com.sun.rowset.CachedRowSetImpl;;
public class Test {

  public static void main(String arg[]) {
    try {
     Class.forName("oracle.jdbc.driver.OracleDriver");
     String url="jdbc:oracle:thin:@***.***.***.***:1521:billdb2";
     Connection c =  DriverManager.getConnection(url,"test","test");
     Statement s =  c.createStatement();
     ResultSet r = s.executeQuery("select * from b");
     CachedRowSet crs = new CachedRowSetImpl();
     crs.populate(r);
     while(crs.next()){
      System.out.println(crs.getInt(1));
      System.out.println(crs.getFloat(2));
     }
    }catch(Exception e) {
     System.out.println(e.toString());
    }
   }

}
SQL> desc a
Name Type   Nullable Default Comments
---- ------ -------- ------- --------
ID   NUMBER Y                        
ID2  NUMBER Y                        
SQL> desc b
Name Type        Nullable Default Comments
---- ----------- -------- ------- --------
ID   NUMBER(1)   Y                        
ID2  NUMBER(5,2) Y    

使用CachedRowSetImpl 接口,如果数据库表字段为number且未指定精度(如a表),就会出现该错误。
如果指定精度程序运行正常(如b表)
使用CachedRowSetImpl 接口,如果数据库表字段为number且未指定精度,就会出现该错误。

也可以看参见 aggie2000 http://forum.springsource.org/showthread.php?t=19848

如果本机没有问题,则看看服务上tomcat本身有没有问题!


分享到:
评论

相关推荐

    spring jdbctemplate 封裝

    import org.springframework.jdbc.support.rowset.SqlRowSet; import org.springframework.jdbc.support.rowset.SqlRowSetMetaData; import org.springframework.stereotype.Service; import ...

    SPRING API 2.0.CHM

    All Classes AbstractAdvisorAutoProxyCreator AbstractApplicationContext AbstractApplicationEventMulticaster AbstractAspectJAdvice AbstractAspectJAdvisorFactory AbstractAspectJAdvisorFactory....

    SpringFramework Notes For Professionals(对于专业人士的Spring笔记)

    4. 使用SimpleJdbcCall获取SqlRowSet:这部分内容展示了如何在Spring环境中通过SimpleJdbcCall来执行存储过程并获取结果集。同时,还涉及了如何处理Oracle数据库,因为Spring对不同数据库的支持方式可能会有所不同。...

    JdbcTemplate查询

    相较于传统的JDBC编程方式,使用`JdbcTemplate`可以极大地减少样板代码的编写,并能更好地处理异常、事务以及资源管理等问题。 #### 二、JdbcTemplate简介 `JdbcTemplate`作为Spring JDBC的核心类,主要负责执行...

    JdbcTemplate操作总结

    在示例中,需要的jar文件包括`common-logging.jar`(日志处理),`spring.jar`(Spring核心库),以及数据库驱动对应的jar(如`ojdbc.jar`,这里是Oracle驱动)。 总结起来,JdbcTemplate是Spring框架中一个强大的...

    jdbcTemplate使用方法实例解析

    在本文中,我们将详细介绍jdbcTemplate的使用方法实例解析,以及其在实际开发中的应用价值。 一、JdbcTemplate概述 JdbcTemplate是Spring JDBC抽象框架的核心类,提供了大量实用的方法来简化数据库操作。它通过...

    ROWSET介绍加实例

    在Java编程中,ROWSET允许程序员在离线环境中处理数据库数据,这意味着它可以在没有实际数据库连接的情况下操作数据。这使得ROWSET在处理大量数据时更为高效,因为减少了与数据库的来回通信。 ROWSET基于JavaBeans...

Global site tag (gtag.js) - Google Analytics