JDBC Connection Pooling

Spring JDBC Connection Pooling Mechanism

In Database related applications, if we want to perform database operations first, we have to create Connection object then we have to close connection object when the database operations are completed.

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

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

  1. Default Connection Pooling Mech.
  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 is existed in the form of org.springframework.jdbc.datasource. DriverManagerDataSource, it is useful up to testing only, it is not useful for production environment of the application. If we want to use default Connection Pooling mechanism in Spring JDBC application then we have to configure org.springframework.jdbc.datasource. DriverManagerDataSource in 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 are able to 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 which is 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, that is, JNDI will share any resource like “DataSource” to all the applications which are running in the present application server.

In general, almost all the Application Servers are having their own Connection Pooling mechanisms, if we want to use Application Servers provided Connection pooling mechanisms, we have to 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