Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Prevent duplicate data entry

 
Ed Carrington
Ranch Hand
Posts: 77
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ed,
Does it have to be in Java? The best thing to do is put in place a database constraint.
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 77
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic