Java SQL异常: Closed Resultset: next even though neither connection or resultset gets closed

Question

基本上，我有以下方法可以从 oracle 表中获取 id:

public Integer findByName(String name) throws SQLException {
    Connection con = Database.getConnection();
    try (Statement stmt = con.createStatement();
         ResultSet rs = stmt.getResultSet()) {
        stmt.executeQuery("select id from artists where name='" + name + "'");
        return rs.next() ? rs.getInt(1) : null;
    }
}

在 Main 方法中，我尝试像这样测试它:

public static void main(String[] args)
    {
        try {
            ArtistController artists = new ArtistController();
            AlbumController albums = new AlbumController();


            artists.create("Radiohead", "United Kingdom");
            artists.create("Phoenix", "Romania");
            Database.commit();


            int radioheadId = artists.findByName("Radiohead");
            System.out.println(radioheadId);

            albums.create(radioheadId,"OK COMPUTER", 1977);
            albums.create(radioheadId, "Kid A", 2000);
            albums.create(radioheadId, "In Rainbows", 2007);

            Database.commit();


            Database.closeConnection();
        } catch (SQLException e) {
            System.err.println(e);
            Database.rollback();
        }
    }

我得到了异常: java.sql.SQLException: Closed Resultset: next 即使如你所见，我既没有关闭连接也没有关闭 rs 之前的语句，我不明白为什么

Answer 1

您的try-with-resources确实关闭ResultSet，但这不是真正的问题。您需要在执行Statement之前设置它(并且首选PreparedStatement和绑定(bind)参数)。比如，

public Integer findByName(String name) throws SQLException {
    String sql = "select id from artists where name=?";
    Connection con = Database.getConnection();
    try (PreparedStatement stmt = con.prepareStatement(sql)) {
        stmt.setString(1, name);
        try (ResultSet rs = stmt.executeQuery()) {
            return rs.next() ? rs.getInt(1) : null;
        }
    }
}