事务与批处理

在 Java 中查询 PostgreSQL 数据库

Miller Trujillo

Staff Software Engineer

问题是什么？

事务：数据库操作的安全网
要么全部成功，要么全部失败

银行转账事务示例

ACID 特性

原子性：要么全部完成，要么不生效
一致性：数据库保持有效状态
隔离性：防止相互干扰
持久性：已提交的事务永久保存

ACID 属性

默认情况下，JDBC 为自动提交模式 🔄

JDBC 中的事务控制

Connection conn = DriverManager.getConnection(DB_URL, USERNAME, PASSWORD)
conn.setAutoCommit(false);

try (...) {
    // Execute your SQL statements here

    conn.commit();

} catch (SQLException e) {
  conn.rollback();
}

银行转账示例

// Reduce the sender's balance
String withdrawSQL = "UPDATE accounts SET balance = balance - ?
  WHERE account_id = ? AND balance >= ?";

// Increase the recipient's balance
String depositSQL = "UPDATE accounts SET balance = balance + ?
  WHERE account_id = ?";

UPDATE - 修改表中的现有行
SET - 指定要更改的列

银行转账示例

try (Connection conn = DriverManager.getConnection(DB_URL, USERNAME, PASSWORD)) {

  conn.setAutoCommit(false);

  try {
    try (PreparedStatement withdrawStmt = conn.prepareStatement(withdrawSQL);
         PreparedStatement depositStmt = conn.prepareStatement(depositSQL)) {
      // Set parameters and execute both statements...
    }

    conn.commit();

  } catch (SQLException e) {
    conn.rollback(); 
  }
}

批处理

将多条操作打包执行

2025-12-03 10.40.25 的截图

批处理示例

PreparedStatement pstmt = conn.prepareStatement("INSERT INTO transfers
   (sender_id, recipient_id, amount) VALUES (?, ?, ?)")

for (Object[] transfer : transfers) {
  pstmt.setInt(1, (Integer) transfer[0]);
  pstmt.setInt(2, (Integer) transfer[1]); 
  pstmt.setInt(3, (Integer) transfer[2]);

  pstmt.addBatch(); // Queue for later
}

int[] results = pstmt.executeBatch(); // Send all at once

Vamos praticar!

在 Java 中查询 PostgreSQL 数据库