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本身有沒有問題!