萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

JSP 連接 Oracle 核心實戰:CLOB/BLOB 處理、中文編碼轉換、連接池與日期處理最佳實踐

核心挑戰三部曲

  1. 大對象 (LOB) 讀寫:CLOB/BLOB 不能直接 INSERT/SELECT,需定位器兩階段操作;
  2. 中文編碼:JVM (UTF-16) ↔ Web (GBK/UTF-8) ↔ Oracle (DB Character Set) 三層轉碼;
  3. 資源管理:連接、Statement、ResultSet 必須確定性關閉,防洩漏。

一、CLOB 大文本讀寫標準模式

寫入 (兩階段:EMPTY_CLOB → SELECT FOR UPDATE → 寫入)

Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
    conn = dataSource.getConnection();
    conn.setAutoCommit(false); // 關鍵:手動交易
    
    // 階段 1: 插入主記錄,CLOB 用 EMPTY_CLOB()
    String sql = "INSERT INTO articles (id, title, content) VALUES (seq.nextval, ?, EMPTY_CLOB())";
    pstmt = conn.prepareStatement(sql, new String[]{"id"});
    pstmt.setString(1, title);
    pstmt.executeUpdate();
    
    // 獲取生成的 ID
    rs = pstmt.getGeneratedKeys();
    long id = 0;
    if (rs.next()) id = rs.getLong(1);
    close(rs, pstmt);
    
    // 階段 2: 鎖定行並寫入 CLOB 內容
    sql = "SELECT content FROM articles WHERE id = ? FOR UPDATE";
    pstmt = conn.prepareStatement(sql);
    pstmt.setLong(1, id);
    rs = pstmt.executeQuery();
    
    if (rs.next()) {
        Clob clob = rs.getClob(1);
        // 方式 A: 字串流寫入 (支援中文)
        Writer writer = clob.setCharacterStream(1);
        writer.write(content);
        writer.close();
        // 方式 B: 直接 putString (適合 < 32KB)
        // clob.putString(1, content);
    }
    
    conn.commit();
} catch (Exception e) {
    rollback(conn);
    throw new RuntimeException(e);
} finally {
    close(rs, pstmt, conn);
}

讀取 (字符流逐塊讀取)

String sql = "SELECT title, content FROM articles WHERE id = ?";
try (Connection conn = dataSource.getConnection();
     PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setLong(1, id);
    try (ResultSet rs = pstmt.executeQuery()) {
        if (rs.next()) {
            String title = rs.getString("title");
            Clob clob = rs.getClob("content");
            Reader reader = clob.getCharacterStream(); // 關鍵:字符流而非字節流
            char[] buffer = new char[8192];
            int len;
            StringBuilder sb = new StringBuilder();
            while ((len = reader.read(buffer)) != -1) {
                sb.append(buffer, 0, len);
            }
            String content = sb.toString();
            // 輸出至 JSP
        }
    }
}

二、BLOB 二進制文件讀寫

-- 寫入
Blob blob = null;
OutputStream out = null;
InputStream in = new FileInputStream(file);
try {
    // 同 CLOB 兩階段獲取 blob 定位器
    out = blob.setBinaryStream(1);
    byte[] buf = new byte[8192];
    int len;
    while ((len = in.read(buf)) != -1) out.write(buf, 0, len);
} finally { close(in, out); }

-- 讀取
Blob blob = rs.getBlob("file_data");
InputStream in = blob.getBinaryStream();
// 直接輸出至 ServletOutputStream

三、中文編碼終極解決方案

根因:三層編碼鏈路

瀏覽器 (UTF-8/GBK) → Web 容器 (ISO-8859-1 默認解碼) → JVM (UTF-16) 
    → JDBC 驅動 (按 NLS_LANG/連接屬性轉換) → Oracle (DB Character Set: AL32UTF8/ZHS16GBK)

統一配置檢查清單

