DB connection close monitor

Question

I have the following use case.

Our client app will make an API(this is me) call which returns a java resultset after executing a db call to the client app. I would like to know when the clients are done using the resultset so that I can close the resultset an the connection associated with it. So the design I thought was to send the resultset wrapped in an new object which has behaviors to close the resultset and the connection associated with it. This cleanup method has to be invoked by the client. Now, due to some reason if this cleanup method was never invoked by the client application, then we are going to endup with an open connection and un-freed DB resources. Is there a way to some how better monitor this and make sure all the DB resource are freed promptly. Additionally if there is any design pattern that might help in this use case please let me know.

Thanks.

Answer 1

one way would be to clean up the resultset/connection from the object using phantom references (I would prefer that over using finalize) - this way, you should be able to clean up the object and simply return it to the pool. Depending on your use case, you could look at timeouts as well.

Also, agreed with the other suggestion about not passing resultsets if possible.
hope it helps.

Answer 2

Never return an expensive DB resource like a ResultSet to outside the API where you lose all the control. Instead map it to a List<Map<String, Object>> or something reuseable and return it.