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本身有没有问题!