JDBC to read and write CLOB

Posted on 2009-11-17 filed under: Oracle (RSS) 21 views

Creating a Test Table

create table CLOB_TEST (ID number (10), CLOB_TEXT CLOB)

import java.sql.*;
import java.io.OutputStream;
import java.io.Writer;
import java.io.Reader;

// create table CLOB_TEST(ID number(10), CLOB_TEXT CLOB)

public class ClogTest {

    public static void main(String[] args) throws Exception{
        Class.forName("oracle.jdbc.driver.OracleDriver");
        
        Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:zhongyj", "scott", "tiger");

        // 每次执行时，要修改第二个参数id

        insertClob(conn, 4);
        readClob(conn, 4);
        
        conn.close();
    }
    
    static void insertClob(Connection conn, int id) throws Exception {
        conn.setAutoCommit(false);
        PreparedStatement ps = conn.prepareStatement("insert into CLOB_TEST(ID, CLOB_TEXT) values(?,empty_clob())");
        ps.setInt(1, id);
        ps.executeUpdate();
        
        ps= conn.prepareStatement("select CLOB_TEXT from CLOB_TEST where");
        ps.setInt(1, id);
        
        ResultSet rs = ps.executeQuery();
        Clob clob = null;
        
        if(rs.next()) {
            clob = rs.getClob(1);
        }
        
        // 这一段往CLOB_TEXT字段存入CLOB_TEXT_CONTENT

        String str = "Write in String: Hello Jdbc CLOB";
        clob.setString(10, str); // 第一个参数是从第几个字符开始写

        
//        // 这一段往CLOB_TEXT字段存入CLOB_TEXT_CONTENT

//        Writer wr = clob.setCharacterStream(10); // 参数是从第几个字符开始写

//        wr.write("Write in Writer");

//        wr.flush();

//        wr.close();

        
        rs.close();
        conn.commit();
        ps.close();
    }

    static void readClob(Connection conn, int id) throws Exception{
        PreparedStatement ps = conn.prepareStatement("select CLOB_TEXT from CLOB_TEST where");
        ps.setInt(1, id);
        
        ResultSet rs = ps.executeQuery();
        
        if(rs.next()) {
            Clob clob = rs.getClob("CLOB_TEXT");
            Reader rd = clob.getCharacterStream();
            char [] str = new char[12];
            while(rd.read(str) != -1) {
                System.out.print(str);
            }
        }
    }
}