6

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.

Community
  • 1
  • 1
nevets1219
  • 7,692
  • 4
  • 32
  • 47
  • 1
    Not posting this as an answer because it's only a hunch, but I suspect it's this way to give some leeway to JDBC driver developers, allowing them to not define what happens when you call it more than once. In your particular case it works, but if you ever switched JDBC drivers it might not. – Mike Daniels Apr 02 '12 at 22:19
  • 1
    I would think it is just as simple as that: the ResultSet object has state. There is only one ResultSet object as a member of the Statement. Getting the object does not re-execute the statement. Getting the object a second time is not guaranteed to be in the same state as the first get if you modified something after the first get. So it is just a caution. Looking in the Postres jdbc driver (org.postgresql.jdbc2.AbstractJdbc2Statement), we can see that internally it makes many calls like this: return (result != null && result.getResultSet() != null); – Glenn Apr 03 '12 at 01:08

1 Answers1

4

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.

Gray
  • 115,027
  • 24
  • 293
  • 354
  • Definitely helpful! If I remember correctly, closing the `ResultSet` does not close any potential blobs created. I'm a bit confused about the 3rd paragraph regarding multiple connections. Wouldn't the `ResultSet` use the same `Connection` to retrieve any data from the database? Also, I'm unclear why the same `Connection` from multiple `ResultSet` is bad - I'm sure there's something obvious I'm overlooking. Additionally, if you can provide an example (even if it's for a specific JDBC drivers) of where calling `getResult` twice would be problematic I think it would be the perfect answer. – nevets1219 Apr 03 '12 at 21:41
  • Re the 3rd paragraph, I'm just saying that connection management is a problem. Having multiple copies of the `Connection` object or if two threads each had a `ResultSet` and were doing operations on the same unsynchronized `Connection` it would be dangerous. – Gray Apr 03 '12 at 21:46
  • In terms of providing an example, I don't have the time for that. This is about violation of the contract. Postgres might upgrade their driver and change the behavior slightly but still fulfill the contract and your code would break if you were depending on multiple calls to work. It's a matter of principle. – Gray Apr 03 '12 at 21:47