How to deal with clob data type with Java using JDBC?

The purpose of this post is to know how to deal with oracle clob data type with Java using JDBC driver.

Below isn't full source code but only snippet of source.

JDBC4.0 이후에는 다른 Data Type과 동일한 방식으로 Query해 주면 되지만 그 이전 Version의 JDBC에서는 아래와 같은 방법을 이용해야 한다.

 

==================================== Start =================================

import java.io.BufferedWriter;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import oracle.sql.CLOB;

 

==================================== 중략 =================================

==================================== 중략 =================================

 

 /* Create Connection */
 Connection conn = null;
 String dburl    = "jdbc:oracle:thin:@127.0.0.1:1521:ORACLE";
 String user     = "scott";
 String password = "tiger";
 
 Class.forName("oracle.jdbc.driver.OracleDriver");
 conn = DriverManager.getConnection(dburl, user, password);
 
 conn.setAutoCommit(false);
 
/*************************************************************************************
 **************************** Select Clob data from table ******************************* 
 **************************************************************************************/

 PreparedStatement pstmt1 = null;
 ResultSet         rs1    = null;
 
 String sql1 = "select packet from b_gv_rcv_packet where cpl = :1 and input_address = :2 and productionseq = :3";
 
 pstmt1 = conn.prepareStatement(sql1);
 pstmt1.setString(1, "KB2");
 pstmt1.setString(2, "KL1LA69L");
 pstmt1.setString(3, "200906240000910"); 
 rs1 = pstmt1.executeQuery(); 
 
 CLOB   clob1   = null; 
 Reader reader1 = null;
 char[] buffer1 = null;
 
 StringBuffer sb1 = new StringBuffer();
 if(rs1.next()) {
     clob1   = (CLOB)rs1.getClob(1);
     reader1 = clob1.getCharacterStream();
     buffer1 = new char[1024];
     for(int byteRead=0; (byteRead=reader1.read(buffer1,0,1024))!=-1; ) {
         sb1.append(buffer1,0,byteRead);
     }
     reader1.close();
     System.out.println("Retrieved Packet:["+ sb1.toString() +"]");
 }
 
/*************************************************************************************
 ************************ Insert Clob data to specified table ****************************** 
 **************************************************************************************/  
 PreparedStatement pstmt2_1 = null;
 PreparedStatement pstmt2_2 = null;
 ResultSet         rs2      = null;
 
 String packet2 = "B45799KL1LA69LE9B1491092GT0000001 20080 RVE69 5TI6G281I92UA02A36A70AARABZAG4AJ7ALUAM1AM2AMEARD";
 String sql2_1  = "insert into b_gv_rcv_packet (cpl, input_address ,productionseq ,packet values (:1 ,:2 ,:3 ,empty_clob())";
 String sql2_2  = "select packet from b_gv_rcv_packet where cpl = :1 and input_address = :2 and productionseq = :3 for update";
 
 pstmt2_1 = conn.prepareStatement(sql2_1);
 pstmt2_1.setString( 1, "KB2"            );
 pstmt2_1.setString( 2, "KL1LA69L"       );
 pstmt2_1.setString( 3, "200907080000040");
 pstmt2_1.executeUpdate();
 
 pstmt2_2 = conn.prepareStatement(sql2_2);
 pstmt2_2.setString( 1, "KB2");
 pstmt2_2.setString( 2, "KL1LA69L");
 pstmt2_2.setString( 3, "200907080000040");
 
 rs2 = pstmt2_2.executeQuery();
 
 CLOB clob2      = null;
 BufferedWriter writer2 = null;
 if (rs2. next()) {
     // Cast to CLOB to use oracle
     clob2   = (CLOB)rs2.getClob(1);     
     //Get Writer from CLOB to write data to Clob column.
     writer2 = new BufferedWriter(clob2.getCharacterOutputStream());
     writer2.write(packet2);

     writer2.flush();
     writer2.close();
     conn.commit();
 }
 
 /*************************************************************************************
  ************************ Update Clob data to specified table ***************************** 
  **************************************************************************************/  
 PreparedStatement pstmt3 = null;
 ResultSet         rs3    = null;
 
 String packet3= "A study by the Harvard Medical School of Public Health confirmed that public health officials could convince....";
 String sql3   = "select packet from b_gv_rcv_packet where cpl = :1 and input_address = :2 and productionseq = :3 for update";
 
 pstmt3 = conn.prepareStatement(sql3);
 pstmt3.setString(1, "KB2");
 pstmt3.setString(2, "KL1LA69L");
 pstmt3.setString(3, "200907080000040");
 
 rs3 = pstmt3.executeQuery();
 
 CLOB clob3      = null;
 Reader reader3  = null;
 char[] buffer3  = null;
 BufferedWriter writer3 = null;

 StringBuffer sb3 = new StringBuffer();
 if (rs3. next()) {
     // Cast to CLOB to use oracle
     clob3   = (CLOB)rs3.getClob(1);
     reader3 = clob3.getCharacterStream();
     buffer3 = new char[1024];
     for(int byteRead=0;(byteRead=reader3.read(buffer3,0,1024))!=-1;) {

         sb3.append(buffer3,0,byteRead);
     }
     reader3.close();

     //Get Writer from CLOB to write data to Clob column.
     writer3 = new BufferedWriter(clob3.getCharacterOutputStream());
    
     sb3.append(packet3);
     System.out.println("sb:["+sb3.toString()+"]");
    
     writer3.write(sb3.toString());

     writer3.flush();
     writer3.close();
     conn.commit();
 }

==================================== End =================================