This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Update or Insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Update or Insert" Watch "Update or Insert" New topic
Author

Update or Insert

Rick Portugal
Ranch Hand

Joined: Dec 17, 2002
Posts: 243
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?


IBM 286, SCJP, SCWCD, EIEIO
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
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
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Update or Insert
 
Similar Threads
SQL Database Structure
Somtimes I get an unique constraint error on an update statement
Insert/Update if no record there
Insert a new row with values from an existing record
sql-insert and stored procedure