CODEWITHSUNDEEP
javamysqlsqljdbcresultset
SirVirgin
SirVirgin

Reputation: 153

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

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

Upvotes: 0

Views: 313

Answers (1)

sidgate
sidgate

Reputation: 15244

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.

Upvotes: 3

Related Questions