First, how many open ResultSets can we have on single Statement object? I have read that it depends on JDBC driver. If JDBC driver employs ODBC bridge then we can have only one open ResultSet. If new query is executed then previous ResultSet is closed. Is it true? Does JDBC spec specifies anything about it?
Second, does each result set corresponds to own cursor in DB?
Back when I was starting JDBC I used to open many result sets, often nested, such as how you would have 2-3 levels for loops, but in this case with statements and result sets. Not only was the code hard to maintain, but it was confusing to read data from the database, unsafe, and did not perform well. As I developed though, I learned how to write everything I want in a single statement at a time, either by writing more complex/better queries that utilize joins (and indexes), or storing the information from queries in memory. Often times, the information you want from each query does not take up much memory, such as a list of ids. Even if the query itself does a lot of work, storing the data in memory is rarely an issue these days.
I checked Java API doc it says: 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.