This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes JDBC and the fly likes Prevent duplicate data entry 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 "Prevent duplicate data entry" Watch "Prevent duplicate data entry" New topic
Author

Prevent duplicate data entry

Ed Carrington
Ranch Hand

Joined: Oct 11, 2007
Posts: 77
I would like to make sure there are no duplicate data entries in my JSP that populates an Oracle database with a table called MainTable. MainTable has an Id field that is the primary key, ValData with a varchar data type, Fid and Fid2 are number data types. Fid and Fid2 are foreign key values that are taken from another table.



Sometimes someone can enter duplicate data and the ValData, Fid and Fid2 will end up like this:



Is there anything in Java I can implement to prevent duplicate data entry in the above example?
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30789
    
157

Ed,
Does it have to be in Java? The best thing to do is put in place a database constraint.


[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 Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by Ed Carrington:
I would like to make sure there are no duplicate data entries in my JSP that populates an Oracle database with a table called MainTable. MainTable has an Id field that is the primary key, ValData with a varchar data type, Fid and Fid2 are number data types. Fid and Fid2 are foreign key values that are taken from another table.





Sometimes someone can enter duplicate data and the ValData, Fid and Fid2 will end up like this:



Is there anything in Java I can implement to prevent duplicate data entry in the above example?


I'm assumming ID is a database sequence that is auto-incremented... in your app you should try to do something likey this (sorry this is the PL/SQL version... but I'm a long way from Java/JDBC profecient):
BEGIN
Select 1
FROM MainTable
WHERE ValData = myValData
AND Fid = myFid
AND Fid2 = myFid2

Exception
When no_data_found Then
INSERT INTO MainTable (ValData, Fid, Fid2)
VALUES (myValData, myFid, MyFid2);
END


My idea differs than Jeanne's because I come from a database-centric background. This doesn't make my suggestion better... it's just a suggestion. Without knowing more about how the data in the table was used... I would just handle it outside of adding more RI to my database.
[ October 21, 2007: Message edited by: Paul Campbell ]
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30789
    
157

Paul: The funny thing is that I suggested a DB solution and you suggested a logical one.

Ed: If you translate Paul's solution to Java, make sure you place both JDBC calls inside a transaction. Otherwise, the database state could change after the select.

Both solutions are valid and mainly a matter of preference. I lean towards the constraint (RI = referential integrity) because in my apps it isn't guaranteed that nobody else will be updating the values. And even if it was, someone could be helpful and update the database by hand. I prefer the database enforce something if it is important to me.
Ed Carrington
Ranch Hand

Joined: Oct 11, 2007
Posts: 77
Thanks it now works great.

I ended up creating constraints in the Oracle Database:


In the Helper Class that is called by my Servlet Controller I put in a condition (ORA-00001 is Oracle's Duplicate message) in the SQLException Catch area and it catches all my Duplicate entry attempts and shows message to the user.

Here is what I have and would like to know if this is the way you would do it??

[ October 22, 2007: Message edited by: Ed Carrington ]
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30789
    
157

Ed,
That's what I would do. The only thing I would change is to make the "ORA-00001" a constant. I wouldn't want to have to keep looking up what it means!
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by Jeanne Boyarsky:
Paul: The funny thing is that I suggested a DB solution and you suggested a logical one.


I found it amusing too.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Prevent duplicate data entry