오라클 CLOB 데이터 입출력

1. 오라클 10g에서 CLOB 처리

 

오라클 10g에서 CLOB 처리는 다른 데이터 타이입을 처리할 때와 마찬가지로 처리하면 된다.

오라클 9i 이하에서는 아래 2번 항목 참고

 

2. 오라클 9i 이하에서 CLOB 처리

 

1) 저장하기

String sql = "insert into board (seq,name, conenet, reg_date) values (board_seq.nextval,?,empty_clob(),sysdate)";

                   // empty_clob()를 이용해 데이터를 입력하기 위해 공간 확보

try{
conn = getConnection();

conn.setAutoCommit(false); // auto commit 해제

pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,bd.getName());
pstmt.executeUpdate();
pstmt.close();

 

ps = con.prepareStatement("select board_seq.CURRVAL from dual");

            // CLOB 타입의 데이터를 입력하기 위해 현재 시퀀스 번호를 조회함
rs = ps.executeQuery();
rs.next();
int seq = rs.getInt(1);

 

String sql2 = " select  content from board  where seq = ? for update ";

                      // for update를 이용해 CLOB column에 lock 건다
 
pstmt = conn.prepareStatement(sql2);
pstmt.setInt(1,seq);
rs = pstmt.executeQuery();
if(rs.next()) {

 

CLOB clob = ((OracleResultSet)rs).getCLOB(1);    

// ======1 번 방법 ================    
Writer writer = clob.setCharacterStream(0L);
StringReader src = new StringReader((db.getContent());
char[] buffer = new char[1024];
int read = 0;
while ( (read = src.read(buffer) != -1) {
writer.write(buffer, 0, read);
src.close();        
writer.close();

 

// =======2번 방법 ================

BufferedWriter bw = new BufferedWriter(clob.setCharacterStream(0L));
bw.write(db.getContent());

bw.close();
}
}
conn.commit();

catch (Exception e) {
   conn.rollback();
   ..................

}finally{

  conn.setAutoCommit(true);  

 ..................
}
}

 

2) 불러오기

String sql = " select * from board  where seq = ? ";

try{
conn = getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,seq);
rs = pstmt.executeQuery();
if (rs.next()) {
      ...
      ...      
// =======1번 방법 ================
StringBuffer sb = new StringBuffer();
Reader rd = rs.getCharacterStream("content");
char[] buffer = new char[1024];
int byteRead;
while((byteRead=rd.read(buffer,0,1024))!=-1){
sb.append(buffer,0,byteRead);
}
rd.close();
bean.setContent(sb.toString()); 
// =======2번 방법 ================
CLOB c1 = ((OracleResultSet)rs).getCLOB("content");
BufferedReader br = new BufferedReader(c1.getCharacterStream());
String temp = "";
String comments = "";
while( (temp=br.readLine()) != null ) {
comments += temp+System.getProperty("line.separater");
}
br.close();
bean.setContent((comments));
      ...
      ...
}
} finally {
   ..........
}
return bean;
}