wood burning stoves 2.0*
The moose likes JDBC and the fly likes Need Help Looping through table to find the right primary key and delete that row Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Need Help Looping through table to find the right primary key and delete that row" Watch "Need Help Looping through table to find the right primary key and delete that row" New topic
Author

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

Charles Sexton
Ranch Hand

Joined: Sep 26, 2013
Posts: 192
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.




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.



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



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
Bartender

Joined: Mar 01, 2009
Posts: 1703
    
  14

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.

No more Blub for me, thank you, Vicar.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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

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
Ranch Hand

Joined: Sep 26, 2013
Posts: 192
Martin Vajsar wrote:The problem lies here (apart from really bad choice of the sql2 variable name):

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.......
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Need Help Looping through table to find the right primary key and delete that row