Connection close method doesnt work, too many clients error

Question

Why connection close doesnt work in this code? I tried way without finally block by creating connection in try() but after executing testDbConnection a few times I have too many connections error and when I am trying to connect to my database by pgAdmin I see too many clients connected. How can I resolve it? Why closing connection doesn't work?

 private List<DataSource> getDataSources() {
        connectionsNumber = 2;
        List<DataSource> dataSources = new ArrayList<>();
        for (int i = 1; i <= connectionsNumber; i++) {
            Connection connection;
            DataSource dataSource;
            String jdbcUrl = environment.getProperty(String.format("database%d.url", i));
            String user = environment.getProperty(String.format("database%d.username", i));
            String password = environment.getProperty(String.format("database%d.password", i));
            ComboPooledDataSource cpds = new ComboPooledDataSource();
            try {
                cpds.setDriverClass("org.postgresql.Driver");
            } catch (PropertyVetoException e) {
                e.printStackTrace();
            }
            cpds.setJdbcUrl(jdbcUrl);
            cpds.setUser(user);
            cpds.setPassword(password);
            cpds.setMinPoolSize(3);
            cpds.setAcquireIncrement(5);
            cpds.setMaxPoolSize(20);
            cpds.setMaxIdleTime(1);
            cpds.setMaxConnectionAge(600);
            cpds.setMaxStatements(500);
            dataSource = cpds;
            dataSources.add(dataSource);
        }
        return dataSources;
    }

public void testDbConnection() throws SQLException {
        String query = "select id from users;";
        Statement st = null;
        ResultSet rs = null;
        Connection connection = null;
        List<DataSource> dataSources = getDataSources();
        for (DataSource dataSource : dataSources) {
            try {
                connection = dataSource.getConnection();
                st = connection.createStatement();
                rs = st.executeQuery(query);
                while (rs.next()) {
                    System.out.println("Connection");
                }
            } finally {
                if (st != null) {
                    st.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (connection != null) {
                    connection.close();
                }
                st = null;
                rs = null;
                connection = null;
            }
        }
    }

Answer 1

In my opinion,when using the c3p0, connection.close() is not really closing the connection, just put it back in the pool.if you want to clean up the DataSource,you can use DataSources.destroy(dataSource);