import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import oracle.sql.BLOB;

public class BlobUtil {

 private static BlobUtil bu;
 private String env;
 public static BlobUtil getInstance(String env) {
  bu = new BlobUtil();
  bu.env = env;
  return bu;
 }

 /**
  * <p>得到數據庫鏈接</p>
  * @return
  * @throws Exception
  */
 private Connection getConnection() throws Exception {
  String driver = "oracle.jdbc.driver.OracleDriver";
  Class.forName(driver);
  String env = this.env;
  Properties pro = new Properties();
  // 讀取classes 目錄下的配置文件  
  pro.load(new FileReader(Class.class.getResource("/config.properties").getFile()));
  String host = pro.getProperty(env + ".host");
  String database = pro.getProperty(env + ".database");
  String username = pro.getProperty(env + ".username");
  String password = pro.getProperty(env + ".password");
  String port = pro.getProperty(env + ".port");
  String url = "jdbc:oracle:thin:@" + host + ":" + port +":" + database;
  return DriverManager.getConnection(url, username, password);
 }

 /**
  * <p>傳入項目，任務，附件名稱，文件路徑，寫入數據庫</p>
  * @param projectId
  * @param taskId
  * @param fileName
  * @param file
  * @return 返回是否成功
  */
 public boolean write(String projectId, String taskId, String fileName, String file) {
  Connection conn = null;
  ResultSet rs = null;
  PreparedStatement ps = null;
  BLOB blob = null;
  boolean flag = false;
  try {
   conn = getConnection();
   conn.setAutoCommit(false);

   String sql = "INSERT INTO PS_ZP_PRJ_WBS_BLOB BLB(ZP_PRJ_ID, ZZ_SEQ_NUM, ZZ_FILE_NAME, ZZ_IMAGE_BLOB) VALUES(?, ? ,? ,empty_blob())";
   ps = conn.prepareStatement(sql);
   ps.setString(1, projectId);
   ps.setString(2, taskId);
   ps.setString(3, fileName);
   ps.executeUpdate();
   sql = "SELECT ZZ_IMAGE_BLOB FROM PS_ZP_PRJ_WBS_BLOB WHERE ZP_PRJ_ID = ? AND ZZ_SEQ_NUM = ? AND ZZ_FILE_NAME = ? FOR UPDATE";
   ps = conn.prepareStatement(sql);
   ps.setString(1, projectId);
   ps.setString(2, taskId);
   ps.setString(3, fileName);
   rs = ps.executeQuery();
   if(rs.next()) {
    blob = (BLOB) rs.getBlob(1);
   }
   InputStream in = new FileInputStream(file);
   OutputStream out = blob.setBinaryStream(1L);
   byte[] buffer = new byte[1024];
   int length = -1;
            while ((length = in.read(buffer)) != -1){
                out.write(buffer, 0, length);
            }
            in.close();
            out.close();
            conn.commit();
            conn.setAutoCommit(true);
            flag = true; 
  }
  catch(Exception e) {
   if(conn != null) {
    try {
     conn.rollback();
    } catch (SQLException e1) {
     e1.printStackTrace();
    }
   }
  }
  finally {
   try {
    rs.close();
    ps.close();
    conn.close();
   }
   catch(Exception e) {
    e.printStackTrace();
   }
  }
  return flag;
 }

 /**
  * <p>根據項目Id，任務Id，文件名讀取數據庫blob字段文件，寫入指定的文件路徑</p>
  * @param projectId
  * @param taskId
  * @param fileName
  * @param file
  * @return 返回是否成功
  */
 public boolean read(String projectId, String taskId, String fileName, String file) {
  Connection conn = null;
  ResultSet rs = null;
  PreparedStatement ps = null;
  BLOB blob = null;
  boolean flag = false;
  try {
   conn = getConnection();
   String sql = "SELECT ZZ_IMAGE_BLOB FROM PS_ZP_PRJ_WBS_BLOB WHERE ZP_PRJ_ID = ? AND ZZ_SEQ_NUM = ? AND ZZ_FILE_NAME = ?";
   ps = conn.prepareStatement(sql);
   ps.setString(1, projectId);
   ps.setString(2, taskId);
   ps.setString(3, fileName);
   rs = ps.executeQuery();
   if(rs.next()) {
    blob = (BLOB) rs.getBlob(1);
   }
   InputStream in = blob.getBinaryStream();
   byte[] buf = new byte[1024];
   int bytesIn = 0;
   FileOutputStream out = new FileOutputStream(file);
   while ((bytesIn = in.read(buf, 0, 1024)) != -1) {
    out.write(buf, 0, bytesIn);
   }
            in.close();
            out.close();
            flag = true;
  }
  catch(Exception e) {
   e.printStackTrace();
  }
  finally {
   try {
    rs.close();
    ps.close();
    conn.close();
   }
   catch(Exception e) {
    e.printStackTrace();
   }
  }
  return flag;
 }

 public static void main(String[] args) {
  BlobUtil bu = BlobUtil.getInstance("MSDEV");
  System.out.println(bu.write("CB", "001", "image1", "D://61e44b02jw1dw4xbp2zo6j.jpg"));
  System.out.println(bu.read("CB", "001", "image1", "D://2.jpg"));
 }

}