JDBC Connection Pooling

Spring JDBC Connection Pooling Mechanism

In this tutorial, we will discuss Spring JDBC Connection Pooling Mechanism in the spring framework. In Database related applications, if we want to perform database operations first, we have to create a Connection object, then we have to close the connection object when the database operations are completed.

In Database related applications, every time creating a Connection object and every time destroying Connection objects may reduce application performance because, Creating Connection objects and destroying Connection objects are two expensive processes, which may reduce application performance.

JDBC Connection Pooling

To overcome the above problem, we have to use JDBC Connection Pooling in applications. In JDBC Connection pooling, we will create a set of Connection objects in the form of a pool at the application startup time. We will reuse those Connection objects while executing applications. When database operations are completed, we will send back those connection objects to the Pool object without destroying that connection objects. In Spring JDBC applications, there are three approaches to provide connection pooling.

  1. Default Connection Pooling Mechanism
  2. Third Party Connection Pooling Mechanisms
  3. Application Servers provided Connection Pooling Mechanism
1. Default Connection Pooling Mech

In Spring Framework, the Default Connection pooling mechanism exists in the form of org.springframework.jdbc.datasource.DriverManagerDataSource. It is helpful up to testing only. It is not helpful for the production environment of the application. If we want to use the default Connection Pooling mechanism in the Spring JDBC application, we have to configure org.springframework.jdbc.datasource.DriverManagerDataSource in the beans configuration file with the following properties.

  • driverClassName
  • url
  • username
  • Password

E.g

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
   <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
   <property name="url" value="jdbc:mysql://localhost:3306/employee"/>
   <property name="username" value="root"/>
   <property name="password" value="ashok"/>
</bean>
2. Third Party Connection Pooling Mechanisms

In Spring JDBC applications, we can use the following third-party connection pooling mechanisms

  • Apache DBCP
  • C3P0
  • Proxool

1. Apache DBCP

To use Apcahe DBCP connection pooling mechanism then we have to configure

org.apache.commons.dbcp2.BasicDataSource class with the following properties in spring beans configuration file.

  • driverClassName
  • url
  • username
  • password
  • initialSize: It will take Initial pool size.
  • maxTotal: It will allow the specified no of max connections.

E.g

<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
   <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
   <property name="url" value="jdbc:mysql://localhost:3306/employee" />
   <property name="username" value="root" />
   <property name="password" value="ashok" />
   <property name="initialSize" value="20" />
   <property name="maxTotal" value="30" />
</bean>

Note

To use this mechanism in Spring JDBC Applications then we have to add the following two jar files to Library.

  1. commons-dbcp2-2.2.0.jar
  2. commons-pool2-2.5.0.jar

2. C3P0

To use C3P0 connection pooling mechanism then we have to configure

com.mchange.v2.c3p0.ComboPooledDataSource class with the following properties in spring beans configuration file.

  • driverClass
  • jdbcUrl
  • user
  • password
  • minPoolSize: It will take Initial pool size.
  • maxPoolSize: It will allow the specified no of max connections.
  • maxStatements: Max statements it allows.
  • testConnectionOnCheckOut:true/false for Checking Connection before use.

E.g

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
   <property name="driverClass" value="com.mysql.cj.jdbc.Driver" />
   <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/employee" />
   <property name="user" value="root" />
   <property name="password" value="ashok" />
   <property name="maxPoolSize" value="30" />
   <property name="minPoolSize" value="10" />
   <property name="maxStatements" value="100" />
   <property name="testConnectionOnCheckout" value="true" />
</bean>

Note

To use this mechanism in Spring JDBC Applications then we have to add the following two jar files to Library.

1. c3p0-0.9.5.2.jar
2. mchange-commons-java-0.2.11.jar

3. Proxool

To use Proxool connection pooling mechanism then we have to configure

org.logicalcobwebs.proxool.ProxoolDataSource class with the following properties in spring beans configuration file.

  • driver
  • driverUrl
  • user
  • password
  • minimumConnectionCount:It will take Initial pool size.
  • maximumConnectionCount: It will allow the specified no of max connections.

E.g

<bean id="dataSource" class="org.logicalcobwebs.proxool.ProxoolDataSource">
   <property name="driver" value="com.mysql.cj.jdbc.Driver" />
   <property name="driverUrl" value="jdbc:mysql://localhost:3306/employee" />
   <property name="user" value="root" />
   <property name="password" value="ashok" />
   <property name="maximumConnectionCount" value="30" />
   <property name="minimumConnectionCount" value="10" />
</bean>

Note

To use this mechanism in Spring JDBC Applications then we have to add the following two jar files to Library.

1.proxool-0.9.1.jar
2.proxool-cglib.jar

3. Application Servers provided Connection Pooling Mechanism throw JNDI

JNDI [Java Naming And Directory Interface]: JNDI is a Middleware Service or an abstraction provided by SUN Microsystems as part of J2EE and implemented by all the Application Servers vendors like Weblogic, JBOSS, Glassfish, etc.

JNDI is existed inside the application Servers to provide any resource with Global Scope. i.e., JNDI will share any resource like “DataSource” to all the applications running in the present application server.

In general, almost all the Application Servers have their own Connection Pooling mechanisms. If we want to use Application Servers provided Connection pooling mechanisms, we must use the following steps.

  1. Install Application Server.
  2. Configure Connection Pooling and Datasource in JNDI provided by Application Servers.
  3. Add the required new JARs to Library.
  4. Provide JNDI Setups in beans configuration File.
JDBC Connection Pooling
Scroll to top