CODEWITHSUNDEEP
javaoracle-databasejdbc
v_manoliu
v_manoliu

Reputation: 1

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

Basically, I have the following method that would get me the id from an oracle table : 

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

And in the Main method where i try to test it like this : 

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

I get the exception: java.sql.SQLException: Closed Resultset: next even though as you can see I am closing neither the connection or the statement before rs and I do not understand why

Upvotes: 0

Views: 3021

Answers (1)

Elliott Frisch
Elliott Frisch

Reputation: 201447

Your try-with-resources does close the ResultSet, but that isn't the real problem. You need to set-up the Statement before you execute it (and prefer PreparedStatement and bind parameters). Something like,

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

Upvotes: 1

Related Questions