做了個(gè)簡(jiǎn)單的通告管理，在本機(jī)測(cè)試時(shí)數(shù)據(jù)量比較小。最開(kāi)始用的時(shí)varchar2到2000，之后用hibernate中的text類型，對(duì)應(yīng)數(shù)據(jù)庫(kù)中的Clob類型。當(dāng)數(shù)據(jù)量小于4000時(shí)，可以直接使用setObject()插入數(shù)據(jù)庫(kù)。當(dāng)數(shù)據(jù)量大于4000時(shí)。報(bào)錯(cuò)：socket write error。
查找了一些資料，說(shuō)當(dāng)數(shù)據(jù)量超過(guò)4000時(shí)，應(yīng)該使用流方式操作。看到頭昏昏，煩都煩死了......
最后發(fā)現(xiàn)Spring封裝了對(duì)lob數(shù)據(jù)的操作，可以直接使用jdbcTemple操作lob數(shù)據(jù)。
當(dāng)使用MySql、MSSQL、Oracle 10g時(shí)，使用DefaultLobHandler；使用Oracle 9i時(shí)使用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，否則報(bào)錯(cuò)：要求使用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ū)動(dòng)版本很多，要求使用正確的驅(qū)動(dòng)。當(dāng)驅(qū)動(dòng)版本錯(cuò)誤時(shí),報(bào)錯(cuò)：無(wú)法釋放LobHandler；無(wú)法關(guān)閉Writer；無(wú)法從套接字讀取更多的信息。執(zhí)行插入操作，但是Clob類型數(shù)據(jù)為空。