The blog of Astro Qi

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;

 
    /**
     * 往數據庫中插入一個新的CLOB對象
     */
    public static void save(BO obj) throws Exception {
        /* 一定要設定不自動提交，否則拋出ORA-01002: 讀取違反順序 */
        boolean defaultCommit = conn.getAutoCommit();
        conn.setAutoCommit(false);
 
        try {
            stmt = conn.createStatement();
            /* 插入一個空的CLOB對象 */
            stmt.executeUpdate("INSERT INTO TEST_CLOB VALUES ('1000', EMPTY_CLOB())");//一定要使用Oracle中的EMPTY_CLOB()函數
           
            stmt.close();//記得關掉我哦 :-)
            stmt= null;

            /* 查詢此CLOB對象并鎖定 */
            //stmt = conn.prepareStatement();//如果是PrepareStatement接口,一定要重新創建該對象,否則拋出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對象 */
                oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");

                /* 向CLOB對象中寫入數據 */
                Writer out = clob.getCharacterOutputStream();
                //out.write(new String(obj.getEmail()));//obj.getEmail()返回byte[]類型,但是當obj.getEmail()絕對大時,執行new String(byte[])時,JVM會拋出內存溢出異常

                byte[] emails = obj.getEmail();
                ClobStreamHandler csh = new ClobStreamHandler(emails);
                String[] arrx = csh.pagedClobStream();//要解決內存溢出異常，必須把絕對大的byte[]進行分頁
                if (arrx != null){
                    for (int i = 0; i < arrx.length; i++) {
                        out.write(arrx[i]);
                        out.flush();//要解決內存溢出異常,必須一頁一頁的flush()到數據庫
                    }
                }
                else out.write("");
                out.close();
            }
            /* 正式提交 */
            conn.commit();

            /* 恢復原提交狀態 */
            conn.setAutoCommit(defaultCommit);
        } catch (Exception ex) {
            /* 出錯回滾 */
            conn.rollback();
            throw ex;
        } finally {相關關閉操作}
 
      
    }
 
    /**
     * 修改CLOB對象（是在原CLOB對象基礎上進行覆蓋式的修改）
     *
     * @param obj - 數據對象
     * @throws java.lang.Exception
     * @roseuid 3EDA04B60367
     */
    public static void modify(BO obj) throws Exception {
        /* 設定不自動提交 */
        boolean defaultCommit = conn.getAutoCommit();
        conn.setAutoCommit(false);
 
        try {
            /* 查詢CLOB對象并鎖定 */
            ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='1000' FOR UPDATE");
            while (rs.next()) {
                /* 獲取此CLOB對象 */
                oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");  
                
                /* 進行覆蓋式修改 */
                Writer out = clob.getCharacterOutputStream();
                byte[] emails = obj.getEmail();
                ClobStreamHandler csh = new ClobStreamHandler(emails);
                String[] arrx = csh.pagedClobStream();//要解決內存溢出異常，必須把絕對大的byte[]進行分頁
                if (arrx != null){
                    for (int i = 0; i < arrx.length; i++) {
                        out.write(arrx[i]);
                        out.flush();//要解決內存溢出異常,必須一頁一頁的flush()到數據庫
                    }
                }
                else out.write("");
                out.close();
            }
            /* 正式提交 */
            conn.commit();

            /* 恢復原提交狀態 */
            conn.setAutoCommit(defaultCommit);
        } catch (Exception ex) {
            /* 出錯回滾 */
            conn.rollback();
            throw ex;
        } finally {相關關閉操作}
 
        /* 恢復原提交狀態 */
        conn.setAutoCommit(defaultCommit);
    }
 
    /**
     * 替換CLOB對象（將原CLOB對象清除，換成一個全新的CLOB對象）
     *
     * @param obj - 數據對象
     * @throws java.lang.Exception
     * @roseuid 3EDA04BF01E1
     */
    public static void replace(BO obj) throws Exception {
        /* 設定不自動提交 */
        boolean defaultCommit = conn.getAutoCommit();
        conn.setAutoCommit(false);
 
        try {
            /* 清空原CLOB對象 */
            stmt.executeUpdate("UPDATE TEST_CLOB SET CLOBCOL=EMPTY_CLOB() WHERE ID='1000'");
            
            /* 查詢CLOB對象并鎖定 */
            ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='1000' FOR UPDATE");
            
            while (rs.next()) {
                /* 獲取此CLOB對象 */
                oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
                /* 更新數據 */
                Writer out = clob.getCharacterOutputStream();
                byte[] emails = item.getEmail();
                ClobStreamHandler csh = new ClobStreamHandler(emails);
                String[] arrx = csh.pagedClobStream();//要解決內存溢出異常，必須把絕對大的byte[]進行分頁
                if (arrx != null){
                    for (int i = 0; i < arrx.length; i++) {
                        out.write(arrx[i]);
                        out.flush();//要解決內存溢出異常,必須一頁一頁的flush()到數據庫
                    }
                }
                else out.write("");
                out.close();
            }
            /* 正式提交 */
            conn.commit();

            /* 恢復原提交狀態 */
            conn.setAutoCommit(defaultCommit);
        } catch (Exception ex) {
            /* 出錯回滾 */
            conn.rollback();
            throw ex;
        } finally {相關關閉操作}
 
       
    }
 
    /**
     * 讀取CLOB對象
     */
    public static byte[] read() throws Exception {
        /* 設定不自動提交 */
        boolean defaultCommit = conn.getAutoCommit();
        conn.setAutoCommit(false);
 
        try {
            /* 查詢CLOB對象 */
            ResultSet rs = stmt.executeQuery("SELECT * FROM TEST_CLOB WHERE ID='1000'");
            while (rs.next()) {
                /* 獲取CLOB對象 */
                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();//把數據寫入內存
                          }
                   
                          baos.close();
                          is.close();
                          return baos.toByteArray();//不會內存溢出了,呵呵. 原因是把數據寫入了內存,而不是JVM的內存管理區域
                     } catch (SQLException e) {
                           //e.printStackTrace();
                     }
                }
                else return new byte[0];
                break;
            }
        } catch (Exception ex) {
            conn.rollback();
            throw ex;
        } finally {相關關閉操作}
 
        /* 恢復原提交狀態 */
        conn.setAutoCommit(defaultCommit);
    }
 
    /**
     * 建立測試用表格
     * @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 {
        /* 裝載驅動,建立數據庫連接 */
        Class.forName(DRIVER);
        conn = DriverManager.getConnection(URL, USER, PASSWORD);
        stmt = conn.createStatement();
 
        /* 建立測試表格 */
        createTables();
    }
}


    對Clob字符流進行分頁的算法：