Oracle 에서 Java를 사용하여 Blob형을 파일로 저장하기

ojdbc7.jar

3.53MB

Oracle을 사용하다가 보면 Blob 형으로 들어가 있는 바이너리파일을 실제 물리 파일로 저장해야 되는 경우가 있다.
특히, 프로젝트를 기존 Legacy 시스템을 Migration 할 때 종종 사용 되어 졌다 ㅜㅜ
샘플 코드를 참조로 하면 쉽게 사용방법을 알 수 있을 것이다. 
즐거운 프로그램이 되시길~~

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.OracleResultSet;

public class BlobDownloadApplication {

	public static void main(String[] args) {
		BlobDownloadApplication bda = new BlobDownloadApplication();
		Connection conn = bda.connect("192.168.186.129", "1521", "KJPDM", "X3DMIGRATION", "X3DMIGRATION");

		String insertQuery = "INSERT INTO TEST_BLOB(file_name, file_data) values (?, EMPTY_BLOB())";
		String selectQuery = "SELECT file_data FROM TEST_BLOB WHERE file_name=? FOR UPDATE";
		bda.insertBlob(conn, "test.png", "c:\\temp\\test.png", insertQuery, selectQuery);

		String selectDownloadQuery = "SELECT file_data FROM TEST_BLOB WHERE file_name=?";
		bda.downloadBlob(conn, "test.png", "c:\\temp\\test2.png", selectDownloadQuery);

		bda.disconnect(conn);
	}

	private void downloadBlob(Connection conn, String fileName, String downloadFilepath, String selectQuery) {

		InputStream is = null;
		FileOutputStream os = null;

		try {
			conn.setAutoCommit(false);
			PreparedStatement pstmt = conn.prepareStatement(selectQuery);
			pstmt.setString(1, fileName);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				Blob blob = rs.getBlob(1);
				is = blob.getBinaryStream();
				os = new FileOutputStream(downloadFilepath);
				byte[] buf = new byte[1024];
				int len = -1;
				while ((len = is.read(buf)) != -1) {
					os.write(buf, 0, len);
				}
				
				conn.commit();
			}
			else {
				conn.rollback();
			}
		}
		catch(Exception e) {
			try {
				conn.rollback();
			}
			catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally{
			if(is != null) {
				try {
					is.close();
				}
				catch (IOException e) {
					e.printStackTrace();
				}
			}

			if(os != null) {
				try {
					os.close();
				}
				catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	private void insertBlob(Connection conn, String fileName, String filePath, String insertQuery, String selectQuery) {
		File file = null;
		FileInputStream is = null;
		OutputStream os = null;

		try {
			conn.setAutoCommit(false);

//			String insertQuery = "INSERT INTO TEST_BLOB(file_name, file_data) values (?, EMPTY_BLOB())";
//			String selectQuery = "SELECT file_data FROM TEST_BLOB WHERE file_name=? FOR UPDATE";

			PreparedStatement pstmt = conn.prepareStatement(insertQuery);
			pstmt.setString(1, fileName);
			pstmt.executeUpdate();

			PreparedStatement pstmt2 = conn.prepareStatement(selectQuery);
			pstmt2.setString(1, fileName);
			OracleResultSet rs = (OracleResultSet)pstmt2.executeQuery();
			if(rs.next()) {
				Blob tmpBlob = ((OracleResultSet)rs).getBlob(1);

				file = new File(filePath);
				is = new FileInputStream(file);
				os = tmpBlob.setBinaryStream(1);

				byte[] buffer = new byte[1024];
				int length = -1;
				while((length = is.read(buffer)) != -1) {
					os.write(buffer, 0, length);
				}

				os.close();
				os = null;
				
				conn.commit();
			}
			else {
				conn.rollback();
			}
		}
		catch(Exception e) {
			try {
				conn.rollback();
			}
			catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally{
			if(is != null) {
				try {
					is.close();
				}
				catch (IOException e) {
					e.printStackTrace();
				}
			}

			if(os != null) {
				try {
					os.close();
				}
				catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	private Connection connect(String ip, String port, String sid, String id, String password) {
		Connection conn = null;

		try {
			String host = "jdbc:oracle:thin:@"+ip+":"+port+"/"+sid;
			String userId = id;
			String userPassword = password;

			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(host, userId, userPassword);
		}
		catch (Exception e) {
			e.printStackTrace();
		}

		return conn;
	}
	
	private void disconnect(Connection conn) {
		try {
			if(conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}