層級配置項建議值
JSP 頁面`<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>`UTF-8
Web.xml`` CharacterEncodingFilter `` (Spring) 或 `UTF-8` (Tomcat 8+)UTF-8
JDBC URL`jdbc:oracle:thin:@host:1521:sid?characterEncoding=UTF-8` (Thin 驅動 11.2+ 支援)顯式指定
Oracle 客戶端`NLS_LANG=.AL32UTF8` (環境變量/登錄表)AL32UTF8
資料庫`SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET'`AL32UTF8 (推薦)

避坑:舊系統 GBK 強制轉碼工具類 (僅維護舊項目用)

public final class LegacyCharsetUtil {
    private LegacyCharsetUtil() {}
    
    // ISO-8859-1 (Tomcat 默認解碼) → GBK (Oracle 老庫)
    public static String isoToGbk(String iso) {
        if (iso == null) return null;
        try {
            return new String(iso.getBytes("ISO-8859-1"), "GBK");
        } catch (UnsupportedEncodingException e) {
            return iso;
        }
    }
    
    // GBK (資料庫讀出) → ISO-8859-1 (舊頁面輸出)
    public static String gbkToIso(String gbk) {
        if (gbk == null) return null;
        try {
            return new String(gbk.getBytes("GBK"), "ISO-8859-1");
        } catch (UnsupportedEncodingException e) {
            return gbk;
        }
    }
}

新專案嚴禁使用此類轉碼**,全鏈路統一 UTF-8 (AL32UTF8)。

四、日期時間處理最佳實踐

操作錯誤做法正確做法
插入日期`pstmt.setString(1, "2024-01-15")``pstmt.setDate(1, java.sql.Date.valueOf("2024-01-15"))` 或 `setTimestamp`
查詢日期`rs.getString("dt")` 再 parse`rs.getTimestamp("dt")` 獲取 `java.sql.Timestamp` (含毫秒)
時區忽略時區Oracle `TIMESTAMP WITH TIME ZONE` + Java `OffsetDateTime` (JDBC 4.2+)
SYSDATEJava 端 `new Date()`資料庫端 `SYSDATE` / `SYSTIMESTAMP`,避免時鐘不同步

五、連接池與資源管理鐵律



    poolPreparedStatements="true"
    maxOpenPreparedStatements="100"
/>

六、JSP 頁面完整範例 (含異常處理)

<%@ page import="java.sql.*, javax.sql.*, javax.naming.*" %>
<%
  DataSource ds = (DataSource) new InitialContext().lookup("java:comp/env/jdbc/OracleDS");
  String action = request.getParameter("action");
  
  if ("save".equals(action)) {
    String title = request.getParameter("title");
    String content = request.getParameter("content"); // 大文本
    
    try (Connection conn = ds.getConnection()) {
      conn.setAutoCommit(false);
      try (PreparedStatement pstmt = conn.prepareStatement(
           "INSERT INTO docs (id, title, content) VALUES (seq.nextval, ?, EMPTY_CLOB())",
           new String[]{"id"})) {
        pstmt.setString(1, title);
        pstmt.executeUpdate();
        try (ResultSet rs = pstmt.getGeneratedKeys()) {
          if (rs.next()) {
            long id = rs.getLong(1);
            try (PreparedStatement pstmt2 = conn.prepareStatement(
                 "SELECT content FROM docs WHERE id = ? FOR UPDATE")) {
              pstmt2.setLong(1, id);
              try (ResultSet rs2 = pstmt2.executeQuery()) {
                if (rs2.next()) {
                  Clob clob = rs2.getClob(1);
                  try (Writer w = clob.setCharacterStream(1)) {
                    w.write(content);
                  }
                }
              }
            }
          }
        }
      }
      conn.commit();
      out.print("{\"success\":true}");
    } catch (Exception e) {
      out.print("{\"success\":false,\"msg\":\"" + e.getMessage().replace("\"", "\\\"") + "\"}");
    }
  }
%>

總結

JSP 操作 Oracle 核心在於:**LOB 兩階段寫入、全鏈路 UTF-8 (AL32UTF8)、繫結變數防注入、連接池 Statement Cache、try-with-resources 確定性資源釋放**。遵循此規範,可消除 90% 以上常見生產故障。

copyright © 萬盛學電腦網 all rights reserved