SQLException:java.sql.SQLException: Operation not allowed after ResultSet closed, While ResultSet never closed

Question

What I know-we cannot call ResultSet after this has been closed.and this is a good practice to close every ResultSet,StatementandConnection after using this.

But in my code I never closed connection then why there is-

java.sql.SQLException: Operation not allowed after ResultSet closed

my code is as follows:

Edited-

try{
        Connection con=CommonUtil.getConnection();
        Statement st=con.createStatement();

        ResultSet rs=st.executeQuery("select * from logirecord");
        int flag=0;
        while(rs.next()){
            if(rs.getString(2).trim().equals(username)&&rs.getString(3).trim().equals(password)){
                flag=1;

                ResultSet rs1=st.executeQuery("select * from personrecord where LoginId='"+ rs.getString(1).trim()+"'");
                if(rs1.next()){
                String name=rs1.getString(1);
                String address=rs1.getString(2);
                String hobby=rs1.getString(4);
                }
                //Exception here.
                ResultSet rs2=st.executeQuery("select * from interest where LoginId='"+rs.getString(1).trim()+"'");
                //at com.org.test.LoginServlet.doPost(LoginServlet.java:49)
                if(rs2.next()){
                String interest=rs2.getString(2);
                String interest2=rs2.getString(3);
                String interest3=rs2.getString(4);
                }
                request.getRequestDispatcher("display.jsp").forward(request, response);
                return;
            }
        }   
    }catch(Exception e){
        e.printStackTrace();
    }

@DarshanLila it is giving data i tested,let assume this have null result still why resltset closed exception?This should not be. — , Jun 05 '15 at 10:27
@AbishekManoharan console never shows line number in case of resulsetclose exception.we have to take take,from where this can be arise. — , Jun 05 '15 at 10:29
instead of `System.out.println(e);` in your catch block, use `e.printstacktrace()`. It will print the stacktrace to the console. — Codebender, Jun 05 '15 at 10:32
You have to check whether something is returned in the second and third query as, if (rs1.next()) { String name=rs1.getString(1); String address=rs1.getString(2); String hobby=rs1.getString(4); } — Rajesh Balan, Jun 05 '15 at 10:33
@RajeshBalan right,but this is not cause of resultset closed exception,this can cause NPE — , Jun 05 '15 at 10:35
@dubey-theHarcourtians, that's not true -- if the initial next() call moves the pointer to the last record (in case your query returned no records ) also, you might get that. ResultSet object will never be null even if you have "no records". So, add the if-condition for the next() call and try. Also suggest to get the value to a variable and use that instead of the rs.getString() calls — Rajesh Balan, Jun 05 '15 at 10:41
I used this and set in requst and forward to next source,but not added complete code,since this is only code related with my excepttion,yes right ResultSet object will never be null even if you have "no records". but if u do resulSet.next().getString(1) cause NPE. — , Jun 05 '15 at 10:45
You cannot have multiple result sets open from the same statement object. When you execute a new statement using `st`, any previously opened result set from that same statement is **closed**. And if your connection is auto-commit true, then executing another statement may close result sets from other statements (exact behavior varies between JDBC driver, as some default to close on commit, while others are holdable over commit). — Mark Rotteveel, Jun 05 '15 at 11:04
@MarkRotteveel this is fix of my problem.thats why calling `rs.getString()` after crating `rs1` cause resulset exception. — , Jun 05 '15 at 11:05

Spark-Beginner · Answer 1 · 2015-06-05T10:57:16.903

You have several ResultSet pertaining to the same Statement object concurrently opened. (see here - http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html):

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

And you are not closing rs that make it more worst.

score 0 · Accepted Answer · answered Jun 05 '15 at 10:31

0

Well you are getting

java.sql.SQLException: Operation not allowed after ResultSet closed

Because you are trying to traverse a result set while traversing another result set of same connection.

If you're nesting the processing of two result sets from the same database, you're doing something wrong. The combination of those sets should be done on the database side.

answered Jun 05 '15 at 10:31

Darshan Lila

5,772
2
24
34

Although that happens with autocommit true, the problem is more specific: traversing three result sets of **the same statement**. – Mark Rotteveel Jun 05 '15 at 10:59

SQLException:java.sql.SQLException: Operation not allowed after ResultSet closed, While ResultSet never closed

2 Answers2