How to properly close and maintain connections in a connection pool in Java

Question

A few weeks ago I began using JDBC to establish a connection to my mysql database and send queries. However, I noticed that it slowed down my Java application so I started reading about connection pooling. I decided to use c3p0 to establish my connection pool. This is the code I used:

public static void setUpPool(String id, String pass, String url, String driver) {

    cpds = new ComboPooledDataSource();
    try {
        cpds.setDriverClass(driver);
    } catch (PropertyVetoException e) {
        e.printStackTrace();
    }
    cpds.setJdbcUrl(url);
    cpds.setUser(id);
    cpds.setPassword(pass);

    // the settings below are optional -- c3p0 can work with defaults
    cpds.setMinPoolSize(3);
    cpds.setAcquireIncrement(5);
    cpds.setMaxPoolSize(20);

}

public static Connection getConnection() {
    // The DataSource cpds is now a fully configured and usable pooled
    // DataSource
    try {
        return cpds.getConnection();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

So my question is 1. How do you properly get rid of the connection once you are done with it? Will close() be enough? 2. Should I use the method cpds.setCheckoutTimeout(checkoutTimeout)? 3. Is there anything else I should keep in mind when maintaining connections in a connection pool?

Moreover, I am also getting this warning: WARNING: Bad pool size config, start 3 < min 5. Using 5 as start. Does anyone know what this means?

Thanks in advance.

Answer 1

1. How do you properly get rid of the connection once you are done with it? Will close() be enough?

Yes. Just make sure each Connection gets close()ed, using try-with-resources or in carefully written finally clauses [carefully written so that Exceptions while close()ing other resources, for example, don't prevent Connections from getting close() called].

2. Should I use the method cpds.setCheckoutTimeout(checkoutTimeout)?

No, not necessarily. If your application is running well, there'll be little need for it, as Conections will be checked out promptly. If your application leaks Connections (i.e., you sometimes fail to close() them), then absence of a checkout timeout will cause clients to hang indefinitely, which is usefully clear. a checkoutTimeout is most helpful in circumstances where, due to hardware limitations or whatever, you can't configure your application to always handle its load, and you'd rather tell clients to come back later than have them stand in line.

3. Is there anything else I should keep in mind when maintaining connections in a connection pool?

Yes. You should probably set up Connection testing. That might be as simple as adding

cpds.setTestConnectionOnCheckout( true )

to your setUpPool(...) method. See here