Hibernate constraint ConstraintViolationException. Is there an easy way to ignore duplicate entries?

Question

Basically I've got the below schema and I'm inserting records if they don't exists. However when it comes to inserting a duplicate it throws and error as I would expect. My question is whether there is an easy way to make Hibernate to just ignore inserts which would in effect insert duplicates?

CREATE TABLE IF NOT EXISTS `method` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;


SEVERE: Duplicate entry 'GET' for key 'name'
Exception in thread "pool-11-thread-4" org.hibernate.exception.ConstraintViolationException: could not insert:

Answer 1

My question is whether there is an easy way to make Hibernate to just ignore inserts which would in effect insert duplicates?

How could Hibernate possibly know that a record has a non unique value without actually inserting the record?

If you are doing batch inserts and don't want to rollback the whole transaction and discard the session in case of a ConstraintViolationException (that's what you should do in theory after an exception, see this thread and this previous answer), my suggestion would be to use the StatelessSession API and to catch the ConstraintViolationException.

Answer 2

Just like Pascal said, Hibernate won't know if it is a duplicate until it has actually been insrted.

I encountered a similar situation before; several threads were doing insertions to the DB, but some of those would be duplicates. All I did was to catch and gracefully recover when one of those exceptions were thrown.

Answer 3

Hibernate can't do this. You can however do it yourself. Essentially there are two options:

1. Check for duplicates before insert; or
2. Catch the ConstraintViolationException and then make sure that the violation is due to a duplicate (since there can be other reasons, like null fields).

The former has the disadvantage that you are checking for duplicates on every insert when the likelihood of there actually being a duplicate may be low. The latter will be faster for usual case where no duplicates occur, but has the disadvantage that you need to check for duplicates after the face. When a ConstraintViolationException is thrown it invalidates the current session; this means you will need to flush before doing a search of a duplicate.

Checking for duplicates before insert is probably the cleanest approach unless there is a major performance problem that you need to worry about. Make sure you do the lookup and insert in a transaction to ensure that someone doesn't add a duplicate between the lookup and insert, otherwise you will get a ConstraintViolationException.

Answer 4

Instead of using createQuery() in hibernate, use createSQLQuery() and try a query like "REPLACE INTO method (id) VALUES (123);"