如何使用jpa repository saveAll忽略保存操作的重复插入？-解网

问：

我需要能够使用 spring data CrudRepository 的 saveAll（）方法并忽略唯一约束上的重复插入。

在我当前的项目中，我目前正在使用 spring boot -> jpa 堆栈，我必须处理 excel 文件的导入，最终将多个实体保存到数据库中。我正在使用 liquibase 来跟踪数据库上的更改，并使用 spring 配置文件来管理两个不同的环境（一个使用 Postgres DB，另一个使用 Oracle 19-c）。excel 文件不时通过外部 ftp 服务上传。我有一个文件夹监视服务，它等待该文件夹中的文件更改，并在创建新文件时触发导入。

问题是我有一个独特的约束，文件可能有新行和旧行。通过 POI 处理 Excel 文件非常繁重，我通过 java.util.Set 将计算结果保存在内存中，直到我必须使用 CrudRepository 的 saveAll（）方法持久化 Set 的时间点。

我目前遇到：错误：重复的键值违反了唯一约束“csspd_avoid_duplicates”

org.springframework.transaction.UnexpectedRollbackException：事务被静默回滚，因为它已被标记为仅回滚

导入在第一次遇到副本时停止。

对于同一个项目，我在循环中单次插入（单次 save（））时遇到了类似的问题，并且我设法使用以下代码片段解决了该问题：

                try {

                    repository.save(entity);
                } catch (DataIntegrityViolationException e) {
                    this.log.debug("Duplicate found, skipping");
                }

我已经尝试了相同的批量保存方法，但没有任何运气。

这是感兴趣的方法：

public boolean triggerExcelImport(Path path) {
    try {

        ExcelDataDTO excelExtractedData;
        if (path == null) {
            excelExtractedData = excelImporterService.importExcelFiles();
        } else {
            excelExtractedData = excelImporterService.importStandardXLSXFiles(path);
        }

        staticProductDataRepository.saveAll(excelExtractedData.getProductData());  // <-- This saveAll might have duplicates

        for (Set<AllocationChartData> allocationList : excelExtractedData.getAllocationMap().values()) {
            allocationChartDataRepository.saveAll(allocationList);   // <-- This saveAll might have duplicates
        }

        performanceRepository.saveAll(excelExtractedData.getPerformanceData());    // <-- This saveAll might have duplicates

        return true;
    } catch (DataIntegrityViolationException e) {
        this.log.debug("Duplicate found, skipping");
    }
    return false;
}

我希望导入过程能够实际完成导入，避免在第一个副本上终止。该解决方案必须独立于数据库（我使用的是使用 liquibase 上下文管理的 Postgres 和 Oracle）

这是我遇到的实际异常：

        org.springframework.transaction.UnexpectedRollbackException: Transaction silently rolled back because it has been marked as rollback-only
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:755) ~[spring-tx-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:714) ~[spring-tx-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:533) ~[spring-tx-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:304) ~[spring-tx-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at com.juliusbaer.ims.toolbox.services.CenshareService$$EnhancerBySpringCGLIB$$8e7529c1.triggerCenshareExcelImport(<generated>) ~[classes/:?]
        at com.juliusbaer.ims.toolbox.services.FolderWatchService.readAndCleanUpFile(FolderWatchService.java:168) ~[classes/:?]
        at com.juliusbaer.ims.toolbox.services.FolderWatchService.initializeFileImportAtStartup(FolderWatchService.java:99) ~[classes/:?]
        at com.juliusbaer.ims.toolbox.services.FolderWatchService.setUp(FolderWatchService.java:93) ~[classes/:?]
        at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
        at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
        at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
        at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
        at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleElement.invoke(InitDestroyAnnotationBeanPostProcessor.java:363) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMetadata.invokeInitMethods(InitDestroyAnnotationBeanPostProcessor.java:307) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:136) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyBeanPostProcessorsBeforeInitialization(AbstractAutowireCapableBeanFactory.java:414) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1770) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:593) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:515) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:320) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) [spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:318) [spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) [spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:849) [spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:877) [spring-context-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:549) [spring-context-5.1.6.RELEASE.jar:5.1.6.RELEASE]
        at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:142) [spring-boot-2.1.4.RELEASE.jar:2.1.4.RELEASE]
        at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:775) [spring-boot-2.1.4.RELEASE.jar:2.1.4.RELEASE]
        at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397) [spring-boot-2.1.4.RELEASE.jar:2.1.4.RELEASE]
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:316) [spring-boot-2.1.4.RELEASE.jar:2.1.4.RELEASE]
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:1260) [spring-boot-2.1.4.RELEASE.jar:2.1.4.RELEASE]
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:1248) [spring-boot-2.1.4.RELEASE.jar:2.1.4.RELEASE]
        at com.juliusbaer.ims.toolbox.Application.main(Application.java:12) [classes/:?]

谢谢你的帮助。

编辑：只是为了给你一个简短的解释，唯一约束不是基于单个标识符 ID，它是多列（总共 8 列）的组合

java 异常 jdbc spring-data-jpa 更新插入

如何使用jpa repository saveAll忽略保存操作的重复插入？

How to ignore duplicate insertion for save operation using jpa repository saveAll?

评论