如题，数据量较大时，通过后台循环表里，单条插入，占用数据库时间太长，请问在使用EntityManager执行原生sql事，是否有办法可以将需要插入的集合数据，一起批量提交
你好，谢谢解答，但是我现在的需求是不使用DM，使用EntityManager执行原生sql，如何实现批量insert
你好，因为demo里DataManager和EntityManager都是对实体对象进行批量提交，我这边需求是执行原生sql实现，所以参考了JdbcTest文件里的逻辑，但是编译的时候，无法注入
请问是哪里的配置需要修改吗
能发一下完整的错误堆栈吗？
你用的是 CUBA？我看注入的关键字使用的是 @Inject
是用的cuba，7.1版本，用@Autowired和@Inject都报一样的错
refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'saftyedutrain_AppOnlineExamService': Unsatisfied dependency expressed through field 'jdbc'; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 'org.springframework.jdbc.core.JdbcTemplate' available: expected at least 1 bean which qualifies as autowire candidate. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true)}
17:58:50.283 INFO c.h.c.c.s.CubaThreadPoolTaskScheduler - Shutting down ExecutorService 'restapi_scheduler'
17:58:50.286 INFO c.h.c.c.s.CubaThreadPoolTaskScheduler - Shutting down ExecutorService 'scheduler'
17:58:50.330 ERROR c.h.c.c.s.AbstractWebAppContextLoader - Error initializing application
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'saftyedutrain_AppOnlineExamService': Unsatisfied dependency expressed through field 'jdbc'; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 'org.springframework.jdbc.core.JdbcTemplate' available: expected at least 1 bean which qualifies as autowire candidate. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true)}
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:596) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:90) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessProperties(AutowiredAnnotationBeanPostProcessor.java:374) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1411) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:592) ~[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.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:144) ~[spring-context-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:95) ~[spring-context-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at com.haulmont.cuba.core.sys.CubaClassPathXmlApplicationContext.<init>(CubaClassPathXmlApplicationContext.java:27) ~[cuba-global-7.1.1.jar:7.1.1]
at com.haulmont.cuba.core.sys.CubaCoreApplicationContext.<init>(CubaCoreApplicationContext.java:26) ~[cuba-core-7.1.1.jar:7.1.1]
at com.haulmont.cuba.core.sys.AppContextLoader.createApplicationContext(AppContextLoader.java:94) ~[cuba-core-7.1.1.jar:7.1.1]
at com.haulmont.cuba.core.sys.AppContextLoader.createApplicationContext(AppContextLoader.java:39) ~[cuba-core-7.1.1.jar:7.1.1]
at com.haulmont.cuba.core.sys.AbstractAppContextLoader.initAppContext(AbstractAppContextLoader.java:62) ~[cuba-global-7.1.1.jar:7.1.1]
at com.haulmont.cuba.core.sys.AbstractWebAppContextLoader.contextInitialized(AbstractWebAppContextLoader.java:83) ~[cuba-global-7.1.1.jar:7.1.1]
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4682) [catalina.jar:9.0.19]
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5150) [catalina.jar:9.0.19]
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183) [catalina.jar:9.0.19]
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:713) [catalina.jar:9.0.19]
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:690) [catalina.jar:9.0.19]
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:695) [catalina.jar:9.0.19]
at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1133) [catalina.jar:9.0.19]
at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1867) [catalina.jar:9.0.19]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_171]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_171]
at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75) [tomcat-util.jar:9.0.19]
at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:112) [na:1.8.0_171]
at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:1045) [catalina.jar:9.0.19]
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:429) [catalina.jar:9.0.19]
at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1576) [catalina.jar:9.0.19]
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:309) [catalina.jar:9.0.19]
at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:123) [catalina.jar:9.0.19]
at org.apache.catalina.util.LifecycleBase.setStateInternal(LifecycleBase.java:423) [catalina.jar:9.0.19]
at org.apache.catalina.util.LifecycleBase.setState(LifecycleBase.java:366) [catalina.jar:9.0.19]
at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:929) [catalina.jar:9.0.19]
at org.apache.catalina.core.StandardHost.startInternal(StandardHost.java:831) [catalina.jar:9.0.19]
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183) [catalina.jar:9.0.19]
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1377) [catalina.jar:9.0.19]
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1367) [catalina.jar:9.0.19]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_171]
at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75) [tomcat-util.jar:9.0.19]
at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:134) [na:1.8.0_171]
at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:902) [catalina.jar:9.0.19]
at org.apache.catalina.core.StandardEngine.startInternal(StandardEngine.java:262) [catalina.jar:9.0.19]
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183) [catalina.jar:9.0.19]
at org.apache.catalina.core.StandardService.startInternal(StandardService.java:423) [catalina.jar:9.0.19]
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183) [catalina.jar:9.0.19]
at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:932) [catalina.jar:9.0.19]
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183) [catalina.jar:9.0.19]
at org.apache.catalina.startup.Catalina.start(Catalina.java:633) [catalina.jar:9.0.19]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_171]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_171]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:350) [bootstrap.jar:9.0.19]
at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:492) [bootstrap.jar:9.0.19]
Caused by: org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 'org.springframework.jdbc.core.JdbcTemplate' available: expected at least 1 bean which qualifies as autowire candidate. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true)}
at org.springframework.beans.factory.support.DefaultListableBeanFactory.raiseNoMatchingBeanFound(DefaultListableBeanFactory.java:1654) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1213) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1167) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at com.haulmont.cuba.core.sys.CubaDefaultListableBeanFactory.resolveDependency(CubaDefaultListableBeanFactory.java:59) ~[cuba-global-7.1.1.jar:7.1.1]
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:593) ~[spring-beans-5.1.6.RELEASE.jar:5.1.6.RELEASE]
... 61 common frames omitted
二月 14, 2023 5:58:50 下午 org.apache.catalina.core.StandardContext listenerStart
严重: 异常将上下文初始化事件发送到类的侦听器实例.[com.haulmont.cuba.core.sys.AppContextLoader]
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'saftyedutrain_AppOnlineExamService': Unsatisfied dependency expressed through field 'jdbc'; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 'org.springframework.jdbc.core.JdbcTemplate' available: expected at least 1 bean which qualifies as autowire candidate. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true)}
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:596)
at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:90)
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessProperties(AutowiredAnnotationBeanPostProcessor.java:374)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1411)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:592)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:515)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:320)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:318)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:849)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:877)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:549)
at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:144)
at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:95)
at com.haulmont.cuba.core.sys.CubaClassPathXmlApplicationContext.<init>(CubaClassPathXmlApplicationContext.java:27)
at com.haulmont.cuba.core.sys.CubaCoreApplicationContext.<init>(CubaCoreApplicationContext.java:26)
at com.haulmont.cuba.core.sys.AppContextLoader.createApplicationContext(AppContextLoader.java:94)
at com.haulmont.cuba.core.sys.AppContextLoader.createApplicationContext(AppContextLoader.java:39)
at com.haulmont.cuba.core.sys.AbstractAppContextLoader.initAppContext(AbstractAppContextLoader.java:62)
at com.haulmont.cuba.core.sys.AbstractWebAppContextLoader.contextInitialized(AbstractWebAppContextLoader.java:83)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4682)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5150)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:713)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:690)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:695)
at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1133)
at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1867)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:112)
at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:1045)
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:429)
at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1576)
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:309)
at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:123)
at org.apache.catalina.util.LifecycleBase.setStateInternal(LifecycleBase.java:423)
at org.apache.catalina.util.LifecycleBase.setState(LifecycleBase.java:366)
at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:929)
at org.apache.catalina.core.StandardHost.startInternal(StandardHost.java:831)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1377)
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1367)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:134)
at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:902)
at org.apache.catalina.core.StandardEngine.startInternal(StandardEngine.java:262)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
at org.apache.catalina.core.StandardService.startInternal(StandardService.java:423)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:932)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
at org.apache.catalina.startup.Catalina.start(Catalina.java:633)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:350)
at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:492)
Caused by: org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 'org.springframework.jdbc.core.JdbcTemplate' available: expected at least 1 bean which qualifies as autowire candidate. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true)}
at org.springframework.beans.factory.support.DefaultListableBeanFactory.raiseNoMatchingBeanFound(DefaultListableBeanFactory.java:1654)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1213)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1167)
at com.haulmont.cuba.core.sys.CubaDefaultListableBeanFactory.resolveDependency(CubaDefaultListableBeanFactory.java:59)
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:593)
... 61 more
CUBA 中，需要在你的 core 模块的 spring.xml 中初始化 JdbcTemplate bean：
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="cubaDataSource"/>
</bean>
1 个赞
你好，请问用jdbc.batchUpdate这种方法批量提交，是否需要在代码里再手动关闭连接，现在我们的应用场景是大批量用户考试，每个人执行一次批量提交，将自己的答案提交入库，现在的现象是数据库并发数特别高，CPU占用基本满了，应该是连接没有释放
你的方法上有 @Transactional注解吗？
没有添加
加上试试。可以帮助管理会话，一次批量提交完了之后会关闭会话
这个只是事务注解，如果不加，会导致连接不释放吗？
连接是由 cubaDataSource 管理的，最后交给连接池 HikariCP 管理。可以看看 CUBA 对连接池数量的配置 cuba.dataSource.maximumPoolSize：连接至数据库 - CUBA 框架开发者手册
CPU 高可能跟会话有关，也可能跟并发有关，如果并发高，调大连接池的数量是有效果的。
另外，数据库服务器的性能和表结构的设计也有关系，比如插入数据时是否会触发别的数据库处理等（例，索引太多，插入大量数据需要重新索引）。
请问一下，我们用的中间件的jndi模式连接的数据库，中间件用的是wildfly（undertow），中间件里面也有数据库连接池的相关配置，数据库连接是否就由中间件管理了，跟 cubaDataSource和HikariCP 是不是就没关系了？
请问一下，我们同一个方法里，既用JdbcTemplate写了这段批处理的代码，又调用其他接口或方法通过EntityManager执行nativeSQL和dataManager.commit访问数据库了，这种情况会不会有什么隐患，比如导致我们现在出现的这种连接不释放的情况
是的，这种情况就由中间件管理了，连接池也在中间件配置。
混用是可以的，但是 EM 需要单独管理会话，DM 会自己管理会话。
建议从数据库层面看看 CPU 高时具体连接的使用情况，比如共有多少可用连接，用了多少。