Spring JDBC'选择更新' [英] Spring jdbc 'select for update'
问题描述
我有以下与Spring JDBC一起使用的方法
I have the following method that I use with Spring JDBC
public String getState() {
String stateLink = template.queryForObject(
"select state_url from state_scrape_queue where in_use = false ORDER BY scrape_timestamp NULLS FIRST LIMIT 1",
(result, rowNum) -> {
return result.getString("state_url");
});
return stateLink;
}
我找不到如何执行使用Spring JDBC进行更新.我希望将in_use
设置为true进行更新.
I can't find an example of how to do a for update with Spring JDBC. I want in_use
to be set to true using for update.
我需要使用select进行更新,因为此应用程序将以多线程方式使用.我不希望有多个线程获得同一行,而防止这种情况的方法是使用select for update
I need to use select for update since this application will be used in a multi-threaded fashion. I don't want more than one thread to get the same row and the way to prevent that is by using select for update
我能够使用普通JDBC做到这一点,这是我问如何使用普通JDBC的问题
I was able to do this with plain JDBC, here is the question I asked how to do it with plain JDBC
有人知道该怎么做吗?
推荐答案
这是我想出的,随时建议进行改进
This is what I came up with, feel free to recommend improvements
public String getState() throws SQLException {
String state = null;
Connection conn = DataSourceUtils.getConnection(template.getDataSource());
try {
conn.setAutoCommit(false);
String[] colNames = { "id", "state_url", "in_use" };
String query = "select " + Stream.of(colNames).collect(Collectors.joining(", "))
+ " from state_scrape_queue where in_use = false ORDER BY scrape_timestamp NULLS FIRST LIMIT 1 FOR UPDATE";
System.out.println(query);
try (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.
state = rs.getString(colNames[1]);
rs.updateBoolean(colNames[2], true);
rs.updateRow();
conn.commit();
}
}
} catch (SQLException e) {
conn.setAutoCommit(true);
e.printStackTrace();
} finally {
conn.setAutoCommit(true);
}
return state;
}
这篇关于Spring JDBC'选择更新'的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!