Web 應用需存儲超過 4000 字元的長文本 (文章、公告、合同等),Oracle CLOB 是最佳選擇。本文提供基於 JSP/Servlet 的完整寫入、讀取實作代碼。
CREATE TABLE t_flfg (
xlh NUMBER PRIMARY KEY,
title VARCHAR2(200),
content CLOB,
rq DATE,
sort VARCHAR2(50),
type VARCHAR2(50),
qy VARCHAR2(50)
);
CREATE SEQUENCE flfg_seq START WITH 1 INCREMENT BY 1;
<%@ page import="java.sql.*, oracle.sql.*" %>
<%
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
// 1. 獲取連線 (建議使用連線池 DataSource)
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.1.2:1521:lqxm", "lqxm", "lqxm");
conn.setAutoCommit(false); // 手動控制交易
// 2. 獲取參數
request.setCharacterEncoding("GBK");
String title = request.getParameter("title");
String content = request.getParameter("content");
// ... 其他參數
// 3. 第一階段:插入主記錄,CLOB 使用 EMPTY_CLOB()
String sql = "INSERT INTO t_flfg (xlh, title, content, rq, sort, type, qy) " +
"VALUES (FLFG_SEQ.NEXTVAL, ?, EMPTY_CLOB(), ?, ?, ?, ?)";
pstmt = conn.prepareStatement(sql, new String[]{"xlh"}); // 返回生成鍵
pstmt.setString(1, title);
pstmt.setString(2, rq);
pstmt.setString(3, sort);
pstmt.setString(4, type);
pstmt.setString(5, qy);
pstmt.executeUpdate();
// 4. 獲取自動生成的主鍵
rs = pstmt.getGeneratedKeys();
long xlh = 0;
if (rs.next()) xlh = rs.getLong(1);
rs.close();
pstmt.close();
// 5. 第二階段:鎖定該行並寫入 CLOB 內容
sql = "SELECT content FROM t_flfg WHERE xlh = ? FOR UPDATE";
pstmt = conn.prepareStatement(sql);
pstmt.setLong(1, xlh);
rs = pstmt.executeQuery();
if (rs.next()) {
CLOB clob = ((oracle.sql.CLOB) rs.getClob(1));
// 直接寫入字串 (適合中等大小)
clob.putString(1, content);
// 或大文本分塊寫入
// Writer writer = clob.getCharacterOutputStream();
// writer.write(content);
// writer.close();
}
conn.commit();
out.println("存儲成功,ID=" + xlh);
} catch (Exception e) {
if (conn != null) conn.rollback();
e.printStackTrace();
out.println("錯誤:" + e.getMessage());
} finally {
// 6. 嚴格關閉資源
try { if (rs != null) rs.close(); } catch(Exception e) {}
try { if (pstmt != null) pstmt.close(); } catch(Exception e) {}
try { if (conn != null) conn.close(); } catch(Exception e) {}
}
%>
<%
String xlh = request.getParameter("xlh");
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection(...);
String sql = "SELECT title, content FROM t_flfg WHERE xlh = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setLong(1, Long.parseLong(xlh));
rs = pstmt.executeQuery();
if (rs.next()) {
String title = rs.getString("title");
CLOB clob = (CLOB) rs.getClob("content");
// 字串流讀取 (支援中文)
Reader reader = clob.getCharacterStream();
char[] buffer = new char[4096];
int len;
StringBuilder sb = new StringBuilder();
while ((len = reader.read(buffer)) != -1) {
sb.append(buffer, 0, len);
}
String content = sb.toString();
// 輸出 HTML
out.println("" + title + "
");
out.println("" + content.replace("\n", "
") + "");
}
} finally {
// 關閉資源...
}
%>
| 環節 | 關鍵點 | 常見錯誤 |
|---|---|---|
| 插入階段 | 使用 EMPTY_CLOB() 獲取定位器 | 直接 INSERT 字串超過 4000 字元報錯 |
| 交易控制 | setAutoCommit(false) 確保原子性 | 自動提交導致 FOR UPDATE 鎖定失效 |
| 鎖定讀取 | SELECT ... FOR UPDATE 必須 | 無鎖寫入報 ORA-22920 |
| 編碼處理 | request.setCharacterEncoding + CLOB 字串流 | 中文亂碼 (ISO-8859-1 vs GBK/UTF-8) |
| 大文本分塊 | > 32KB 建議使用 getCharacterOutputStream 分批寫入 | 單次 putString 記憶體溢位 |
@Transactional 宣告式交易管理;JdbcTemplate 或 MyBatis 簡化 JDBC 樣板代碼;MultipartFile 流式處理,避免全量載入記憶體;