java - 如何在PreparedStatement中运行SQL TRANSACTION

Question

我有一个无法运行的 SQL 事务查询。谁能告诉我为什么吗？我也无法使用 preparedstatement.executequery(); 运行它。

START TRANSACTION;
SELECT total_installment_remaining  FROM payment_loan WHERE loan_id = 1 FOR UPDATE;
UPDATE payment_loan SET total_installment_remaining =total_installment_remaining-1 WHERE loan_id = 1;
COMMIT;

Answer 1

关闭自动提交，然后使用 Connection.commit() 结束事务。

connection.setAutocommit(false);
Statement stmt = con2.createStatement();

// this will automatically start a transaction
ResultSet rs = stmt.executeQuery("SELECT total_installment_remaining  FROM payment_loan WHERE loan_id = 1 FOR UPDATE");

// process the result if needed
...

stmt.executeUpdate("UPDATE payment_loan SET total_installment_remaining =total_installment_remaining-1 WHERE loan_id = 1");

// end the transaction and persist then changes
connection.commit();

<小时/>

如果您的代码中不需要 SELECT 的结果，那么您首先就不需要 SELECT ... FOR UPDATE，因为 UPDATE 无论如何都会锁定该行。