• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to avoid Duplicate entry

 
Samar Land
Ranch Hand
Posts: 60
1
  • Mark post as helpful
  • send pies
  • 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:

 
Campbell Ritchie
Sheriff
Posts: 51469
87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Too difficult for this forum:moving to JSP forum.
 
Knute Snortum
Bartender
Pie
Posts: 2912
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • 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: 60
1
  • Mark post as helpful
  • send pies
  • 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
Bartender
Pie
Posts: 2912
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • 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.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • 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: 60
1
  • Mark post as helpful
  • send pies
  • 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?
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • 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: 60
1
  • Mark post as helpful
  • send pies
  • 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
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • 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: 60
1
  • Mark post as helpful
  • send pies
  • 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
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • 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: 60
1
  • Mark post as helpful
  • send pies
  • 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!

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic