title: Spring jdbcTemplate - Connection is Closed
body: <p>I've had a long running issue with my Jersey REST application that utilizes Spring <code>jdbcTemplate</code> to make basic <code>SELECT</code>, <code>INSERT</code>, <code>UPDATE</code>, and <code>DELETE</code> queries to our database (we use DB2).</p>
  
  <p>This issue happens every few days so I don't have a System.out of the contents of the error (next time it happens I will include a screenshot of the error). Every few days or so, some of the queries in my REST services start failing due to a "Connection is Closed." error. Whenever I get this error, I simply restart the tomcat application server and the issue is resolved for a few days until it starts happening again.</p>
  
  <p>Restarting the server every few days will not be an acceptable solution to our end users once they start using this. So if anyone has any idea why this happens and how I can go about resolving the issue permanently, please let me know.</p>
  
  <p>Here's my Spring datasource config:</p>
  
  <pre><code>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);
      }
  }
  </code></pre>
  
  <p>Here's my application.properties:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>My pom.xml where I include the spring jdbc and driver dependencies</p>
  
  <pre><code>&lt;dependency&gt;
      &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
      &lt;artifactId&gt;spring-boot-starter-jdbc&lt;/artifactId&gt;
  &lt;/dependency&gt;
  &lt;dependency&gt;
      &lt;groupId&gt;ibm.db2&lt;/groupId&gt;
      &lt;artifactId&gt;db2jcc4.jar&lt;/artifactId&gt;
      &lt;version&gt;4.19.26&lt;/version&gt;
  &lt;/dependency&gt;
  </code></pre>
  
  <p>And lastly, an example of me executing a simple <code>SELECT</code> query using jdbcTemplate</p>
  
  <pre><code>@Autowired
  @Qualifier("ds1")
  private JdbcTemplate jdbcTemplate;
  
  List&lt;Something&gt; sampleQuery(){
      String sqlQuery = "SELECT * FROM TABLE";
      try {
          return this.jdbcTemplate.query(
                  sqlQuery,
                  (rs, rowNum) -&gt; {
  
                      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&lt;&gt;();
      }
  }
  </code></pre>
  
  <p><strong>EDIT:</strong> Error is still happening. This time I'm able to capture the log. It basically just says "Failed to validate a newly established connection." I don't know the why though.</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>EDIT#2:</strong> Finally marking @exoddus's answer as the correct one. After reading his answer and looking through the Spring JDBC documentation he provided (which led me to the underlying tomcat JDBC connection pool documentation), I started playing around with a lot of different properties until it just worked.</p>
  
  <p>Here is the current configuration I use in my production environment:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Repeat for ds2. This configuration has been working just fine since I implemented it roughly 6 months ago.</p>