How do I create a standalone connection pool in Java?

Below is the method I generally prefer to create the connection pool for each database. I am using the BasicDataSource class developed by the Apache Software Foundation and available for free. To work with it 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, simply instantiate 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 also set connection pool properties.

ds.setMaxActive(20);

ds.setMaxIdle(2);

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

Typically, you pack all of these statements into one method, like this:

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;

}

To retrieve this, all you have to do is call javax.sql.DataSource’s getConnection() method.

DataSource ds=getDataSource();

Connection conn=ds.getConnection();

Don’t forget to close the connection as this would ensure your connection returns to the pool.

Download sample program from [http://kvinod.com/standalone-dbcp-in-java/StandaloneDBCP.java]

Once you have the connection, you can use it to create statements, PreparedStatements, or for transaction management, etc.

Thanks to Vinod K Kayartaya | #create #standalone #connection #pool #Java