|
今天在JavaEye上看大牛們的博客突然看到一篇文章標(biāo)題為:"PreparedStatement中 setString方法的異常"讓我大喜，以前在項(xiàng)目中遇到這個(gè)問(wèn)題，一直沒(méi)有得到解決今天終于明白了！ 以前項(xiàng)目中的代碼如下： java 代碼
而數(shù)據(jù)庫(kù)中對(duì)應(yīng)的表Interface_log.info varchar2(4000); 在方法頭加入如下代碼： java 代碼
當(dāng)再次Debug時(shí)還是拋出：Java.sql.SQLException:數(shù)據(jù)大小超出此類型的最大值！ 當(dāng)使用原始的Statement接口后問(wèn)題解決,郁悶啊！?。?/font> 今天在文章中看到： 現(xiàn)在通過(guò)Oracle提供的JDBC文檔來(lái)詳細(xì)看看問(wèn)題的來(lái)由。
我們都知道Oracle提供了兩種客戶端訪問(wèn)方式OCI和thin， 在這兩種方式下，字符串轉(zhuǎn)換的過(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在轉(zhuǎn)換過(guò)程中對(duì)字符串的長(zhǎng)度做了限制。這個(gè)限制和數(shù)據(jù)庫(kù)中字段的實(shí)際長(zhǎng)度沒(méi)有關(guān)系。
所以，setCharacterStream()方法可以逃過(guò)字符轉(zhuǎn)換限制，也就成為了解決此問(wèn)題的方案之一。 而JDBC對(duì)轉(zhuǎn)換字符長(zhǎng)度的限制是為了轉(zhuǎn)換過(guò)程中的數(shù)據(jù)擴(kuò)展。 根據(jù)實(shí)際測(cè)試結(jié)果，在ZHS16GBK字符集和thin驅(qū)動(dòng)下，2000－4000長(zhǎng)度的varchar字段都只能插入1333個(gè)字節(jié)（約666個(gè)漢 字）。 To sum，解決PreparedStatement的setString中字符串長(zhǎng)度問(wèn)題可以有兩種辦法：
1、使用setCharacterStream()方法； java 代碼
2、使用OCI驅(qū)動(dòng)連接Oracle數(shù)據(jù)庫(kù)。 |
|
|
來(lái)自： ShangShujie > 《我的圖書館》
