Java JDBC MySQL exception: "Operation not allowed after ResultSet closed"

Question

I've been working at this for almost a day and a half now and I can't seem to work this error out. I don't know why the ResultSet is being closed. Maybe some of you can help me out.

MySQLDatabase:

package net.gielinor.network.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public abstract class MySQLDatabase {

    private String host;
    private String database;
    private String username;
    private String password;
    private Connection connection = null;
    private Statement statement;

    public MySQLDatabase(String host, String database, String username, String password) {
        this.host = host;
        this.database = database;
        this.username = username;
        this.password = password;
    }

    public abstract void cycle() throws SQLException;

    public abstract void ping();

    public void connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(String.format("jdbc:mysql://%s/%s", host, database), username, password);
            statement = connection.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void ping(String table, String variable) {
        try {
            statement.executeQuery(String.format("SELECT * FROM `%s` WHERE `%s` = 'null'", table, variable));
        } catch (Exception e) {
            connect();
        }
    }

    public ResultSet query(String query) throws SQLException {
        if (query.toLowerCase().startsWith("select")) {
            return statement.executeQuery(query);
        } else {
            statement.executeUpdate(query);
        }
        return null;
    }

    public Connection getConnection() {
        return connection;
    }

}

MySQLHandler

package net.gielinor.network.sql;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import net.gielinor.network.sql.impl.MySQLDonation;

public class MySQLHandler extends Thread {

    private static final MySQLHandler mysqlHandler = new MySQLHandler();

    public static MySQLHandler getMySQLHandler() {
        return mysqlHandler;
    }

    private static List<MySQLDatabase> updateList;
    private static String host;
    private static String database;
    private static String username;
    private static String password;

    @Override
    public void run() {
        while (true) {
            for (MySQLDatabase database : updateList) {
                try {
                    if (database.getConnection() == null) {
                        database.connect();
                    } else {
                        database.ping();
                    }
                    database.cycle();
                } catch (Exception ex) {
                    ex.printStackTrace();
                }

                try {
                    Thread.sleep(10000);
                } catch (Exception ex) {
                }
            }
        }
    }

    private static void loadProperties() {
        Properties p = new Properties();
        try {
            p.load(new FileInputStream("./sql.ini"));
            host = p.getProperty("host");
            database = p.getProperty("database");
            username = p.getProperty("username");
            password = p.getProperty("password");   
        } catch (Exception ex) {
            System.out.println("Error loading MySQL properties.");
        }
    }

    public static String getHost() {
        return host;
    }

    static {
        loadProperties();
        updateList = new ArrayList<MySQLDatabase>();
        updateList.add(new MySQLDonation(host, database, username, password));
    }

}

MySQLDonation

package net.gielinor.network.sql.impl;

import java.sql.ResultSet;
import java.sql.SQLException;

import net.gielinor.game.model.player.Client;
import net.gielinor.game.model.player.PlayerHandler;
import net.gielinor.game.model.player.PlayerSave;
import net.gielinor.network.sql.MySQLDatabase;

public final class MySQLDonation extends MySQLDatabase {

    public MySQLDonation(String host, String database, String username, String password) {
        super(host, database, username, password);
    }

    @Override
    public void cycle() throws SQLException {
        ResultSet results = query("SELECT * FROM `gieli436_purchases`.`donations`");
        if (results == null) {
            return;
        }
        while (results.next()) {
            String username = results.getString("username").replace("_", " ");
            System.out.println("name=" + username);
            Client client = (Client) PlayerHandler.getPlayer(username.toLowerCase());
            System.out.println(client == null);
            if (client != null && !client.disconnected) {
                int creditamount = results.getInt("creditamount");
                if (creditamount <= 0) {
                    continue;
                }
                handleDonation(client, creditamount);
                query(String.format("DELETE FROM `gieli436_purchases`.`donations` WHERE `donations`.`username`='%s' LIMIT 1", client.playerName.replaceAll(" ", "_")));
            }
        }
    }

    @Override
    public void ping() {
        super.ping("donations", "username");
    }

    private void handleDonation(Client client, int creditamount) throws SQLException {
        client.credits = (client.credits + creditamount);
        client.sendMessage("Thank you for your purchase. You have received " + creditamount + " store credits.");
        PlayerSave.save(client);
    }

}

The exception occurs here: in the while loop within MySQLDonation and the actual stacktrace is this:

java.sql.SQLException: Operation not allowed after ResultSet closed
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
    at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:794)
    at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7077)
    at net.gielinor.network.sql.impl.MySQLDonation.cycle(Unknown Source)
    at net.gielinor.network.sql.MySQLHandler.run(Unknown Source)

With this information let me say that this does work, I get my message and what not in-game but it repeats, like the user is never removed from the query so it gives them infinite rewards. If you need any more information feel free to ask.

What does your query function do. It may be closing the ResultSet. — shazin
– shazin, Commented Apr 22, 2013 at 5:49
public ResultSet query(String query) throws SQLException { if (query.toLowerCase().startsWith("select")) { return statement.executeQuery(query); } else { statement.executeUpdate(query); } return null; } There is "query", if the string equals select we execute the query; otherwise we send for an update to specified query. — johnathenwhiter94
– johnathenwhiter94, Commented Apr 22, 2013 at 5:52
MySQLDonation.ping call super.ping which is abstract! This code doesn't compile... — Aubin
– Aubin, Commented Apr 22, 2013 at 5:52
@Aubin how would that be my issue? It's totaly unrelated to the exception. The code compiles fine. MySQLDonation extends MySQLDatabase which is the parent of ping therefore I can call super.ping(...); — johnathenwhiter94
– johnathenwhiter94, Commented Apr 22, 2013 at 5:53
@user1787084 what do you pass in the host? Are you absolutely sure that you are getting a connection to the database? — Lakshmi
– Lakshmi, Commented Apr 22, 2013 at 5:54

Pradeep Pati · Accepted Answer · 2013-04-22 06:01:07Z

17

When you run the Delete query, you use the same Statement that was used in the Select query. When you re-execute on the same Statement, the previous ResultSet gets closed.

To avoid this, you should create a new Statement everytime you execute a query. So remove statement = connection.createStatement(); from the connect() method in MySQLDatabase class, and replace all statement in that class to connection.createStatement(). You may also choose to delete the private variable statement altogether.

You can read more about it here.

edited Apr 22, 2013 at 6:01

answered Apr 22, 2013 at 5:56

Pradeep Pati

5,9293 gold badges31 silver badges43 bronze badges

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

4 Comments

johnathenwhiter94 Over a year ago

Alright, that makes more sense. Would it be my "delete" query causing the issue or the initial query to the table?

Pradeep Pati Over a year ago

its the delete query. If you take it out of the loop, your code would work, but I believe the logic will be all wrong.

johnathenwhiter94 Over a year ago

How so? Mind explaining a little more in depth? Okay, @galuano1, I removed the statement instance and the "query" method and gave every query their own statement.

Pradeep Pati Over a year ago

come to the chat room chat.stackoverflow.com/rooms/info/28633/…

Karan · Accepted Answer · 2014-08-06 08:57:10Z

2

this error is some time occur when we use same statement object for diff. types

check Statement objectsss;

answered Aug 6, 2014 at 8:57

Karan

5851 gold badge6 silver badges17 bronze badges

Collectives™ on Stack Overflow

Java JDBC MySQL exception: "Operation not allowed after ResultSet closed"

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related