getResultSet() "should be called only once per result"

Question

According to the documentation for getResultSet in java.sql.Statement, it says:

Retrieves the current result as a ResultSet object. This method should be called only once per result.

Using some test code, I ran executeQuery() and several calls to getResultSet() and observed that the ResultSet returned pointed to the same object. So I'm guessing it is not returning a different ResultSet which you would need to close individually. But of course this could be unique to my JDBC drivers.

Looking at the documentation for ResultSet it says:

A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row.

This seems like a good reason why it may not be a good idea to call it multiple times as it may lead to some "gotcha" situation. If this was the sole reason, I felt that they could have just said that so I think there might be more to it than just this.

So does anyone know why one shouldn't call getResultSet more than once per result? This question is what made me curious in the first place.

Answer 1

The ResultSet object is an interface provided by Java JDBC -- they do not provide an implementation. Even though your particular database code and associated drivers implement ResultSet so that you can call it multiple times per result, if you depend on such behavior that is outside of the contract, you are certainly playing with fire.

One possible reason that the contract was written with the this method should be called only once per result line is for efficiency reasons. Building the ResultSet will most likely make a JDBC RPC call to the database and the authors of the JDBC specification wanted to discourage multiple round trips. They may not have wanted to force implementers to protect against multiple calls per result efficiently. Again, even though your database is protecting against that behavior does not mean the next one will.

Most ResultSet implementations also hold a connection to the database open so that when you get certain fields (such as large blobs) it can call back to the database to get the data. Having multiple connections open or (worse) using the same connection from multiple ResultSet objects would be very dangerous/confusing.

Also, they may have been worried about two parts of your code calling getResultSet() twice and were returned a reference to the same single unsynchronized object. This would cause confusion when next() was called and overwrote the object with multiple references.

I'm speculating of course but I hope this helps.