File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC 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
Bookmark "Multiple queries on single statement" Watch "Multiple queries on single statement" New topic
Author

Multiple queries on single statement

Denis Berezhnoy
Greenhorn

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,
Denis
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31079
    
163

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

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.


My Blog: Down Home Country Coding with Scott Selikoff
Denis Berezhnoy
Greenhorn

Joined: Mar 18, 2006
Posts: 6
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Multiple queries on single statement