(cross posted from http://forum.springframework.org/showthread.php?t=30331)

I am hoping there is someone out there that can help me with a problem we are having using hibernate with some legacy code.

The general pattern we follow is if we try to delete a row from the database, and it fails due to a constraint error (DataIntegrityViolationException), we will automatically switch the hard delete into a soft delete (update the deleted flag from N to Y). We need to do this because we have no control over how our customers may link in to our base tables for extensions.

We are currently using JdbcTemplate, Declarative TX management and hand crafted sql to do everything and our application/service layer may look like this:


public void deleteCustomer(String id)
{
Customer customer = dao.select(id);

//check customer etc....

try{
dao.delete(customer);
}
catch(DataIntegrityViolationException dive)
{
customer.setDeleted(true);
dao.save(customer);
}


This all works fine and good now, but when we swapped out the DAO implementations with JdbcTemplate -> Hibernate, we soon discovered that if we use Declarative TX management, the DataIntegrityViolationException is not thrown by the DAO (since hibernate does not flush the session until tx.commit())


We added a manual flush() to the dao, but are still running into issues.


For this example, assume customer has a child attribute that maps to another table (CustomerCompany).

In the dao.delete(), the CustomerCompany record may be successfully deleted, but when the flush() issues the sql statement to delete the Customer record, the DataIntegrityViolationException is thrown.

I have tried all manner of session.evict() session.clear() session.load() session.get() to try to re-initialize the Customer and CustomerCompany records from the database, but since the CustomerCompany was successfully deleted, I would have to manually rollback the tx in my dao in order to have refresh() work!


I have to assume that someone else out there has dealt with this problem before (I hope).


The only was I was able to get this to work is using merge() to save, but that will cause the re-insertion of CustomerCompany after it had already been successfully deleted. The set of CustomerCompany in customer is set to cascade all-delete-orphan if that matters.

We are just getting started using hibernate, so please let me know if I left anything out.


Thanks