File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Get 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 "Get or Insert" Watch "Get or Insert" New topic

Get or Insert

Brian Shultz

Joined: Jan 10, 2007
Posts: 2
I am trying to create a method that either gets and Id or inserts the a new row, what i have works some of the time but not all the time. sometimes i get a Duplicate Key message back. The table is keyed on the combination of identifierref and object_type_id, can anyone recommend a better more stable way, This is using the SQLServerDriver class for 2005 and Microsoft SQL Server 2000. Thank you in advance.

Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33124

Welcome to JavaRanch!

It looks like the duplicate key exception is thrown when two callers execute the code at the same time. This could happen if you have the following sequence of events:
1) You check if record exists and it doesn't
2) Another thread inserts a record with that key
3) You insert the record and get a duplicate key exception

Take a look at transactions to eliminate this scenario.

[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
Brian Shultz

Joined: Jan 10, 2007
Posts: 2
That makes sense, so would the best way around this be a try catch that looks for duplicate key again, and then if caught try to select it and if not found try to insert it again. Or is there a good way around this at all?
I agree. Here's the link:
subject: Get or Insert
jQuery in Action, 3rd edition