package book.database;

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

/**
 * 存取BLOB數(shù)據(jù)到數(shù)據(jù)庫。
 */
public class BlobData {
	/**
	 * 寫B(tài)lob數(shù)據(jù)到數(shù)據(jù)庫
	 * @param con	
	 * @param infilePath	保存到數(shù)據(jù)庫的文件	
	 */
	public static void writeBlob(Connection con, String infilePath){
		FileInputStream fis = null;
		PreparedStatement psm = null;
		try {
			//打開輸入文件
			File file = new File(infilePath);
			fis = new FileInputStream(file);
			psm = con.prepareStatement(
					"insert into student_photo(name, filename, filedata)"
					+ " values('mary', ?, ?)");
			//第一個參數(shù)為文件名
			psm.setString(1, file.getName());
			//第二個參數(shù)為文件的二進制流
			psm.setBinaryStream(2, fis, fis.available());
			// 執(zhí)行
			psm.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//關閉打開的對像
			if (fis != null){
				try {
					fis.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			OperateDB.closeStatement(psm);
		}
	}
	/**
	 * 從數(shù)據(jù)庫中讀Blob數(shù)據(jù)
	 * @param con
	 * @param srcFileName	要讀取的文件名
	 * @param outFilePath	保存到本地的文件
	 */
	public static void readBlob(Connection con, String srcFileName, 
			String outFilePath){
        Statement sm = null;
        FileOutputStream fos = null;
        InputStream is = null;
		try {
	        sm = con.createStatement();
	        ResultSet rs = sm.executeQuery(
	        		"SELECT * FROM student_photo where name = 'mary'"
	        		+ " and filename = '" + srcFileName + "'");
	        if (rs.next()) {
	            // 從列中讀取Blob數(shù)據(jù)
	            Blob blob = rs.getBlob("filedata");
	    
	            // 獲取Blob的字節(jié)數(shù)，是long類型，表示字節(jié)數(shù)很多。
	            long blobLength = blob.length();
	            // 可以通過blob的getBytes方法從Blob對象中提取字節(jié)數(shù)據(jù)
	            // 也通過Blob對象的二進制輸入流讀數(shù)據(jù)
	            is = blob.getBinaryStream();
				byte[] buffer = new byte[4096];
				int size;
				File file = new File(outFilePath);
				try {
					fos = new FileOutputStream(file);
					while ((size = is.read(buffer)) != -1){
						fos.write(buffer, 0, size);
					}
				} catch (IOException eee) {
					eee.printStackTrace();
				}
	        }
	        rs.close();
	    } catch (SQLException e) {
	    	e.printStackTrace();
	    } finally {
	    	OperateDB.closeStatement(sm);
			if (is != null){
				try {
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			if (fos != null){
				try {
					fos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
	    }
	}

	public static void main(String[] args) throws ClassNotFoundException,
			SQLException {
		String dbName = "studentdb";
		String userName = "test";
		String password = "test";
		
		Connection con = null;
		try {
			// 獲得數(shù)據(jù)庫連接
			con = DBConnector.getMySQLConnection(null, null, null, dbName,
					userName, password);
			// 寫B(tài)lob數(shù)據(jù)
			BlobData.writeBlob(con, "C:/temp/mary_photo.jpg");
			// 讀Blob數(shù)據(jù)
			BlobData.readBlob(con, "mary_photo.jpg", "C:/temp/mary_photo_db.jpg");
		} catch (ClassNotFoundException e1) {
			throw e1;
		} catch (SQLException e2) {
			throw e2;
		} finally {
			// 關閉數(shù)據(jù)庫連接
			OperateDB.closeConnection(con);
		}
	}
}