- if (info!=null && info.length()>2000){
- nfo = info.substring(0,2000);
- }
當再次Debug時(shí)還是拋出：Java.sql.SQLException:數據大小超出此類(lèi)型的最大值！
當使用原始的Statement接口后問(wèn)題解決,郁悶?。。?！
今天在文章中看到：
現在通過(guò)Oracle提供的JDBC文檔來(lái)詳細看看問(wèn)題的來(lái)由。
我們都知道Oracle提供了兩種客戶(hù)端訪(fǎng)問(wèn)方式OCI和thin，
在這兩種方式下，字符串轉換的過(guò)程如下：
1、JDBC OCI driver：
在JDBC文檔中是這么說(shuō)的：
“
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.”
原來(lái)是JDBC在轉換過(guò)程中對字符串的長(cháng)度做了限制。這個(gè)限制和數據庫中字段的實(shí)際長(cháng)度沒(méi)有關(guān)系。
所以，setCharacterStream()方法可以逃過(guò)字符轉換限制，也就成為了解決此問(wèn)題的方案之一。
而JDBC對轉換字符長(cháng)度的限制是為了轉換過(guò)程中的數據擴展。
根據實(shí)際測試結果，在ZHS16GBK字符集和thin驅動(dòng)下，2000－4000長(cháng)度的varchar字段都只能插入1333個(gè)字節（約666個(gè)漢 字）。
To sum，解決PreparedStatement的setString中字符串長(cháng)度問(wèn)題可以有兩種辦法：
1、使用setCharacterStream()方法；
java 代碼
- pstmt.setCharacterStream(index,new InputStreamReader(myString, myString.length());
2、使用OCI驅動(dòng)連接Oracle數據庫。 
