Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Update or Insert

 
Rick Portugal
Ranch Hand
Posts: 243
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Rick,

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.

HTH

Jules
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34383
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rick,
I would favor doing the update. If the returned result (rows returned) is zero, you can do the insert. This way, you only have one round trip when the record is there.
 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Jules
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic