Database Connection Pooling in Java: Concepts, Implementation, and Configuration

Creating a new database connection for every request consumes significant resources and can cause memory overflow; a connection pool mitigates this by reusing existing connections.

The pool maintains a minimum number of ready connections and caps the maximum, queuing excess requests until a connection becomes available.

Implementing a pool requires a class that implements java.sql.DataSource , creates connections in its constructor, and returns proxies that return connections to the pool on close() instead of actually closing them.

1 proxyConn = (Connection) Proxy.newProxyInstance(this.getClass()
              .getClassLoader(), conn.getClass().getInterfaces(),
              new InvocationHandler() {
    // when close is called, return conn to pool, otherwise invoke method
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        if (method.getName().equals("close")) {
            pool.addLast(conn);
            return null;
        }
        return method.invoke(conn, args);
    }
});

A full example of a custom pool (JdbcPool) is provided, which loads configuration from db.properties and initializes a LinkedList<Connection> of connections.

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy
username=root
password=XDP
jdbcPoolInitSize=10

Utility classes (JdbcUtil, JdbcUtils_DBCP, JdbcUtils_C3P0, JdbcUtils_JNDI) show how to obtain and release connections, and test classes demonstrate inserting a row and retrieving generated keys.

public static Connection getConnection() throws SQLException {
    return pool.getConnection();
}

Open‑source pools are introduced: DBCP (requires commons-dbcp.jar and commons-pool.jar ) and C3P0 (requires c3p0-0.9.2-pre1.jar and mchange-commons-0.2.jar ), each with sample configuration files and test code.

<Context>
    <Resource name="jdbc/datasource" auth="Container"
              type="javax.sql.DataSource" username="root" password="XDP"
              driverClassName="com.mysql.jdbc.Driver"
              url="jdbc:mysql://localhost:3306/jdbcstudy"
              maxActive="8" maxIdle="4"/>
</Context>

Tomcat JNDI configuration is explained: define the resource in context.xml , place the driver JAR in Tomcat’s lib directory, and retrieve the DataSource via InitialContext lookup.

Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
dataSource = (DataSource)envCtx.lookup("jdbc/datasource");

A servlet example (JNDITest) shows how to obtain a connection from the JNDI DataSource, execute an insert, and release resources.

public void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    Connection conn = null;
    PreparedStatement st = null;
    ResultSet rs = null;
    try {
        conn = JdbcUtils_JNDI.getConnection();
        String sql = "insert into test1(name) values(?)";
        st = conn.prepareStatement(sql);
        st.setString(1, "gacl");
        st.executeUpdate();
        rs = st.getGeneratedKeys();
        if (rs.next()) {
            System.out.println(rs.getInt(1));
        }
    } finally {
        JdbcUtils_JNDI.release(conn, st, rs);
    }
}