Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Multiple queries on single statement

 
Denis Berezhnoy
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34178
340
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Saloon Keeper
Posts: 4010
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic