选择“用于更新"用JDBC? [英] select "for update" with JDBC?

问题描述

我想使用JDBC在Java中创建for update select语句，但不确定如何实现.

I want to create a for update select statement in Java using JDBC, but not sure how it would be done.

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

If you are unfamiliar with for update you can read about it here https://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-FOR-UPDATE-SHARE

例如，我有以下选择语句

For example, I have the following select statements

我的选择语句

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 = ?

在使用for update的情况下，如何使用JDBC在Java中完成此操作?

How would this be done in Java using JDBC while using for update?

推荐答案

您首先将for update添加到选择的内容(以及要更新的其他列)中，然后再对其进行更新.另外，如注释中所述，请确保您的getConnection返回不自动提交的Connection.并且您需要为滚动和CONCUR_UPDATABLE设置Statement类型.像

You first add for update to your select (and your other columns you want to update), and then you update them. Also, as noted in the comments, make sure your getConnection returns a Connection without autocommit. And you need to set a Statement type for scrolling and CONCUR_UPDATABLE. Something like,

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();
}

这篇关于选择“用于更新"用JDBC?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！