java - JDBC 和可更新的结果集

Question

我正在学习一些关于 JDBC 以及如何与数据库交互的知识。我了解了基础知识，但现在遇到了可更新结果集的问题。在本练习中，我必须检查数据库中每种啤酒的库存并向其添加 50。

所以这段代码确实有效，但不是一直有效。我已经运行了好几次，大约 50% 的时间股票实际上上涨了。为了检查这一点，我编写了另一个小的 while 循环，它打印一个包含必要数据的表格。有什么想法为什么它不能 100% 地工作吗？

数据库中有 1071 条记录，我在得到结果后立即运行了这段代码。我应该等待再运行它吗？

package stockbier;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class bierenstock {

public static void main(String[] args) {

try (Connection con = DriverManager.getConnection(
            "jdbc:mysql://noelvaes.eu/StudentDB", "student", "student123");
    PreparedStatement stmt = con.prepareStatement
        ("select * from Beers", 
        ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_UPDATABLE);) {

    ResultSet rs = stmt.executeQuery();
    while (rs.next()) {
            // edited int stock
        int stock = rs.getInt("Stock") + 50;
        rs.updateInt("Stock", stock);
        rs.updateRow();

    }

    rs.close();

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

}

Answer 1

尝试进行以下更改:

public static void main(String[] args) {

try (Connection con = DriverManager.getConnection(
        "jdbc:mysql://noelvaes.eu/StudentDB", "student", "student123");
PreparedStatement stmt = con.prepareStatement
    ("select * from Beers", 
    ResultSet.TYPE_SCROLL_SENSITIVE,
    ResultSet.CONCUR_UPDATABLE);) {

ResultSet rs = stmt.executeQuery();
while (rs.next()) {
    int stock = rs.getInt("Stock") + 50;
    rs.updateInt("Stock", stock);
    rs.updateRow();
}

} catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
} finally {
    if (stmt != null) { stmt.close(); }
}    
}    

ResultSet 对象的类型决定了它在两个方面的功能级别:操作游标的方式，以及 ResultSet 对象如何反射(reflect)对基础数据源所做的并发更改。

TYPE_SCROLL_INSENSITIVE:

The result can be scrolled; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position. The result set is insensitive to changes made to the underlying data source while it is open. It contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.

TYPE_SCROLL_SENSITIVE:

The result can be scrolled; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position. The result set reflects changes made to the underlying data source while the result set remains open.

引用Updating ResultSet了解更多详情。