Blob

package com.mj.services;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
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.ArrayList;
import java.util.List;

import oracle.sql.BLOB;

public class BlobTest {
	
	private static Connection conn=null;	
	private PreparedStatement pstm=null;	
	private ResultSet rst=null;
	
	static{
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:WMG", "hibernate", "hibernate");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 上传数据到数据库
	 * @param fileid
	 * @param filename
	 * @param filepath
	 * @return
	 */
	public int insBlob(String fileid, String filename, String filepath){
		int result=0;
		String sql="INSERT INTO blobtest VALUES(?,?,empty_blob())";
		BLOB blob=null;
		try {
			conn.setAutoCommit(false);
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, fileid);
			pstm.setString(2, filename);
			pstm.executeUpdate();
			pstm.close();
			
			sql="SELECT file_ FROM blobtest WHERE fileid=? FOR UPDATE";
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, fileid);
			rst=pstm.executeQuery();
			if(rst.next()){
				blob=(BLOB)rst.getBlob(1);
			}
			
			OutputStream out=blob.getBinaryOutputStream();
			File file=new File(filepath);
			InputStream fin=new FileInputStream(file);
			byte[] buffer=new byte[1024*8];
			int length=0;
			while((length=fin.read(buffer, 0, 1024*7))!=-1){
				out.write(buffer, 0, length);
				//必须的啊
				out.flush();
			}
			pstm.close();
			
			sql="UPDATE blobtest SET file_=?";
			pstm=conn.prepareStatement(sql);
			pstm.setBlob(1, blob);
			result=pstm.executeUpdate();
			//一定不要忘记手动提交事务，关闭各种资源
			conn.commit();
			pstm.close();
			out.close();
			fin.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	/**
	 * 从数据库下载数据
	 * @param filename
	 * @return int 0代表下在失败，1代表下载成功
	 */
	public int downBlob(String filename){
		BLOB blob=null;
		String sql="SELECT file_ FROM blobtest WHERE filename=?";
		//设立一个标志位，来监控整个运行流程
		int result=0;
		try {
			conn.setAutoCommit(true);
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, filename);
			rst=pstm.executeQuery();
			while(rst.next()){
				blob=(BLOB)rst.getBlob(1);
				result=1;
			}
			InputStream in=blob.getBinaryStream();
			File file=new File("c:\\download_mj\\"+filename);
			OutputStream out=new FileOutputStream(file);
			byte[] buffer=new byte[1024*8];
			int length=0;
			//刚开始忘了加length结果读的是空的，还让我郁闷了很长时间，太粗心了还是。。。
			while((length=in.read(buffer, 0, 1024*7))!=-1){
				result=1;
				out.write(buffer, 0, length);
				out.flush();
			}
			pstm.close();
			out.close();
			in.close();
			
		} catch (SQLException e) {
			result=0;
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			result=0;
			e.printStackTrace();
		} catch (IOException e) {
			result=0;
			e.printStackTrace();
		}
		return result;
	}
	
	/**
	 * 查询文件名
	 * @return
	 */
	public List findNames(){
		List list=new ArrayList();
		String sql="SELECT filename FROM blobtest";
		try {
			pstm=conn.prepareStatement(sql);
			rst=pstm.executeQuery();
			while(rst.next()){
				list.add(rst.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public boolean checkFileid(String fileid){
		boolean suc=true;
		String sql="SELECT * FROM  blobtest WHERE fileid=?";
		try {
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, fileid);
			rst=pstm.executeQuery();
			if(rst.next()){
				suc=false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return suc;
	}
	public static void main(String[] args){
		new BlobTest().downBlob("web.xml");
	}
}