您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

Java JDBC 存取 MySQL BLOB：圖片寫入與讀取完整範例

編輯：MySQL 資料庫相關知識

前言

MySQL 的 BLOB 型別可儲存二進位檔案（圖片、PDF 等）。透過 JDBC PreparedStatement.setBinaryStream() 寫入、ResultSet.getBlob().getBinaryStream() 讀取，即可實現資料庫與檔案系統的雙向轉換。本文提供完整可編譯的 Java 範例碼。

資料表結構

CREATE TABLE employee_photo (
    employee_id VARCHAR(50) PRIMARY KEY,
    binary_photo LONGBLOB NOT NULL,
    last_mod TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

一、寫入圖片至資料庫

import java.awt.Graphics;
import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import javax.imageio.ImageIO;
import javax.swing.ImageIcon;

public class PhotoDao {

    private static final String INSERT_SQL =
        "INSERT INTO employee_photo (employee_id, binary_photo, last_mod, created) " +
        "VALUES (?, ?, NOW(), NOW()) " +
        "ON DUPLICATE KEY UPDATE binary_photo=VALUES(binary_photo), last_mod=NOW()";

    public void insertPhoto(String empId, String imagePath) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBPool.getConnection();           // 自行實作連線池
            // 1. 讀取圖片並轉為 BufferedImage
            ImageIcon icon = new ImageIcon(imagePath);
            java.awt.Image img = icon.getImage();
            BufferedImage bi = new BufferedImage(
                    img.getWidth(null), img.getHeight(null), BufferedImage.TYPE_INT_RGB);
            Graphics g = bi.getGraphics();
            g.drawImage(img, 0, 0, null);
            g.dispose();

            // 2. 壓縮為 JPEG 並寫入 ByteArrayOutputStream
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            ImageIO.write(bi, "jpg", baos);
            byte[] imgBytes = baos.toByteArray();

            // 3. 以 BinaryStream 寫入 BLOB
            ByteArrayInputStream bais = new ByteArrayInputStream(imgBytes);
            ps = conn.prepareStatement(INSERT_SQL);
            ps.setString(1, empId);
            ps.setBinaryStream(2, bais, imgBytes.length);
            ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeQuietly(ps);
            closeQuietly(conn);
        }
    }

    private void closeQuietly(AutoCloseable ac) {
        if (ac != null) try { ac.close(); } catch (Exception ignore) {}
    }
}

關鍵點

使用 BufferedImage.TYPE_INT_RGB 確保相容性，避免 Alpha 通道導致 JPEG 壓縮失敗。
setBinaryStream(InputStream, long length) 需正確傳入長度，否則驅動可能拋出例外。
採用 ON DUPLICATE KEY UPDATE 實現「不存在則插入、存在則更新」。

二、從資料庫讀取並存檔

public void exportPhoto(String empId, String destPath) {
    String sql = "SELECT binary_photo FROM employee_photo WHERE employee_id = ?";
    Connection conn = null;
    PreparedStatement ps = null;
    java.sql.ResultSet rs = null;
    java.io.FileOutputStream fos = null;
    java.io.InputStream is = null;
    try {
        conn = DBPool.getConnection();
        ps = conn.prepareStatement(sql);
        ps.setString(1, empId);
        rs = ps.executeQuery();
        if (rs.next()) {
            java.sql.Blob blob = rs.getBlob("binary_photo");
            is = blob.getBinaryStream();
            fos = new java.io.FileOutputStream(destPath);
            byte[] buffer = new byte[1024 * 100]; // 100KB 緩衝
            int len;
            while ((len = is.read(buffer)) != -1) {
                fos.write(buffer, 0, len);
            }
            fos.flush();
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        closeQuietly(is);
        closeQuietly(fos);
        closeQuietly(rs);
        closeQuietly(ps);
        closeQuietly(conn);
    }
}