select시에 데이터가 없을때를 대비하여 아래와 같이 if문에서 Reader가 null인지 체크
하여 주시면 될것입니다.
참고로 아래소스는 ClobUtil이라는 별도의 유틸리티 클래스를 이용하고 있는 모습니다.
(특별히 이렇게 분리 시키지 않아도 되지만 많이 사용되는만큼 분리시키시는게 편리
할것입니다.)
또한 아래 참조 소스는 clob data가 여러컬럼일때 작업하는 방법도 함께 예제로 넣었습니다.
많은 참고가 되었으면 합니다.
[ClobUtil클래스 (※전체소스는 첨부파일 참조)]
public class ClobUtil{
...
public static String getClobOutput(Reader input) throws IOException,Exception{
return getClobOutput(input, 4096);
}
public static String getClobOutput(Reader input, int buffer_size) throws IOException,Exception{
if(input != null){ // 이부분이 Reader가 null인지 체크하는 부분
try{
StringBuffer output = new StringBuffer();
char[] buffer = new char[buffer_size];
int byteRead;
while((byteRead=input.read(buffer, 0, buffer_size)) != -1){
output.append(buffer, 0, byteRead);
}
input.close();
return output.toString();
}catch(Exception e){// Trap SQL and IO errors
throw new Exception("getClobOutput() Fail !!"); // runtime 에러시 화면에 찍힘.
}
}else{
return "";
}
}
/*
다른방법1 (oracle 권장 (sample sorce중에서))
// Open a stream to read Clob data
Reader src = p_clob.getCharacterStream();
// Holds the Clob data when the Clob stream is being read
StringBuffer l_suggestions = new StringBuffer();
// Read from the Clob stream and write to the stringbuffer
int l_nchars = 0; // Number of characters read
char[] l_buffer = new char[10]; // Buffer holding characters being transferred
while ((l_nchars = l_clobStream.read(l_buffer)) != -1) // Read from Clob
l_suggestions.append(l_buffer,0,l_nchars); // Write to StringBuffer
l_clobStream.close(); // Close the Clob input stream
*/
...
}
[특정클래스에서]
import java.sql.*;
import java.io.*;
import java.util.*;
import oracle.sql.*; // CLOB 때문
import oracle.jdbc.driver.*; // OracleResultSet...때문
import common.db.DBConnHandler;
import common.utils.ClobUtil; // CLOB 때문
public class EntpDB {
// 여러개의 clob data를 insert하는 예제
public boolean insert(EntpRec entity) throws SQLException,IOException,Exception{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
StringBuffer sb = new StringBuffer(300);
boolean rtn = false;
try{
db = new DBConnHandler();
conn = db.getConnection();
conn.setAutoCommit(false);
sb.append(" INSERT INTO TEST_TABLE ");
sb.append(" (id,class_code,content_1,content_2,content_3,cre_user_id,cre_dt) ");
sb.append(" VALUES((Select NVL(MAX(id), 0)+1 id FROM TEST_TABLE),?,empty_clob(),empty_clob(),?,sysdate) ");
pstmt = conn.prepareStatement(sb.toString());
pstmt.setInt(1, entity.class_code);
pstmt.setString(2, entity.cre_user_id);
if(pstmt.executeUpdate()>0){
pstmt.close();
sb.setLength(0);
sb.append(" SELECT content_1,content_2,content_3 FROM TEST_TABLE ");
sb.append(" WHERE (id=(SELECT NVL(MAX(id), 0) id FROM TEST_TABLE)) ");
sb.append(" FOR UPDATE");
pstmt = conn.prepareStatement(sb.toString());
rs = pstmt.executeQuery();
while(rs.next()){
CLOB clob = ((OracleResultSet)rs).getCLOB(1);
Writer writer = clob.getCharacterOutputStream();
ClobUtil.writeClob(writer, entity.content_1);
clob = null;
writer = null;
clob = ((OracleResultSet)rs).getCLOB(2);
writer = clob.getCharacterOutputStream();
ClobUtil.writeClob(writer, entity.content_2);
clob = null;
writer = null;
clob = ((OracleResultSet)rs).getCLOB(3);
writer = clob.getCharacterOutputStream();
ClobUtil.writeClob(writer, entity.content_3);
}
/*
(예전에 직접처리하던방식-불편함)
...
pstmt.close();
sb.setLength(0);
sb.append("select bd_body_1,bd_body_2 from board_table where id=? for update");
pstmt = conn.prepareStatement(sb.toString());
pstmt.setInt(1, boardRec.getBd_seq);
rs = pstmt.executeQuery();
while(rs.next()){
CLOB clob = ((OracleResultSet)rs).getCLOB(1);
Writer writer = clob.getCharacterOutputStream();
Reader src = new CharArrayReader((boardRec.getBd_body_1()).toCharArray());
char[] buffer = new char[1024];
int read = 0;
while( (read = src.read(buffer,0,1024)) != -1){
writer.write(buffer, 0, read); // write clob.
}
src.close();
writer.close();
clob = null;
writer = null;
clob = ((OracleResultSet)rs).getCLOB(2);
writer = clob.getCharacterOutputStream();
Reader src = new CharArrayReader((boardRec.getBd_body_2()).toCharArray());
char[] buffer = new char[1024];
int read = 0;
while( (read = src.read(buffer,0,1024)) != -1){
writer.write(buffer, 0, read); // write clob.
}
src.close();
writer.close();
...
}
*/
conn.commit();
rtn = true;
}else{
logs.logIt("error","insert 처리(CLOB의 내용 제외)를 하지 못하고 db 에러남.\n"+
" > entity : "+entity.toString()+"\n"+
" > query : "+sb.toString()+"\n");
}
}catch(SQLException e){
logs.logIt("error","insert 처리를 하지 못하고 에러남.\n"+
" > entity : "+entity.toString()+"\n"+
" > query : "+sb.toString()+"\n", e);
if(conn != null) conn.rollback();
throw e;
}finally{
conn.setAutoCommit(true);
if(rs != null) rs.close();
if(pstmt != null) pstmt.close();
db.release();
}
return rtn;
}
// 여러개의 clob data를 select예제
public EntpRec select(int id) throws SQLException,IOException,Exception{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
StringBuffer sb = new StringBuffer(1000);
EntpRec entity = null;
try{
db = new DBConnHandler();
conn = db.getConnection();
sb.append(" SELECT id,class_code,cre_user_id,upt_user_id,to_char(cre_dt,'YYYY/MM/DD HH24:MI') cre_dt, ");
sb.append(" to_char(upt_dt,'YYYY/MM/DD HH24:MI') upt_dt ");
sb.append(" FROM TEST_TABLE ");
sb.append(" WHERE (entp_id=? "+where+")");
pstmt = conn.prepareStatement(sb.toString());
pstmt.setInt(1, entp_id);
rs = pstmt.executeQuery();
if(rs.next()){
entity = new EntpRec();
entity.entp_id = rs.getInt("entp_id");
entity.class_code = rs.getInt("class_code");
entity.content_1 = ClobUtil.getClobOutput(rs.getCharacterStream("content_1"));
entity.content_2 = ClobUtil.getClobOutput(rs.getCharacterStream("content_2"));
entity.content_3 = ClobUtil.getClobOutput(rs.getCharacterStream("content_3"));
entity.cre_user_id = rs.getString("cre_user_id");
entity.upt_user_id = rs.getString("upt_user_id");
entity.cre_dt = rs.getString("cre_dt");
entity.upt_dt = rs.getString("upt_dt");
Utility.fixNull(entity);
}
}catch(SQLException e){
logs.logIt("error","[특정 pk에 따른 한 레코드 select하지 못하고 에러남.\n"+
" > id : "+id+"\n"+
" > query : "+sb.toString()+"\n", e);
}finally{
if(rs != null) rs.close();
if(pstmt != null) pstmt.close();
db.release();
}
return entity;
}
// 여러개의 clob data를 update하는 예제
public boolean update(EntpRec entity) throws SQLException,IOException,Exception{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
StringBuffer sb = new StringBuffer(1000);
boolean rtn = false;
try{
db = new DBConnHandler();
conn = db.getConnection();
conn.setAutoCommit(false);
sb.append(" UPDATE TEST_TABLE");
sb.append(" SET class_code=?,content_1=empty_clob(),content_2=empty_clob(),content_3=empty_clob(), ");
sb.append(" upt_user_id=?,upt_dt=sysdate ");
sb.append(" WHERE (id=?)");
pstmt = conn.prepareStatement(sb.toString());
pstmt.setInt(1, entity.class_code);
pstmt.setString(51, entity.upt_user_id);
pstmt.setInt(52, entity.id);
if(pstmt.executeUpdate()>0){
pstmt.close();
sb.setLength(0);
sb.append(" SELECT content_1,content_2,content_3 FROM TEST_TABLE ");
sb.append(" WHERE (id=?)");
sb.append(" FOR UPDATE");
pstmt = conn.prepareStatement(sb.toString());
pstmt.setInt(1, entity.id);
rs = pstmt.executeQuery();
while(rs.next()){
CLOB clob = ((OracleResultSet)rs).getCLOB(1);
Writer writer = clob.getCharacterOutputStream();
ClobUtil.writeClob(writer, entity.content_1);
clob = null;
writer = null;
clob = ((OracleResultSet)rs).getCLOB(2);
writer = clob.getCharacterOutputStream();
ClobUtil.writeClob(writer, entity.content_2);
clob = null;
writer = null;
clob = ((OracleResultSet)rs).getCLOB(3);
writer = clob.getCharacterOutputStream();
ClobUtil.writeClob(writer, entity.content_3);
}
conn.commit();
rtn = true;
}else{
logs.logIt("error","[update 처리(CLOB의 내용 제외)를 하지 못하고 db 에러남.\n"+
" > entity : "+entity.toString()+"\n"+
" > query : "+sb.toString()+"\n");
}
}catch(SQLException e){
logs.logIt("error","[update 처리를 하지 못하고 에러남.\n"+
" > entity : "+entity.toString()+"\n"+
" > query : "+sb.toString()+"\n", e);
if(conn != null) conn.rollback();
throw e;
}finally{
conn.setAutoCommit(true);
if(rs != null) rs.close();
if(pstmt != null) pstmt.close();
db.release();
}
return rtn;
}
|
invalid-file