CODEWITHSUNDEEP
javadatabase-connectionapache-commons-dbcptry-with-resources
Ujjwal Jung Thapa
Ujjwal Jung Thapa

Reputation: 634

Apache dbcp connection pooling not working properly

I am trying to connect to mysql database using apache dbcp2 connection pooling following its tutorial from here:

https://git-wip-us.apache.org/repos/asf?p=commons-dbcp.git;a=blob;f=doc/PoolingDataSourceExample.java;h=2a12c74898930b9623223db1597b8a8052a6f1df;hb=HEAD

My Database connection class that return the connection looks something like this:

public class DbConnection {

private static interface Singleton {

    final DbConnection INSTANCE = new DbConnection();
}

private final PoolingDataSource<PoolableConnection> dataSource;

private DbConnection() {
    // A ConnectionFactory that the pool will use to create Connections.
    DriverManagerConnectionFactory connectionFactory = new DriverManagerConnectionFactory(url, user, pass);  //url,user,pass for db connnection

    //implement the pooling functionality.
    PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);
    GenericObjectPoolConfig config = new GenericObjectPoolConfig();
    config.setMaxWaitMillis(500);
    config.setMaxTotal(20);
    config.setMaxIdle(5);
    config.setMinIdle(5);
    //PoolingDataSource expect an ObjectPool
    ObjectPool<PoolableConnection> connectionPool = new GenericObjectPool<>(poolableConnectionFactory, config);
    // Set the poolableConnectionFactory's pool property to the owning pool
    poolableConnectionFactory.setPool(connectionPool);

    this.dataSource = new PoolingDataSource<>(connectionPool);
}

public static Connection getCon() throws SQLException {
    return Singleton.INSTANCE.dataSource.getConnection();
}
}

I am using apache jmeter to test the connection and return something from my mysql database. I created 100 users with Ramp-up periods(in seconds) being 2 second. I created a Http request and when I tried to see my response in view results tree I successfully get response for first 20 request. Later requests from (21 to 100) have blank response. I have gone through many of the issues involving:

java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object

I access my code as:

try (PreparedStatement ps = DbConnection.getCon().prepareStatement("SELECT id FROM test WHERE id =?;")) {
        ps.setString(1, id);
        try (
                ResultSet rs = ps.executeQuery()) {
            return rs.next();
        }
    } catch (Exception ex) {

    }

Upvotes: 1

Views: 1297

Answers (1)

Ori Marko
Ori Marko

Reputation: 58774

You aren't closing Connection object, you must declare such variable in try-with-resources block :

try (Connection conn = DbConnection.getCon(); 
       PreparedStatement ps = conn.prepareStatement("SELECT id FROM test WHERE id =?;")) {

Also you need to close ResultSet, either call close method ResultSet in method or add it to try-with-resources block using a new method returning it

Upvotes: 1

Related Questions