Following is the method I generally prefer to create the connection pool for any database. I use the BasicDataSource class developed by “Apache Software Foundataion” and available for free. For working with this, you may need to add these jar files to your classpath:

1. commons-dbcp.jar

2. commons-pooljar

3. commons-collections.jar

4. mysql-connector-java-5.1.5-bin.jar (for MySQL database server. Replace this with classes12.jar if you are using Oracle)

To create a DataSource object, just instanciate the BasicDataSource class and set the properties related to the database.

BasicDataSource ds = new BasicDataSource();

ds.setDriverClassName("com.mysql.jdbc.Driver");

ds.setUsername("root");

ds.setPassword("secret");

ds.setUrl("jdbc:mysql://localhost/vinod");

Replace the word “localhost” with the IP address of the remote server, if you are using one.

You can now set the connection-pool properties also.

ds.setMaxActive(20);

ds.setMaxIdle(2);

That’s it. You have just created 20 ready to use connection objects in a pool.

Typically you wrap all these statements in a method as below:

DataSource getDataSource(){

BasicDataSource ds = new BasicDataSource();

ds.setDriverClassName("com.mysql.jdbc.Driver");

ds.setUsername("root");

ds.setPassword("secret");

ds.setUrl("jdbc:mysql://localhost/vinod");

ds.setMaxActive(20);

ds.setMaxIdle(2);

return ds;

}

All you have to do to retrieve this is to call the getConnection() method of javax.sql.DataSource.

DataSource ds=getDataSource();

Connection conn=ds.getConnection();

Don’t forget to close the connection, as this would ensure your connection is returned to the pool.

Download sample program from [

Once you have the connection, you can use the same to create Statement, PreparedStatement or use it for transaction management etc.