Jdbc 读写数据库Blob字段

向数据库表Blob字段写文件和从Blob字段读文件例子

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.RandomAccessFile;
import java.nio.channels.Channels;
import java.nio.channels.FileChannel;
import java.nio.channels.ReadableByteChannel;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class BlobReader {
	public static void main(String[] args) {
		BlobReader blobReader=new BlobReader();
		//blobReader.writeFile();
		//blobReader.readFile();
    }
	/**
	 * 写文件到Blob字段
	 */
	public void writeFile(){
		String filePath="d:/my/燃气表缴费流程.pdf";
		File file = new File(filePath);
        if (!file.exists()) {
            System.err.println("文件不存在：" + filePath);
            return;
        }
        
		String insql="insert into esb_driver(id,driverfile)values(?,?)";
		Connection conn=null;
        PreparedStatement ps=null;
        FileInputStream fis=null;
        try{
        	fis = new FileInputStream(file);// 获得文件流
        	conn = getConnection();
            ps = conn.prepareStatement(insql);
            ps.setString(1,"1");
            ps.setBinaryStream(2, fis, (int) file.length());// 将文件保存
            ps.executeUpdate();
            System.out.println("写文件成功");
        }catch(Exception e){
        	e.printStackTrace();
        }finally{
        	if(fis!=null){
        		try {
					fis.close();
				} catch (Exception e1) {
				}
        	}
        	colseConn(null,ps,conn);
        }
	}
	/**
	 * 读Blob到本地文件
	 */
	public void readFile(){
		String sql = "SELECT driverfile FROM esb_driver WHERE id = '1'";
        String filePath="d:/out.pdf";
                
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        
        InputStream is = null;
		ReadableByteChannel source = null;
		RandomAccessFile raf = null;
		FileChannel fileChannel = null;
        try{
        	conn = getConnection();
            ps = conn.prepareStatement(sql);
        	rs = ps.executeQuery();
        	if (rs.next()){
        		is =rs.getBinaryStream("driverfile");//
        		if(is!=null){
        			source = Channels.newChannel(is);
    				raf = new RandomAccessFile(filePath, "rw");
    				fileChannel = raf.getChannel();
    				fileChannel.transferFrom(source,0,is.available());
        		}
        	}
        }catch(Exception e){
        	e.printStackTrace();
        }finally{
        	closeFileConn(fileChannel,raf,source,is);
        	colseConn(rs,ps,conn);
        }
	}
	/**
	 * 创建连接
	 * @return
	 * @throws SQLException
	 */
	private Connection getConnection() throws SQLException{
		String url = "jdbc:mysql://localhost:3306/ry-vue";
        String username = "test";
        String password = "test";
		return DriverManager.getConnection(url, username, password);
	}
	/**
	 * 关闭文件流
	 * @param fileChannel
	 * @param raf
	 * @param source
	 * @param is
	 */
	private void closeFileConn(FileChannel fileChannel,RandomAccessFile raf,ReadableByteChannel source,InputStream is){
		if (fileChannel != null) {
			try {
				fileChannel.close();
			} catch (Exception e) {
			}
		}
		if (raf != null) {
			try {
				raf.close();
			} catch (Exception e) {
			}
		}
		if (source != null) {
			try {
				source.close();
			} catch (Exception e) {
			}
		}
		if (is != null) {
			try {
				is.close();
			} catch (Exception e) {
			}
		}
	}
	/**
	 * 关闭数据库连接
	 * @param rs
	 * @param ps
	 * @param conn
	 */
	private void colseConn(ResultSet rs,PreparedStatement ps,Connection conn){
		if(rs!=null){
    		try {
				rs.close();
			} catch (SQLException e) {
			}
    	}
    	if(ps!=null){
    		try {
				ps.close();
			} catch (SQLException e) {
			}
    	}
    	if(conn!=null){
    		try {
				conn.close();
			} catch (SQLException e) {
			}
    	}
	}
}