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.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 [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.

This entry was posted in Uncategorized and tagged , , , , , , , , , . Bookmark the permalink.

Leave a Reply