java Spring jdbcTemplate连接已关闭
我的Jersey REST应用程序存在一个长期运行的问题，它利用Spring jdbcTemplate对数据库进行基本的SELECT、INSERT、UPDATE和DELETE查询（我们使用DB2）
这个问题每隔几天就会发生一次，所以我没有一个系统。在错误内容之外（下次发生时，我将包括错误的屏幕截图）。每隔几天左右，我的REST服务中的一些查询就会因为“连接关闭”而失败错误每当我遇到这个错误时，我只需重新启动tomcat应用程序服务器，问题就会在几天内得到解决，直到它再次发生
一旦终端用户开始使用，每隔几天重新启动服务器将不是一个可接受的解决方案。因此，如果有人知道为什么会发生这种情况，以及我如何能够永久解决这个问题，请让我知道
以下是我的Spring数据源配置：
package com.my.package;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import javax.sql.DataSource;
@Configuration
public class DataSourceConfig {
@Bean (name = "dataSource1")
@Primary
@ConfigurationProperties(prefix = "ds1.datasource")
public DataSource dataSource1() {
return DataSourceBuilder.create().build();
}
@Bean(name = "ds1")
public JdbcTemplate jdbcTemplate1(@Qualifier("dataSource1") DataSource dataSource1) {
return new JdbcTemplate(dataSource1);
}
@Bean (name = "dataSource2")
@ConfigurationProperties(prefix="ds2.datasource")
public DataSource dataSource2() { return DataSourceBuilder.create().build(); }
@Bean(name = "ds2")
public JdbcTemplate jdbcTemplate2(@Qualifier("dataSource2") DataSource dataSource2) {
return new JdbcTemplate(dataSource2);
}
}
这是我的申请表。特性：
ds1.datasource.url=url1
ds1.datasource.username=user1
ds1.datasource.password=pass1
ds1.datasource.driver-class-name=com.ibm.db2.jcc.DB2Driver
ds2.datasource.url=url2
ds2.datasource.username=user2
ds2.datasource.password=pass2
ds2.datasource.driver-class-name=com.ibm.db2.jcc.DB2Driver
我的pom。xml，其中包括spring jdbc和驱动程序依赖项
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>ibm.db2</groupId>
<artifactId>db2jcc4.jar</artifactId>
<version>4.19.26</version>
</dependency>
最后是我使用jdbcTemplate执行一个简单的SELECT查询的示例
@Autowired
@Qualifier("ds1")
private JdbcTemplate jdbcTemplate;
List<Something> sampleQuery(){
String sqlQuery = "SELECT * FROM TABLE";
try {
return this.jdbcTemplate.query(
sqlQuery,
(rs, rowNum) -> {
Something something = new Something();
something.setVal1(rs.getString("FIELD1").trim());
something.setVal2(rs.getString("FIELD2").trim());
return something;
});
}catch (Exception ex){
ex.printStackTrace();
System.out.println("error...");
return new ArrayList<>();
}
}
编辑：错误仍在发生。这次我能够捕获日志。它基本上只是说“无法验证新建立的连接。”我不知道为什么
org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Failed to validate a newly established connection.
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:615)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:680)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:722)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:772)
at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:144)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:161)
at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$TypeOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:205)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:99)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:389)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:347)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:102)
at org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:326)
at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)
at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)
at org.glassfish.jersey.internal.Errors.process(Errors.java:315)
at org.glassfish.jersey.internal.Errors.process(Errors.java:297)
at org.glassfish.jersey.internal.Errors.process(Errors.java:267)
at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:317)
at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:305)
at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1154)
at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:473)
at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:427)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:388)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:341)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:228)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.springframework.boot.actuate.autoconfigure.EndpointWebMvcAutoConfiguration$ApplicationContextHeaderFilter.doFilterInternal(EndpointWebMvcAutoConfiguration.java:261)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.springframework.boot.actuate.trace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:115)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:87)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:121)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.springframework.boot.actuate.autoconfigure.MetricsFilter.doFilterInternal(MetricsFilter.java:103)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:522)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1095)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:672)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1502)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1458)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Failed to validate a newly established connection.
at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:811)
at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:626)
at org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:185)
at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:127)
at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
... 81 more
编辑#2:最后将@exodus的答案标记为正确答案。在阅读了他的答案并查看了他提供的SpringJDBC文档（这使我找到了底层的TomcatJDBC连接池文档）之后，我开始使用许多不同的属性，直到它开始工作为止
以下是我在生产环境中使用的当前配置：
ds1.datasource.url=jdbc:db2://database.domain.com:12345/DBMS
ds1.datasource.username=admin
ds1.datasource.password=admin
ds1.datasource.driver-class-name=com.ibm.db2.jcc.DB2Driver
ds1.datasource.max-active=200
ds1.datasource.max-idle=200
ds1.datasource.max-wait=20000
ds1.datasource.min-idle=50
ds1.datasource.test-while-idle=true
ds1.datasource.test-on-borrow=true
ds1.datasource.validation-query=SELECT 1 FROM SYSIBM.SYSDUMMY1
ds1.datasource.time-between-eviction-runs-millis=30000
ds1.datasource.remove-abandoned=true
ds1.datasource.remove-abandoned-timeout=30
ds1.datasource.abandon-when-percentage-full=50
ds1.datasource.initial-size=50
ds1.datasource.jdbcInterceptors=ResetAbandonedTimer
对ds2重复上述步骤。自从我在大约6个月前实现它以来，这个配置一直工作得很好
# 1 楼答案
基于您的上下文，我将尝试配置
DataSourcebean的一些参数。也许过了几天，你就失去了联系，因为有些联系从未被释放或完成（我只是猜测）尝试将这些属性添加到应用程序中。属性：
同样适用于
ds2正确的值取决于您的环境/硬件。请查看here以获得有关数据源和池的简明解释
Here您可以在spring上找到另一个关于DataSource的有趣问题，这可能会对您有所帮助