aspose file tools*
The moose likes JDBC and the fly likes Reusing Statement Object Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Reusing Statement Object" Watch "Reusing Statement Object" New topic
Author

Reusing Statement Object

Vineela Devi
Ranch Hand

Joined: Dec 20, 2003
Posts: 191
Hi All,

Please tell me whether this is possible or not:

ResultSet rs = stmt.executeQuery("Some Select Query.....");
while(rs.next()){

ResultSet rs1 = stmt.executeQuery("Second Select Query.....");

}


Regards,
Vineela
[ February 01, 2006: Message edited by: Vineela Devi ]
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

Have you tried it?

The answer is generally 'no'. Some JDBC driver implementations may support such a syntax, but it is far from standard. In the 'lucky' case that the first statement return 0 or 1 results (in which case it should be an 'if' statement not a 'while' loop), then it would not produce an error.

Where it is likely to produce an error is not on the second result set creation, but on the second call to rs.next().


My Blog: Down Home Country Coding with Scott Selikoff
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

BTW, please use a meaningful subject line for your posts in the future, since "Help pls" isn't very descriptive. For more information, consult the guide: http://faq.javaranch.com/view?HowToAskQuestionsOnJavaRanch
Vineela Devi
Ranch Hand

Joined: Dec 20, 2003
Posts: 191
Hi,

Iam not able to get what the problem will be on second call to rs.next().

What I feel is since I already have a handle to the first ResultSet ,using the same stmt object again may not give any error.

Correct me If Iam wrong.

Regards,
Vineela
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

I reckon what you feel and what java does ain't always the same thing. The second call to rs.next() produces the following error:

From the ResultSet API:

"A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results."

Therefore when you call the second query on the original statement object, you force the first result set to be closed. Any future calls to the first result set, will produce an error. This does not mean you cannot use the same statement with multiple queries and result set, it just means you have to read the results in the order you execute them. Scattered results, such as those in nested events must be read using separate statements.
[ February 01, 2006: Message edited by: Scott Selikoff ]
Michael Duffy
Ranch Hand

Joined: Oct 15, 2005
Posts: 163
Originally posted by Vineela Devi:
Hi All,

Please tell me whether this is possible or not:

ResultSet rs = stmt.executeQuery("Some Select Query.....");
while(rs.next()){

ResultSet rs1 = stmt.executeQuery("Second Select Query.....");

}


Regards,
Vineela

[ February 01, 2006: Message edited by: Vineela Devi ]


Not only is it not possible, it's probably not desireable. A good DBA who knows SQL well can probably write a JOIN query that will bring back all the data you need in one round trip with one Statement.

You're committing the "classic" sin of doing an (n+1) query. The query outside the loop returns n rows, and then you perform a network roundtrip evrey time you execute the loop. It'll perform like a pig if n is large, because network latency is the bottleneck.


%
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Reusing Statement Object