Clob에 저장하기
1: import java.sql.*;
2: import java.io.*;
3: import oracle.sql.*;
4: import oracle.jdbc.driver.*;
5: ...
6:
7: public void insertQuery(UploadBoard up,int re_step,int re_level)
throws Exception
8: {
9: ResultSet rs = null;
10: PreparedStatement pstmt = null;
11: Connection conn = null;
12: String query = "insert into "+up.getTableName()+" (seq,re_step,
re_level,name,title,pwd,email,readnum,writeday,ip,relativeCnt,
homepage,imgInfo,content,tag)
values(?,?,?,?,?,?,?,0,sysdate,?,0,?,?,empty_clob(),?)";
오라클 명령어 empty_clob()을 이용해 공간을 확보한다.
13: try{
14: conn = DBManager.getClobConnection();
15: conn.setAutoCommit(false);
CLOB column을 업데이트 하는동안 다른 process의 접근을 막기위해
setAutoCommit(false)를 반드시 설정해야 한다. 이부분이 가장 중요하다.
16: pstmt = conn.prepareStatement(query);
17: pstmt.setInt(1,up.getSeq());
18: pstmt.setInt(2,re_step);
19: pstmt.setInt(3,re_level);
20: pstmt.setString(4,up.getName());
21: pstmt.setString(5,up.getTitle());
22: pstmt.setString(6,up.getPwd());
23: pstmt.setString(7,up.getEmail());
24: pstmt.setString(8,up.getIp());
25: pstmt.setString(9,up.getHomepage());
26: pstmt.setString(10,up.getImgInfo());
27: pstmt.setString(11,up.getTag());
28: pstmt.executeUpdate();
29: pstmt.close();
30: String query2 = " select /*+ index_desc("+up.getTableName()+
" "+up.getTableName()+"_indx) */ content from "+
up.getTableName()+" where seq = ? for update ";
for update를 이용해 CLOB column을 lock한다.
31: pstmt = conn.prepareStatement(query2);
32: pstmt.setInt(1,up.getSeq());
33: rs = pstmt.executeQuery();
34: if(rs.next()) {
35: CLOB clob = ((OracleResultSet)rs).getCLOB(1);
36: Writer writer = clob.getCharacterOutputStream();
37: Reader src = new CharArrayReader(up.getContent().toCharArray());
38: char[] buffer = new char[1024];
39: int read = 0;
40: while ( (read = src.read(buffer,0,1024)) != -1) {
41: writer.write(buffer, 0, read); // write clob.
42: }
43: src.close();
44: writer.close();
45: }
46: conn.commit();
47: conn.setAutoCommit(true);
CLOB column에 데이터을 저장하였다면 commit()을 실행시키고
conn.setAutoCommit(true)로 다시 설정한다.
48: }finally{
49: DBManager.close(rs,pstmt,conn);
50: }
51: }
Clob 불러오기
...
1: public BoardTable getViewData(String tableName,int seq) throws Exception
2: {
3: BoardTable bTable = new BoardTable();
4: String query = " select * from "+tableName+" where seq = ? ";
5: ResultSet rs = null;
6: PreparedS\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0tatement pstmt = null;
7: Connection conn = null;
8: try{
9: conn = DBManager.getConnection();
10: pstmt = conn.prepareStatement(query);
11: pstmt.setInt(1,seq);
12: rs = pstmt.executeQuery();
13: if (rs.next()) {
14: ...
15: ...
16: StringBuffer output = new StringBuffer();
17: Reader input = rs.getCharacterStream("content");
18: char[] buffer = new char[1024];
19: int byteRead;
20: while((byteRead=input.read(buffer,0,1024))!=-1){
21: output.append(buffer,0,byteRead);
22: }
23: input.close();
24: bTable.setContent(output.toString());
CLOB 데이터를 불러오기 위해서는 위에서처럼
rs.getCharacterStream("content")로 불러서 StringBuffer에 담아야 한다.
25: ...
26: ...
27: }
28: } finally {
29: DBManager.close(rs,pstmt,conn);
30: }
31: return bTable;
32: }
문서에 대하여
- 작성자 : 김정식 (oramaster _at_ naver.com)
- 작성일 : 2005-10-11
- 강좌 URL : http://www.oracleclub.com/lecture/1870
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 오라클클럽의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.