Connections not being released to pool in multithreaded program

Question

Hello all

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.

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:

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();
    }

}

For each function which requires the use of a database connection retrieve it by calling:

Connection con = DataSource.getInstance().getConnection();

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.

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:

resultSet.close();
statement.close();

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?

I am using the following maven dependencies:

<dependency>
  <groupId>org.apache.commons</groupId>
  <artifactId>commons-dbcp2</artifactId>
  <version>2.1.1</version>
</dependency>
<dependency>
  <groupId>org.apache.commons</groupId>
  <artifactId>commons-pool2</artifactId>
  <version>2.4.3</version>
</dependency>
<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.1.4</version>
</dependency>

Edit 1

Would this be a solution?

   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();
        }
    }

Here the uploadFile method is the following:

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;
}

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?

Solution

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.

Answer 1

When using a connection pool you need to close the connection to release it to the pool for re-use. Do not hold the connection in the thread.