java servlet 读写mysql中存储的二进制文件

数据准备

CREATE TABLE stuinfo (
	id INT (11) NOT NULL auto_increment,
	NAME VARCHAR (10) DEFAULT NULL,
	content LONGTEXT,
	image LONGBLOB,
	PRIMARY KEY (id)
) ENGINE = INNODB;

servlet 及本地方法 

需要mysql驱动包及servlet-api.jar

访问地址：http://localhost:8080/xxx/download

package test;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.BufferedReader;
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.InputStreamReader;
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 javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet(name = "download", urlPatterns = { "/download" })
public class TestFile extends HttpServlet {
	
	//servlet 的例子
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		// 驱动程序名
        String driver = "com.mysql.jdbc.Driver";
        // URL指向要访问的数据库名scutcs
        String url = "jdbc:mysql://127.0.0.1:3306/antdb";
        // MySQL配置时的用户名
        String user = "root"; 
        // MySQL配置时的密码
        String password = "123456";
         
        //transaction dealing
        PreparedStatement pstam=null;
        String sql="select * from stuinfo where name=?";
        try {
        	// 加载驱动程序
            Class.forName(driver);
            // 连续数据库
            Connection conn = DriverManager.getConnection(url, user, password);
            pstam=conn.prepareStatement(sql);
            pstam.setString(1, "cjc");
            ResultSet reset=pstam.executeQuery();
            while(reset.next()){
                BufferedReader br=new BufferedReader(reset.getCharacterStream(3));
                String str=null;
                while((str=br.readLine())!=null){
                    System.out.println(str);
                }

                BufferedInputStream bis=new BufferedInputStream(reset.getBinaryStream(4));
                // 写明要下载的文件的大小  
                //resp.setContentLength((int) file.length());  
                resp.setHeader("Content-Disposition", "attachment;filename="  
                        + "ookkkk.jpg");// 设置在下载框默认显示的文件名  
                resp.setContentType("application/octet-stream");// 指明response的返回对象是文件流  
                // 读出文件到response  
                // 这里是先需要把要把文件内容先读到缓冲区  
                // 再把缓冲区的内容写到response的输出流供用户下载  
                //FileInputStream fileInputStream = new FileInputStream(file);  
                //BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream);  
                byte[] b = new byte[bis.available()];  
                bis.read(b);  
                OutputStream outputStream = resp.getOutputStream();  
                outputStream.write(b);  
                // 人走带门  
                bis.close();  
                outputStream.flush();  
                outputStream.close();  
                
                reset.close();
                pstam.close();
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e){
            e.printStackTrace();
        } catch (IOException e){
            e.printStackTrace();
        } catch (Exception e){
            e.printStackTrace();
        }
	}
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		doGet(req, resp);
	}
	public static void main(String[] args) throws Exception {
		readd();
	}
	
	//本地存储文件 图片到服务器的例子
	public static void writer() throws Exception {
		// 驱动程序名
        String driver = "com.mysql.jdbc.Driver";
        // URL指向要访问的数据库名scutcs
        String url = "jdbc:mysql://127.0.0.1:3306/antdb";
        // MySQL配置时的用户名
        String user = "root"; 
        // MySQL配置时的密码
        String password = "123456";
		// 加载驱动程序
        Class.forName(driver);
        // 连续数据库
        Connection conn = DriverManager.getConnection(url, user, password);
         
        //transaction dealing
        PreparedStatement pstam=null;
        String sql="insert into stuinfo(name,content,image) values(?,?,?);";
        try {
            pstam=conn.prepareStatement(sql);
            pstam.setString(1, "cjc");
             
            File file=new File("D:/安装前必看.txt");
            InputStream itxt=new FileInputStream(file);
            BufferedReader br=new BufferedReader(new InputStreamReader(itxt));
            pstam.setCharacterStream(2,br,(int)file.length());
             
            File file1=new File("D:/18285693_231156450339_2.jpg");
            InputStream isimg=new FileInputStream(file1);
            pstam.setBinaryStream(3, isimg, (int)file1.length());
             
            pstam.executeUpdate();
             
            br.close();
            itxt.close();
            isimg.close();
            pstam.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e){
            e.printStackTrace();
        } catch (IOException e){
            e.printStackTrace();
        }
    }
	
	//本地读取 文件图片的例子
	public static void readd() throws Exception {
		// 驱动程序名
        String driver = "com.mysql.jdbc.Driver";
        // URL指向要访问的数据库名scutcs
        String url = "jdbc:mysql://127.0.0.1:3306/antdb";
        // MySQL配置时的用户名
        String user = "root"; 
        // MySQL配置时的密码
        String password = "123456";
		// 加载驱动程序
        Class.forName(driver);
        // 连续数据库
        Connection conn = DriverManager.getConnection(url, user, password);
         
        //transaction dealing
        PreparedStatement pstam=null;
        String sql="select * from stuinfo where name=?;";
        try {
            pstam=conn.prepareStatement(sql);
            pstam.setString(1, "cjc");
            ResultSet reset=pstam.executeQuery();
            while(reset.next()){
                System.out.println("Read text document...");
                BufferedReader br=new BufferedReader(reset.getCharacterStream(3));
                String str=null;
                while((str=br.readLine())!=null){
                    System.out.println(str);
                }
                System.out.println("Read text document OK!");
                 
                System.out.println("Read image file...");
                BufferedOutputStream bos=new BufferedOutputStream(new FileOutputStream(new File("D://result.jpg")));
                byte[] buf=new byte[1024];
                BufferedInputStream bis=new BufferedInputStream(reset.getBinaryStream(4));
                int count=-1;
                while((count=bis.read(buf, 0, 1024))!=-1){
                    bos.write(buf, 0, count);
                }
                bos.flush();
                System.out.println("Read image file OK!");
                bos.close();
            }
            reset.close();
            pstam.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e){
            e.printStackTrace();
        } catch (IOException e){
            e.printStackTrace();
        }
    }
}