Spring JDBC'选择更新'

本文介绍了Spring JDBC'选择更新'的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下与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

选择用于更新" 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(); connmit(); } } } catch (SQLException e) { conn.setAutoCommit(true); e.printStackTrace(); } finally { conn.setAutoCommit(true); } return state; }