java jdbc访问多个结果集 [英] java jdbc accessing multiple resultsets

问题描述

我有以下结构：

列表 - > List_Participant - >参与者

列表可能包含几个参与者。我尝试在java中读取这个：

  stat = con.createStatement 
 ResultSet rs = stat.executeQuery（select * from list;）; 
 //获取括号表的信息
 while（rs.next（））{
 string name = rs.getString（Name）; 
 ResultSet rs2 = stat.executeQuery（select * from List_Participant where name ='+ name +';）; 
 
 while（rs2.next（））{
 //获取参与者
 
} 
 rs2.close（）; 
} 
 rs.close（）; 
  

但这不起作用。我没有收到异常或任何其他输出。我建议打开第二个结果集将关闭第一个结果集，因为我做第一个结果集，存储在一个数组列表中的数据，并关闭它，然后第二个它会工作，但是导致一个糟糕的性能，因为我必须总是搜索

$ b

这是什么可能是更好的解决方案？当前尝试：

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

如何修改列，因为它们可能具有相同的名称？

解决方案

您可以尝试使用两个不同的语句查询。请参见JavaDoc的 java.sql.Statement 。下面的例子显示了原理。

 语句statement1 = connection.createStatement（）; 
语句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（））{
 //获取参与者
} 
} 
  

但是：这不是JDBC或SQL的标准用法。它剥夺了数据库的任何优化可能性，并移动到数据库和你的应用程序之间的很多数据没有好的理由（见JohnSkeet和BalusC的意见）。

更好地使用适当的 JOIN 可以通过数据库优化此 ：

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

添加任何过滤器/条件以最小化检索的数据。 / p>

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.

这篇关于java jdbc访问多个结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！