Mark,
Here's an answer and an alternative.
Whenever you use a stmt.executeQuery method you change your stmt object. So
you'll need to create as many Statement objects as however many ResultSets you
plan to use at the same time.
However, as long as your database is on a decent server you should be better
off using a nested query. The database server should optimize your query for
you and execute the selection far faster than any loop code you wrote into
java. Remember too that in your code you are doing a database query for every
loop which has time consuming overhead.
I know that this is just a simplified query but I'd suggest just going the
straight forward route based off something like:
ResultSet rs = stmt.executeQuery("SELECT questionText, questionID"
+ "FROM Text"
+ "WHERE questionID ="
+ "(SELECT questionID FROM Questions)");
out.println("Question ID: " + rs.getString("questionID")
+ " Question Text: " + rs.getString("questionText"))
If your database server is smart enough you could just do the SQL statement:
SELECT Text.questionText, Text.questionID
FROM Text, Questions
WHERE Text.questionID = Questions.questionID
See if you can adapt your more complex query off of this but I would think that
this would be far faster than maintaining your own looping structer with
multiple database calls.
Matt Penner
Quoting Mark Weidner <[EMAIL PROTECTED]>:
> Hi Everyone,
>
> I'm looking to use nested resultsets to retrieve data.
> Basically, I
> need to retrieve data based on certain values retrieved by another
> resultset. The following code is a much simplified version of what I'd
> like
> to do. The real SQL statements are much more complex, and it would be
> a
> huge performance hit to attempt to retrieve all the data in one
> ResultSet.
> It is my understanding that executing a query on an existing statement
> deletes the references to any previous resultset. Therefore, copying
> the
> data from a first resultset to an object would work (Vector/Hashtable?),
> but
> there would be serious performance implications there as well. Any
> ideas?
> (I apologize....I come from the ASP world)
>
> Thanks.
>
> -Mark
>
> ---------------
>
> //Connection con has already been created
>
> Statement stmt = con.createStatement();
> ResultSet rsQuestions = stmt.executeQuery("SELECT questionID FROM
> Questions");
>
> //Loop through questions from first resultset
> while (rsQuestions.next()) {
>
> int ID = rsQuestions.getInt("questionID");
>
> //Lookup results from second resultset which uses paramaters
> from
> the first resultset
> ResultSet rsText = stmt.executeQuery("SELECT questionText FROM
> Text
> WHERE questionID=" + ID);
> String sText = rsText.getString("questionText");
> out.println("Question ID: " + ID + " Question Text: " + sText)
> }
>
> ___________________________________________________________________________
> To unsubscribe, send email to [EMAIL PROTECTED] and include in the
> body
> of the message "signoff SERVLET-INTEREST".
>
> Archives: http://archives.java.sun.com/archives/servlet-interest.html
> Resources: http://java.sun.com/products/servlet/external-resources.html
> LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
>
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
