This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach 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 Murach's Java Servlets and JSP this week in the Servlets 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: 30068
    
149

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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Update or Insert
 
Similar Threads
SQL Database Structure
Insert/Update if no record there
Insert a new row with values from an existing record
sql-insert and stored procedure
Somtimes I get an unique constraint error on an update statement