So I'm writing a quick app to compare to large data sets. As they are large I put them into an H2 database figuring this would be an easy and quick solution. Headaches later, I've run into a problem where I can query one table but using that the ResultSet to query another ResultSet leads me to the following error:
org.h2.jdbc.JdbcSQLException: The object is already closed
This occurs in the outer while loop of the code below.
Is there something I'm missing about JDBC that is causing it to close one ResultSet as soon I create another ResultSet?
Your problem maybe because you are reading from the same column more than once and the JDBC driver for your database doesn't support multiple reads. Try assigning the values to a local variable and using the variable to print out the value and create the query. The Java tutorial on result sets says "For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once."
Yeah, what you're missing is where the documentation for the Statement interface says
The API docs wrote:By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.
Is there any reason you can't just do this in a single SQL statement? Matching, sorting and filtering data is what SQL is for.
No more Blub for me, thank you, Vicar.
posted 5 years ago
This is why I've loved this place for so many years. Direct response in addition to other ways to think about implementation.
@Paul: Makes perfect sense.
@Chris: In all honesty, complexity, laziness, and this application is being built for this one time use. Plus I'm doing this all in memory at the moment using H2 and I'm trying to mitigate the H2 learning curve without changing to using a file.