JavaFX error when attempting to delete row from database

Question

I get the following error when I attempt to delete a row: java.sql.SQLException: Before start of result set. Can not call deleteRow()

What I want to do is add a delete button, which removes the selected row from the TableView. It works to delete it from the Table. Yet it doesn't delete the row in the database and I've struggled a bit with different approaches trying to solve how to do it...

The delete button:

 Button removeButton1 = new Button("Remove");
    removeButton1.setMaxWidth(150);
    removeButton1.setOnAction(e -> {
        try {
            int selectedIndex = itemTable.getSelectionModel().getSelectedIndex();
            itemTable.setEditable(true);
            itemTable.getItems().remove(selectedIndex);
            inventoryData.removeItem();

        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    });

Remove item method (error):

public void removeItem() throws SQLException {
    Statement stmt = null;

    try {
        stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet uprs = stmt.executeQuery("SELECT * FROM Items");

        uprs.deleteRow();
        uprs.first();

    } catch (Exception e){
        e.printStackTrace();
    } finally {
        if(stmt != null){
            stmt.close();
        }
    }

}

The problem (I think) is with deleteRow() as I am not expressing a row for it to delete... Yet I am quite lost as to how to solve this problem... With TextField, for example, I can get the string/int values and it'll find it in the database for me... But how do I do so when the user simply clicks a row? I tried to look up previous questions on stackoverflow but alas my confusion was only excarbated...

Thanks in advance.

EDIT

private final IntegerProperty itemId = new SimpleIntegerProperty(this,"itemId");

public IntegerProperty itemIdProperty(){
    return itemId;
}

public final int getItemId(){
    return itemIdProperty().get();
}

public final void setItemId(int itemId){
    itemIdProperty().set(itemId);
}

You have to iterate through a ResultSet with next() as far as i can remember. — OH GOD SPIDERS, Jun 19 '17 at 12:22
@OHGODSPIDERS I tried this and while it doesn't give an error there are no changes to the database. Unless I am doing that wrong as well...? — purpleman, Jun 19 '17 at 12:29
A `SELECT` SQL statement selects entities from a database table. To delete from a table, use a `DELETE` statement. See, maybe https://stackoverflow.com/questions/27345614/how-to-delete-row-from-sql or the [mysql documentation](https://dev.mysql.com/doc/refman/5.6/en/delete.html) — James_D, Jun 19 '17 at 12:37
@James_D So something like this? "DELETE FROM Items WHERE item_id = ?" ... item_id is the primary key... but how do I get that item_id from user selecting the row? — purpleman, Jun 19 '17 at 12:49
Presumably you made the id a property of the class representing the data in the row, so you can just get it from the selected item. No one on this forum is clairvoyant: we have no idea how you have set up your model class. — James_D, Jun 19 '17 at 12:51
@James_D I added what I think you refer to in my post?... So it would be something like "DELETE FROM Items WHERE item_id = itemId"? (Sorry for total newb question, I am just confused right now...) — purpleman, Jun 19 '17 at 13:03

score 1 · Accepted Answer · answered Jun 19 '17 at 13:27

You can do

removeButton1.setOnAction(e -> {
    try {
        Item selectedItem = itemTable.getSelectionModel().getSelectedItem();
        itemTable.getItems().remove(selectedItem);
        inventoryData.removeItem(selectedItem.getItemId());

    } catch (SQLException e1) {
        e1.printStackTrace();
    }
}

with

public void removeItem(int itemId) throws SQLException {
    PreparedStatement stmt = null;

    try {
        stmt = connection.prepareStatement("DELETE FROM Items where item_id = ?");
        stmt.setInt(1, itemId);
        stmt.execute();
    } catch (Exception e){
        e.printStackTrace();
    } finally {
        if(stmt != null){
            stmt.close();
        }
    }

}

(I'm assuming the model class for your JavaFX table is Item, i.e. you have a TableView<Item>: change the type of selectedItem in the first block as needed.)

This solved it. Cheers! One further question: would you be able to recommend some tutorials/links regarding this? — purpleman, Jun 19 '17 at 13:46
There are many: I would just google "Intro to SQL". [W3schools](https://www.w3schools.com/sql/default.asp) is a reasonable place to start for a beginner. — James_D, Jun 19 '17 at 16:21

JavaFX error when attempting to delete row from database

1 Answers1