JDBC-ResultSet 在 while-Loop 中关闭 [英] JDBC-ResultSet is closed in while-Loop

问题描述

我在使用 ResultSet 时遇到了非常糟糕的情况，它在迭代此 ResultSet 的 while 循环中关闭.我知道 ResultSet 关闭的确切行，但我不知道为什么.

I'm having a really bad time with a ResultSet, which is closed within a while-Loop for iterating this ResultSet. I have know the exact line in which the ResultSet is closed, but i have no idea why.

    public LinkedList<Athlet> alleAbrufen () throws SQLException {
        LinkedList<Athlet> alleAthleten = new LinkedList<Athlet>();
        String abrufenAthleten = "SELECT * FROM Athlet ORDER BY athlet_id";
        ResultSet athleten_rs = stmt.executeQuery(abrufenAthleten);
        while (athleten_rs.next()) {
            long id = athleten_rs.getInt(1);
            String name = athleten_rs.getString(2);
            LinkedList<Leistung> alleLeistungen = alleAbrufen((int) (id)); //after this line the ResultSet gets closed
            alleAthleten.add(new Athlet(id, name, alleLeistungen));
        }
        return alleAthleten;
    }

    public LinkedList<Leistung> alleAbrufen(int athlet_id) throws SQLException {
        LinkedList<Leistung> alleLeistungen = new LinkedList<Leistung>();
        String selectLeistungen = "SELECT * FROM Leistung WHERE athlet_id="+athlet_id;
        ResultSet rs = stmt.executeQuery(selectLeistungen);
        while (rs.next()) {
            long id = rs.getInt(1); 
            String bezeichnung = rs.getString(2);
            String datum = rs.getString(3);
            double geschwindigkeit = rs.getDouble(4);
            boolean selectedForSlopeFaktor = rs.getBoolean(5);
            int strecke_id = rs.getInt(7);
            long longAthlet_id = (long) athlet_id;
            Leistung leistung = new Leistung(strecke_id, longAthlet_id, bezeichnung, datum, geschwindigkeit);
            leistung.setLeistungID(id);
            leistung.setIsUsedForSlopeFaktor(selectedForSlopeFaktor);
            alleLeistungen.add(leistung);
        }
        return alleLeistungen;
    }

我用注释标记了 ResultSet 结束后的那一行.Alle 上面例子中使用的其他方法、构造函数等都经过测试可以正常工作.有谁知道为什么调用第二个方法会关闭第一个方法中的 ResultSet?

I marked the line after which the ResultSet is closed with a comment. Alle other methods, constructors, etc used in the above example are tested an working properly. Does anyone have a clue why calling the second method closes the ResultSet in the first method?

推荐答案

问题是Statement 只能维护一组ResultSet 的每个执行的语句.由于您为两个方法共享相同的 Statement stmt，在 alleAbrufen 中，Statement 执行另一个语句，这将破坏对先前 <代码>结果集.

The problem is that the Statement can only maintain a single group of ResultSets per executed statement. Since you share the same Statement stmt for your two methods, in alleAbrufen the Statement executes another statement, which will break the reference to the prior ResultSet.

这种情况的最佳解决方案是为每个语句执行创建一个 Statement.也就是说，每个方法都应该包含其唯一的Statement 和相关的ResultSet.

The best solution for this case is to create a Statement per statement execution. This is, every method should contain its unique Statement and related ResultSets.

public LinkedList<Athlet> alleAbrufen () throws SQLException {
    LinkedList<Athlet> alleAthleten = new LinkedList<Athlet>();
    String abrufenAthleten = "SELECT * FROM Athlet ORDER BY athlet_id";
    //here
    Statement stmtAlleAbrufen = con.createStatement();
    ResultSet athleten_rs = stmtAlleAbrufen.executeQuery(abrufenAthleten);
    while (athleten_rs.next()) {
        long id = athleten_rs.getInt(1);
        String name = athleten_rs.getString(2);
        LinkedList<Leistung> alleLeistungen = alleAbrufen((int) (id)); //after this line the ResultSet gets closed
        alleAthleten.add(new Athlet(id, name, alleLeistungen));
    }
    return alleAthleten;
}

public LinkedList<Leistung> alleAbrufen(int athlet_id) throws SQLException {
    LinkedList<Leistung> alleLeistungen = new LinkedList<Leistung>();
    //here again, but since you need to use parameters in your query
    //use PreparedStatement instead
    //note that I commented the current query
    //String selectLeistungen = "SELECT * FROM Leistung WHERE athlet_id="+athlet_id;
    //this is how a query with parameters look like
    String selectLeistungen = "SELECT * FROM Leistung WHERE athlet_id=?";
    //the connection prepares the statement
    PreparedStatement pstmt = con.prepareStatement(selectLeistungen);
    //then we pass the parameters
    pstmt.setInt(1, athlet_id);
    ResultSet rs = pstmt.executeQuery();
    while (rs.next()) {
        long id = rs.getInt(1); 
        String bezeichnung = rs.getString(2);
        String datum = rs.getString(3);
        double geschwindigkeit = rs.getDouble(4);
        boolean selectedForSlopeFaktor = rs.getBoolean(5);
        int strecke_id = rs.getInt(7);
        long longAthlet_id = (long) athlet_id;
        Leistung leistung = new Leistung(strecke_id, longAthlet_id, bezeichnung, datum, geschwindigkeit);
        leistung.setLeistungID(id);
        leistung.setIsUsedForSlopeFaktor(selectedForSlopeFaktor);
        alleLeistungen.add(leistung);
    }
    return alleLeistungen;
}

使用后不要忘记关闭资源，Statement 和 ResultSet.

Don't forget to close the resources, Statement and ResultSet, after using them.

这篇关于JDBC-ResultSet 在 while-Loop 中关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！