How To Create A Standalone Connection Pool In Java?

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.jar2. commons-pooljar3. commons-collections.jar4. 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 [http://kvinod.com/standalone-dbcp-in-java/StandaloneDBCP.java]

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