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

问题描述

我在使用ResultSet时非常糟糕，而ResultSet在while-Loop中关闭以迭代此ResultSet。我已经知道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 s 。由于您为两种方法共享相同的 Statement stmt ，因此在 alleAbrufen 中语句执行另一个语句，它会破坏对前一个 ResultSet 的引用。

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 s。

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;
}

不要忘记关闭资源，声明和 ResultSet ，在使用它们之后。

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

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