Java: ResultSet closing strategy, apart from closing it in finally

Question

I am facing ORA-01000: maximum open cursors exceeded although I am closing the resultsets in finally block. But I suspect there is some trouble with my legacy code, below is my pseudo-code

while (someCondition) {
rs1=executePreparedStatementNew(query1,param1,""); 
//do something with rs1
rs1=executePreparedStatementNew(query2,param2,""); 
}

1. If the loops runs 5 times, how many cursor will be opened by this code ?
2. If I close rs1 in finally, how many cursors will be closed, some people say that rs1 instance for query1 will not be closed as it is masked by query2 instance.
3. Does resultsets really gets masked this way , if so, how to ensure that all the instances are closed.

Appreciate any help.

Answer 1

You haven't said where your finally block is, but if it's outside the while loop, then yes you will have unclosed result sets. The rs1 variable will refer to the "latest" result set fetched - so that's the only one which will be closed. There's nothing magical going on here - it's just the normal behaviour of variables.

I would suggest that you separate each "fetch result set and use it" case into its own method, and close the result set in a try/finally block within that method. That will make it fairly clear what's going on.