How to use BoneCP Java Database Connection pool (JDBC Pool) library


Hi Friends, the following course is created by me;

      Click here to attend Spring Framework 4.x and certification course with a discount

Spring Framework and Core Spring Certification Udemy course with discount
Spring Framework and Core Spring Certification Udemy course with a discount coupon

In this post, basically I am going to show you how to configure and get a connection from BoneCP Java Database Connection by providing code examples. This configuration option allows you to define Connection Pool manually without configuring server for the Connection Pool (You don’t need to do anything on Application Server Machine).

By using this solution, you can define the CP from Java Code. And this example is designed to work on any Java EE Servers. If you investigate the documentation of BoneCP, there are some points omitted when you first look at but I will try to explain it by providing more information on this example.

To use the BoneCP first you should download the required jars from the following link;

http://repo1.maven.org/maven2/com/jolbox/bonecp/0.7.1.RELEASE/bonecp-0.7.1.RELEASE.jar

Downloading BoneCP is not enough because there are some dependencies as you can download from here;

BoneCP uses a logging framework so you should download it http://www.slf4j.org/download.html (file name:slf4j-1.6.4.zip)
And for some the of underlying operations it uses Google’s Guava Library can be downloaded here

http://search.maven.org/remotecontent?filepath=com/google/guava/guava/10.0.1/guava-10.0.1.jar

So basically, in this web application, we are going to implement the basic approach (I will provide a better approach such as using Spring’s JDBC template and BoneCP together on my future blog post)  for getting a connections from the database and we will open and close it manually by configuring the BoneCP localy for opening and closing connections.

BoneCP will open connections and  store inside it for us. You can configure how many connections you want by changing the BoneCPConfig config = new BoneCPConfig(); object’s parameters.

First, for this web application we need a ServletContextListener to initialize the connection pool from locally.

Here is the code;

package com.tunatore.db;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

public final class ContextListener implements ServletContextListener {

    public void contextInitialized(ServletContextEvent sce) {
        ConnectionManager.configureConnPool();
    }

    public void contextDestroyed(ServletContextEvent sce) {
        ConnectionManager.shutdownConnPool();
    }

}

The main purpose of this listener is to initialize the connection pool and make the necessary configurations inside the main ConnectionManager class. And there are two methods,

Inside the first method;
ConnectionManager.configureConnPool();
method is used for configuring Conn. Pool and opening the connections automatically for the application. (Or you can set lazy initialization functionality by changing the BoneCPConfig’s config.setLazyInit(true); property. and this allows the program open a connection when you call the getConnection() method for the first time from your application.)

The second method  ConnectionManager.shutdownConnPool(); is used to shutdown the pool and close all connections . This method is important because you have to close the pool otherwise all connections will stay alive for some  time (time-out) If you forget to call this method and run your code, there will be many connections unclosed by the Application.  So the contextDestroyed() method will call it only once when the web application un-deployed and this is what we need.

The main class is;

package com.tunatore.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;

