Java를 이용한 CLOB, BLOB 조작법

Java를 이용한 CLOB, BLOB 조작법

출처 : http://lambert.egloos.com/3069062

1) import 시켜야할 API

① oracle.sql.BLOB

② oracle.sql.CLOB

③ oracle.jdbc.driver.OracleResultSet

2) CLOB

① DB에 CLOB 데이터형 쓰기

       // UPDATE 또는 INSERT 명령으로 DB 에 공간 확보
     String query = "UPDATE TABLE SET CLOB_DATA = EMPTY_CLOB() " ;
     stmt.executeUpdate(query);

     // 그런 다음 다시 요놈을 다시 SELECT
     query = "SELECT CLOB_DATA  FROM TABLE WHERE ~ " ;   

     stmt = dbConn.createStatement();
     rs = stmt.executeQuery(query);

     if(rs.next()) {
          CLOB clob = null;
          Writer writer = null;
          Reader src = null;
          char[] buffer = null;
          int read = 0;  

          clob = ((OracleResultSet)rs).getCLOB(1);        
          writer = clob.getCharacterOutputStream();

          // str -> DB에 넣을 내용
          src = new CharArrayReader(str.toCharArray());
          buffer = new char[1024];
          read = 0;
          while ( (read = src.read(buffer,0,1024)) != -1) {
               writer.write(buffer, 0, read); // write clob.
          }
          src.close();        
          writer.close();
     }

     dbConn.commit();
     dbConn.setAutoCommit(true);

 ② DB에서 CLOB 데이터형 읽기

      // SELECT
     String query = "SELECT CLOB_DATA  FROM TABLE WHERE ~ " ;   

     stmt = dbConn.createStatement();
     rs = stmt.executeQuery(query);

     if(rs.next()) {

          StringBuffer output = new StringBuffer();
          Reader input = rs.getCharacterStream("CLOB_DATA");
          char[] buffer = new char[1024];
          int byteRead = 0;
          while((byteRead=input.read(buffer,0,1024))!=-1){
               output.append(buffer,0,byteRead);
          }
         

          // contents -> CLOB 데이터가 저장될 String
          String contents = output.toString();

     }

     dbConn.commit();
     dbConn.setAutoCommit(true);

3) BLOB

① DB에 BLOB 데이터형 쓰기

      // UPDATE 또는 INSERT 명령으로 DB 에 공간 확보
     String query = "UPDATE TABLE SET BLOB_DATA = EMPTY_BLOB() " ;
     stmt.executeUpdate(query);

     // 그런 다음 다시 요놈을 다시 SELECT
     query = "SELECT BLOB_DATA  FROM TABLE WHERE ~ " ;   

     stmt = dbConn.createStatement();
     rs = stmt.executeQuery(query);

     if(rs.next()) {

          BLOB blob = null;
          BufferedOutputStream out = null;
          BufferedInputStream in = null;
          byte[] buf = null;
          int bytesRead= 0;  

          blob = ((OracleResultSet)rs).getBLOB(1);
          out = new BufferedOutputStream(blob.getBinaryOutputStream());

          // str -> DB에 넣을 내용
          in = new BufferedInputStream(new StringBufferInputStream(str));
          int nFileSize = (int)str.length();
          buf = new byte[nFileSize];
         
          while ((bytesRead = in.read(buf)) != -1){
               out.write(buf, 0, bytesRead);

          }

          in.close();
          out.close();
     }

     dbConn.commit();
     dbConn.setAutoCommit(true);

 ② DB에서 BLOB 데이터형 읽기

      // SELECT
     String query = "SELECT CLOB_DATA  FROM TABLE WHERE ~ " ;   

     stmt = dbConn.createStatement();
     rs = stmt.executeQuery(query);

     if(rs.next()) {

          BLOB blob = ((OracleResultSet)rs).getBLOB(1);

          BufferedInputStream in = new BufferedInputStream(blob.getBinaryStream());
          int nFileSize = (int)blob.length();
          byte[] buf = new byte [nFileSize];   
          int nReadSize = in.read(buf, 0, nFileSize);
          in.close();

           // contents -> BLOB 데이터가 저장될 String

          String contents = new String(buf);
     }

     dbConn.commit();
     dbConn.setAutoCommit(true);