java - oracle - Clob 쓰고 읽기 예제

I. 테이블

CREATE TABLE clob_test (

    code CHAR(1) PRIMARY KEY,

    text CLOB

);
 
 

II. 쓰기

...

connection.setAutoCommit(false);

preparedstatement = connection.prepareStatement("INSERT INTO clob_test ('1', empty_clob())");

preparedstatement.executeUpdate();

preparedstatement.close();

 

content = "저장할 clob 데이터";

preparedstatement = connection.prepareStatement("SELECT text FROM clob_test WHERE code = '1' FRO UPDATE");
resultset = preparedstatement.executeQuery();

if (resultset.next()) {

    Writer writer = null;
    Reader reader = null;
    try {
        Clob clob = resultset.getClob(1);
        writer = ((oracle.sql.CLOB)clob).getCharacterOutputStream();
        reader = new CharArrayReader(content.toCharArray());
   
        char[] buffer = new char[1024];
        int read = 0;
        while ( (read = reader.read(buffer,0,1024)) != -1){
            writer.write(buffer, 0, read);
        }

    } catch (Exception exception) {
        logger.error("Error at BoardDAO.boardInsert",exception);
        throw exception;
    } finally {
        if (reader != null) try { reader.close(); } catch (Exception exception) {}
        if (writer != null) try { writer.close(); } catch (Exception exception) {}
    }
}

..

connection.commit();

..
 
 

III. 읽기

preparedstatement = connection.prepareStatement("SELECT text FROM clob_test WHERE code = '1'");

resultset = preparedstatement.executeQuery();

 

String text = null;

StringBuffer stringbuffer = new StringBuffer();
char[] charbuffer = new char[1024];
int read = 0;

Reader reader = null;
try {
    reader = resultset.getCharacterStream(1);

    while ((read = reader.read(charbuffer, 0, 1024)) != -1)
        stringbuffer.append(charbuffer, 0, read);

    text = stringbuffer.toString();

} catch (Exception exception) {
    throw exception;
} finally {
    if (reader != null) reader.close();
}

 

System.out.println(text);