4

I am trying to achieve below native query logic using hibernate (Spring JPA). But save(Iterable) throws exception and rollback the entire transaction if one of the record fails to persist. Is there any way to do to catch the record error and proceed with insertion on other records.

eg:-

Native Sql Query

set autocommit=false
delete from EMPLOYEE;
insert into EMPLOYEE(id, name, sal) values(2, ‘Roy’, ‘rt’); —-stmt1
insert into EMPLOYEE(id, name, sal) values(2, ‘Joe’, 3000);
commit;

Note: sal column is numeric in EMPLOYEE table. Execution continues eventhough stmt1 failed.

Hibernate (CrudRepository)

@Autowired
CrudRepository employeeRepository;

@Transactional
public void saveToDB(List dataList) {
   employeeRepository.deleteAll();
   employeeRepository.save(dataList);
}
Shameer
  • 51
  • 1
  • 1
  • 2
  • Post your hibernate `Entity` and `service`, `DAO` class – sam May 16 '18 at 12:15
  • It is a single transaction and thus atomic either everything succeeds or everything fails. If that isn't happening with plain SQL then it isn't transactional and each statement is executed in its own transaction. So I would say the error is on your plain sql side and not the Spring side (as it adhere's to the ACID principles of the transaction whereas the SQL one isn't). – M. Deinum May 16 '18 at 13:28
  • Make sure that transactions are even enabled in Spring. It's usually done with `@EnableTransactionManagement` annotation on application class or any included `@Configuration` class – Nikolai Shevchenko Jun 29 '20 at 13:13

5 Answers5

6

Anyone else stumbling upon this problem. I managed to get it work with writing own deleteAll Method in RepositoryInterface and setting annotation like this:

@Modifying(flushAutomatically = true)
@Query("delete from MyEntity")
void deleteAllCustom()
runtime_expection
  • 101
  • 1
  • 5
  • good answer but is better to use JpaRepository or CrudRepository – Azzabi Haythem May 05 '21 at 12:51
  • Flush is needed to send instructions to database, but is not committing them so you can still rollback if one instruction fails. If you won't flush, all instructions are cached and executed when you commit them, that's why it is failing. https://stackoverflow.com/a/4275973/4270929 – Paweł Sosnowski Jul 19 '23 at 09:06
3

Use flush between deleteall and save.

RKT
  • 174
  • 1
  • 4
1

I just changed from deleteAll to deleteAllInBatch (JpaRepository interface) and it worked.

deleteAllInBatch results in Hibernate registering a prepared statement delete from yourtable right when deleteAllInBatch is called.

With deleteAll, Hibernate

  1. select all your data into memory, as entities
  2. for each entity, it calls EntityManager.delete
  3. each delete generates a EntityDeleteAction which will be called when transaction ends and session flushes. But for whatever reason, the actions for the inserts end up being called before the deletes (maybe the order is unpredictable, I'm not sure).
Tonsic
  • 890
  • 11
  • 15
  • The order of actions is defined. See my answer. – Shaolin Jun 13 '23 at 09:36
  • Cool, but deleteAllInBatch is still much better than deleteAll+flushs, because it prevents the items I mentioned, plus IMHO avoids the dev having to know the low-level order of actions of a flush, something that should be transparent, abstracted away detail. – Tonsic Jun 15 '23 at 21:29
0

Heey Warriors,finally this works for me.

@Modifying(flushAutomatically = true)
@Transactional
void deleteByProjet(Projet projet);

Good Luck ;)

soufiane ELAMMARI
  • 807
  • 8
  • 12
0

It's important to understand why it happens. There is a defined order of actions performed while flushing the transaction:

OrphanRemovalAction
AbstractEntityInsertAction
EntityUpdateAction
QueuedOperationCollectionAction
CollectionRemoveAction
CollectionUpdateAction
CollectionRecreateAction
EntityDeleteAction

As you can see. The deletion actions are performed after insert actions.

To avoid possible constraint problems you need to flush between deleteAll and inserts.

Shaolin
  • 154
  • 10