• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Prevent duplicate data entry

 
Ranch Hand
Posts: 77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic