4

I am writing batch processing. I load CSV file (up to 10.000 rows) and I insert them in database. I do not want my processing to fail in case of duplicates.

Is it possible to do such INSERT in single transaction, catch exception for duplicates, proceed with INSERT and commit the transaction? Or, is there no other way than transaction-per-INSERT pattern?

Currently, my transaction is always rolled back for PersistenceException, no matter how I configure noRollbackFor attribute for my transaction (Transactional annotation).

I use Spring Boot 1.4.2 (with Hibernate 5 and Spring Framework 4.3.5).

roeygol
  • 4,908
  • 9
  • 51
  • 88
Bartosz Bilicki
  • 12,599
  • 13
  • 71
  • 113
  • 1
    Please check this link http://stackoverflow.com/questions/29852748/jdbc-preparedstatement-batch-continue-insert-on-error. Might help. – SachinSarawgi Dec 05 '16 at 12:06
  • Have you tried `try{} catch(DataAccessException e){//do nothing}` inside the `@Transactional` method? – isah Dec 05 '16 at 12:09
  • yes, it still rollbacks. Debuging shows that PersistenceException is burried inside entityManager... – Bartosz Bilicki Dec 05 '16 at 12:22
  • PersistenceException extends RuntimeException so I think catching it and doing a noop is not gonna work: http://codetreasury.com/2014/01/25/springs-transactional-important-points/ Do you have other transactional methods being called from your @Transactional method? If so, read the link and see if it applies. You could perhaps show your code? – Steve Harrington Dec 05 '16 at 17:15
  • Possible duplicate of [JDBC PreparedStatement Batch continue insert on error](https://stackoverflow.com/questions/29852748/jdbc-preparedstatement-batch-continue-insert-on-error) – Bartosz Bilicki Nov 02 '17 at 08:47
  • @Bartosz Bilicki, How have you fixed the issue? I am also facing the same. I want Spring to ignore duplicate Key Exceptions and proceed further. https://stackoverflow.com/questions/54343137/problem-in-handling-unit-of-work-using-hibernate-jpa – Alagammal P Jan 28 '19 at 12:21
  • @AlagammalP I have choosen to check if rows I am about to insert are already present, and reject rows where primary key was already existing. It somewhat complicates and slows down the process, but it provides good error reporting (list existing primary keys). Note that check-then insert pattern does not work if there are multiple concurrent upload processes. – Bartosz Bilicki Feb 01 '19 at 21:27

0 Answers0