java.sql.SQLException: ResultSet is closed

在遍歷 ResultSet 時若又額外 Query 會造成異常

String url = "";
String user = "";
String password = "";
String sql1 = "";
String sql2 = "";

try (Connection connection = DriverManager.getConnection(url, user, password);
     Statement statement = connection.createStatement();
     ResultSet resultSet1 = statement.executeQuery(sql1)) {
    
    while (resultSet1.next()) {
        ResultSet resultSet2 = statement.executeQuery(sql2); // SQLException
    }
    
} catch (SQLException e) {
    
}

解決

不同的 Query 要給不同的 Statement
避免共用 Statement

String url = "";
String user = "";
String password = "";
String sql1 = "";
String sql2 = "";

try (Connection connection = DriverManager.getConnection(url, user, password);
     Statement statement1 = connection.createStatement();
     Statement statement2 = connection.createStatement();
     ResultSet resultSet1 = statement1.executeQuery(sql1)) {
    
    while (resultSet1.next()) {
        
        try (ResultSet resultSet2 = statement2.executeQuery(sql2);) {
            
        } catch (Exception e) {
            
        }
    }
    
} catch (SQLException e) {
    
}

註：
這樣的寫法是不佳的
在迴圈中又執行 query 是多次存取資料庫
有效能問題
盡可能藉由 SQL JOIN 語法降低查詢次數

Tags: ,