Java JDBC MySQL exception: “Operation not allowed after ResultSet closed”

Question

So I have looked several times trough the function and nothing seems wrong. I don't pass any ResultSets to the function so I really do not get what is wrong with it. But I still get an error. The code works fine, its just that the function is used quite a lot of times and errors do not look nice on the console.

    void addItemToBody(String userId, MessageReceivedEvent event, int id) throws HTTP429Exception, DiscordException, MissingPermissionsException{
    String sql = "SELECT ItemType FROM items WHERE ID=?";
    PreparedStatement state;
    try {
        state = Main.conn.prepareStatement(sql);
        state.setInt(1, id);
        ResultSet result = state.executeQuery();
        while(result.next()){
            String type = result.getString("ItemType");
            if(type.equals("Head")){
                state.executeUpdate("UPDATE body SET headID="+id+" WHERE playerID='"+userId+"'");
            }
            if(type.equals("Chest")){
                state.executeUpdate("UPDATE body SET chestID="+id+" WHERE playerID='"+userId+"'");
            }
            if(type.equals("Pants")){
                state.executeUpdate("UPDATE body SET pantsID="+id+" WHERE playerID='"+userId+"'");
            }
            if(type.equals("Boots")){
                state.executeUpdate("UPDATE body SET bootsID="+id+" WHERE playerID='"+userId+"'");
            }
            if(type.equals("Melee")||type.equals("Magic")||type.equals("Ranged")){
                state.executeUpdate("UPDATE body SET weaponID="+id+" WHERE playerID='"+userId+"'");
            }
            sendMessage("Item equipped!",event);
            result.close();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

This is the error:

java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:743)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6320)
at martacus.mart.bot.rpg.InventoryHandler.addItemToBody(InventoryHandler.java:143)
at martacus.mart.bot.rpg.InventoryHandler.equipItem(InventoryHandler.java:92)
at martacus.mart.bot.rpg.InventoryHandler.OnMesageEvent(InventoryHandler.java:38)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at sx.blah.discord.handle.EventDispatcher.dispatch(EventDispatcher.java:104)
at sx.blah.discord.api.internal.DiscordWS.messageCreate(DiscordWS.java:323)
at sx.blah.discord.api.internal.DiscordWS.onMessage(DiscordWS.java:144)
at org.java_websocket.client.WebSocketClient.onWebsocketMessage(WebSocketClient.java:312)
at org.java_websocket.WebSocketImpl.decodeFrames(WebSocketImpl.java:368)
at org.java_websocket.WebSocketImpl.decode(WebSocketImpl.java:157)
at org.java_websocket.client.WebSocketClient.interruptableRun(WebSocketClient.java:230)
at org.java_websocket.client.WebSocketClient.run(WebSocketClient.java:188)
at java.lang.Thread.run(Unknown Source)

@wero is correct for solving your current issue - but a further optimization would be to make all of your update queries be PreparedStatements as well — SnakeDoc
– SnakeDoc, Commented Feb 16, 2016 at 16:27
@SnakeDoc Yeah ive done that now, i used to do this but I replaced them now. Thanks :P — Martacus
– Martacus, Commented Feb 16, 2016 at 19:09

wero · Accepted Answer · 2016-02-16 16:26:22Z

2

You shouldn't close the ResultSet while you are still looping through it

while(result.next()){
    ...
    result.close();
}

answered Feb 16, 2016 at 16:26

wero

33.1k3 gold badges63 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SnakeDoc Over a year ago

beat me by 2 seconds!

Mark Rotteveel Over a year ago

Even if that is done, executing another query on the same statement object like is done in the code should also close the ResultSet. See also stackoverflow.com/questions/32671535/…

Collectives™ on Stack Overflow

Java JDBC MySQL exception: “Operation not allowed after ResultSet closed”

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related