Ranga Rajan Ranga Rajan - 7 months ago 17
Java Question

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

Answer

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.