Connection Pools in Java using Apache’s DBCP Library

How to easily implement connection pooling in Java using Apache Common’s DBCP2 library.

Github Repository

For most basic Java applications, such as school projects or one-off prototypes, having a simple database Connector class where individual connections are handled are enough to complete the assignment. However, many production systems require multiple connections to be handled appropriately to ensure application stability.

Database connection without a connection pool

Let’s use a simple example of what a connection class to a database may look like:

Very simply, we first check to ensure the appropriate driver is loaded, then we initialize the connection using the given credentials.

However there are a few things wrong with the above snippet, mainly the Connection object is handled outside the scope of the method. What happens if the caller doesn’t close the Connection object after use? The connection then stays open until the database server terminates the connection.

Furthermore, what if we had a multi-threaded environment, where 5 threads called this method? Or even worse, what if a bug in our code creates 500 threads, each of which requesting a database connection? That would mean the program would make 500 calls to the method, opening and closing the connection each time, which is very inefficient.

A properly configured connection pool can solve both of these problems.

What is a Connection Pool?

connection pool diagram
From IBM’s article https://www.ibm.com/developerworks/data/library/techarticle/dm-1105fivemethods/index.html

A connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required.

Wikipedia – Connection Pool

Depending on the configuration, a connection pool will open a predefined amount of connections for use. Once a connection is requested, the pool dedicates the specific connection to the requested process. Once the process closes the connection, the pool then places the connection back into the “pool” of available connections.

This system allows us to restrict the amount of opened connections, and from a design perspective, allows us to integrate a multi-threaded solution more easily.

How do I implement a connection pool?

Most things in the programming world have already been written, tested, and packaged into a library. Connection pools are no different, thus, we’ll be looking at Apache Common’s DBCP Library

Here’s the code snippet, then we’ll discuss each part

Note: The above code snippets don’t represent best software engineering practices (for instance hard-coding the database username/password)

First off, we see the method signature and class variable are both static. Note the BasicDataSource is not a replacement for a Connection, but instead is a layer above. Below we’ll show an example of how to use it.

The JavaDocs for BasicDataSource class is rather extensive (found here). I’ll briefly explain the options above:

setInitialSize
Sets the initial size of the connection pool.
setMaxTotal
Sets the maximum total number of idle and borrows connections that can be active at the same time.
setMinIdle
Sets the minimum number of idle connections in the pool.
setMaxIdle
Sets the maximum number of connections that can remain idle in the pool.
setMaxOpenPreparedStatementsMaximum amount of open prepared statements

On startup, the connection pool’s size will be 3 connections (setInitialSize), then will grow to 25 active connections (setMaxTotal). Then, if there are no active requests, the connection pool will idle with a minimum of 0 connections (setMinIdle) and a maximum of 8 idle connections (setMaxIdle). Lastly, the max amount of open prepared statements is at 100 (setMaxOpenPreparedStatements).

  • Active Connection: a connection in-use by a process
  • Idle Connection: a connection within the pool that is ready to be used

Lets look at another piece of code actually using the connection pool:

From a usability perspective, the only difference between using a connection pool and directly calling for the Connection object (from our Connector class) is the extra ‘getDataSource()’ method call.

What if the max active connections limit (in our case, 25) is reached?

If all 25 active connections are in use, then the request is queued until a connection is released.

What happens when we close the connection? Do we still have to close connections?

By closing the connection retrieved from the BasicDataSource object, we let the connection pool know the connection is ready for reuse by another process. Closing the connection doesn’t actually close the connection, because we let the connection pool manage connections.

So how do we actually shutdown the connection pool?

Looking at the ‘shutdown()’ method in the second Gist, we can see when calling the ‘close()’ method on the DataSource, this actually closes the pool. You’ll want to call this during the shutdown event for your application.

Conclusion

With a connection pool, if the thread bug mentioned earlier attempted to start 500 threads, each requesting a connection, the connection pool would not allow for more than 25 total active connections. The 475 other connection requests would be sent into a queue, making for a happy DBA.

If you have any ideas on other programming topics, let me know in the comments!

Leave a Reply