Java 儲存和讀取 oracle CLOB 類型欄位的實用方法

標籤：取出   gre   buffer   out   except   connect   ati   todo   clob欄位   

import java.io.BufferedReader;import java.io.IOException;import java.io.Reader;import java.io.StringReader;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import oracle.jdbc.OracleDriver;import oracle.sql.CLOB;public class ClobTest {    String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";    String user = "his_xs";    String pwd = "123456";    String text = "這是要插入到CLOB裡面的資料";    private void clobImport() throws ClassNotFoundException, SQLException {        // TODO Auto-generated method stub        DriverManager.registerDriver(new OracleDriver());        Connection conn = DriverManager.getConnection(url, user, pwd);// 得到連線物件        String sql = "insert into clob_test(id,str) values (‘1‘,?)";// 要執行的SQL語句        PreparedStatement stmt = conn.prepareStatement(sql);// 載入SQL語句        // PreparedStatement支援SQL帶有問號？，可以動態替換？的內容。        Reader clobReader = new StringReader(text); // 將 text轉成流形式        stmt.setCharacterStream(1, clobReader, text.length());// 替換sql語句中的？        int num = stmt.executeUpdate();// 執行SQL        if (num > 0) {            System.out.println("ok");        } else {            System.out.println("NO");        }        stmt.close();        conn.close();    }    // 將字CLOB轉成STRING類型    public String ClobToString(CLOB clob) throws SQLException, IOException {        String reString = "";        Reader is = clob.getCharacterStream();// 得到流        BufferedReader br = new BufferedReader(is);        String s = br.readLine();        StringBuffer sb = new StringBuffer();        while (s != null) {// 執行迴圈將字串全部取出付值給StringBuffer由StringBuffer轉成STRING            sb.append(s);            s = br.readLine();        }        reString = sb.toString();        return reString;    }    private void clobExport() throws ClassNotFoundException, SQLException,            IOException {        // TODO Auto-generated method stub        CLOB clob = null;        String sql = "select * from clob_test where id=1";        DriverManager.registerDriver(new OracleDriver());        Connection conn = DriverManager.getConnection(url, user, pwd);// 得到連線物件        PreparedStatement stmt = conn.prepareStatement(sql);        ResultSet rs = stmt.executeQuery();        String id = "";        String content = "";        if (rs.next()) {            id = rs.getString("id");// 獲得ID            clob = (oracle.sql.CLOB) rs.getClob("str"); // 獲得CLOB欄位str            // 注釋： 用 rs.getString("str")無法得到 資料 ，返回的 是 NULL;            content = ClobToString(clob);        }        stmt.close();        conn.close();        // 輸出結果        System.out.println(id);        System.out.println(content);    }        public static void main(String[] args) throws Exception {        ClobTest a = new ClobTest();        a.clobImport();        a.clobExport();    }}

 

Java 儲存和讀取 oracle CLOB 類型欄位的實用方法