^[pRoUd]^ 작업실

CLOB형의 데이터들은 별도의 저장공간에 저장되기 때문에..
핸들이 좀 특이합니다.

INSERT INTO test(testcode, content) VALUES('test1', empty_clob());
또는
UPDATE test SET content = test_clob() WHERE testcode = 'test1';

이런식으로요..

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;
    }