select "for update" with JDBC?

Question

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

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

My select statement

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

My update statement

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

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

Answer 1

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