- if (info!=null && info.length()>2000){
- nfo = info.substring(0,2000);
- }
當(dāng)再次Debug時還是拋出：Java.sql.SQLException:數(shù)據(jù)大小超出此類型的最大值！
當(dāng)使用原始的Statement接口后問題解決,郁悶?。。。?/font>
今天在文章中看到：
現(xiàn)在通過Oracle提供的JDBC文檔來詳細看看問題的來由。
我們都知道Oracle提供了兩種客戶端訪問方式OCI和thin，
在這兩種方式下，字符串轉(zhuǎn)換的過程如下：
1、JDBC OCI driver：
在JDBC文檔中是這么說的：
“
If the value of NLS_LANG is set to a character set other than US7ASCII or WE8ISO8859P1, then the driver uses UTF8 as the client character set. This happens automatically and does not require any user intervention. OCI then converts the data from the database character set to UTF8. The JDBC OCI driver then passes the UTF8 data to the JDBC Class Library where the UTF8 data is converted to UTF-16. ”
2、JDBC thin driver:
JDBC文檔是這樣的：
“If the database character set is neither ASCII (US7ASCII) nor ISO Latin1 (WE8ISO8859P1), then the JDBC thin driver must impose size restrictions for SQL CHAR bind parameters that are more restrictive than normal database size limitations. This is necessary to allow for data expansion during conversion.
The JDBC thin driver checks SQL CHAR bind sizes when a setXXX() method (except for the setCharacterStream() method) is called. If the data size exceeds the size restriction, then the driver returns a SQL exception (SQLException: Data size bigger than max size for this type) from the setXXX() call. This limitation is necessary to avoid the chance of data corruption when conversion of character data occurs and increases the length of the data. This limitation is enforced in the following situations:
(1)Using the JDBC thin driver
(2)Using binds (not defines)
(3)Using SQL CHAR datatypes
(4)Connecting to a database whose character set is neither ASCII (US7ASCII) nor ISO Latin1 (WE8ISO8859P1)
When the database character set is neither US7ASCII nor WE8ISO8859P1, the JDBC thin driver converts Java UTF-16 characters to UTF-8 encoding bytes for SQL CHAR binds. The UTF-8 encoding bytes are then transferred to the database, and the database converts the UTF-8 encoding bytes to the database character set encoding.”
原來是JDBC在轉(zhuǎn)換過程中對字符串的長度做了限制。這個限制和數(shù)據(jù)庫中字段的實際長度沒有關(guān)系。
所以，setCharacterStream()方法可以逃過字符轉(zhuǎn)換限制，也就成為了解決此問題的方案之一。
而JDBC對轉(zhuǎn)換字符長度的限制是為了轉(zhuǎn)換過程中的數(shù)據(jù)擴展。
根據(jù)實際測試結(jié)果，在ZHS16GBK字符集和thin驅(qū)動下，2000－4000長度的varchar字段都只能插入1333個字節(jié)（約666個漢 字）。
To sum，解決PreparedStatement的setString中字符串長度問題可以有兩種辦法：
1、使用setCharacterStream()方法；
java 代碼
- pstmt.setCharacterStream(index,new InputStreamReader(myString, myString.length());
2、使用OCI驅(qū)動連接Oracle數(shù)據(jù)庫。 
