ResultSet更新行不起作用 [英] ResultSet update row is not working

问题描述

我想在遍历结果集时更新ResultSet中的raw.以下是我的代码

I want to update raw in my ResultSet while I looping through the result set. Following is my code

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:结果集不可更新.这个结果集 必须来自以以下结果集类型创建的语句 ResultSet.CONCUR_UPDATABLE，查询必须只选择一个表，可以 不要使用函数，必须从该表中选择所有主键. 有关更多详细信息，请参见《 JDBC 2.1 API规范》第5.6节. 结果集必须来自使用结果创建的语句 设置ResultSet.CONCUR_UPDATABLE的类型，查询必须仅选择一个 表，不能使用功能，必须从中选择所有主键 那张桌子.有关更多信息，请参见《 JDBC 2.1 API规范》第5.6节. 详细信息.

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.

推荐答案

正如stacktrace所说，您必须创建一条语句，使其结果集可更新:

As the stacktrace tells, you have to create a statement that allows its resultset to be updateable:

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

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

From the API of ResultSet (http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html):

默认ResultSet对象是不可更新的，并且具有只能向前移动的光标.因此，您只能迭代一次，并且只能从第一行到最后一行进行迭代.可以产生可滚动和/或可更新的ResultSet对象.以下代码片段(其中con是有效的Connection对象)说明了如何制作可滚动且对其他人的更新不敏感并且可更新的结果集.有关其他选项，请参见ResultSet字段.

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

这篇关于ResultSet更新行不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！