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:/");
    }
---*/
}