Win a copy of Learn Spring Security (video course) this week in the Spring forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Charles Sexton
Ranch Hand
Posts: 241
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2407
32
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 Vajsar
Sheriff
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 241
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.......
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic