12.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.

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.