File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Multiple queries on single statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Multiple queries on single statement" Watch "Multiple queries on single statement" New topic

Multiple queries on single statement

Denis Berezhnoy

Joined: Mar 18, 2006
Posts: 6
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,
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33117

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3753

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.

[OCA 8 Book] [Blog]
Denis Berezhnoy

Joined: Mar 18, 2006
Posts: 6

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,
I agree. Here's the link:
subject: Multiple queries on single statement
It's not a secret anymore!