Spring 整合 Oracle 9i BLOB/CLOB 讀寫完整解決方案

Oracle 9i 大物件欄位處理的 Spring 配置陷阱

專案實戰發現 Oracle 9i 的 BLOB/CLOB 讀寫在 Spring 中有特殊要求，標準 DefaultLobHandler 不支援 9i，需專用 OracleLobHandler。

一、BLOB 讀寫完整配置

Spring Bean 定義

DAO 寫入 BLOB

public void updateBlob(final String key, final InputStream contentStream, final int contentLength) {
  getJdbcTemplate().execute(
    "UPDATE TABLE SET CONTENT = ? WHERE ID = ?",
    new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) {
      protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
        lobCreator.setBlobAsBinaryStream(ps, 1, contentStream, contentLength);
        ps.setString(2, key);
      }
    }
  );
}

DAO 讀取 BLOB

public void getBlobContent(final String id, final OutputStream contentStream) {
  getJdbcTemplate().query(
    "SELECT CONTENT FROM TABLE WHERE ID = ?",
    new String[]{id},
    new AbstractLobStreamingResultSetExtractor() {
      protected void handleNoRowFound() {
        throw new IncorrectResultSizeDataAccessException("Image with id '" + id + "' not found", 1, 0);
      }
      public void streamData(ResultSet rs) throws SQLException, IOException {
        InputStream is = lobHandler.getBlobAsBinaryStream(rs, 1);
        if (is != null) FileCopyUtils.copy(is, contentStream);
      }
    }
  );
}

Controller 層暴露下載

public void getBlobContent(HttpServletRequest request, HttpServletResponse response) {
  serviceDisplayDAO.getBlobContent(request.getParameter("id"), response.getOutputStream());
}

前端連結：href="/blob.do?action=getBlobContent&id='"+id+"'"

二、CLOB 讀寫關鍵點

較簡單，但 必須更換 Oracle JDBC 驅動：不可用 classes12.zip，需從 Oracle 官網下載較新版本 (具體版本視環境而定)。

DAO 查詢 CLOB

public List findByPrimaryKey(String key) {
  return getJdbcTemplate().query(
    "SELECT SERVICEID, CONTENTTYPE, TEMPLATETEXT FROM TABLE WHERE SERVICEID = ?",
    new Object[]{key},
    new RowMapper() {
      public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
        String serviceId = rs.getString(1);
        String contentType = rs.getString(2);
        String templateText = lobHandler.getClobAsString(rs, 3);
        return new ServConTempBean(serviceId, contentType, templateText);
      }
    }
  );
}

DAO 更新 CLOB

public void updateContentByPrimaryKey(final String key, final String templateText) {
  getJdbcTemplate().execute(
    "UPDATE TABLE SET TEMPLATETEXT = ? WHERE SERVICEID = ?",
    new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) {
      protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
        lobCreator.setClobAsString(ps, 1, templateText);
        ps.setString(2, key);
      }
    }
  );
}

關鍵總結

• Oracle 9i 必須使用 OracleLobHandler + C3P0NativeJdbcExtractor
• BLOB 使用 setBlobAsBinaryStream / getBlobAsBinaryStream
• CLOB 使用 setClobAsString / getClobAsString
• 驅動程式升級是 CLOB 正常運作的前提條件