JDBC:结果集关闭后不允许操作[重复]

Question

单击按钮时，此代码显示此错误消息：

结果集关闭后不允许操作

代码：

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

                        }
                        }

                    }
                    }

Answer 1

问题出在ResultSet qs=stmt行。executeQuery（SQL1）。使用语句执行另一个查询将关闭上一个结果集。修改上面的代码可能会起作用，我没有测试解决方案，但它通常会起作用。

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是更好的性能选择，https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

Answer 2

当您进行另一个查询时，ResultSet将关闭。对于此代码，您可以首先收集ps的所有数据。然后迭代结果以执行其他查询。

我不得不说，在循环中进行查询是一种性能很差的操作。有很多方法可以避免这种情况。例如，您可以在一个查询中获取所有学生记录以及所有课程记录。然后使用lessonuser找出应该插入到chooselesson表中的记录，并以批处理模式运行insertSQL。