/*
* Oracle 10g, Clob, Blob example
* 이 프로그램을 테스트하려면 먼저 다음과 같은 구조의 테이블을 생성해야 한다.
*
* create table test_lob (c1 number, c2 clob, c3 blob);
*/
import java.sql.*;
import java.io.*;
public class ClobBlob {
static String TABLE_NAME = "test_lob";
public static void main ( String args [] ) throws SQLException {
// 아래의 주석을 한 라인씩 제거해 가면서 결과를 확인한다. 동시에 한 라인만 실행해야 한다.
//insertBlob();
//selectBlob();
//insertClob();
//selectClob();
//updateBlob();
//updateClob();
}
public static Connection getConnection() {
try {
DriverManager.registerDriver( new oracle.jdbc.OracleDriver() );
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL",
"scott","tiger" );
return conn;
}catch(Exception e){e.printStackTrace();}
return null;
}
public static void insertBlob() {
Connection conn = getConnection();
try{
byte[] blobDataWritten = null;
PreparedStatement pstmt = conn.prepareStatement("insert into " + TABLE_NAME + " values (?, null, ?)");
StringBuffer myStrBuf = new StringBuffer();
for (int i = 0; i <= 1024; i++) {
myStrBuf.append(String.valueOf(i));
}
String myString = myStrBuf.toString();
blobDataWritten = myString.getBytes();
System.out.println("Length of Blob to be inserted: " + blobDataWritten.length);
pstmt.setLong(1, 1);
ByteArrayInputStream bais = new ByteArrayInputStream(blobDataWritten);
pstmt.setBinaryStream(2, bais, blobDataWritten.length);
int insertstatus= pstmt.executeUpdate();
System.out.println("\nBlob successfully inserted. Num Rows Inserted: " + insertstatus + " \n");
pstmt.close();
bais.close();
conn.close();
} catch (Throwable t) {
t.printStackTrace();
}
}
public static void insertClob() {
Connection conn = getConnection();
try{
PreparedStatement pstmt = conn.prepareStatement("insert into " + TABLE_NAME + " values (?, ?, null)");
StringBuffer myStrBuf = new StringBuffer();
for (int i = 0; i <= 1024; i++) {
myStrBuf.append(String.valueOf(i));
}
String myString = myStrBuf.toString();
System.out.println("Length of Clob to be inserted: " + myString.length());
pstmt.setLong(1, 2);
StringReader sr = new StringReader(myString);
pstmt.setCharacterStream(2, sr, myString.length());
int insertstatus= pstmt.executeUpdate();
System.out.println("\nClob successfully inserted. Num Rows Inserted: " + insertstatus + " \n");
pstmt.close();
sr.close();
conn.close();
} catch (Throwable t) {t.printStackTrace(); }
}
public static void selectBlob() {
Connection conn = getConnection();
// Select for update so we can also write data.
String stmt = "select c1, c2, c3 from "+TABLE_NAME+" where c1=1";
try{
// Prepare the statement.
PreparedStatement pstmt = conn.prepareStatement( stmt );
// Execute.
ResultSet rs = pstmt.executeQuery();
// Output the data.
rs.next();
System.out.println( rs.getInt( 1 ) );
// Get the Blob contents.
Blob b_lob = rs.getBlob( 3 );
byte[] raw_buffer = new byte[(int)b_lob.length()];
// First via streaming as a single chunk.
InputStream raw_instream = b_lob.getBinaryStream();
raw_instream.read( raw_buffer );
String out_buffer = new String( raw_buffer );
System.out.println( out_buffer );
raw_instream.close();
// Now directly as a byte array.
raw_buffer = b_lob.getBytes( 1, (int)b_lob.length() );
out_buffer = new String( raw_buffer );
System.out.println( out_buffer );
rs.close();
pstmt.close();
conn.close();
}catch(Exception e){e.printStackTrace();}
}
public static void selectClob() {
Connection conn = getConnection();
String stmt = "select c1, c2, c3 from "+TABLE_NAME + " where c1=2";
try{
PreparedStatement pstmt = conn.prepareStatement( stmt );
ResultSet rs = pstmt.executeQuery();
rs.next();
System.out.println( rs.getInt( 1 ) );
Clob c_lob = rs.getClob( 2 );
char[] chr_buffer = new char[(int)c_lob.length()];
Reader chr_instream = c_lob.getCharacterStream();
chr_instream.read( chr_buffer );
System.out.println( chr_buffer );
chr_instream.close();
rs.close();
pstmt.close();
conn.close();
}catch(Exception e){e.printStackTrace();}
}
// Blob 컬럼에 저장된 데이터 전체를 지우고 새로 저장할 경우
public static void updateBlob() {
Connection conn = getConnection();
String stmt = "update test_lob set c3=? where c1=1";
try{
PreparedStatement pstmt = conn.prepareStatement( stmt );
byte[] newData = "This is updated String".getBytes();
ByteArrayInputStream bais = new ByteArrayInputStream(newData);
pstmt.setBinaryStream(1, bais, newData.length);
int insertstatus= pstmt.executeUpdate();
pstmt.close();
conn.close();
System.out.println("Blob 업데이트 성공");
selectBlob();
}catch(Exception e){e.printStackTrace();}
}
// Clob 컬럼에 저장된 데이터 전체를 지우고 새로 저장할 경우
public static void updateClob() {
Connection conn = getConnection();
String stmt = "update test_lob set c2=? where c1=2";
try{
// Prepare the statement.
PreparedStatement pstmt = conn.prepareStatement( stmt );
String newData = "This is new Clob String";
StringReader sr = new StringReader(newData);
pstmt.setCharacterStream(1, sr, newData.length());
// Execute.
int updateCounts = pstmt.executeUpdate();
System.out.println("변경된 레코드 수:"+updateCounts);
pstmt.close();
conn.close();
System.out.println("Clob 업데이트 성공");
selectClob();
}catch(Exception e){e.printStackTrace();}
}
}