In an application server environment, should one prefer javax.sql.DataSource or javax.sql.ConnectionPoolDataSource?

Question

An existing question expresses something similar, but I want to call out a slightly different nuance here.

The basic question: All application servers give the ability to specify both the (standard) interface and the (vendor-supplied) implementation classes for a (little-d, little-s) data source when you define a connection pool. If a vendor supplies an implementation for both a ConnectionPoolDataSource and a regular ol' DataSource, which one is to be preferred?

What about for vendor implementations that implement DataSource, ConnectionPoolDataSource and XADataSource in the same implementation class?

The documentation for javax.sql.ConnectionPoolDataSource says, almost in total:

A factory for PooledConnection objects. An object that implements this interface will typically be registered with a naming service that is based on the JavaTM Naming and Directory Interface (JNDI).

That's basically useless, but it is worth noting that javax.sql.ConnectionPoolDataSource does not itself extend javax.sql.DataSource, which means that implementations of it need not provide a getConnection() method, which is the one most callers are accustomed to using. This tells me that all application servers MUST either:

• wrap a javax.sql.ConnectionPoolDataSource implementation with a javax.sql.DataSource implementation so that callers can use javax.sql.DataSource#getConnection(), or

• detect that a javax.sql.ConnectionPoolDataSource implementation is also a javax.sql.DataSource implementation, and (somehow) trust that its getConnection() method will delegate to getPooledConnection() (how on earth would they do this?)

The documentation for javax.sql.DataSource says, in part:

The DataSource interface is implemented by a driver vendor. There are three types of implementations:

• Basic implementation -- produces a standard Connection object
• Connection pooling implementation -- produces a Connection object that will automatically participate in connection pooling. This implementation works with a middle-tier connection pooling manager.
• Distributed transaction implementation -- produces a Connection object that may be used for distributed transactions and almost always participates in connection pooling. This implementation works with a middle-tier transaction manager and almost always with a connection pooling manager.

This is also useless, and incorrect to boot (or at least underspecified: javax.sql.DataSource is also implemented by application server vendors, who must provide an implementation so that client code may (for example) inject a javax.sql.DataSource into their server-side code). It also seems to imply that connection pooling might or might not be offered by any given DataSource implementation, which would make me wonder how an application server is supposed to tell when a connection pool is set up that has specified the javax.sql.DataSource interface (instead of the javax.sql.ConnectionPoolDataSource interface).

Note: I am not looking for an answer here about How I Did This On Tomcat, or The Steps I Took On GlassFish That Worked For Me or anything of that nature. I'm looking for an answer that refers back to the JDBC specification, or the Java EE specification, or a bug report, or something that indicates why these separate (unrelated!) interfaces exist, and how they are supposed to be unified or distinguished between on a generic Java EE application server that is therefore obligated to offer connection pooling.

Answer 1

You should not use a ConnectionPoolDataSource directly, it is intended as a source for physical connections (aka PooledConnections) that are then used by a DataSource that actually implements connection pooling. A ConnectionPoolDataSource should not actually implement pooling itself.

Such a DataSource is for example provided by your application server. It usually takes a JNDI url (or a direct reference) to the ConnectionPoolDataSource, and itself exposes the DataSource interface to hand out 'logical' connections.

DataSources are discussed in JDBC 4.1 section 9.4:

The DataSource interface [..] is the preferred approach to obtaining data source connections.

and

A logical name is mapped to a DataSource object via a naming service that uses the Java Naming and Directory InterfaceTM (JNDI). The DataSource object, represents a physical data source and provides connections to that data source.

If we then look at the description of Connection Pooling (chapter 11) in the introduction specifies:

the JDBC driver provides an implementation of ConnectionPoolDataSource that the application server uses to build and manage the connection pool.

The algorithm used to manage the connection pool is implementation-specific and varies with application servers. The application server provides its clients with an implementation of the DataSource interface that makes connection pooling transparent to the client. As a result, the client gets better performance and scalability while using the same JNDI and DataSource APIs as before. (emphasis mine)

The interaction between the (AS) DataSource (hereafter: ASDS) and the (driver) ConnectionPoolDataSource (hereafter CPDS) is described in section 11.1 - 11.3. Section 11.3 specifically describes the interaction:

The following sequence of steps outlines what happens when a JDBC client requests a connection from a DataSource object that implements connection pooling:

• The client calls DataSource.getConnection.
• The application server providing the DataSource implementation looks in its connection pool to see if there is a suitable PooledConnection object — a physical database connection— available. Determining the suitability of a given PooledConnection object may include matching the client’s user authentication information or application type as well as using other implementation-specific criteria. The lookup method and other methods associated with managing the connection pool are specific to the application server.
• If there are no suitable PooledConnection objects available, the application server calls the ConnectionPoolDataSource.getPooledConnection method to get a new physical connection. The JDBC driver implementing ConnectionPoolDataSource creates a new PooledConnection object and returns it to the application server.
• Regardless of whether the PooledConnection was retrieved from the pool or was newly created, the application server does some internal bookkeeping to indicate that the physical connection is now in use.
• The application server calls the method PooledConnection.getConnection to get a logical Connection object. This logical Connection object is actually a “handle” to a physical PooledConnection object, and it is this handle that is returned by the DataSource.getConnection method when connection pooling is in effect.
• The application server registers itself as a ConnectionEventListener by calling the method PooledConnection.addConnectionEventListener. This is done so that the application server will be notified when the PooledConnection object is available for reuse.
• The logical Connection object is returned to the JDBC client, which uses the same Connection API as in the basic DataSource case. Note that the underlying PooledConnection object cannot be reused until the client calls the method Connection.close.

The last item is not entirely true: an ASDS can forcibly close/invalidate the logical connection from a client by handing out a new Connection obtained from the same PooledConnection (see section 11.4).

Now to come to your question in the comment: Why does an AS allow you to specify both a DataSource and ConnectionPoolDataSource interface in the datasource configuration: The reference between the ASDS and CPDS is usually done through JNDI as well. This for example allows easy reconfiguration (switching the ASDS to a different underlying CPDS, or switching the ASDS to a normal, basic DataSource while still maintaining the config of the CPDS for example because it is also used by a different ASDS). See also section 11.5 of the JDBC spec:

Deploying a DataSource object that implements connection pooling requires that both a client-visible DataSource object and an underlying ConnectionPoolDataSource object be registered with a JNDI-based naming service.

Answer 2

Just to shed some light: I'm using Postgres, and the API doc from PGConnectionPoolDataSource is quite clear for me:

public class PGConnectionPoolDataSource
extends org.postgresql.ds.jdbc4.AbstractJdbc4ConnectionPoolDataSource
implements javax.sql.ConnectionPoolDataSource

PostgreSQL implementation of ConnectionPoolDataSource. The app server or middleware vendor should provide a DataSource implementation that takes advantage of this ConnectionPoolDataSource. If not, you can use the PostgreSQL implementation known as PoolingDataSource, but that should only be used if your server or middleware vendor does not provide their own. Why? The server may want to reuse the same Connection across all EJBs requesting a Connection within the same Transaction, or provide other similar advanced features.