package TestPool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.dbcp2.BasicDataSource;

public class TestConnPool {

/**
 * @param args the command line arguments
 */
public static void main(String[] args) {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    try {
        BasicDataSource bds = DataSource.getInstance().getBds();
        connection = bds.getConnection();
        statement = connection
                .prepareStatement("select id from TestConnPool;");
        resultSet = statement.executeQuery();
        while (resultSet.next()) {
            System.out.println(resultSet.getString("id"));
        }

    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
}

package TestPool;

import org.apache.commons.dbcp2.BasicDataSource;

public class DataSource {

    private static final String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";
    private static final String DB_URL = "jdbc:mysql://localhost:3306/test";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "root";
    private static final int CONN_POOL_SIZE = 50;
    private static final int MIN_CONN_POOL_SIZE = 30;
    private static final int MAX_CONN_POOL_SIZE = 45;

    private BasicDataSource bds = new BasicDataSource();

    private DataSource() {
        //Set database driver name
        bds.setDriverClassName(DRIVER_CLASS_NAME);
        //Set database url
        bds.setUrl(DB_URL);
        //Set database user
        bds.setUsername(DB_USER);
        //Set database password
        bds.setPassword(DB_PASSWORD);
        //Set the connection pool size
        bds.setInitialSize(CONN_POOL_SIZE);
        //Set the connection minimum pool size
        bds.setMinIdle(MIN_CONN_POOL_SIZE);
        //Set the connection maximum pool size
        bds.setMaxTotal(MAX_CONN_POOL_SIZE);

    }

    public static class DataSourceHolder {
        private static final DataSource INSTANCE = new DataSource();
    }

    public static DataSource getInstance() {
        return DataSourceHolder.INSTANCE;
    }

    public BasicDataSource getBds() {
        return bds;
    }

    public void setBds(BasicDataSource bds) {
        this.bds = bds;
    }
}