import java.io.*;
import java.util.*;
import java.sql.*;
public class ClobTest {
private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:ora10g";
private static final String USER = "sc";
private static final String PASSWORD = "sc";
private static Connection conn = null;
private static Statement stmt = null;
/**
* 往數(shù)據(jù)庫(kù)中插入一個(gè)新的CLOB對(duì)象
*/
public static void save(BO obj) throws Exception {
/*
一定要設(shè)定不自動(dòng)提交，否則拋出
ORA-01002: 讀取違反順序 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
stmt = conn.createStatement();
/* 插入一個(gè)空的CLOB對(duì)象 */
stmt.executeUpdate("INSERT INTO TEST_CLOB VALUES ('1000', EMPTY_CLOB())");//
一定要使用Oracle中的EMPTY_CLOB()函數(shù)
stmt.close();//記得關(guān)掉我哦 :-)
stmt= null;
/* 查詢此CLOB對(duì)象并鎖定 */
//stmt = conn.prepareStatement();//如果是PrepareStatement接口,
一定要重新創(chuàng)建該對(duì)象,否則拋出
ORA-01006: 賦值變量不存在
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='1000' FOR UPDATE");//
一定要for update鎖定該記錄，否則拋出
ORA-22920: 未鎖定含有 LOB 值的行
while (rs.next()) {
/* 取出此CLOB對(duì)象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 向CLOB對(duì)象中寫入數(shù)據(jù) */
Writer out = clob.getCharacterOutputStream();
//out.write(new String(obj.getEmail()));//obj.getEmail()返回byte[]類型,但是當(dāng)obj.getEmail()絕對(duì)大時(shí),執(zhí)行new String(byte[])時(shí),JVM會(huì)拋出
內(nèi)存溢出異常
byte[] emails = obj.getEmail();
ClobStreamHandler csh = new ClobStreamHandler(emails);
String[] arrx = csh.pagedClobStream();//要解決內(nèi)存溢出異常，必須把絕對(duì)大的byte[]進(jìn)行
分頁(yè)
if (arrx != null){
for (int i = 0; i < arrx.length; i++) {
out.write(arrx[i]);
out.flush();//要解決內(nèi)存溢出異常,必須
一頁(yè)一頁(yè)的flush()到數(shù)據(jù)庫(kù)
}
}
else out.write("");
out.close();
}
/* 正式提交 */
conn.commit();
/* 恢復(fù)原提交狀態(tài) */
conn.setAutoCommit(defaultCommit);
} catch (Exception ex) {
/* 出錯(cuò)回滾 */
conn.rollback();
throw ex;
} finally {相關(guān)關(guān)閉操作}
}
/**
* 修改CLOB對(duì)象（是在原CLOB對(duì)象基礎(chǔ)上進(jìn)行覆蓋式的修改）
*
* @param obj - 數(shù)據(jù)對(duì)象
* @throws java.lang.Exception
* @roseuid 3EDA04B60367
*/
public static void modify(BO obj) throws Exception {
/* 設(shè)定不自動(dòng)提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 查詢CLOB對(duì)象并鎖定 */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='1000' FOR UPDATE");
while (rs.next()) {
/* 獲取此CLOB對(duì)象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 進(jìn)行覆蓋式修改 */
Writer out = clob.getCharacterOutputStream();
byte[] emails = obj.getEmail();
ClobStreamHandler csh = new ClobStreamHandler(emails);
String[] arrx = csh.pagedClobStream();//要解決內(nèi)存溢出異常，必須把絕對(duì)大的byte[]進(jìn)行
分頁(yè)
if (arrx != null){
for (int i = 0; i < arrx.length; i++) {
out.write(arrx[i]);
out.flush();//要解決內(nèi)存溢出異常,必須
一頁(yè)一頁(yè)的flush()到數(shù)據(jù)庫(kù)
}
}
else out.write("");
out.close();
}
/* 正式提交 */
conn.commit();
/* 恢復(fù)原提交狀態(tài) */
conn.setAutoCommit(defaultCommit);
} catch (Exception ex) {
/* 出錯(cuò)回滾 */
conn.rollback();
throw ex;
} finally {相關(guān)關(guān)閉操作}
/* 恢復(fù)原提交狀態(tài) */
conn.setAutoCommit(defaultCommit);
}
/**
* 替換CLOB對(duì)象（將原CLOB對(duì)象清除，換成一個(gè)全新的CLOB對(duì)象）
*
* @param obj - 數(shù)據(jù)對(duì)象
* @throws java.lang.Exception
* @roseuid 3EDA04BF01E1
*/
public static void replace(BO obj) throws Exception {
/* 設(shè)定不自動(dòng)提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 清空原CLOB對(duì)象 */
stmt.executeUpdate("UPDATE TEST_CLOB SET CLOBCOL=EMPTY_CLOB() WHERE ID='1000'");
/* 查詢CLOB對(duì)象并鎖定 */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='1000' FOR UPDATE");
while (rs.next()) {
/* 獲取此CLOB對(duì)象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 更新數(shù)據(jù) */
Writer out = clob.getCharacterOutputStream();
byte[] emails = item.getEmail();
ClobStreamHandler csh = new ClobStreamHandler(emails);
String[] arrx = csh.pagedClobStream();//要解決內(nèi)存溢出異常，必須把絕對(duì)大的byte[]進(jìn)行
分頁(yè)
if (arrx != null){
for (int i = 0; i < arrx.length; i++) {
out.write(arrx[i]);
out.flush();//要解決內(nèi)存溢出異常,必須
一頁(yè)一頁(yè)的flush()到數(shù)據(jù)庫(kù)
}
}
else out.write("");
out.close();
}
/* 正式提交 */
conn.commit();
/* 恢復(fù)原提交狀態(tài) */
conn.setAutoCommit(defaultCommit);
} catch (Exception ex) {
/* 出錯(cuò)回滾 */
conn.rollback();
throw ex;
} finally {相關(guān)關(guān)閉操作}
}
/**
* 讀取CLOB對(duì)象
*/
public static byte[] read() throws Exception {
/* 設(shè)定不自動(dòng)提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 查詢CLOB對(duì)象 */
ResultSet rs = stmt.executeQuery("SELECT * FROM TEST_CLOB WHERE ID='1000'");
while (rs.next()) {
/* 獲取CLOB對(duì)象 */
oracle.sql.CLOB c= (oracle.sql.CLOB)rs.getClob("CLOBCOL");
if (c != null){
try {
oracle.jdbc.driver.OracleClobInputStream is = (OracleClobInputStream) c.getAsciiStream();
java.io.ByteArrayOutputStream baos = new java.io.ByteArrayOutputStream();
byte[] by = new byte[1024 * 200];
while(is.read(by, 0, by.length) != -1){
baos.write(by, 0, by.length);
baos.flush();//
把數(shù)據(jù)寫入內(nèi)存
}
baos.close();
is.close();
return baos.toByteArray();//
不會(huì)內(nèi)存溢出了,呵呵. 原因是把數(shù)據(jù)寫入了內(nèi)存,而不是JVM的內(nèi)存管理區(qū)域
} catch (SQLException e) {
//e.printStackTrace();
}
}
else return new byte[0];
break;
}
} catch (Exception ex) {
conn.rollback();
throw ex;
} finally {相關(guān)關(guān)閉操作}
/* 恢復(fù)原提交狀態(tài) */
conn.setAutoCommit(defaultCommit);
}
/**
* 建立測(cè)試用表格
* @throws Exception
*/
public static void createTables() throws Exception {
try {
stmt.executeUpdate("CREATE TABLE TEST_CLOB (ID VARCHAR2(4), CLOBCOL CLOB)");
stmt.executeUpdate("CREATE TABLE TEST_BLOB (ID VARCHAR2(4), BLOBCOL BLOB)");
} catch (Exception ex) {
}
}
public static void main(String[] args) throws Exception {
/* 裝載驅(qū)動(dòng),建立數(shù)據(jù)庫(kù)連接 */
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USER, PASSWORD);
stmt = conn.createStatement();
/* 建立測(cè)試表格 */
createTables();
}
}
對(duì)Clob字符流進(jìn)行分頁(yè)的算法：
1 package privy.astroqi.oracle.db.handler;
2
3 /**
4 *
5 * @author Astro Qi
6 * @since 2008-07-23 00:05
7 *
8 */
9 public class ClobStreamHandler {
10
11 private static int PAGE_SIZE = 1024 * 200 * 1;
12
13 private byte[] dataes;
14
15 private int length;
16
17 private int pageCount;
18
19 public ClobStreamHandler(byte[] data){
20 if (data == null){
21 throw new java.lang.IllegalArgumentException("參數(shù)byte[]不能為空,否則無(wú)法處理接下來(lái)的操作.");
22 }
23
24 dataes = data;
25 length = dataes.length;
26 pageCount = (length % PAGE_SIZE == 0) ? (length / PAGE_SIZE) : (length / PAGE_SIZE) + 1;
27
28 }
29
30 public String[] pagedClobStream(){
31
32 String[] arr = new String[pageCount];
33
34 for (int i = 1; i <= pageCount; i++) {
35 int sheYuByte = length - (PAGE_SIZE * (i - 1));
36 byte[] b = null;
37 if (sheYuByte > PAGE_SIZE){
38 b = new byte[PAGE_SIZE];
39 }
40 else {
41 b = new byte[sheYuByte];
42 }
43 for (int j = 0; j < b.length; j++){
44 b[j] = dataes[(i - 1) * PAGE_SIZE + j];
45 }
46 arr[i - 1] = new String(b);
47 }
48
49 return arr;
50 }
51 }