package oracle.clob; 

import java.io.BufferedReader; 

import java.io.File; 

import java.io.FileReader; 

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.driver.OracleDriver; 

import oracle.sql.CLOB; 

public class ClobTest { 

    String url = "jdbc:oracle:thin:@192.168.2.157:1521:orcl"; 

    String user = "xj"; 

    String pwd = "xj"; 

    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(); 

    } 

    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); 

    } 

    // 将字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; 

    } 

    

    // TODO Auto-generated method stub 

    public static void main(String[] args) throws IOException, 

            ClassNotFoundException, SQLException { 

        // TODO Auto-generated method stub 

        ClobTest clobtest = new ClobTest(); 

        // read file 

        FileReader _frd = new FileReader(new File("D:\\DOS.txt")); 

        BufferedReader _brd = new BufferedReader(_frd); 

        String _rs = _brd.readLine(); 

        StringBuffer _input = new StringBuffer(); 

        while (_rs != null) { 

            _input.append(_rs); 

            _rs = _brd.readLine(); 

        } 

        // System.out.println(_input.toString()); 

        // 输入测试 

        clobtest.text = _input.toString(); 

        clobtest.clobImport(); 

        // 输出测试 

        // clobtest.clobExport(); 

    } 

}