java jdbc accessing multiple resultsets

Question

I have the following structure:

List -->List_Participant -->Participant

so a list may contain several participants.I try to read this in java:

        stat = con.createStatement();
        ResultSet rs = stat.executeQuery("select * from list;");
        // get the informations about the bracket sheet
        while (rs.next()) {
          string name = rs.getString("Name");
          ResultSet rs2 = stat.executeQuery("select * from List_Participant where name= '"+name+"';"); 

            while (rs2.next()) {
               // get the participants

            }
            rs2.close();
        }
        rs.close();

But this does not work. I don't receive an exception nor any other output. I suggest opening a second resultset will close the first one because since I do the first resultset, store the data in an arraylist and close it and afterwards the second it would work, but that leads to a poor performance because I have to search always in the arraylist.

What might be a better solution?

Edit: Solution is to make a Join, my current try:

 select * from List_participant 
INNER JOIN List ON List.name = List_participant.List 
INNER JOIN participant ON List_participant.participant =participant.ROWID;

How do I adress the columns now, since they might have the same name?

Any reason you're not doing a join at the database? Seems like the obvious solution. — Jon Skeet, Dec 20 '11 at 17:45
You should delegate the job as much as possible to the SQL language so that it returns **exactly** the result you want in only one SQL query. This is a win-win for both Java and the DB. Learn the `JOIN` clause. — BalusC, Dec 20 '11 at 18:05
@JonSkeet: indeed. I was just not used to SQL. How would I make two Joins at once? So that I get all participants in all lists. I updated the answer with my status quo. Jon please write your comment as answer, so I can mark it! — Anthea, Dec 20 '11 at 18:40

score 10 · Accepted Answer · edited May 10 '22 at 20:44

You can try using two different Statement instances for each query. See the JavaDoc for java.sql.Statement. The following example shows the principle.

Statement statement1 = connection.createStatement();
Statement statement2 = connection.createStatement();

ResultSet resultSet1 = statement1.executeQuery("select * from list");
while (resultSet1.next()) {
    String name = resultSet1.getString("Name");
        
    ResultSet resultSet2 = statement2.executeQuery(
        "select * from List_Participant where name= '" + name + "'");
    while (resultSet2.next()) {
        // get the participants
    }
}

BUT: This is not standard usage of JDBC or SQL for good reasons. It deprives the database of any optimization possibility and moves to much data between the DB and your app for no good reason (See the comments of JohnSkeet and BalusC).

Better use appropriate JOINs in your one and only statement. This can be optimized by the DB:

SELECT lp.* FROM list l JOIN List_Participant lp ON l.name = lp.name

Add any filters/conditions you like to minimize the data retrieved.

This is the right answer. You should expand it a bit more with some sample code and/or a link or two. Here's a good link for example: http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html — Gray, Dec 20 '11 at 18:45
As pointed by @Gray, Java 6 API Statement allows it as: `Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects` — ATorras, Feb 29 '16 at 17:42

score 1 · Answer 2 · edited May 12 '23 at 23:05

Here is the reason why you can't have two ResultSet opened from the same Statement

ResultSet javadoc :

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.

So basicly, a Statement can only give you one ResultSet at a time, so you loose the first result when you execute the second query.

Solution :

Using one instance of Statement per ResultSet needed.
merging the queries to only have one

score 0 · Answer 3 · answered Dec 20 '11 at 17:50

0

Nested query ? Something like Select * from List_Participant where name in (select name from List); This can be made to work for your third tabel as well.

answered Dec 20 '11 at 17:50

ping

1,229
3
21
41

java jdbc accessing multiple resultsets

3 Answers3

Linked