multiple use of the same ResultSet in Java

Question

Is it bad habit to use the same ResultSet object in Java? Why or why not? //It seems to be my only option here, as I am trying to query a table for its record count, and then query a view of that table; I don't know how to change the view.

In other words, is

ResultSet myResultSet = statement.executeQuery("SELECT count(*) FROM table");
myResultSet.next();
int recordCount = myResultSet.getInt(1);
myResultSet = statement.executeQuery("SELECT * FROM tableView");
//set other variables based on contents of fetched view

a bad idea?

Answer 1

The code you have posted does not "reuse the same ResultSet", it simply discards one ResultSet object and then uses the same variable to hold a reference to a different ResultSet created by the second executeQuery. It would be good coding practice to call close() on the first result set before you overwrite the reference with the second one, but this is not strictly necessary because

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

(from the ResultSet JavaDoc documentation, my bold)

Answer 2

Is it bad habit to use the same ResultSet object in Java?

I would say it depends on the particular requirement and way of coding .

I believe what you are looking for is a scrollable ResultSet which was introduced as a new and improved feature in Version 2.0/2.1 (integrated into JDK 1.2), which comes in two flavors : TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE.

If we see the code you have posted :

ResultSet myResultSet = statement.executeQuery("SELECT count(*) FROM table");
myResultSet.next();
int recordCount = myResultSet.getInt(1);
myResultSet = statement.executeQuery("SELECT * FROM tableView");

What you are basically doing is setting the myResultSet reference variable to a new ResultSet object. I don't see anything bad in that , perhaps , someone more enlightened than me can point out .