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.

Answer 1

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.

Answer 2

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

check Statement objectsss;