CODEWITHSUNDEEP
javaspringhibernatespring-dataspring-data-jpa
Shameer
Shameer

Reputation: 71

Spring data: DeleteAll and Insert in same transaction

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);
}

Upvotes: 6

Views: 15713

Answers (5)

Shaolin
Shaolin

Reputation: 173

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.

Upvotes: 1

Tonsic
Tonsic

Reputation: 1138

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).

Upvotes: 2

soufiane ELAMMARI
soufiane ELAMMARI

Reputation: 1029

Heey Warriors,finally this works for me.

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

Good Luck ;)

Upvotes: 0

runtime_expection
runtime_expection

Reputation: 141

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()

Upvotes: 10

RKT
RKT

Reputation: 314

Use flush between deleteall and save.

Upvotes: 5

Related Questions