Skip to content

Instantly share code, notes, and snippets.

@fliedonion
Created December 20, 2022 17:29
Show Gist options
  • Save fliedonion/7f6ca1c218c0002cf7ac91bfb91aa401 to your computer and use it in GitHub Desktop.
Save fliedonion/7f6ca1c218c0002cf7ac91bfb91aa401 to your computer and use it in GitHub Desktop.
Spring Boot Insert with manual transaction to Ignore DuplicateKeyException Example
package net.case_of_t.egpostgresmybatis.insertIgnoreUniqErrorDomain;
import lombok.RequiredArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import java.util.List;
@Service
@RequiredArgsConstructor
public class InsertIgnoreUniqErrorManualTxService {
private final InsertKeyMapper mapper;
// instead of @Transactional.
@Autowired
PlatformTransactionManager txManager;
public int insertTxManually() {
var def = new DefaultTransactionDefinition();
def.setName("InsertIgnoreDuplicateExceptionSampleTransaction");
def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
TransactionStatus status = txManager.getTransaction(def);
int affectCount=0;
try {
var data = List.of("3", "4", "3", "5");
// 通常の例外と違い、DuplicatieKeyExceptionが無視できない。そのためチェックポイントで制御する。
// (内部トランザクションでロールバックされてしまうのか?)
// Unlike normal exceptions, DuplicatieKeyException cannot be ignored. Therefore, it is controlled by a checkpoint.
// (Will it be rolled back by an internal transaction?)
Object savepoint = status.createSavepoint();
for (var item : data) {
try{
mapper.insert(Integer.parseInt(item));
System.out.println("inserted");
affectCount++;
if (savepoint != null) status.releaseSavepoint(savepoint);
savepoint = status.createSavepoint();
} catch(DuplicateKeyException ex) {
System.out.println("Duplicate");
if (savepoint != null) status.rollbackToSavepoint(savepoint);
}
// DuplicateKeyException 以外は外側のcatchによって ロールバックされる。
// Except for DuplicateKeyException, all other exceptions are rolled back by the outer catch.
}
} catch (Exception ex) {
txManager.rollback(status);
System.out.println(ex.getMessage());
throw ex;
}
txManager.commit(status);
return affectCount;
}
}
package net.case_of_t.egpostgresmybatis.insertIgnoreUniqErrorDomain;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
@Mapper
public interface InsertKeyMapper {
String createTableSql = """
create table insert_uniq_error_testtable(
id numeric(10),
primary key (id)
);
""";
@Insert("""
insert into insert_uniq_error_testtable(
id
) values ( #{id} )
""")
int insert(@Param("id")int id);
}
package net.case_of_t.egpostgresmybatis.insertIgnoreUniqErrorDomain;
// Appendix: You can also use jdbc template instead of mybatis mapper. repository class sample is here.
import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
@RequiredArgsConstructor
public class JdbcInsertKeyRepository {
private final JdbcTemplate jdbcTemplate;
public void insert(String key){
jdbcTemplate.update("insert into insert_uniq_error_testtable(id) values (?)", Integer.parseInt(key));
}
public void insert(int key){
jdbcTemplate.update("insert into insert_uniq_error_testtable(id) values (?)", key);
}
}
package net.case_of_t.egpostgresmybatis.insertIgnoreUniqErrorDomain;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.io.PrintWriter;
import java.io.StringWriter;
@RestController
@RequestMapping
@RequiredArgsConstructor
public class SampleController {
record SimpleResult(String result, String detail, String stackTrace){}
private final InsertIgnoreUniqErrorManualTxService service;
@GetMapping
SimpleResult index() {
try{
service.insertTxManually();
return new SimpleResult("Success", null, null);
}catch(Exception ex) {
StringWriter sw = new StringWriter();
PrintWriter pw = new PrintWriter(sw);
ex.printStackTrace(pw);
return new SimpleResult("Fail", ex.getMessage(), sw.toString());
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment