Fetching two ResultSet values empties both ResultSets even with different Statement objects

Question

I have tried googling and looked at these threads on SO:

java jdbc accessing multiple resultsets
Open two resultsets simultaneously
JDBC returning empty result set
JDBC returns an empty ResultSet (rs.isBeforeFirst() == true) although the table isn't empty

and they're all quite irrelevant.

private void searchStocks(){
        rs=stmt.executeQuery("select * from table1;");  //ResultSet rs;  Statement stmt; ****LINE 1
        rs2=stmt2.executeQuery("select * from table2;"); //ResultSet rs2; Statement stmt2; ****LINE 2
        while (rs.next()){    //next() method returns false 
            while(rs2.next()){

            }
        }
    }

Here rs is empty if LINE 2 is executed. however, if comment out LINE 2 then rs has values. I am using two different Statement objects so rs is not closed when LINE 2 executes. (stmt for rs and stmt2 for rs2)

I am using the same Connection object for both. What am I doing wrong? Is there a way to do this without using SQL JOINs?

(Here's the declaration should you need it)

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","test");
Statement stmt=con.createStatement();
Statement stmt2=con.createStatement();

UPDATE
I have also tried using different Connection objects to the same database:

            con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","test");            
            con2=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","test");            
            stmt=con.createStatement();
            stmt2=con2.createStatement();

No results again.

UPDATE 2
(In response to @sidgate 's comment)

private ResultSet rs=null;
private ResultSet rs2=null;

UPDATE 3
(The mcve version of the Entire method where things went wrong; The actual identifier names I've used in my code are left unchanged but were changed in above fragments for the sake of convenience of reader)

private void searchStocks(){        
    String query=jTextField8.getText().trim();

    try {            
        if (query.equals("Search for stocks")||query.length()==0){
            rs=stmt.executeQuery("select * from masterstocks;");
            rs2=stmt2.executeQuery("select * from userstock_"+getUsernameFromEmail(loginEmail)+";");
        }                
        else{
            rs=stmt.executeQuery("select * from masterstocks where name like \"%"+query+"%\" or symbol like \"%"+query+"%\";");                
            rs2=stmt2.executeQuery("select * from userstock_"+getUsernameFromEmail(loginEmail)+" where name like \"%"+query+"%\" or symbol like \"%"+query+"%\";");
        } 
        while (rs.next()){   
            //stuff                     
            if (jCheckBox3.isSelected()){
                rs2.beforeFirst();
                while(rs2.next()){
                    //stuff
                    }
                }
            }
        }
    } catch (SQLException ex) {
        ex.printStackTrace();
        showSQLError();
    }
}

are you sure this is your only code? one thing is RS initialization is missing. can you please share the complete code? — sidgate, May 08 '16 at 15:48
@sidgate The complete code is around 2500 lines... however check update for `rs` initialization — SirVirgin, May 08 '16 at 15:55
these bits and pieces won't help. I sure there is more going around the lines of code you have pasted. — sidgate, May 08 '16 at 16:06
While we greatly appreciate your not posting a ~2500-line "code dump", we also cannot glean useful information from tiny fragments of code. That's why you need to create and post a [mcve] that illustrates the issue. — Gord Thompson, May 08 '16 at 16:18
@RangaRajan and now that you have an if-else statement, what is the value of `query` variable? does it create the right SQL which returns some result? — sidgate, May 08 '16 at 16:23
@sidgate query can have any String value, but that's not the problem, because in both cases of the if-else ladder, there's the same result. And there's no problem if I don't get values for rs2. If I comment out `rs2=stmt2.executeQuery("select * from userstock_"+getUsernameFromEmail(loginEmail)+";");` it's all fine — SirVirgin, May 08 '16 at 16:26
hmm.. now I have to ask you what goes in `getUsernameFromEmail`. I am sure the resultset variable is getting reused in that method — sidgate, May 08 '16 at 16:31
@sidgate How could I have been so dumb. that was the problem. post an answer so i can mark it — SirVirgin, May 08 '16 at 16:38
*"How could I have been so dumb"* - Hey, it happens to all of us now and again. However, if you had tried to create a [mcve] with code [like this](http://pastebin.com/y2ZRpUXR) it probably would have pointed you in the direction of finding your mistake (because that MCVE code works fine). Reducing the scope and simplifying the problem is an important part of the troubleshooting process. — Gord Thompson, May 08 '16 at 16:55

score 3 · Accepted Answer · answered May 08 '16 at 16:42

ResultSet variable should be used for single query in its scope. As per your question, the ResultSet variable is defined at class level and reused at two different methods searchStocks and getUsernameFromEmail. To avoid this, define the variables within the method scope.

Fetching two ResultSet values empties both ResultSets even with different Statement objects

1 Answers1