• 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

How to avoid Duplicate entry

 
Ranch Hand
Posts: 68
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
How can I validate the input in JSP form before save it to the database to avoid Duplicate entry java error.

Currently I'm using Hibernate, Spring


Service.java


Controller.java


Dao.java


I'm getting this error:

 
Marshal
Posts: 79179
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Too difficult for this forum:moving to JSP forum.
 
Sheriff
Posts: 7125
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't believe you can do it in the JSP; I think you have to do it in the try/catch block, but I'll move this thread to the DB forum and maybe we can get a more authoritative answer.
 
Samar Land
Ranch Hand
Posts: 68
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I do have a index in my db:
UNIQUE INDEX `ak_team_1` (`team_name`)

So, I should be able to check the data in java/JSP before add it to the db, but not sure how to do it.
 
Knute Snortum
Sheriff
Posts: 7125
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, you can write a SQL query (however that's done in Hibernate) and check for the existence to the key first. This is probably better than getting an exception.
 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Knute Snortum wrote:Well, you can write a SQL query (however that's done in Hibernate) and check for the existence to the key first. This is probably better than getting an exception.



Depends.
Are two trips to the Db (potentially) better than one, when compared to the overhead of an exception?
 
Samar Land
Ranch Hand
Posts: 68
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, I would warn the user that the entry he entered not valid. Is it possible to do an entry validation using Javascript?
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This is starting to get into database stuff, but unfortunately, if your application needs to care about "duplicate entries" in the database, then you need to care about how these duplicates can be identified and handled in your code, including the database.

Your method is called "addOrUpdateTeam", so does this mean you want to INSERT a record if it isn't there already, but UPDATE a matching record if it already exists?

If so, then there is a SQL statement (often called "upsert") which will do this for you. The syntax is slightly awkward, but you can code your INSERT to do an UPDATE of specific fields if that is what you want. The MySQL documentation is here: http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

There's also an example with an explanation here: http://mechanics.flite.com/blog/2013/09/30/how-to-do-an-upsert-in-mysql/

If you're using an ORM like Hibernate, you will need to find out how to do a "merge" or "upsert" in Hibernate.

On the other hand, if you simply want to display a sensible error to the user if a duplicate record exists (i.e. you don't want to do an upsert), why not catch the MySQLIntegrityConstraintViolationException and handle it appropriately?

But the only way to check at runtime if there is a duplicate record in the DB is to look in the DB at runtime. You can either do this before the INSERT (resulting in 2 DB operations for the overall insert process), or you can let the SQL INSERT find the duplicate and then you either do an upsert or handle the exception. There's no magic here, so just figure out which approach you want to take and then write your code appropriately.
 
Samar Land
Ranch Hand
Posts: 68
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

You can either do this before the INSERT (resulting in 2 DB operations for the overall insert process), or you can let the SQL INSERT find the duplicate and then you either do an upsert or handle the exception.


Thanks for your great reply, It gave me a lot of ways to think more about it, with hibernate I think I will try merge first.

I Like to share with you what it comes to my mind this morning;
I can do a list in Java code with for loop to check through teams I have already in DB before adding the record.

 
chris webster
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Samar Land wrote:I Like to share with you what it comes to my mind this morning;
I can do a list in Java code with for loop to check through teams I have already in DB before adding the record.


No.

If you really want to run a pre-check to see if the new record already exists in the DB, run a specific query e.g. SELECT id FROM teams WHERE team_name = 'new name'. The SQL query can search the DB's index (a primary key is enforced via a DB unique index) for the new value, which is much more efficient than if you try to fetch and check all the team records in Java.

Don't use Java to do things that can be done more efficiently in SQL, like queries, because that's what the database is designed for.
 
Samar Land
Ranch Hand
Posts: 68
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I've tried the merge in hibernate, but it didn't work; Still getting the same exception,



Don't use Java to do things that can be done more efficiently in SQL


Wasn't know that, thanks
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It's not the primary key that's the issue, which is what "merge" works from.
Your issue is with a UNIQUE column, for which you'll have to do the work yourself.
So either attempt an INSERT and then handle the constraint exception, or do a search for the value and then an INSERT (though you would still have to handle the possibility of a UNIQUE constraint).

I would go with the former for the simple reason that the search doesn't actually help you.
 
Samar Land
Ranch Hand
Posts: 68
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Just tried again the same code and it worked for me, didn't get any exception and new record is just merged without duplicating the record!

 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic