File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes read-test-insert, keep field unique Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "read-test-insert, keep field unique" Watch "read-test-insert, keep field unique" New topic
Author

read-test-insert, keep field unique

Kovacs Antal
Greenhorn

Joined: May 17, 2010
Posts: 11
Hi!

Let's suppose a database table containing (ID, username, detail1, detail2, status ('d'eleted, 'h'istory, 'a'ctual).
I have a method, that selects the rows that are status='a'ctual and username=desired_new_username. If
the result set is empty, then I insert the new record, else say choose a different username. How can I make
sure, that this works well in multi-threaded environment?

case 1: my single application is hitting the database: is the best approach to make all methods inserting into
this table synchronized with the same lock?

case 2: different, unknown applications are also inserting users into this table. how can I make sure, that
no one can write a new record into that table after my SELECT and before my INSERT?

can this be done by choosing a right transaction isolation level?

thanks for anyone, who answers me!
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
Kovacs Antal wrote:Hi!
case 2: different, unknown applications are also inserting users into this table. how can I make sure, that
no one can write a new record into that table after my SELECT and before my INSERT?

Create a primary key on a table (or unique constraint) that consist of username+status fields,
and in your program simply try to insert record, then check if a database raise an exception 'duplicate key/index values'.
If yes, the user must enter a different username.
If no - the record is inserted.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30502
    
150

Kovacs,
Welcome to JavaRanch!

Yes. A transaction will help ensure a record isn't added between the check and addition.


[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
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Is that right Jeanne? How does a transaction prevent an insert statement? It will (depending on the isolation level) prevent updates to the selected data, but it surely can only block against existign data?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30502
    
150

Paul,
So I wrote up an explanation of why I was right, got to the end, and wasn't anymore. You are correct. A transaction protects against updates.
Kovacs Antal
Greenhorn

Joined: May 17, 2010
Posts: 11
Hi!

I'm sorry for not writing so far, but I had no time to react. Thanks for all your answers guys.
Ireneusz Kordal, you said, that "Create a primary key on a table (or unique constraint) that consist of username+status fields"
My question is that how to do it, if I have many (name=user1, status="(H)istory") records and only one (name=user1, status="(A)ctive) ones.
Can I limit the number of records with status = 'A' and name=something to 1?
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30502
    
150

Kovacs Antal wrote:Ireneusz Kordal, you said, that "Create a primary key on a table (or unique constraint) that consist of username+status fields"
My question is that how to do it, if I have many (name=user1, status="(H)istory") records and only one (name=user1, status="(A)ctive) ones.
Can I limit the number of records with status = 'A' and name=something to 1?

No. You might consider refactoring your database design to have History and Active in different tables. You can still use a view to give the appearance of one table for querying.
Kovacs Antal
Greenhorn

Joined: May 17, 2010
Posts: 11
Hi!

I think using two separate tables for storing active/deleted/history records is unnecessary, as they contain the very same fields. (Partitioning the table into an active-nonactive part is a handy solution for archiving/performance issues.) Do you think it would be possible with JPA/Hibernate to store some entities in the history table and some in the active table depending on their status?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: read-test-insert, keep field unique