[FIXED] Operation not allowed after ResultSet closed during while loop

Issue

I'm having a problem with Resultset during while loop. It's giving me an error java.sql.SQLException: Operation not allowed after ResultSet closed and I can't figure this out. Can anyone help me out? Thanks!

public static void CandidatesPartyList_JComboBox() {
    try {
        conn1 = VotingSystem.con();
        ps = conn1.prepareStatement("SELECT * FROM partylist WHERE p_status = 'Active' ORDER BY p_name ASC");
        rs = ps.executeQuery();
        candidates_filter_partylist.removeAllItems();
        candidates_filter_partylist.addItem("- Select PartyList -");
        while (rs.next()) { **<< The problem is coming from here**
            candidates_filter_partylist.addItem(rs.getString("p_name"));
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (conn1 != null) {
            try {
                conn1.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

Solution

You've got static variables named conn1, ps, and rs. Static means there is only one variable for the entire virtual machine.

Clearly then, CandidatesPartyList_JComboBox is called twice by different threads and thus the variables are overwritten.

The solution is: None of those things should be fields. They should be local variables, and you should be using try-with-resources, which makes this code less than half the size and fixes the problem. Let's also fix the bad error handling ('print the stack trace' is not handling things, so never write that and update your IDE templates).

public static void CandidatesPartyList_JComboBox() {
    try (Connection c = VotingSystem.con();
         PreparedStatement ps = c.prepareStatement("SELECT * FROM partylist WHERE p_status = 'Active' ORDER BY p_name ASC");
         ResultSet rs = ps.executeQuery()) {

        candidates_filter_partylist.removeAllItems();
        candidates_filter_partylist.addItem("- Select PartyList -");
        while (rs.next()) {
            candidates_filter_partylist.addItem(rs.getString("p_name"));
        }
    } catch (SQLException e) {
        throw new RuntimeException("Unhandled", e);
    }
}

Answered By - rzwitserloot
Answer Checked By - Gilberto Lyons (JavaFixing Admin)