java - 结果集更新行不起作用

Question

我想在循环遍历结果集时更新结果集中的原始数据。以下是我的代码

try {
    String query="SELECT * FROM smsmessage WHERE recipient = ? and sent_status = 'pending' LIMIT ? ";
PreparedStatement prepStmt = conn.prepareStatement(query);
    prepStmt.setString(1,shortCode);
    prepStmt.setInt(2, Integer.parseInt(batchSize));
    ResultSet rs=prepStmt.executeQuery();
    while (rs.next()) {

        //update the selected message sent status to "sent" from "pending"
        rs.updateString("sent_status","sent");
        rs.updateRow();
    }

} catch (SQLException e) {
    log.error("MySQL exception",e);
}

这应该是什么原因？

出现以下错误

com.mysql.jdbc.NotUpdatable: Result Set not updatable. This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.

Answer 1

如堆栈跟踪所示，您必须创建一个允许其结果集可更新的语句:

PreparedStatement prepStmt= conn.prepareStatement(query,
    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

来自 ResultSet ( http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html ) 的 API:

A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. The following code fragment, in which con is a valid Connection object, illustrates how to make a result set that is scrollable and insensitive to updates by others, and that is updatable. See ResultSet fields for other options.

   Statement stmt = con.createStatement(
                                  ResultSet.TYPE_SCROLL_INSENSITIVE,
                                  ResultSet.CONCUR_UPDATABLE);
   ResultSet rs = stmt.executeQuery("SELECT a, b FROM TABLE2");
   // rs will be scrollable, will not show changes made by others,
   // and will be updatable