How to check if deletion was successful in the database?

Question

I wanted an error to popup, when the user entered a wrong id into the delete field. But even if a wrong id is entered, the query still proceeds, but no data is deleted. Here's my code:

String value = jTextField19.getText();

if (value == null || "".equals(value)) {
    JOptionPane.showMessageDialog(null, "The field is blank!");
} else {
    theQuery("DELETE FROM inventorydb WHERE item_id=('"+jTextField19.getText()+"') AND item_id IS NOT NULL");
}

The theQuery method:

private void theQuery(String query) {
    Connection con = null;
    Statement st = null;
    try {
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/inventory", "root", "");
        st = con.createStatement();
        st.executeUpdate(query);
        JOptionPane.showMessageDialog(null, "Done!");
    } catch (Exception ex) { 
        JOptionPane.showMessageDialog(null,"Error!");
    } 
}

Answer 1

First of all: do not ever directly build SQL queries from user input, use prepared statements instead. If you don't know about SQL Injection, you should.

If you are using JDBC, you can check the result of #executeUpdate() to see how many rows were affected. If it was zero, then you can say that it was a wrong id.

This is the method definition:

public int executeUpdate(java.lang.String sql)

The return value is:

An int that indicates the number of rows affected, or 0 if using a DDL statement.

---

In the program at hand, you can just simply do this:

int deleted = st.executeUpdate(query);
if (deleted == 0) {
    JOptionPane.showMessageDialog(null, "Nothing to delete!");
    return;
}