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

Check Also

How Parents Can Organize the Day During Remote Learning at Home

How Parents Can Organize the Day During Remote Learning at Home

As we are in the middle of a pandemic, parents need to take more responsibility …

Leave a Reply

Your email address will not be published. Required fields are marked *