Need Help Looping through table to find the right primary key and delete that row

[Charles Sexton]

I have tried to write this at least ten times but can't seem to find a way to loop through the primary key (customerID) and delete that row. If customerID for that row is equal to what needs to be deleted then delete from database.


Last thing I tried and after reviewing the logic I find it completely stupid because it will return the same thing every time due to the query being before the if statement.

                    if ((message.trim()).startsWith("500")) {

                        input = message.substring(3);
                        sb = new StringBuffer();

                        sql = "Delete FROM customers WHERE customerID=" + input;
                        sql2 = statement.executeUpdate(sql);
                        if (sql2 != Integer.parseInt(input)){
                                                            
                        sb.append("Customer " + input + " was deleted!!!!");
                            
                        }else{

                            sb.append("Customer " + input + " doesn't exist!!!");
                            
                        }                     
                              
                        dataSocket.sendMessage(sb.toString());
                    }

This snippet of code actually works and deletes the row that I need but if the row doesn't exist it doesn't throw an error or doesn't let the user know that the customer wasn't in the database.

                    if ((message.trim()).startsWith("500")) {

                        input = message.substring(3);

                        statement.executeUpdate("Delete FROM customers WHERE customerID=" + input);
                                                    
                        dataSocket.sendMessage("Customer ID " + input + " was deleted!!!!");
                    }

This snippet of code would delete the specific row but throw an exception.

                    if ((message.trim()).startsWith("500")) {

                        input = message.substring(3);

                        ResultSet result = statement.executeQuery("SELECT customerID FROM customers");

                        ResultSetMetaData mData = result.getMetaData();

                        sb = new StringBuffer();

                        while (result.next()) {

                            String customerID = result.getString("customerID");

                            if (customerID.equals(input)) {
                                
                                statement.executeUpdate("Delete FROM customers WHERE customerID=" + input);

                                sb.append("Customer " + customerID + " was deleted!!!");

                            } else{
                                
                                sb.append("Customer " + customerID + " doesn't exist!!!!");
                                
                            }
                        }

                        dataSocket.sendMessage(sb.toString());
                    }

Error for above snippet:

java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:800)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6983)
at server_sexton_it351.Threads.run(Threads.java:194)
at server_sexton_it351.Server_Sexton_IT351.main(Server_Sexton_IT351.java:30)

[chris webster]

You can just execute the DELETE statement using a given WHERE clause, or if you really want to delete all the customers just execute "DELETE FROM customers" without specifying a WHERE clause.

If a record exists that meets the specified criteria, it will be deleted. If not, nothing will be deleted. You can check how many rows were deleted if necessary.

In your code, I see no point checking for the "doesn't exist" condition, because you are fetching the customer IDs from the same table anyway.

Don't try to micro-manage your SQL operations: SQL is a declarative language, which means you tell it what you want to do, and let the SQL engine take care of how it does it.

[Martin Vashko]

The problem lies here (apart from really bad choice of the sql2 variable name):

                        sql = "Delete FROM customers WHERE customerID=" + input;
                        sql2 = statement.executeUpdate(sql);
                        if (sql2 != Integer.parseInt(input)){

The executeUpdate method returns the number of rows that were affected by the statement. It doesn't return the primary key of the affected row -- it would be problematic, as generally there can be many rows affected by an SQL statement.

In your case, as you're deleting the records based on the primary key (or so I assume), you can expect just a value of 1 (the row existed and was deleted) or 0 (the row doesn't exist). Anything else indicates a deep problem, and if it was me, I'd just rollback and throw some an exception in the hope the issue will be noticed and analyzed.

Also note that in production code, you should use PreparedStatements. I'd even suggest to use it even when just learning JDBC - firstly to get used to the proper way to code, and secondly because handling strings and dates ina portable way is actually much easier with PreparedStatements.

[Charles Sexton]

Martin Vajsar wrote:The problem lies here (apart from really bad choice of the sql2 variable name):

                        sql = "Delete FROM customers WHERE customerID=" + input;
                        sql2 = statement.executeUpdate(sql);
                        if (sql2 != Integer.parseInt(input)){

The executeUpdate method returns the number of rows that were affected by the statement. It doesn't return the primary key of the affected row -- it would be problematic, as generally there can be many rows affected by an SQL statement.

In your case, as you're deleting the records based on the primary key (or so I assume), you can expect just a value of 1 (the row existed and was deleted) or 0 (the row doesn't exist). Anything else indicates a deep problem, and if it was me, I'd just rollback and throw some an exception in the hope the issue will be noticed and analyzed.

Also note that in production code, you should use PreparedStatements. I'd even suggest to use it even when just learning JDBC - firstly to get used to the proper way to code, and secondly because handling strings and dates ina portable way is actually much easier with PreparedStatements.

I ended up using a preparedStatement() and it worked fantastically.......