recover Hibernate session state after a failed insert

Question

The following code tries to insert an Item object to the db, using Spring+Hibernate. The Item has an Integer id field as primary key, as well as a name column which is subject to a unique constraint (simplified example).
I know that the item's id is null (the item is transient) however the insert may still fail due to the unique constraint on the name field.

try {
  getHibernateTemplate().save(myItem);
  getHibernateTemplate().flush(); // exception thrown here if the name is not unique
}
catch (DataIntegrityViolationException e) {
  Item itemFromDb = (Item) getHibernateTemplate()
    .find("from Item item where item.name = ?",
           myItem.getName()).get(0);
  //
  // copy some properties from myItem to itemFromDb
  //
  getHibernateTemplate.update (itemFromDb)
}

I need this code to run in a loop for many items, all in one transaction, that's why I am trying to issue an update if the insert fails.

However, after the insert fails, the hibernate session is in a strange state, and when I issue the select statement to obtain the item from the db, the original exception is thrown.

I tried using session.evict() after the insert fails, but to no avail. Any idea?

This is what I see in the console after the select fails. Hibernate fails on the select statement but still prints information about the previous insert statement.

WARN  [http-8080-1] hibernate.util.JDBCExceptionReporter (JDBCExceptionReporter.java:77) - SQL Error: 2627, SQLState: 23000
ERROR [http-8080-1] hibernate.util.JDBCExceptionReporter (JDBCExceptionReporter.java:78) - Violation of UNIQUE KEY constraint 'unq_Item_name'. Cannot insert duplicate key in object 'items'.
ERROR [http-8080-1] event.def.AbstractFlushingEventListener (AbstractFlushingEventListener.java:301) - Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: could not insert: [com.sample.Item]
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)

P.S. please don't tell me about hibernate's saveOrUpdate method, I know what it does.

Answer 1

getHibernateTemplate().merge(myItem);

merge - Copy the state of the given object onto the persistent object with the same identifier. If there is no persistent instance currently associated with the session, it will be loaded. Return the persistent instance. If the given instance is unsaved, save a copy of and return it as a newly persistent instance. The given instance does not become associated with the session. This operation cascades to associated instances if the association is mapped with cascade="merge".

Answer 2

Hibernate does not guarantee anything about a session once a database exception is thrown. You are supposed to roll-back. If I understand Bozho's suggestion, he's saying that you should read from the name column first to ensure that the insert won't fail. Makes sense to me.