JDBC : Operation not allowed after ResultSet closed

Question

This code is showing this error message when the button is clicked:

Operation not allowed after resultset closed

Code:

case 4:
                BufferedReader choosest=new BufferedReader(new InputStreamReader(System.in));
                System.out.print("enter your id");
                int idst=Integer.parseInt(choosest.readLine());
                String SQL = "SELECT * FROM student";
                ResultSet ps = stmt.executeQuery(SQL);
                while (ps.next()) {
                    int iddb = ps.getInt("id");
                if(idst==iddb) {
                    BufferedReader lessonuser = new BufferedReader(new InputStreamReader(System.in));
                    System.out.print("choose lesson ");
                    String lest =lessonuser .readLine();
                    String SQL1 = "SELECT * FROM lesson";
                    ResultSet qs = stmt.executeQuery(SQL1);
                    while (qs.next()) {
                        String ledb = ps.getString("namel");
                        if (lest == ledb) {
                            String insertTableSQL1 ="INSERT INTO chooselesson (id, lesson)  VALUES ("+idst+",'"+lest+"')";
                            stmt.executeUpdate(insertTableSQL1);
                            System.out.print("your unit sucssesfully add ");

                        }
                        }

                    }
                    }

https://stackoverflow.com/questions/10933399/operation-not-allowed-after-resultset-closed — Suraj Rao, Apr 28 '18 at 05:06
you are executing the new statement inside the loop, i think that will invalidate your result set `qs` cursor. You use the statement when you are looping over cursor. — NishanthSpShetty, Apr 28 '18 at 05:06
Duplicate of : https://stackoverflow.com/questions/5840866/getting-java-sql-sqlexception-operation-not-allowed-after-resultset-closed — Rakesh Soni, Apr 28 '18 at 11:20

John · Answer 1 · 2018-04-28T05:12:45.677

0

ResultSet is closed when you make another query. For this code, you could first collect all data of ps. And after that iterate the result to perform other querys.

And I have to say making query in loops is bad performance operation. There are lots ways to avoid this. For example you could fetch all student records as well as all lessons records, each in one query. Then use lessonuser to find out what records you should insert into chooselesson table, and run the insert SQL in batch mode.

edited Apr 28 '18 at 05:12

answered Apr 28 '18 at 05:06

John

1,654
1
14
21

thanks.do you have any suggest to fix it ? – reihane Apr 28 '18 at 05:20

score 0 · Answer 2 · answered Apr 28 '18 at 05:21

The problem is at the line ResultSet qs = stmt.executeQuery(SQL1); .Using statement to execute the other query closes the previous ResultSet. Modifying the above code to might work, I didn't test the solution but it should generally work.

BufferedReader choosest=new BufferedReader(new InputStreamReader(System.in));
            System.out.print("enter your id");
            int idst=Integer.parseInt(choosest.readLine());
            String SQL = "SELECT * FROM student";
            ResultSet ps = stmt.executeQuery(SQL);
            while (ps.next()) {
                int iddb = ps.getInt("id");
            if(idst==iddb) {
                BufferedReader lessonuser = new BufferedReader(new InputStreamReader(System.in));
                System.out.print("choose lesson ");
                String lest =lessonuser .readLine();
                String SQL1 = "SELECT * FROM lesson";
                ResultSet qs = stmtSelect.executeQuery(SQL1);
                while (qs.next()) {
                    String ledb = ps.getString("namel");
                    if (lest == ledb) {
                        String insertTableSQL1 ="INSERT INTO chooselesson (id, lesson)  VALUES ("+idst+",'"+lest+"')";
                        stmtInsert.executeUpdate(insertTableSQL1);
                        System.out.print("your unit sucssesfully add ");

                    }
                    }

                }
                }

PreparedStatement is better choice for performance, https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

JDBC : Operation not allowed after ResultSet closed

2 Answers2