Multiple queries on single statement

[Denis Berezhnoy]

Hi guys!

I am newbie in JDBC and have couple questions.

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?

Can you please help to clarify these questions?

Best regards,
Denis

[Jeanne Boyarsky]

Denis,
It is good practice to have only one resultset open on a statement at any point in time. That way your code will work with all drivers.

[Scott Selikoff]

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.

[Denis Berezhnoy]

Hi,

Thanks for your answers! I got it.

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.


Best regards,
Denis