How to prevent a ResultSet from being invalidated on Connection close?

Question

I'd like to pass out a result set from a function that executes a query and closes the connection.

But the ResultSet gets invalidated as soon as its parent Connection is closed and throws "java.sql.SQLException: Operation not allowed after ResultSet closed". How to avoid this?

Answer 1

You can't. If you want to get all the data, loop the ResultSet and insert the data into a collection of yours.

Take a look at commons-dbutils - it has a lot of useful helpers.

Answer 2

That's just not how ResultSet works. It doesn't store the data itself, it just acts as an interface to it.

What you could try instead is having the class that's returning the ResultSets keep a list of the ones it has given out. Each piece of code that uses the ResultSet can call close() on it when it's done with it. Then the first class can check if all ResultSets that have been given out have been closed (using isClosed()), and if so, close the connection.

Answer 3

You want to turn your thinking inside out!

Create a function that will do something with the ResultSet, and pass it as a closure into the function that runs the query.

def withQuery[T](query:String)(template: ResultSet => T) : T = {
  val resultSet = runQuery(query)
  val ret = template(resultSet)
  resultSet.close
  ret
}

val output = withQuery("select * from ...") {
  resultSet =>
  //some expression that generates a string from the resultset
}

println(output)

Answer 4

Also consider using a disconnected result set: CachedRowSet. Quick googling gave me this.