SQL ResultSet closing suddently

Question

Hello Stackoverflow users,

I'm experiencing a problem with my SQL connection at the moment. First of all, here's how the connector is handled

package com.rs.utils.sql;

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

import com.rs.utils.Logger;

public class DatabaseManager {

private String host;
private String database;
private String username;
private String password;

private Connection connection;
private PreparedStatement statement;

private boolean connected;

public DatabaseManager() {
this.host = "";// Settings.DB_HOST;
this.database = "";// Settings.DB_NAME;
this.username = "";// Settings.DB_USER;
this.password = "";// Settings.DB_PASS;
this.connected = false;
}

public void connect() {
    try {
        connection = DriverManager.getConnection("jdbc:mysql://" + host
                + "/" + database
                + "?jdbcCompliantTruncation=false&autoReconnect=true",
                username, password);
        Logger.info("Successfully connected with " + host + "/" + database);
        connected = true;
    } catch (Exception e) {
        Logger.info("Unable to connect with " + host + "/" + database + ".");
        connected = false;
    }
}

public ResultSet executeQuery(String query) {
    try {

        if (!connected())
            return null;

        statement = connection.prepareStatement(query);
        ResultSet results = statement.executeQuery();
        return results;
    } catch (Exception e) {
        Logger.handle(e);
    }
    return null;
}

public int executeUpdate(String query) {
    try {

        if (!connected())
            return 0;

        statement = connection.prepareStatement(query);
        return statement.executeUpdate();
    } catch (Exception e) {
        Logger.handle(e);
    }

    return 0;
}

public boolean connected() {
    return connected;
}

public PreparedStatement statement() {
    return statement;
}

}

A lot of queries is sent to the database using the DatabaseManager instance, for example:

databaseManager.executeUpdate(query);    

databaseManager is defined as:

private static DatabaseManager databaseManager = new DatabaseManager();    

I"m getting the following Throwable:

java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:803)
at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1126)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5732)
at com.rs.utils.sql.PlayerSaving.load(PlayerSaving.java:362)
at com.rs.utils.sql.PlayerLoader.load(PlayerLoader.java:90)    

load method in PlayerLoader:

public static boolean load(Player player, boolean lobby) {
    ResultSet result = null;
    try {
        final long current = System.currentTimeMillis();
        result = World.database().executeQuery("SELECT " + (lobby ? "displayName, rights" : "*") + " FROM " + PLAYER_TABLE + " WHERE username='" + player.getPlayerDefinition().username() + "' LIMIT 1");

        if (!result.next()) {
            return false;
        }

        if (lobby) {
            player.getPlayerDefinition().setRights(result.getInt("rights")).setDisplayName(result.getString("displayName"));
        } else {
            **player.playerSaving().load(result);**
        }

        Logger.log("Loader", "Player loaded in " + (System.currentTimeMillis() - current) + "ms.");
        return true;
    } catch (Exception e) {
        Logger.log("Loader", "Unable to load player profile.");
        Logger.handle(e);
        System.err.println("Error Loading the account.");
    } finally {
        try {
            if (result != null) {
                result.close();
            }
            result = null;
            World.database().statement().close();
        } catch (SQLException e) {
            Logger.handle(e);
        }
    }
    return false;
}    

the PlayerSaving load method just assign another class instance the values from the database with result.getInt("rowName"), etc, as received on the ResultSet, it's over 250 rows

The ResultSet is just fine most of the time, it's a very rare event, like 1 out of 100 login attempts. But somehow the ResultSet gets closed in the middle of the variable assignments, like, it assigns 100 out of 200 variables, then ResultSet gets closed suddently and it throws the exception.

Any ideas?

Answer 1

Closing the statement will also close the underlying ResultSet. You may have nested calls to your DatabaseManager which only has a single shared PreparedStatement, which in my opinion, is a bad idea.

(I have insufficient rep to add a comment, so I had to post this as an answer)

Below is a sample implementation using a dedicated PreparedStatement and ResultSet for each query operation:

public List<MyDataClass> listMyData(Connection con, String partNumber) {
    ArrayList<MyDataClass> list = new ArrayList<MyDataClass>();
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        ps = con.prepareStatement("SELECT PART_NO, DESC, PRICE FROM MYTABLE WHERE PART_NO = ?");
        ps.setString(1, partNumber);
        rs = ps.executeQuery();
        while(rs.next()) {
            MyDataClass myData = new MyDataClass();
            myData.setPartNumber(rs.getString("PART_NO"));
            myData.setDescription(rs.getString("DESC"));
            myData.setPrice(rs.getBigDecimal("PRICE"));
            list.add(myData);
        }
        con.commit();
    }
    catch(Throwable thrown) {
        try{ con.rollback(); }catch(Throwable t){}
        //Handle the exception here to log etc.
    }
    finally {
        //Always close result set before statement
        if(rs != null) { try{ rs.close(); }catch(Throwable t){} }
        if(ps != null) { try{ ps.close(); }catch(Throwable t){} }
    }

    return list;
}