前言
JDBC 進階特性是企業級數據訪問層的基石。本文通過完整代碼案例，深度講解四大核心場景：事務管理（ACID）、批量處理（性能優化）、自增主鍵回填（主從表關聯）、分頁查詢（大數據集處理），包含最佳實踐與避坑指南。
一、事務管理：保障數據一致性的核心
核心概念
- 事務：一組操作的原子單元，要么全成功，要么全失敗
- ACID：原子性、一致性、隔離性、持久性
- JDBC 默認自動提交：每條 SQL 獨立事務，需手動關閉實現業務事務
經典案例：銀行轉賬
@Test
public void testTransfer() {
String payerId = "00001", payeeId = "00002";
double amount = 1000;
Connection conn = null;
try {
conn = DBUtil.getConnection();
// 1. 關閉自動提交，開啟事務
conn.setAutoCommit(false);
// 2. 驗證收款賬戶存在
String sql = "SELECT money FROM accounts WHERE id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, payeeId);
ResultSet rs = ps.executeQuery();
if (!rs.next()) {
throw new SQLException("收款賬戶不存在");
}
double payeeBalance = rs.getDouble("money");
// 3. 驗證付款餘額充足
ps = conn.prepareStatement(sql);
ps.setString(1, payerId);
rs = ps.executeQuery();
rs.next();
double payerBalance = rs.getDouble("money");
if (payerBalance < amount) {
throw new SQLException("餘額不足");
}
// 4. 扣減付款方餘額
sql = "UPDATE accounts SET money = ? WHERE id = ?";
ps = conn.prepareStatement(sql);
ps.setDouble(1, payerBalance - amount);
ps.setString(2, payerId);
ps.executeUpdate();
// 模擬業務異常（測試回滾）
// Integer.valueOf("abc");
// 5. 增加收款方餘額
ps = conn.prepareStatement(sql);
ps.setDouble(1, payeeBalance + amount);
ps.setString(2, payeeId);
ps.executeUpdate();
// 6. 統一提交事務
conn.commit();
System.out.println("轉賬成功");
} catch (Exception e) {
// 7. 異常回滾
try {
if (conn != null) conn.rollback();
} catch (SQLException e1) {
throw new RuntimeException("回滾失敗", e1);
}
throw new RuntimeException("轉賬失敗", e);
} finally {
DBUtil.close(conn); // 歸還連接池
}
}
關鍵點總結
| 操作 | 方法 | 說明 |
|---|
| 開啟事務 | conn.setAutoCommit(false) | 必須在執行業務 SQL 前調用 |
| 提交事務 | conn.commit() | 所有業務 SQL 成功後調用 |
| 回滾事務 | conn.rollback() | catch 塊中調用，釋放鎖資源 |
| 保存點 | conn.setSavepoint() / rollback(sp) | 大事務中部分回滾，較少用 |
二、批量處理：大幅提升寫入性能
適用場景
- 大量 INSERT/UPDATE/DELETE 重複結構 SQL
- 數據導入、同步、報表生成等批量操作
代碼實戰：批量插入 108 條員工記錄
@Test
public void testBatchInsert() {
Connection conn = null;
try {
conn = DBUtil.getConnection();
conn.setAutoCommit(false); // 批量必須手動提交
String sql = "INSERT INTO emp VALUES (seq.nextval, ?, ?, ?, ?, ?, ?, ?)";
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 1; i <= 108; i++) {
ps.setString(1, "好漢" + i);
ps.setString(2, "打劫");
ps.setInt(3, 0);
ps.setDate(4, null);
ps.setDouble(5, 1000.0);
ps.setDouble(6, 8000.0);
ps.setInt(7, 3);
ps.addBatch(); // 累加到批次緩存
// 每 30 條執行一次，防止緩存過大
if (i % 30 == 0) {
ps.executeBatch(); // 發送批次
ps.clearBatch(); // 清空緩存
}
}
// 執行剩餘不足 30 條的批次
ps.executeBatch();
conn.commit();
System.out.println("批量插入完成");
} catch (SQLException e) {
try { conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); }
throw new RuntimeException("批量插入失敗", e);
} finally {
DBUtil.close(conn);
}
}
性能對比
| 方式 | 網絡往返 | 耗時(10萬條) | 備註 |
|---|
| 單條 executeUpdate | 100,000 次 | ~120 秒 | 極慢 |
| addBatch + executeBatch | ~3,333 次 (批次 30) | ~3 秒 | 快 40 倍 |
| rewriteBatchedStatements=true (MySQL) | 1 次 (客戶端重寫) | ~0.8 秒 | 最快，需連接串配置 |
最佳實踐
- 批次大小 500-1000 平衡內存與性能
- MySQL 添加連接參數
rewriteBatchedStatements=true 客戶端合併 SQL
- Oracle 使用
OraclePreparedStatement 專用 API
- 異常時必須 rollback，否則已執行批次隱式提交導致數據不一致
三、自增主鍵獲取：主從表關聯寫入關鍵
場景
訂單主表（order_id 自增）+ 訂單明細表（order_id 外鍵），插入主表後需獲取生成的 ID 寫入從表。
代碼實戰
@Test
public void testGeneratedKeys() {
Connection conn = null;
try {
conn = DBUtil.getConnection();
conn.setAutoCommit(false);
// 1. 插入部門（主表），指定要返回的自增列名
String sql = "INSERT INTO depts (dname, loc) VALUES (?, ?)";
// 關鍵：第二個參數指定要返回的自增列名數組
PreparedStatement ps = conn.prepareStatement(sql, new String[]{"deptno"});
ps.setString(1, "財務部");
ps.setString(2, "北京");
ps.executeUpdate();
// 2. 獲取生成的主鍵
ResultSet rs = ps.getGeneratedKeys(); // 返回包含自增值的 ResultSet
int deptno = 0;
if (rs.next()) {
deptno = rs.getInt(1); // 獲取第一列（deptno）的值
}
System.out.println("生成的部門 ID: " + deptno);
// 3. 批量插入員工（從表），關聯部門 ID
sql = "INSERT INTO emp (ename, job, deptno) VALUES (?, ?, ?)";
ps = conn.prepareStatement(sql);
ps.setString(1, "張三"); ps.setString(2, "經理"); ps.setInt(3, deptno);
ps.addBatch();
ps.setString(1, "李四"); ps.setString(2, "工程師"); ps.setInt(3, deptno);
ps.addBatch();
ps.executeBatch();
conn.commit();
} catch (SQLException e) {
try { conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); }
throw new RuntimeException("主從表寫入失敗", e);
} finally {
DBUtil.close(conn);
}
}
關鍵 API
| 方法 | 說明 |
|---|
| prepareStatement(sql, String[] columnNames) | 指定要返回的自增列名（如 "id"、"deptno"） |
| prepareStatement(sql, int[] columnIndexes) | 指定要返回的自增列索引（1-based） |
| ps.getGeneratedKeys() | 執行 executeUpdate 後調用，返回 ResultSet 包含自增值 |
數據庫差異
- MySQL：支持
AUTO_INCREMENT，getGeneratedKeys() 完美支持
- Oracle：不支持 自增列（使用 Sequence），需先
SELECT seq.nextval FROM dual 獲取 ID 再插入
- PostgreSQL：支持
SERIAL/IDENTITY，需指定列名 RETURNING id
- SQL Server：支持
IDENTITY，getGeneratedKeys() 支持良好
四、分頁查詢：海量數據高效獲取
通用分頁公式
// 每頁大小 pageSize，當前頁 pageNum (1-based)
int offset = (pageNum - 1) * pageSize;
int limit = pageSize;
主流數據庫分頁寫法
| 數據庫 | 分頁語法 | 示例 |
|---|
| MySQL / PostgreSQL / SQLite | LIMIT offset, limit | SELECT * FROM emp LIMIT 10, 20 |
| Oracle 12c+ | OFFSET ... ROWS FETCH NEXT ... ROWS ONLY | SELECT * FROM emp OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY |
| Oracle 11g- | ROWNUM 子查詢嵌套 | 見下方經典寫法 |
| SQL Server 2012+ | OFFSET ... ROWS FETCH NEXT ... ROWS ONLY | SELECT * FROM emp ORDER BY id OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY |
| SQL Server 2008- | ROW_NUMBER() 窗口函數 | SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY id) rn FROM emp) t WHERE rn BETWEEN 11 AND 30 |
Oracle 經典 ROWNUM 分頁（通用兼容）
String sql = "SELECT * FROM ("
+ " SELECT e.*, ROWNUM r FROM ("
+ " SELECT * FROM emp ORDER BY empno"
+ " ) e WHERE ROWNUM <= ?" // pageSize * pageNum
+ ") WHERE r > ?"; // (pageNum - 1) * pageSize
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, pageSize * pageNum); // 最大行號
ps.setInt(2, (pageNum - 1) * pageSize); // 起始行號
ResultSet rs = ps.executeQuery();
性能優化建議
- 索引覆蓋：ORDER BY 列 + WHERE 列建立聯合索引，避免 filesort
- 避免深分頁：OFFSET 1000000 性能極差，改用游標分頁（WHERE id > lastId ORDER BY id LIMIT 20）
- 統計總數優化：大表 COUNT(*) 慢，緩存總數、近似值或異步計算
- MyBatis/PageHelper：生產環境直接使用成熟分頁插件，自動攔截 SQL 改寫
五、總結
- 事務：setAutoCommit(false) + commit/rollback，保證 ACID，異常必須回滾
- 批量：addBatch + executeBatch + 有界批次 + rewriteBatchedStatements，性能提升數十倍
- 自增鍵：prepareStatement(sql, columnNames) + getGeneratedKeys()，主從表寫入必備
- 分頁：根據數據庫選擇語法，深分頁改用游標，生產用 PageHelper 等插件
- 所有操作統一在
try-catch-finally 中管理連接生命周期，防止連接洩漏
