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);
        }
    }

    // ...
}

Below method gets the application number from database which i need to use in the update query:

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;   
}

Below method gives id which is to be updated:

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

Code here I have written above code to read and update database but when I run the code I get this result set exhausted error I tried to solve this but no success.

Answer 1

You execute another query on stmt while iterating over a result set produced by stmt. As soon as you execute another query on the same statement object, any previous result sets created by that statement object are closed. So when you try use getString afterwards you get this error.

To fix this you need to use another statement object inside the loop (and make sure you have disabled auto-commit).

Also note that your current way of executing statements is unsafe: you are concatenating values into a query string. That leaves you open to SQL injection, instead you should use a prepared statement with parameters.