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 = 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 | ` | 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 (推薦) |
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+) |
| SYSDATE | Java 端 `new Date()` | 資料庫端 `SYSDATE` / `SYSTIMESTAMP`,避免時鐘不同步 |
poolPreparedStatements="true"
maxOpenPreparedStatements="100"
/>
<%@ 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% 以上常見生產故障。