Cannot get a connection, pool error Timeout waiting for idle object while using DBPC2

Question

the pom.xml is following:

 <dependency>
     <groupId>org.apache.commons</groupId>
     <artifactId>commons-dbcp2</artifactId>
     <version>2.9.0</version>
 </dependency>

and the datasource configuration is:

public Connection getConnection(){
 BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName(JDBC_DRIVER);
        dataSource.setUrl(db.url);
        dataSource.setUsername(db.username);
        dataSource.setPassword(db.password);
        dataSource.setInitialSize(2);
        dataSource.setMaxTotal(8);
        dataSource.setLogAbandoned(true);
        dataSource.setMinIdle(2);
        dataSource.setMaxWaitMillis(10000);
        dataSource.setTestOnReturn(true);
        dataSource.setValidationQuery("SELECT 1");
        dataSource.setTimeBetweenEvictionRunsMillis(2);
return dataSource.getConnection()
}

then I called this method to get the connection

 public static JSONArray select_sql(String sql){
 Connection con = null;
        ResultSet rs = null;
        PreparedStatement ps = null;

        try {
            con = DataBasePool.getConnection(systemName);
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery(sql);
            ResultSetMetaData metaData = rs.getMetaData();
            while (rs.next()){
              //do something
            }
         }catch (Exception e) {e.printStrace();
         }finally{
           con.close();// means return this con to pool
         }

}

After get the connection, then do a "for loop", to test what it appears after reach the max connection,

 for (int i = 0; i < 15; i++) {
      JSONArray s = select_sql("Select * from order limit 1");
      System.out.println("---->" + s);
 }

but then the error occurs:

Cannot get a connection, pool error Timeout waiting for idle object

I thought "con.close()" has returned the connection to the pool, when i > 8 in the loop, the dataSource should create an new connection as previous connection has been returned.

then the loop could be continued.

but I am sorry the error occured, the code did NOT follow my idea :(

how could I fix it ? thank you very much!

Answer 1

You are currently experiencing some issues with using a Connection pool. A Connection pool only needs to be created once, and then you can continuously retrieve available connections through operating this Connection pool.

However, your current implementation creates a Connection pool once in each iteration of the for loop, which means it will be created 15 times in your example.

You should create the Connection pool before the for loop, and retrieve connections from the created Connection pool inside the for loop. Then, after you finish using the connection, call close() to release it back to connection pool.

Here is a example for you

public class TestConnectionPool {
    public static void main(String[] args) {
        BasicDataSource dataSource = createConnectionPool();
        
        for (int i = 0; i < 15; i++) {
            Connection conn = null;
            try {
                conn = dataSource.getConnection();
                select_sql("SELECT * FROM mytable", conn);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (conn != null) {
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }
    
    private static BasicDataSource createConnectionPool() {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost/mydb");
        dataSource.setUsername("username");
        dataSource.setPassword("password");
        dataSource.setInitialSize(5);
        dataSource.setMaxTotal(10);
        return dataSource;
    }
    
    private static void select_sql(String sql, Connection conn) throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            // process the result set
        }
        rs.close();
        stmt.close();
    }
}