您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 圖片存儲進階：Stored Procedure + Java Web 整合完整架構

編輯：Oracle 資料庫基礎

延續基礎 BLOB 操作，本文呈現一套完整的企業級圖片存儲解決方案：資料庫端以 DBMS_LOB 封裝載入邏輯，中間層 Java 封裝 BlobDAO (單例模式、連線池、資源自動關閉)，前端 Action 串流輸出圖片，並提供反向寫入 (Web → DB) 的完整程式碼。適合需整合至既有 Java Web 應用 (Struts 1 風格) 的團隊參考。

資料庫端：權限與 Procedure

1. 環境準備 (DBA 執行)

-- 建立儲存表
CREATE TABLE image_lob (
  t_id    VARCHAR2(5) NOT NULL,
  t_image BLOB        NOT NULL
);

-- 建立目錄物件 (需 CREATE ANY DIRECTORY)
CREATE OR REPLACE DIRECTORY images AS 'C:\picture';

-- 授權應用帳號讀寫目錄
GRANT READ, WRITE ON DIRECTORY images TO scott;

2. 圖片載入 Procedure (含完整異常處理)


CREATE OR REPLACE PROCEDURE img_insert (
  p_tid      IN VARCHAR2,
  p_filename IN VARCHAR2
) AS
  v_bfile  BFILE;
  v_blob   BLOB;
BEGIN
  INSERT INTO image_lob (t_id, t_image)
  VALUES (p_tid, EMPTY_BLOB())
  RETURNING t_image INTO v_blob;

  v_bfile := BFILENAME('IMAGES', p_filename);
  DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);

  DBMS_LOB.LOADFROMFILE(
    dest_lob => v_blob,
    src_lob  => v_bfile,
    amount   => DBMS_LOB.GETLENGTH(v_bfile)
  );

  DBMS_LOB.FILECLOSE(v_bfile);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_LOB.FILEISOPEN(v_bfile) = 1 THEN
      DBMS_LOB.FILECLOSE(v_bfile);
    END IF;
    ROLLBACK;
    RAISE;
END img_insert;
/

3. 測試呼叫


-- 將 C:\picture\1.gif 存入 ID='1'
CALL img_insert('1', '1.gif');

Java 中間層：BlobDAO 完整實作


public class BlobDAO {
  // 單例模式
  private static final BlobDAO INSTANCE = new BlobDAO();
  private Connection conn = null;

  private BlobDAO() {}

  public static BlobDAO getInstance() { return INSTANCE; }

  // 初始化連線 (實際專案請改用 DataSource/連線池)
  private void initConn() throws SQLException {
    if (conn == null || conn.isClosed()) {
      conn = DBAccess.getInstance().getConn();  // 假設自訂連線管理類
    }
  }

  // 讀取圖片位元組陣列
  public byte[] getImage(String imgName) {
    BufferedInputStream in = null;
    byte[] bytes = null;
    Blob blob = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    final String sql = "SELECT t_image FROM image_lob WHERE t_id = ?";

    try {
      initConn();
      ps = conn.prepareStatement(sql);
      ps.setString(1, imgName);
      rs = ps.executeQuery();

      if (rs.next()) {
        blob = rs.getBlob("T_IMAGE");
        if (blob != null) {
          in = new BufferedInputStream(blob.getBinaryStream());
          int len = (int) blob.length();
          bytes = new byte[len];
          // 確保完整讀取
          int offset = 0, read;
          while (offset < len && (read = in.read(bytes, offset, len - offset)) != -1) {
            offset += read;
          }
        }
      }
    } catch (SQLException | IOException e) {
      e.printStackTrace();
    } finally {
      closeQuietly(in);
      closeQuietly(rs);
      closeQuietly(ps);
      // 注意：單例模式下連線不關閉，由連線池管理
    }
    return bytes;
  }

  // 從 Web 上傳檔案寫入資料庫 (c:\4.gif → ID=imgId)
  public void saveImg(String imgId) {
    Statement st = null;
    ResultSet rs = null;
    Blob blob = null;
    OutputStream out = null;
    InputStream in = null;
    File file = null;

    try {
      initConn();
      conn.setAutoCommit(false);

      st = conn.createStatement();
      // 1. 插入空 BLOB 並鎖定該列 (FOR UPDATE)
      st.executeUpdate("INSERT INTO image_lob VALUES ('" + imgId + "', EMPTY_BLOB())");
      rs = st.executeQuery(
        "SELECT t_image FROM image_lob WHERE t_id = '" + imgId + "' FOR UPDATE"
      );

      if (rs.next()) {
        blob = (BLOB) rs.getBlob(1);
        out = blob.getBinaryOutputStream();
        file = new File("c:\\4.gif");
        in = new FileInputStream(file);

        byte[] buf = new byte[blob.getBufferSize()];
        int read;
        while ((read = in.read(buf)) != -1) {
          out.write(buf, 0, read);
        }
      }
      conn.commit();
    } catch (Exception e) {
      rollbackQuietly(conn);
      e.printStackTrace();
    } finally {
      closeQuietly(in);
      closeQuietly(out);
      closeQuietly(rs);
      closeQuietly(st);
      // 連線歸還池中，不關閉
    }
  }

  // 輔助方法
  private void closeQuietly(AutoCloseable c) {
    try { if (c != null) c.close(); } catch (Exception ignore) {}
  }
  private void rollbackQuietly(Connection c) {
    try { if (c != null) c.rollback(); } catch (Exception ignore) {}
  }
}

前端 Action 整合 (Struts 1 風格)


public class ImageAction extends Action {
  public ActionForward execute(ActionMapping mapping, ActionForm form,
                               HttpServletRequest req, HttpServletResponse resp) {
    try {
      byte[] imgBytes = BlobDAO.getInstance().getImage("1");
      if (imgBytes != null) {
        resp.setContentType("image/gif");
        resp.getOutputStream().write(imgBytes);
      } else {
        resp.sendError(404);
      }
    } catch (IOException e) {
      e.printStackTrace();
    }
    return null;  // 直接輸出流，不轉發
  }
}

架構優化建議 (生產環境必做)

項目	現狀	建議改進
連線管理	單例持有單一 Connection	注入 `DataSource` (HikariCP/DBCP)，每請求取用歸還
SQL 注入風險	`saveImg` 字串拼接 ID	全面使用 `PreparedStatement` 綁定變數
大檔案支援	一次性 `byte[]` 載入記憶體	改用 `Blob.setBinaryStream()` 分塊寫入 / `getBinaryStream()` 分塊讀出
MIME 型別	寫死 image/gif	資料表增欄 `mime_type`，動態設定 `Content-Type`
快取策略	無	加上 `ETag`/`Last-Modified`、CDN、Redis 快取熱門圖片
異常處理	`printStackTrace`	統一日誌框架 (SLF4J/Logback)，錯誤碼對應 HTTP 狀態碼