I am writing a Java program to put some data into a database using Java and JDBC. In some cases (depending upon the data), I want to do an update statement because there is already a row in the database for the primary key. In other cases, I want to do an insert statement because there is no row in the database for the primary key.
What is the best way to do that?
I could get a count of existing rows from the database and do an insert if the count is 0, otherwise do an update. Alternatively, I could do an update and trap for an error. If I get an error I could do an insert.
I'm sure this is something that is commonly done. What is the best practice?
If you can't know in advance whether the record you're trying to add/update exists then the best practice approach would be to use a stored procedure and let that take care of the logic.
The best approach is to know in advance whether you want to update or insert. Is it possible to incorporate that in your design?
If neither of the above suggestions are palatable then I don't think there's much to choose between your suggested solutions. They both require two round-trips to the database and a potentially superfluous read. Note that if an UPDATE fails to update any rows, that's not an error condition; you'll get no SQLException.
Jeanne has a point. The best option to choose, performance-wise, depends on which situation occurs most often. If the record is normally there, then use the UPDATE first. If the record is usually not there, use the INSERT. If you don't know, toss a coin.