wood burning stoves 2.0*
The moose likes JDBC and the fly likes Primary key violation problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Primary key violation problem" Watch "Primary key violation problem" New topic
Author

Primary key violation problem

Luca kk
Greenhorn

Joined: Dec 14, 2010
Posts: 4
Hello everyone,
I would like to expose my problem.
I created a web application with JSF technology, which interfaces with SQL Server 2005.
The application is quite simple, provides an initial login and allows the logged users to enter
data in the database, such as a new record in a table .
The problem arises when I make the calculation of the ID, which is the primary key of the new
record to insert: as increasing of users logged so grows the probability that two users simultaneously
attempt to insert a new record with the same ID with the problem of violation of the primary key.
Now as the number of users logged each have a different instance of the application, the problem is
unsolvable, and correct me if I'm wrong, if I continue to leave the method of calculation of the ID
in the application itself, such as Session.
I thave to move the metod to the application server as a service (I use Tomcat)? How?
Is there any tool already developed?
Thanks,
Luca
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39478
    
  28
How many instances of the database are there? Can you use locking or transactions to prevent duplicate IDs?

And welcome to JavaRanch
Luca kk
Greenhorn

Joined: Dec 14, 2010
Posts: 4
Hi Ritchie,
there is one istance of DB:

connDb_Suite = UConnections.getConnection();

I use a stored procedure to store the data and i don't
use lock or transactions.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

You can have your key generation logic in the database. Does your datebase support sequences? If it does not, its possible to us a HiLo pattern to achieve a similar end.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Luca kk
Greenhorn

Joined: Dec 14, 2010
Posts: 4
Hi Paul,
I use JDBC, so without persistence.
I use SQL Server 2005 and I never used the sequences.
How can I use them?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

SQL Server does not support sequences. It does however support identity columns. If you are happy enough to tightly couple your application to SQL Server you can use them. Alternatively, if you want it to remain database agnostic, you can use a pattern like the HiLo pattern.
Luca kk
Greenhorn

Joined: Dec 14, 2010
Posts: 4
Unfortunately I don't decide for DB and I have to use
SQL Server.
What are identity columns?
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Primary key violation problem