java.sql.SQLException : Exhausted Resultset

Question

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

public class ReadUpdateDb {

    public void updateDb(Statement stmt) {
        try {
            ArrayList<String> inner = null;
            ResultSet rs = null;
            ResultSet up = null;
            ResultSet mp = null;
            //up = stmt.executeQuery("SELECT application_no FROM ng_lms_rsystems_interface");
            //rs = stmt.executeQuery("SELECT * FROM ng_lms_rsystems_interface");
            rs = stmt.executeQuery("SELECT application_no,ucic_id,validation_flag FROM ng_lms_rsystems_interface");
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnsNumber = rsmd.getColumnCount();
            while (rs.next()) {
                inner = new ArrayList<String>();
                for (int i = 1; i <= columnsNumber; i++) {
                    inner.add(rs.getString(i));
                    String p = rs.getString(i);
                    if (p == null) {
                        System.out.println(p);
                        up = stmt.executeQuery("SELECT application_no FROM ng_lms_rsystems_interface");
                        ReadUpdateDb c = new ReadUpdateDb();
                        long rc = c.update();
                        System.out.println("done4");
                        String updt = c.getApplicationNo(up);
                        System.out.println("done7");
                        mp = stmt.executeQuery("UPDATE ng_lms_rsystems_interface SET ucic_id = '" + rc + "', validation_flag = 'Y' WHERE APPLICATION_NO = " + updt + "");
                    }
                }

                // outer.add(inner);
                    /*int n = (rs).getInt(1);
                    String p = rs.getString(2);
                    String q = rs.getString(3);
                    System.out.println(n+"    "+p+"   "+q);
                    }*/
                //for(String obj:inner) { 
                //  System.out.println(obj);
                //}
            }
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    // ...
}

下面的方法从数据库获取我需要在更新查询中使用的应用程序编号:

public String getApplicationNo(ResultSet up) throws SQLException {
    System.out.println("done1");
    ArrayList<String> inner = null; 
    String n = null;
    ResultSetMetaData rsmd = up.getMetaData();
    int columnsNumber = rsmd.getColumnCount();
    while(up.next()) {
        inner = new ArrayList<String>(); 
        for(int i = 1; i <= columnsNumber; i++){
        inner.add(up.getString(i));        
        //String p = up.getString(i);
    }
    //inner = new ArrayList<String>();
    //System.out.println("done2");
    //inner.add(up.getString(1));   
    //System.out.println("done3");
    int count = 0;
    //String n = up.getString(1);

    for(int i = 1; i <= count; i++) {
        n = inner.get(i);
    }

    System.out.println("done");
    return n;   
}

下面的方法给出了要更新的id:

public long update() {
    long t1 = 4466880011L;
    System.out.println("done9");
    return t1;
}       

这里的代码我已经编写了上面的代码来读取和更新数据库，但是当我运行代码时，我收到此结果集耗尽错误，我尝试解决此问题但没有成功。

Answer 1

您在迭代 stmt 生成的结果集时对 stmt 执行另一个查询。一旦对同一语句对象执行另一个查询，该语句对象创建的任何先前结果集都会被关闭。因此，当您随后尝试使用 getString 时，您会收到此错误。

要解决此问题，您需要在循环内使用另一个语句对象(并确保您已禁用自动提交)。

另请注意，您当前执行语句的方式是不安全的:您正在将值连接到查询字符串中。这会让您对 SQL 注入(inject)持开放态度，而您应该使用带有参数的准备好的语句。