java jdbc 查询多结果,java jdbc访问多个结果集

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?

解决方案

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 JOINsin 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.