Java实现MySQL图片存取

Reference

Java实现对Mysql的图片存取操作
java 字节流读取图片，字符流读取，二进制读取
mysql BLOB字段类型用法介绍

Notes

• Java对图片的读写就跟其它文件一样的，但要用字节流而不用字符流
• MySQL中各种blob的大小限制

TinyBlob Blob MediumBlob LongBlob 225B 65KB 16MB 4GB

MySQL Table

• 表名：receipt

Rid Rpicture Rtype varchar(20) blob varchar(20)

CREATE TABLE receipt (    Rid VARCHAR(20) PRIMARY KEY,    Rpicture BLOB NOT NULL,    Rtype VARCHAR(20) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Code

import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.InputStream;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class PhotoIO {//--- Parameter    private static final String driver = "com.mysql.jdbc.Driver";    private static final String URL = "jdbc:mysql://<IP_ADDRESS>:<PORT>/<DB_NAME>";    private static final String user = "<USER_NAME>";    private static final String psw = "<USER_PASSWORD>";//--- Private Member    private static Connection con = null;    private static PreparedStatement pst = null;    private static ResultSet rs = null;//--- Auto-Run    static {        try {            // 加载驱动            Class.forName(driver);            // 连接数据库            con = DriverManager.getConnection(URL, user, psw);        } catch(ClassNotFoundException e) {            e.printStackTrace();        } catch(SQLException e) {            e.printStackTrace();        }    }//--- Public Function    // 将图片放进数据库    // id->Rid，pic是文件路径    public static void store_file(String id, String pic) {        // "?"是参数，相当于Qt中QString中的"%1"        String sql = "INSERT receipt(Rid, Rpicture, Rtype) VALUES(?, ?, ?)";        String type = pic.substring(pic.lastIndexOf((int)'.'));        try {            pst = con.prepareStatement(sql);            // 打开文件            FileInputStream fis = new FileInputStream(file);            // 将第一个问号替换为id            pst.setString(1, id);            // 替换第二个问号            pst.setBlob(2, fis, fis.available());            // 替换第三个问号            pst.setString(3, type);            // 执行SQL            pst.executeUpdate();            // 关闭资源            fis.close();            pst.close();        } catch(FileNotFoundException e) {            e.printStackTrace();        } catch(IOException e) {            e.printStackTrace();        } catch(SQLException e) {            e.printStackTrace();        }    }    // 将图片从数据库中取出    // id->Rid，path是存图路径    public static boolean load_file(String id, String path) {        String sql = "SELECT * FROM receipt WHERE Rid=?";        boolean got = false;        try {            pst = con.prepareStatement(sql);            pst.setString(1, id);            rs = pst.executeQuery();            if(rs.next()) { // 找到记录                InputStream is = rs.getBinaryStream("Rpicture");                byte buf[] = new byte[is.available()];                is.read(buf);                String type = rs.getString("Rtype");                FileOutputStream fos = new FileOutputStream(path + id + type);                fos.write(buf);                // 刷新缓冲区                fos.flush();                // 关闭流                fos.close();                is.close();                got = true;            } else { // 没找到                System.out.println("Not such id!");                got = false;            }            pst.close();            rs.close();        } catch(SQLException e) {            e.printStackTrace();        } catch(FileNotFoundException e) {            e.printStackTrace();        } catch(IOException e) {            e.printStackTrace();        }        return got;    }/*--- Debug    public static void main(String args[]) {        store_file("12138", "D:/test.jpg");        load_file("12138", "C:/");    }---*/}