5.9.1. JDBC, JDK Version Support

JDBC 2 introduced standard connection pooling features in an add-on API known as the JDBC 2.0 Optional Package (also known as the JDBC 2.0 Standard Extension). These features have since been included in the core JDBC 3 API. The PostgreSQL JDBC drivers support these features with JDK 1.3.x in combination with the JDBC 2.0 Optional Package (JDBC 2), or with JDK 1.4+ (JDBC 3). Most application servers include the JDBC 2.0 Optional Package, but it is also available separately from the Sun JDBC download site.

5.9.2. JDBC Connection Pooling API

The JDBC API provides a client and a server interface for connection pooling. The client interface is javax.sql.DataSource, which is what application code will typically use to acquire a pooled database connection. The server interface is javax.sql.ConnectionPoolDataSource, which is how most application servers will interface with the PostgreSQL JDBC driver.

In an application server environment, the application server configuration will typically refer to the PostgreSQL ConnectionPoolDataSource implementation, while the application component code will typically acquire a DataSource implementation provided by the application server (not by PostgreSQL).

In an environment without an application server, PostgreSQL provides two implementations of DataSource which an application can use directly. One implementation performs connection pooling, while the other simply provides access to database connections through the DataSource interface without any pooling. Again, these implementations should not be used in an application server environment unless the application server does not support the ConnectionPoolDataSource interface.

5.9.3. Application Servers: ConnectionPoolDataSource

PostgreSQL includes one implementation of ConnectionPoolDataSource for JDBC 2, and one for JDBC 3:

Both implementations use the same configuration scheme. JDBC requires that a ConnectionPoolDataSource be configured via JavaBean properties, so there are get and set methods for each of these properties:

Many application servers use a properties-style syntax to configure these properties, so it would not be unusual to enter properties as a block of text.

serverName=localhost
databaseName=test
user=testuser
password=testpassword

serverName=localhost;databaseName=test;user=testuser;password=testpassword

5.9.4. Applications: DataSource

PostgreSQL includes two implementations of DataSource for JDBC 2, and two for JDBC 3. The pooling implementations do not actually close connections when the client calls the close method, but instead return the connections to a pool of available connections for other clients to use. This avoids any overhead of repeatedly opening and closing connections, and allows a large number of clients to share a small number of database connections.

The pooling datasource implementation provided here is not the most feature-rich in the world. Among other things, connections are never closed until the pool itself is closed; there is no way to shrink the pool. As well, connections requested for users other than the default configured user are not pooled. Many application servers provide more advanced pooling features, and use the ConnectionPoolDataSource implementation instead.

All the implementations use the same configuration scheme. JDBC requires that a DataSource be configured via JavaBean properties, so there are get and set methods for each of these properties.

The pooling implementations require some additional configuration properties:

Here's an example of typical application code using a pooling DataSource:

Jdbc3PoolingDataSource source = new Jdbc3PoolingDataSource();
source.setDataSourceName("A Data Source");
source.setServerName("localhost");
source.setDatabaseName("test");
source.setUser("testuser");
source.setPassword("testpassword");
source.setMaxConnections(10);

Connection con = null;
try {
    con = source.getConnection();
    // use connection
} catch(SQLException e) {
    // log error
} finally {
    if(con != null) {
        try {con.close();}catch(SQLException e) {}
    }
}

5.9.5. DataSources and JNDI

All the ConnectionPoolDataSource and DataSource implementations can be stored in JNDI. In the case of the non-pooling implementations, a new instance will be created every time the object is retrieved from JNDI, with the same settings as the instance which was stored. For the pooling implementations, the same instance will be retrieved as long as it is available (e.g. not a different JVM retrieving the pool from JNDI), or a new instance with the same settings created otherwise.

In the application server environment, typically the application server's DataSource instance will be stored in JNDI, instead of the PostgreSQL ConnectionPoolDataSource implementation.

In an application environment, the application may store the DataSource in JNDI so that it doesn't have to make a reference to the DataSource available to all application components that may need to use it:

Jdbc3PoolingDataSource source = new Jdbc3PoolingDataSource();
source.setDataSourceName("A Data Source");
source.setServerName("localhost");
source.setDatabaseName("test");
source.setUser("testuser");
source.setPassword("testpassword");
source.setMaxConnections(10);
new InitialContext().rebind("DataSource", source);

Connection con = null;
try {
    DataSource source = (DataSource)new InitialContext().lookup("DataSource");
    con = source.getConnection();
    // use connection
} catch(SQLException e) {
    // log error
} catch(NamingException e) {
    // DataSource wasn't found in JNDI
} finally {
    if(con != null) {
        try {con.close();}catch(SQLException e) {}
    }
}

5.9.6. Specific Application Server Configurations

Configuration examples for specific application servers will be included here.