Trying To Query Database Using Preparedstatement, Resultset Is Coming Back Closed When It Ought Not To

I am trying to query my database using a prepared statement. Initially, I had this: public ResultSet preparedQueryContactsWhereAccount(String accountName) throws SQLException {

Solution 1:

It looks like your code is fine, however you are missing the rs.next() command. You are attempting to read a result set that has not been set to a specific row, and thus you are receiving an IllegalStateException.

Solution 2:

I recommend you don't do this.

Instead of returning your ResultSet, you should immediately loop through it and pull all the results out and hand them over to something else. It should then be closed.

Passing around things like Statements, ResultSets, and Connections is BOUND to cause a resource leak eventually, not to mention creating unneeded complexity in your code trying to avoid those leaks. They should all be opened, used, and immediately closed in a finally block to avoid any leaks. Anything returned from such a method should NOT be tied to some DB object with a lifecycle and associated resources.

You don't have to take this advice, but I truly believe your life will be easier if you do.

Solution 3:

Unless you've already tried them and they don't have the features you need, I recommend against using JDBC directly. Instead just get Hibernate, iBatis (retired, but still functional) or myBatis(follow on to iBatis) and let them handle the JDBC stuff. It is likely that these ORM tools will do a better job of managing the resources than a homespun project.