22.3.13.3. Connection Pooling with Spring

In many situations, such as web applications, there will be a large number of small database transactions. When this is the case, it usually makes sense to create a pool of database connections available for web requests as needed. Although MySQL does not spawn an extra process when a connection is made, there is still a small amount of overhead to create and set up the connection. Pooling of connections also alleviates problems such as collecting large amounts of sockets in the TIME_WAIT state.

Setting up pooling of MySQL connections with Spring is as simple as changing the data source configuration in the application context. There are a number of configurations that we can use. The first example is based on the Jakarta Commons DBCP library. The example below replaces the source configuration that was based on DriverManagerDataSource with DBCP's BasicDataSource.

<bean id="dataSource" destroy-method="close"  class="org.apache.commons.dbcp.BasicDataSource">    <property name="driverClassName" value="${db.driver}"/>    <property name="url" value="${db.jdbcurl}"/>    <property name="username" value="${db.username}"/>    <property name="password" value="${db.password}"/>    <property name="initialSize" value="3"/></bean>

The configuration of the two solutions is very similar. The difference is that DBCP will pool connections to the database instead of creating a new connection every time one is requested. We have also set a parameter here called initialSize. This tells DBCP that we want three connections in the pool when it is created.

Another way to configure connection pooling is to configure a data source in our J2EE application server. Using JBoss as an example, you can set up the MySQL connection pool by creating a file called mysql-local-ds.xml and placing it in the server/default/deploy directory in JBoss. Once we have this setup, we can use JNDI to look it up. With Spring, this lookup is very simple. The data source configuration looks like this.

<jee:jndi-lookup id="dataSource" jndi-name="java:MySQL_DS"/>