Abstract connection pool class

I am implementing an abstract class for connection pools. The goal is to inherit this class to manage the connection pool for each database used in the program and to encapsulate common queries. I have server doubts:

• Is it efficient to get the connection from the pool and close it after the request is done, or is it better to implement it in a different layer to open the connection? (method selecSimple). I have checked 100,000 requests:

  • It took 86440 milliseconds to get and close the connection from the pool for every request.

  • And 81107 milliseconds of getting only one connection from the pool and using it to do all the requests, so I guess there isn't that much difference.

• I am passing the ResultSet data to another container to release the connection as soon as possible, even if I have to iterate over the data twice to put the data into a new container and another later when it is in use. Do you think this is good practice or is it better to implement queries at a different level?

public abstract class ConnectionPool implements IConnectionPool {
/**  Connection Pool dataSource */
private DataSource datasource;

@Override
public Connection getConnection() throws SQLException {
    return datasource.getConnection();
}

@Override
public void closeConnectionPool() {
    datasource.close();
}

@Override
public void setConnectionPool (String url, String driver, String user, String pass) throws SQLException {
    // Using tomcat connection pool but it could be other
    PoolProperties pool = new PoolProperties();
    pool.setUrl(url);
    // Set pool configuration
    ...    
    datasource = new DataSource();
    datasource.setPoolProperties(pool);
}

// QUERIES  
@Override
public List<Map<String, Object>> selectSimple (String query, String [] params) throws SQLException {        
    PreparedStatement ps = null;
    ResultSet rs = null;
    Connection con = null;
    List<Map<String, Object>> results = null;
    // Set the preparedstatement
    ...     
    try {
        con = getConnection();
        ps = con.prepareStatement(selectString);
        rs = ps.executeQuery(selectString);
        results = resultSetToArrayList(rs);

        rs.close();
        ps.close();
        con.close();
    } catch (SQLException e) {
        throw new SQLException ();
    } finally {
        if (rs != null) rs.close();
        if (ps != null) ps.close();
        if (con != null) ps.close();
    }
    return results;
}

/** Transforms ResultSet into a List of Maps */
private List<Map<String, Object>> resultSetToArrayList(ResultSet rs) throws SQLException {
    ...
    return list;
}

      

        
        
        
      

    

So when the application finishes, will it be sufficient to close the data source to release the resources? (closeConnectionPool method). I would really appreciate any help. Thanks in advance.