Using BoneCP in Java Web Applications

Connection pooling is crucial issue in developing large web applications. Its main purposes are to keep number of database connections under constant control and to reduce number of very time-consuming connection openings.

The idea is to establish certain amount of connections at an application startup and to share them between application threads at the runtime.

Connections from the pool are initialized and ready to use at any time. When request-processing thread (like servlet) needs to make some SQL query, it takes first free connection from the pool. If the pool is out of connections it just waits for another thread to release one. Therefore there is no need for particular thread to interfere with SQL connection layer. It doesn’t waste time for establishing and closing connection which both consume very significant part of the time of whole query execution.

It is not easy to implement good and efficient pooler because it requires among other things proper thread synchronization and smart query queue mechanism. Fortunately there are many of decent pooling utilities. One of them is BoneCP. It seems to be the most efficient Java pooler at the moment (so say its benchmarks: http://jolbox.com/benchmarks.html). Below is a simple example of BoneCP usage. For this example I wrapped basic BoneCP functionality into one utility class names ConnectionPooler.

import java.sql.Connection;

import com.jolbox.bonecp.BoneCP;

import com.jolbox.bonecp.BoneCPConfig;

import org.apache.log4j.Logger;


public class ConnectionPooler 
{
	private static BoneCP connectionPool;
	

	public static void InitializePooler()
	{
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
		} 
		catch (Exception ex) 
		{
			logger.error(ex);
		}
		
		try 
		{
			BoneCPConfig config = new BoneCPConfig();
			
			config.setJdbcUrl("dburl");
			
			config.setUsername("dblogin");
			
			config.setPassword("dbpassword");
			
			config.setMinConnectionsPerPartition(5);
			
			config.setMaxConnectionsPerPartition(20);
			
			config.setPartitionCount(1);
			
			connectionPool = new BoneCP(config);
		}
		catch(Exception ex)
		{
			logger.error(ex);
		}
	}
	
	public static void ReleasePooler()
	{
		if(connectionPool != null)
		{
			connectionPool.shutdown();
		}
	}
	
	public static Connection GetConnection()
	{
		Connection connection = null;
		
		try 
		{
			connection = connectionPool.getConnection();
		}
		catch (Exception ex) 
		{
			logger.error(ex);
		}
		
		return connection;
	}
	
	public static void ReleaseConnection(Connection connection) 
	{
		try
		{
			if (connection != null)
			{
				connection.close();
			}
		}
		catch (Exception ex) 
		{
			logger.error(ex);
		}
	}
}

The main method – called probably only once in application lifetime – is InitializePooler. This it the place where the pooler is initialized and connections are established. Here we need some database info (url, port) and credentials (password, login). Moreover we need to specify connection number limits – minimum and maximum. The first amount means how many connections will be established immediately. The second one means how many connections pooler could establish if current amount is not enough for threads demands.

The other parameter is partition count. While using large amount of connections we can group them in partitions for better performance. If the thread want to acquire connection from the pool it has to be synchronized with the others. When we have 1000 threads using one partition, all of them are synchronized with the same lock. If we divide connections into two partitions, there would be two locks. One for the first 500 threads and the other one for the rest. Therefore accessing connection should be faster for one particular thread.

If we called InitializePooler method, our pooler is ready to use. If there is a need to make some SQL query we just simply call GetConnection(). It returns java.sql.Connection object which we use in normal way to execute query. When query is complete and all of data are fetched we return connection to the pool by calling ReleaseConnection method. It is not closed yet – it just goes back to the pool and waits for the other thread to be used by. When our application is about to be closed, it is recommended to release pooler. In our pooler-wrapper we call ReleasePooler method which actually closes all of opened SQL connections.

That’s all folks. Using that sort of stuff you will get rid of common SQL errors like “Too many connections” or “Connection limit exceeded”. You will upgrade your web application performance by making your SQL interaction faster and under pooler control.