How to Establish a Connection Pool in Jdbc

JDBC connection pool design

I feel it is a bit confusing as they always need to perform a returnConnection and I am afraid they may forget to do so.

Thinking about it I came up with the idea to expose only only method in my connection pool and force the other developers to encapsulate their code and so I handle the getConnection returnConnection inside the connection pool.

I'm concerned with this statement. APIs should not (never?) assume that whoever uses them will do so in some way that is not enforced contractually by whichever method it exposes.

And java.sql.Connection is a widely used interface so you'll be making enemies by telling people how to use it with your pool.

Instead, you should assume that your Connection instances will be used correctly, i.e., that they will be closed (connection.close() in a finally block) once their use is over (see, for instance, Connecting with DataSource Objects):

   Connection con;
   PreparedStatement stmt;
   try {
        con = pool.getConnection();
        con.setAutoCommit(false);
        stmt = con.prepareStatement(...);
        stmt.setFloat(1, ...);
        stmt.setString(2, ...);
        stmt.executeUpdate();

        con.commit();
        stmt.close();
    } catch (SQLException e) {
        con.rollback();
    } finally {
        try {
           if(con!=null) 
               con.close();
           if(stmt!=null) {
               stmt.close();
        } catch (SQLException e) {
             ...
        } finally {

        }
    }

And the Connection implementation of your pool should be recycled when closed.

I second @lreeder's comment that you're really reinventing the wheel here and that most connection pools already available are definitely fast enough for most purposes, and underwent many fine tweakings over time. This also applies to embedded databases.

JDBC Connection Pool - Simple Setup and Library

I have used both DBCP Connection Pool and C3P0 . Both come up with good features however C3P0 has more configuration options than DBCP. I have not used BoneCP so not aware.

You can use last stable build of C3P0 which I personally feel is simple to use and has more freedom than other pools.

Does Db connection pool for standalone Java multi threaded application make sense?

Using a connection pool has a few advantages over not using one;

1. At thread start time, if a connection is available in the pool then the thread will not block to open a new connection on start.
2. A connection can be returned to the pool when one of your threads completes, this reduces the load on the database server as client connections persist (through the application execution); instead of being set up and torn down multiple times

Simply limit the max number of connections in your pool to match your mentioned database limits and make sure you return your connections the pool (with c3p0 I believe you just close the Connection, which is wrapped by their PooledConnection).

How to reset a JDBC Connection object?

If you are talking about what you as a user of a connection should do, then that is simple: call close() on the connection. Closing the logical connection will signal to the connection pool that the connection is available for reuse, and the connection pooling manager is then responsible for performing the necessary reset, invalidation of statement handles, etc.

If you are talking about what you as the implementer of a connection pooling manager should do, that is where things become complicated.

Historically, JDBC provides no way to 'reset' a java.sql.Connection, other than by your own code (or your third-party connection pool) remembering the initial configuration, and restoring it after use, and keeping track of objects like statements and result sets and closing them when the connection is returned to the pool.

Originally, the intended way to reset connections in JDBC was for an application server to use a driver's javax.sql.ConnectionPoolDataSource as a factory for javax.sql.PooledConnection objects. These PooledConnection objects serve as handles for physical connections to be held in a connection pool (to be clear, ConnectionPoolDataSource is not a connection pool, it is a data source for a connection pool). The application server would then expose a javax.sql.DataSource handing out logical Connection objects, where on Connection.close(), the driver specific implementation of PooledConnection would take care of any necessary reset of a connection (though JDBC underspecifies what is 'necessary').

However, in practice this route is hardly ever used because support in drivers was (and often still is) spotty, inconsistent or downright incorrect, and JDBC wasn't clear enough on exactly what needed to be done when the logical connection was closed. The world has also shifted to using third-party connection pool libraries that do not use ConnectionPoolDataSource.

JDBC 4.3 (Java 9 and higher) introduced the methods Connection.beginRequest() and Connection.endRequest() to be called by connection pooling managers, which would seem to fit such pattern, but unfortunately JDBC 4.3 doesn't actually specify what kind of things an implementation should or should not do in response to beginRequest and endRequest.

In short, there is no real general way to reset a connection in JDBC.

JDBC connection pool management

DataSource (javax.sql.DataSource) represents an abstract concept of something you can get database connections from.

So, DataSource itself doesn't define any details of how connections are managed, and different implementations of DataSource may manage connections in different ways:

• A naive implementation (such as Spring's DriverManagerDataSource) may create a new connection each time you request it, and in this case close() actually closes connections.

• An implementation backed by a connection pool (such as Apache DBCP or c3p0) returns existing connections from the pool. Connection object returned by such an implementation is a proxy, and its close() method is overriden to return connection to the pool instead of closing it.

If you want to know how exactly your connection pool manages connections, check documentation of your connection pool implementation.

---

Related Topics