Oracle BLOB

Oracle에 접속해서 BLOB 타입의 데이터를 가져와 파일로 저장한다.

package database;

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

import oracle.sql.*;
/**
* Database:Oracle
* IP: 127.0.0.1:1521
* SID: o10g
* USER: scott
* PASS: tiger
* @author hikasiru
*
*/
public class OracleTest {

public static void main(String[] args)
throws SQLException,
ClassNotFoundException,
IOException {
Connection con;
Statement stmt = null;
ResultSet rst  = null;

// 드라이버를 가져옴
Class.forName("oracle.jdbc.driver.OracleDriver");
// DB에 접속을 시도한다.
con = DriverManager.
getConnection("jdbc:oracle:thin:scott/tiger@127.0.0.1:1521:o10g");
con.setAutoCommit( false );
stmt = con.createStatement();

// Table을 삭제하고 생성한다.
//  stmt.executeQuery("DROP TABLE obj_table");
//  stmt.executeQuery("CREATE TABLE obj_table ( " +
//  "no number," +
//  "b blob default empty_blob(), " +
//  "c clob default empty_clob())");

//  삽입
//  rst = stmt.executeQuery("INSERT INTO OBJ_TABLE VALUES (1, empty_blob(), 'clob column data!!!')");
//  rst = stmt.executeQuery("INSERT INTO OBJ_TABLE VALUES (2, empty_blob(), 'clob column data!!!')");
//  rst = stmt.executeQuery("INSERT INTO OBJ_TABLE VALUES (3, empty_blob(), 'clob column data!!!')");
//  rst = stmt.executeQuery("INSERT INTO OBJ_TABLE VALUES (4, empty_blob(), 'clob column data!!!')");

//  검색
rst = stmt.executeQuery("SELECT * FROM OBJ_TABLE");
while ( rst.next()) {
  System.out.print("No: " + rst.getInt(1));
  System.out.print("\tBlob: " + rst.getBlob(2));
  System.out.println("\tClob: " + rst.getClob(3));
}

//  이미지 업로드
//  rst = stmt.executeQuery("SELECT b FROM OBJ_TABLE where no = 2 for update");
//  while (rst.next()) {
//  BLOB blob = (BLOB) rst.getBlob(1);
//  File file = new File("D:\\blue.jpg");
//  long fileLength = (long)file.length();
//  System.out.println("File size: " + fileLength + " bytes");
//  
//  FileInputStream instream = new FileInputStream(file);
//  OutputStream outstream = blob.getBinaryOutputStream();
//  
//  int size = blob.getBufferSize();
//  System.out.println("BufferSize: " + size + " bytes (#)\n");
//  byte[] buffer = new byte[size];
//  int length = -1;
//  int tlength = 0;
//  while ((length = instream.read(buffer)) != -1) {
//  outstream.write(buffer, 0, length);
//  System.out.print("*");
//  tlength += length;
//  }
//  System.out.println("\ntlength: " + tlength);
//  instream.close();
//  outstream.close();
//  
//  System.out.println("\nUpdate done");
//  
//  }

//  이미지 다운로드
rst = stmt.executeQuery("SELECT no, b, c FROM obj_table where no = 1");
while(rst.next()){
  BLOB blob = (BLOB) rst.getBlob(2);
 
  InputStream instream = blob.getBinaryStream();
  String image_name = System.currentTimeMillis() + ".jpg";
  FileOutputStream outstream = new FileOutputStream("D:\\"  
    + image_name);
 
  int size = blob.getBufferSize();
  byte[] buffer = new byte[size];
  int length = -1;  
 
  while((length = instream.read(buffer)) != -1) {
   outstream.write(buffer, 0, length);
  }
 
  outstream.close();
  instream.close();
 
}

System.out.println("\nDownload done");  
rst.close();


//  접속 종료
stmt.close();
con.commit();
con.close();
}
}