title: Connections not being released to pool in multithreaded program
body: <h3>Hello all</h3>
  
  <p>For some reason my connections are not being released. I have been sitting with this problem for the good part of the day, so now I am hoping one of you can help me.</p>
  
  <p>The DataSource is located in a Swagger jaxws server. So on each request I am retrieving a connection from the pool. This is my DataSource class which returns a connection from the pool:</p>
  
  <pre><code>import java.beans.PropertyVetoException;
  import java.io.IOException;
  import java.sql.Connection;
  import java.sql.SQLException;
  import org.apache.commons.dbcp2.BasicDataSource;
  /**
   *
   * @author Lagoni
   */
  public class DataSource {
      private static DataSource datasource;
      private BasicDataSource ds;
      private DataSource() throws IOException, SQLException, PropertyVetoException {
          ds = new BasicDataSource();
          ds.setDriverClassName("org.postgresql.Driver");
          ds.setUsername("username");
          ds.setPassword("pw");
          ds.setUrl("jdbc:postgresql://host" + 5432 + "/db");
  
          ds.setMaxWaitMillis(20000); //wait 10 seconds to get new connection
          ds.setMaxTotal(5);
          ds.setMaxIdle(5);
          ds.setTestWhileIdle(true);
          ds.setTestOnReturn(true);
          ds.setTimeBetweenEvictionRunsMillis(1000);
          ds.setSoftMinEvictableIdleTimeMillis(100); 
          ds.setMinEvictableIdleTimeMillis(10); 
          ds.setMaxConnLifetimeMillis(1000*60*10);
      }
  
      public static DataSource getInstance() throws IOException, SQLException, PropertyVetoException {
          if (datasource == null) {
              datasource = new DataSource();
          }
          return datasource;
      }
  
      public Connection getConnection() throws SQLException {
          return ds.getConnection();
      }
  
  }
  </code></pre>
  
  <p>For each function which requires the use of a database connection retrieve it by calling:</p>
  
  <pre><code>Connection con = DataSource.getInstance().getConnection();
  </code></pre>
  
  <p>This is where i am getting "Cannot get a connection, pool error Timeout waiting for idle object". I am making sure only one connection is being used per thread. So if the function needs to make multiple calls to the database it reuses the con variable.</p>
  
  <p>I have come to the conclusion that I should never call con.close() since it then will return a null connection to the pool. When a function is done with the connection the following are being called:</p>
  
  <pre><code>resultSet.close();
  statement.close();
  </code></pre>
  
  <p>Is there something I am forgetting to do before connections are being declared idle? Or am I simply not implementing the connection pool correct? Should I try use another kind of pool or?</p>
  
  <p>I am using the following maven dependencies:</p>
  
  <pre><code>&lt;dependency&gt;
    &lt;groupId&gt;org.apache.commons&lt;/groupId&gt;
    &lt;artifactId&gt;commons-dbcp2&lt;/artifactId&gt;
    &lt;version&gt;2.1.1&lt;/version&gt;
  &lt;/dependency&gt;
  &lt;dependency&gt;
    &lt;groupId&gt;org.apache.commons&lt;/groupId&gt;
    &lt;artifactId&gt;commons-pool2&lt;/artifactId&gt;
    &lt;version&gt;2.4.3&lt;/version&gt;
  &lt;/dependency&gt;
  &lt;dependency&gt;
    &lt;groupId&gt;org.postgresql&lt;/groupId&gt;
    &lt;artifactId&gt;postgresql&lt;/artifactId&gt;
    &lt;version&gt;42.1.4&lt;/version&gt;
  &lt;/dependency&gt;
  </code></pre>
  
  <h2>Edit 1</h2>
  
  <p>Would this be a solution? </p>
  
  <pre><code>   try(Connection con = DataSource.getInstance().getConnection()){
          UploadedFile file = new FileServerImplementation().uploadFile(fileType, fileName, folderId, projectId, tokenString, fileInputStream, con);
          if(file != null){
              return Response.ok().entity(file).build();
          }
      }
  </code></pre>
  
  <p>Here the uploadFile method is the following:</p>
  
  <pre><code>public UploadedFile uploadFile(String fileType, String fileName, Long folderId, Long projectId, String tokenString, InputStream fileInputStream, Connection con) throws SQLException, IOException, PropertyVetoException{
      IOController ioController = new IOController();
      DatabaseController controller = DatabaseController.getInstance();
      UploadedFile file = null;
      if(ioController.uploadFile(fileType, fileName, controller.getFolderPath(folderId, con), projectId, fileInputStream)){
          file = controller.uploadFile(fileType, fileName, folderId, projectId, con);
      }else{
          System.out.println("Error uploading " + fileName + " to folder!");
      }
      return file;
  }
  </code></pre>
  
  <p>Where IOController saves the file to on the disk, and afterwards the method uploadFile uploads some data to the database. Or should I each time I call a method in the DatabaseController class get a new connection from the pool?</p>
  
  <h1>Solution</h1>
  
  <p>I ended up using edit 1 as an approach. I just had to make sure i did not create unnecessary connection without closing them again.</p>