ResultSet usage

[Dan Parsons]

Is it okay to keep creating queries with the same ResultSet Object reference (results)? Here is what I am currently using in my Database statements with Oracle and everything works but was wondering if this is okay where I am using ResultSet results for 3 queries in my Tomcat container:

ResultSet results = null;
Statement statement = null;
......
        statement = connection.createStatement();
        results = statement.executeQuery("select name from tableone");
        if(results.next())
        {
             int myvar = ....
        }
 
        results = statement.executeQuery("select food, sugar from tabletwo");
        if(results.next())
        {
             int myvar2 = ....
        }
 
        results = statement.executeQuery("select rock from tablethree");
        if(results.next())
        {
             int myvar3 = ....
        }
 
        ...
        //close results, statement and connection object references

[Paul Clapham]

Sure. It's only a variable, so you can assign as many different objects as you like to it. It's the objects you have to worry about, not the variables. Which is why your code hinted at by "//close results..." is in the wrong place. When you finish using the first ResultSet object, you should close it before you lose the reference to it.

[Dan Parsons]

Thanks, I had to change it to where I create 3 different ResultSets with the same Statement object and closing the 3 different ResultSet objects in a finally block. Here is what I am currently using in my Database statements with Oracle and everything works great. But I am wondering if this will create Database resource leakages or other issues:

.....
ResultSet results1 = null;
ResultSet results2 = null;
ResultSet results3 = null;
Statement statement = null;
......
        statement = connection.createStatement();
        results1 = statement.executeQuery("select name from tableone");
        if(results1.next())
        {
             int myvar = ....
        }
 
        results2 = statement.executeQuery("select food, sugar from tabletwo");
        if(results2.next())
        {
             int myvar2 = ....
        }
 
        results3 = statement.executeQuery("select rock from tablethree");
        if(results3.next())
        {
             int myvar3 = ....
        }
 
        ...
        //Finally block here that closes results1, results2, results3, statement and connection object references