用JDBC选择“for update”？

Question

我想使用JDBC在Java中创建一个for update select语句，但不确定它将如何完成。

如果你不熟悉的更新，你可以读到这里 https://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-FOR-UPDATE-SHARE

例如，我有以下的select语句

我的select语句

select email from email_accounts where already_linked = false order by random() limit 1 

我的更新语句

UPDATE email_accounts set already_linked = true, account_link_timestamp = now() where email = ? 

如何在使用JDBC的Java中使用for update执行此操作？

Answer 1

您首先将for update添加到您的选择（以及您想要更新的其他列），然后更新它们。另外，如评论中所述，确保您的getConnection返回Connection而不自动提交。您需要设置滚动的Statement类型和CONCUR_UPDATABLE。类似的，

String[] colNames = { "email", "already_linked", "account_link_timestamp" }; 
String query = "select " + Stream.of(colNames).collect(Collectors.joining(", ")) 
     + "from email_accounts where already_linked = false for update"; 
try (Connection conn = getConnection(); // Make sure conn.setAutoCommit(false); 
     Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
       ResultSet.CONCUR_UPDATABLE); 
     ResultSet rs = stmt.executeQuery(query)) { 
    while (rs.next()) { 
     // Get the current values, if you need them. 
     String email = rs.getString(colNames[0]); 
     boolean linked = rs.getBoolean(colNames[1]); 
     Timestamp time = rs.getTimestamp(colNames[2]); 
     // ... 
     rs.updateBoolean(colNames[1], true); 
     rs.updateTimestamp(colNames[2], // 
       new Timestamp(System.currentTimeMillis())); 
     rs.updateRow(); 
    } 
} catch (SQLException e) { 
    e.printStackTrace(); 
}