Win a copy of 97 Things Every Java Programmer Should Know this week in the Java in General forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

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

 
Ranch Hand
Posts: 273
  • 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)


 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • 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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • 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: 273
  • 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.......
 
passwords must contain 14 characters, a number, punctuation, a small bird, a bit of cheese and a tiny ad.
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic