CODEWITHSUNDEEP
javasqlhibernatejpajpa-2.0
Umesh Awasthi
Umesh Awasthi

Reputation: 23587

Best way to check duplicate records in database | Hibernate

I need to create gift card entity in DB where gift card codes are being generated by an algorithm and chances of their collision are quite low.

Still before inserting gift card entities in DB , we need to check the possibilities of duplicate gift card entities.

Also need to take care following points

  1. Need to generate same number of gift card entities as required by admin
  2. In order for point 1 to follow, we need to find which gift card code already exists in DB and need to generate code again.

I am using Hibernate for DB operations and I have following options.

First option

  1. Create list of gift card codes
  2. start creating entity for each gift card code and try to insert them in DB.
  3. If we found any exception for pre existing gift card entity, store that code in discarded code list.
  4. Again repeat process for discarded list

Second Option

  1. Generate list of gift card codes.
  2. Check for duplicate codes in DB before creating gift card entities.
  3. For discarded list , again create gift card code and follow point 2.
  4. For final list create entities and insert in to DB

I am a little confused about which approach should be preferable ?

Upvotes: 1

Views: 2652

Answers (2)

user3679868
user3679868

Reputation: 693

To make sure that duplicates are not allowed, you must have a unique index and/or a unique constraint on the column used to store your gift card codes.

That said, I would recommend the first option because it is more efficient if there is low chances of gift card code duplicates. Also in the second option, even if you check for duplicates before inserting new codes, you have no guarantee that a code with the same value has not been inserted in the DB between your check and insertion, so you would also have to handle the case when insertion throws a constraint exception.

Upvotes: 1

uaiHebert
uaiHebert

Reputation: 1912

I would create all and then would check for duplicated values with a JPQL lilke:

select p from Ticket p where p.id in (:idList)

With this kind of "batch" operation you would not need to make a trip to the database at each iteration. That is the problem of your first option.

If you use somekind of database rule, e.g.: constraint or trigger, you could receive a exception that could cause your transaction to rollback.

Upvotes: 0

Related Questions