/*
 * @(#)ClobTest.java ver.1.0.0 2002/09/17
 * @Author Secheong Park
*/

/* 사용할 테이블

CREATE TABLE TEST_CLOB (
  SEQ_NUM   VARCHAR2 (1)  NOT NULL,
  CLOB_COL  CLOB
)

*/

import java.sql.*;
import java.io.*;

/** Class for only management connecting to database */
public class ClobTest{

 Connection con;
 Statement stmt;
 ResultSet rset;
 Writer writer;
 PreparedStatement pstmt;
 ResultSetMetaData rsmd;

 /** Default constructor */
 public ClobTest(){
  try{
   Class.forName("oracle.jdbc.driver.OracleDriver");
  }catch(ClassNotFoundException cfe){cfe.printStackTrace();}
 }
 
 /** Method for connectiong to database */
 public void connect() {
  String url="jdbc:oracle:thin:@localhost:1521:ORCL";
  String id="scott";
  String pw="tiger";
  try{
   con=DriverManager.getConnection(url, id, pw);
   con.setAutoCommit(false);
   stmt=con.createStatement();
  }catch(SQLException sqle){sqle.printStackTrace();}
 }

 /** Method for disconnectiong from database */
 public void disconnect(String flag){
  char choiceChar;
  try{
   if(flag=="query")
    choiceChar='Q';   //Case of query statement
   else if(flag=="update")
    choiceChar='U';   //Case of update statement
   else
    throw new SQLException("Disconnection Error");   
   switch(choiceChar){
    case 'Q':    
     rset.close();
    case 'U':     
     stmt.close();
     con.close();
     break;
   }    
  }catch(SQLException sqle){sqle.printStackTrace();}
 }

 //오라클함수의 empty_clob()을 사용하는 방법
 public void insertClob()throws SQLException,IOException{
  String insString = "Insert String";  
  stmt.executeUpdate("insert into test_clob (seq_num, clob_col) values('1', empty_clob())");
  rset = stmt.executeQuery("SELECT clob_col FROM test_clob WHERE seq_num = '1' for update");
  rset.next();
  oracle.sql.CLOB clob = (oracle.sql.CLOB)(rset.getClob(1));         
  writer = clob.getCharacterOutputStream();   
  writer.write(insString);  
  if(writer!=null) writer.close();  
 }
 
 //PreparedStatement를 이용하는 방법
 public void insertClob2()throws SQLException{
  String insString = "Insert2 String";
  String sqlString = "insert into test_clob (seq_num, clob_col) values('2', ?)";
  /*
   ? -->는 PreparedStatement에서 인서트할 컬럼값 대신 사용한다. 여러개일 경우 index가 1,2,3,4......
   위의 경우는 1개
  */
  pstmt = con.prepareStatement(sqlString); 
  pstmt.setString( 1 , insString);   
 // pstmt.setBytes( 1 , insString.getBytes());   
  pstmt.executeUpdate();
  pstmt.close();
 } 

 //오라클함수의 empty_clob()을 사용하는 방법
 public void updateClob()throws SQLException,IOException{
  String uptString = "Update String";
  stmt.executeUpdate("update test_clob set clob_col = empty_clob() where seq_num = '1' ");     
  rset = stmt.executeQuery("SELECT clob_col FROM test_clob WHERE seq_num = '1' for update");  
  rset.next();
  oracle.sql.CLOB clob = (oracle.sql.CLOB)(rset.getClob(1));      
  writer =  clob.getCharacterOutputStream();   
  writer.write(uptString);
  if(writer!=null) writer.close();
 }

 //PreparedStatement를 이용하는 방법
 public void updateClob2()throws SQLException,IOException{
  String uptString = "Update2 String";
  String sqlString = "update test_clob set clob_col = ? where seq_num = '2'" ;
  pstmt = con.prepareStatement(sqlString);  
  pstmt.setString( 1 , uptString);
  pstmt.executeUpdate();
  pstmt.close();
 }
 
 //CLOB SELECT
 public void selectClob(String sql)throws SQLException,IOException{
  rset = stmt.executeQuery(sql);
  rsmd= rset.getMetaData();
  int count = rsmd.getColumnCount();
  for (int i=1;i<=count;i++) {
   System.out.print(rsmd.getColumnLabel(i)+"\t\t");  
  }
  System.out.println("\n--------------------");
  while(rset.next()){
   System.out.print(rset.getString(1)+"\t\t\t"+getClobData(rset.getClob(2)));     
   System.out.println();   
  }
 }
 
 //삭제
 public void deleteAll()throws SQLException{
  stmt.executeUpdate("delete from TEST_CLOB");
 }

 //CLOB 컬럼데이타 값을 스트링으로 변환
 public String getClobData(Clob clob)throws SQLException,IOException{

   Reader char_stream = clob.getCharacterStream();
   char [] char_array = new char [4096];
   String str="";
   int total_bytes=0;
   int read_bytes=0;
   while( (read_bytes = char_stream.read(char_array, 0, 4096 )) != -1 ) {
    total_bytes += read_bytes;
    str += new String(char_array,0,read_bytes);
   }
   return str;   
 }

 public static void main(String[] agrs)throws SQLException,IOException{ 
  ClobTest ct = new ClobTest();
  ct.connect();
  ct.deleteAll();     //삭제
  ct.insertClob();    //삽입1
  ct.insertClob2();    //삽입2
  System.out.println("<삽입된 후>");
  ct.selectClob("select * from test_clob");
  System.out.println("<조건검색 후>");
  ct.selectClob("select * from test_clob where dbms_lob.instr(clob_col, '2')>0"); //CLOB 검색 오라클함수 이용
  ct.updateClob();    //수정1
  ct.updateClob2();    //수정2
  System.out.println("<수정된 후>");
  ct.selectClob("select * from test_clob");
  /*
   where dbms_lob.instr(clob컬럼명, '찾을문자열')>0  조건절에 이와 같이 준다.
  */
  ct.stmt.executeUpdate("commit");
  ct.disconnect("update");
 }
 
}