public class ConnectionManager {

private static BoneCP connectionPool = null;

public static void configureConnPool() {

try {
Class.forName("com.mysql.jdbc.Driver"); //also you need the MySQL driver
BoneCPConfig config = new BoneCPConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/testproject");
config.setUsername("root");
config.setPassword("admin");
config.setMinConnectionsPerPartition(5); //if you say 5 here, there will be 10 connection available   config.setMaxConnectionsPerPartition(10);
config.setPartitionCount(2); //2*5 = 10 connection will be available
//config.setLazyInit(true); //depends on the application usage you should chose lazy or not
//setting Lazy true means BoneCP won't open any connections before you request a one from it.
connectionPool = new BoneCP(config); // setup the connection pool
System.out.println("contextInitialized.....Connection Pooling is configured");
System.out.println("Total connections ==> " + connectionPool.getTotalCreatedConnections());
ConnectionManager.setConnectionPool(connectionPool);

} catch (Exception e) {
e.printStackTrace(); //you should use exception wrapping on real-production code
}

}

public static void shutdownConnPool() {

try {
BoneCP connectionPool = ConnectionManager.getConnectionPool();
System.out.println("contextDestroyed....");
if (connectionPool != null) {
 connectionPool.shutdown(); //this method must be called only once when the application stops.
//you don't need to call it every time when you get a connection from the Connection Pool
System.out.println("contextDestroyed.....Connection Pooling shut downed!");
}

} catch (Exception e) {
e.printStackTrace();
}
}

public static Connection getConnection() {

Connection conn = null;
try {
 conn = getConnectionPool().getConnection();
//will get a thread-safe connection from the BoneCP connection pool.
//synchronization of the method will be done inside BoneCP source

} catch (Exception e) {
e.printStackTrace();
}
return conn;

}

public static void closeStatement(Statement stmt) {
try {
if (stmt != null)
stmt.close();
} catch (Exception e) {
e.printStackTrace();
}

}

public static void closeResultSet(ResultSet rSet) {
try {
if (rSet != null)
rSet.close();
} catch (Exception e) {
e.printStackTrace();
}

}

public static void closeConnection(Connection conn) {
try {
if (conn != null)
conn.close(); //release the connection - the name is tricky but connection is not closed it is released
   //and it will stay in pool
} catch (SQLException e) {
e.printStackTrace();
}

}

public static BoneCP getConnectionPool() {
return connectionPool;
}

public static void setConnectionPool(BoneCP connectionPool) {
ConnectionManager.connectionPool = connectionPool;
}

}

Here I will code some unit test for getting connection from the Conn. Pool.

package com.tunatore.db;

import static org.junit.Assert.*;
import java.sql.Connection;
import com.tunatore.db.ConnectionManager;
import org.junit.After;
import org.junit.AfterClass;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

public class TestConnectionManager {

   //you application server will call the following two methods for you in production env.
@BeforeClass
public static void setUpBeforeClass() throws Exception {
ConnectionManager.configureConnPool();
}

@AfterClass
public static void tearDownAfterClass() throws Exception {
 ConnectionManager.shutdownConnPool();
}

@Before
public void setUp() throws Exception {
}

@After
public void tearDown() throws Exception {
}

@Test
public void testGetConnection() {
Connection conn = ConnectionManager.getConnection();
 assertNotNull(conn);
 ConnectionManager.closeConnection(conn);      //releasing the connection

}

}

The output;

contextInitialized…..Connection Pooling is configured
Total connections ==> 10
705 [main] INFO com.jolbox.bonecp.BoneCP – Shutting down connection pool…
710 [main] INFO com.jolbox.bonecp.BoneCP – Connection pool has been shutdown.
contextDestroyed….
contextDestroyed…..Connection Pooling shut downed!

IMPORTANT: If you want to use this manual  approach,  do not forget to close statements and resulset objects before releasing the connection to the Conn. Pool. You can close all these inside finally block.

I will post How to use BoneCP and Spring Framework post later and show why using Spring JDBC Template provides a good solution for accessing the database,opening connections,closing connections or similar operations.

****update: Here you can find new post regarding BoneCP and Spring Framework****

https://tunatore.wordpress.com/2011/11/16/how-to-use-spring-jdbc-template-with-bonecp-connection-pooling/

Advertisements

9 thoughts on “How to use BoneCP Java Database Connection pool (JDBC Pool) library

  1. awesome, nice guidance, now I got the path to make it more customized according to my requirement.
    Few question.
    1. How to use loadbalancer here with mysql.
    2, how can I switch database on runtime safely. BoneCP provides that feature.

  2. Great! That shows everything I have to know to setup a library. Good work, thanks for shareing.

  3. if you are using BoneCP Java Database Connection than you will need six jar file.jars name are following ……………………
    ” bonecp-0.7.1.RELEASE.jar , guava-10.0.1.jar,logback-classic-0.9.8.jar , logback-core-0.9.6.jar , mysql-connector-java-5.1.6.jar , slf4j.jar”

  4. thanks buddy ..this is so useful.. i was using DBCP in my old framework.i want to replce this with BoneCp.

    If you have any basic web app link with BoneCP pls post it for reference..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s