做了個簡單的通告管理，在本機測試時數(shù)據(jù)量比較小。最開始用的時varchar2到2000，之后用hibernate中的text類型，對應(yīng)數(shù)據(jù)庫中的Clob類型。當(dāng)數(shù)據(jù)量小于4000時，可以直接使用setObject()插入數(shù)據(jù)庫。當(dāng)數(shù)據(jù)量大于4000時。報錯：socket write error。
查找了一些資料，說當(dāng)數(shù)據(jù)量超過4000時，應(yīng)該使用流方式操作。看到頭昏昏，煩都煩死了......
最后發(fā)現(xiàn)Spring封裝了對lob數(shù)據(jù)的操作，可以直接使用jdbcTemple操作lob數(shù)據(jù)。
當(dāng)使用MySql、MSSQL、Oracle 10g時，使用DefaultLobHandler；使用Oracle 9i時使用OracleLobHandler。
示例代碼：【轉(zhuǎn)值javaWorld】
?1?????????final?File?binaryFile?=?new?File("c:\\workspace\\wish.jpg"
);
?2?????????final?File?txtFile?=?new?File("c:\\workspace\\test.txt"
);
?3?
???????
?4?????????final?InputStream?is?=?new
?FileInputStream(binaryFile);
?5?????????final?Reader?reader?=?new
?FileReader(txtFile);
?6?
???????
?7?????????JdbcTemplate?jdbcTemplate?=?new
?JdbcTemplate(dataSource);
?8?
???????
?9?????????final?LobHandler?lobHandler?=?new
?DefaultLobHandler();
10?
???????
11?????????jdbcTemplate.execute("INSERT?INTO?test?(txt,?image)?VALUES(?,??)"
,
12??????????????????????new
?AbstractLobCreatingPreparedStatementCallback(lobHandler)?{
13?????????????????????????protected?void
?setValues(PreparedStatement?pstmt,?LobCreator?lobCreator)
14????????????????????????????????????????????????????????????throws
?SQLException,?DataAccessException?{
15?????????????????????????????lobCreator.setClobAsCharacterStream(pstmt,?1,?reader,?(int
)?txtFile.length());
16?????????????????????????????lobCreator.setBlobAsBinaryStream(pstmt,?2,?is,?(int
)?binaryFile.length());
17?
????????????????????????}
18?
?????????????????????});
19?
???????
20?
????????reader.close();
21?
????????is.close();
22?
讀取代碼：
?1?????????final?Writer?writer?=?new?FileWriter("c:\\workspace\\test_bak.txt"
);
?2?????????final?OutputStream?os?=?new?FileOutputStream(new?File("c:\\workspace\\wish_bak.jpg"
));
?3?
???????
?4?????????jdbcTemplate.query("SELECT?txt,image?FROM?test?WHERE?id?=??"
,
?5?????????????????new?Object[]?{new?Integer(1
)},
?6?????????????????new
?AbstractLobStreamingResultSetExtractor()?{
?7?????????????????????protected?void?streamData(ResultSet?rs)?throws
?SQLException,?IOException,?DataAccessException?{
?8?????????????????????????FileCopyUtils.copy(lobHandler.getClobAsCharacterStream(rs,?1
),?writer);
?9?????????????????????????FileCopyUtils.copy(lobHandler.getBlobAsBinaryStream(rs,?2
),?os);
10?
????????????????????}
11?
?????????????????});
12?
????????writer.close();
13?
????????os.close();
14?
15?
備注1：OracleLobHandler要求注入CommonsDbcpNativeJdbcExtractor，否則報錯：要求使用OracleConnection，不可以使用Dbcp連接池。
1
????
<
bean?id
=
"
lobHandler
"
?
class
=
"
org.springframework.jdbc.support.lob.OracleLobHandler
"
>
2
????
<
property?name
=
"
nativeJdbcExtractor
"
>
3
???????
<
bean?
class
=
"
org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor
"
/>
4
???
</
property
>
5
???
</
bean
>
備注2：Oracle驅(qū)動版本很多，要求使用正確的驅(qū)動。當(dāng)驅(qū)動版本錯誤時,報錯：無法釋放LobHandler；無法關(guān)閉Writer；無法從套接字讀取更多的信息。執(zhí)行插入操作，但是Clob類型數(shù)據(jù)為空。