ARTICLE :
 

Spring based JDBC Connection Pool using BoneCP

by Yogaraj Khanal on June 12th, 2014

Introduction

This article is about how to use open source JDBC connection pooling mechanism using BoneCP

Background

Connection pool is required in software projects so that connections can be reused when future requests to the database are required. They enhance the performance of database connections since getting data to and from database if one of the expensive process performance wise. It makes use of resources effectively and cuts down on time a user must wait to establish a connection to the database.

Detail

In this article I am demonstrating how we can use BoneCP with Spring.

1)First step is to write Spring applicationContext.xml file and put it in class path.
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
	http://www.springframework.org/schema/context
	http://www.springframework.org/schema/context/spring-context-4.0.xsd">
	<context:annotation-config />
	<context:property-placeholder location="file:${catalina.home}/conf/application.properties"/>
	<bean id="baseMgr" class="gov.nih.nichd.ctdb.common.BaseManager">
		<property name="dataSource" ref="mainDataSource" />
	</bean>
	
	<!-- BoneCP configuration -->
	<bean id="mainDataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
	   <property name="driverClass" value="org.postgresql.Driver" />
	   <property name="jdbcUrl" value="${proforms.db.jdbc.url}" />
	   <property name="username" value="${proforms.db.username}"/>
	   <property name="password" value="${proforms.db.password}"/>
	   <property name="maxConnectionsPerPartition" value="30"/>
	   <property name="minConnectionsPerPartition" value="10"/>
	   <property name="partitionCount" value="3"/>
	   <property name="deregisterDriverOnClose" value="true"/>
	</bean>
</beans>

2)Then you would write a base class to inject this bean
@Component
public class BaseManager
{
	@Autowired
	private static DataSource dataSource;

	public static DataSource getDataSource() {
		return dataSource;
	}
	
	public static void setDataSource(DataSource dataSource) {
		CtdbManager.dataSource = dataSource;
	}

	/**
	 * Default constructor
	 */
	public BaseManager() {	
	}
	
    protected final Connection getConnection()
            throws ApplicationException {
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			throw new ApplicationException ("Unable to get connection: " + e.getMessage(), e);
		}
    }
}

3)This is what will go in application.properties file to externalize your connection information
project.db.jdbc.url=jdbc:postgresql://localhost:5432/your_schema
project.db.username=db_username
project.db.password=db_password	

4)I am using Spring 4.0.3 release do you can get those jars from here
http://mvnrepository.com/artifact/org.springframework
Latest boneCP (I am using 0.8.0) form download section of the links in references
See the requirements sections for details and to Simple Logging Fa├žade for Java(SLF4J) jar

Conclusion

I hope you will find this article useful and can help you configure and run you application in no time with connection pool. Please write comments on this article if you have any questions.

References

http://jolbox.com/
http://projects.spring.io/spring-framework/
http://en.wikipedia.org/wiki/Connection_pool

Author:

Profile Photo
Yogaraj Khanal
Date: June 12th, 2014

Comments:



Profile Photo
Commented by Yogaraj Khanal
on June 12th, 2014 at 10:23:19 EST
Then you can have

Account Manager extend BaseManager{

public void someMethod() throws Exception{
Connection conn = null;
try{
conn = this.getConnection(BaseManager.AUTOCOMMIT_FALSE);

.....do your stuff
conn.commit();


finally
{
conn.rollback();
conn.close();
}




}