Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
The moose likes JDBC and Relational Databases and the fly likes Update or Insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Update or Insert" Watch "Update or Insert" New topic

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?

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.


Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33124

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, 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.

I agree. Here's the link:
subject: Update or Insert
It's not a secret anymore!