Spring Boot Insert with manual transaction to Ignore DuplicateKeyException Example

InsertIgnoreUniqErrorManualTxService.java

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

}

InsertKeyMapper.java

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


}

JdbcInsertKeyRepository.java

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

SampleController.java

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