(JAVA/SQL) ResultSet only retrieving one row from MySQL when it should retrieve more

Question

I'm having a little trouble retrieving data from a MySQL database in my Java servlet. For reference, my web app is constructed like a library - you can check out items, and check them back in.

When you first click "check out", your request gets sent to an admin, let's call that person a librarian, who must approve or disapprove your request. Let's say I login, pick some items, and hit "checkout". Here's what happens:

1. The items get added to my "cart"
2. In the master list of items on the database, each item has a VARCHAR called STATUS, which identifies whether or not that item is "In Stock". For each item I chose, that variable gets changed to a unique identifier associated with my user account.
3. My request gets added to a table called requests_list

Approval, generic check-in, and so on work fine. What doesn't work so well is clicking "disapprove". Here's what should happen:

1. The request gets removed from requests_list
2. The requestor's "cart" of items gets truncated
3. The items themselves get declared "In Stock"

Here's what's been happening:

1. The request gets removed from requests_list
2. The requestor's "cart" of items gets truncated
3. Only the first item in the cart gets checked back in

Here's the code I'm working with so far:

stmt.execute("DELETE FROM requests_list WHERE UNIQUE_ID = '"
                        + selected_ID + "'"); // this seems to work

stmt.execute("TRUNCATE TABLE checkout_" + selected_ID); // also seems to work

ResultSet all_gear = stmt
                        .executeQuery("SELECT * FROM gear_master_list");

while (all_gear.next()) {
    if (all_gear.getString(5).equals(selected_ID)) {
        stmt.execute("UPDATE gear_master_list SET STATUS = 'In Stock' WHERE ID_NUM = "
                                + all_gear.getInt(6));
    }
}

all_gear.close();

Some notes, to explain a few of the variables:

• selected_ID - The librarian clicks a drop-down menu to access the list of users who have made requests. After he or she chooses one, that user's unique identifier becomes selected_ID
• checkout_selected_ID - Your "cart" is a table in the database. This table is named after your unique ID, so if my unique ID were bunnies, my table would be checkout_bunnies
• stmt and the Connection it draws its Statement from are all valid and working
• gear_master_list is the table with all the gear, all_gear.getInt(6) references the unique inventory number that each piece of gear has, and all_gear.getString(5) is the STATUS variable I mentioned

I have tried doing this differently, such that the code runs through all the items in the cart, updates the status, then truncates the table. That didn't work either - it was still finicky about updating the status, and the table wouldn't truncate.

TL;DR: My ResultSet is only getting me one row from my table (I think), even though I'm using a while loop and there's more than one row. What's going on? Is there a better, more efficient way to do this?

Answer 1

This may be a concurrent modification issue - your first query holds open a cursor on the database, and while looping over that cursor you're performing updates on that same data, all within the same transaction.

Could you try this?

    stmt.execute("DELETE FROM requests_list WHERE UNIQUE_ID = '"
            + selected_ID + "'"); // this seems to work

    stmt.execute("TRUNCATE TABLE checkout_" + selected_ID); // also seems to
                                                            // work

    ResultSet all_gear = stmt
            .executeQuery("SELECT * FROM gear_master_list");

    List<String> gear_items = new ArrayList<>();
    while (all_gear.next()) {
        gear_items.add(all_gear.getString(5));
    }
    all_gear.close();

    for (String gear_item: gear_items){
        if (all_gear.getString(5).equals(selected_ID)) {
            stmt.execute("UPDATE gear_master_list SET STATUS = 'In Stock' WHERE ID_NUM = "
                    + all_gear.getInt(6));
        }
    }

    stmt.close();

Answer 2

Many thanks to hugh for his great help with this question - his answer is correct. I just had to make a couple modifications to help his response fit my code.

In case anyone else is having this issue, hugh was right that this is a concurrent modification problem. However, there was one slight typo in his response (the ResultSet gets closed before certain statements are called), so I figured I would post my modified code just in case it proved helpful to anyone:

stmt.execute("DELETE FROM requests_list WHERE UNIQUE_ID = '" + selected_ID + "'"); // this seems to work

stmt.execute("TRUNCATE TABLE checkout_" + selected_ID); // also seems to work

ResultSet all_gear = stmt.executeQuery("SELECT * FROM gear_master_list");

ArrayList<Object[]> gear_items = new ArrayList<Object[]>();
while (all_gear.next()) {
    Object[] added = new Object[2];
    added[0] = all_gear.getString(5);
    added[1] = all_gear.getInt(6);
    gear_items.add(added);
}
all_gear.close();

for (Object[] gear_item : gear_items) {
    if (gear_item[0].equals(selected_ID)) {
        stmt.execute("UPDATE gear_master_list SET STATUS = 'In Stock' WHERE ID_NUM = " + gear_item[1]);
    }
}

stmt.close();

// thanks again to hugh!