PostgreSQL™ includes two implementations of DataSource for JDBC 2 and two for JDBC 3, as shown in Table 11.3, “DataSource Implementations”. 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 data-source 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.
Table 11.3. DataSource Implementations
| JDBC | Pooling | Implementation Class |
|---|---|---|
| 2 | No | org.postgresql.jdbc2.optional.SimpleDataSource |
| 2 | Yes | org.postgresql.jdbc2.optional.PoolingDataSource |
| 3 | No | org.postgresql.jdbc3.Jdbc3SimpleDataSource |
| 3 | Yes | org.postgresql.jdbc3.Jdbc3PoolingDataSource |
All the implementations use the same configuration scheme. JDBC requires that a DataSource be configured via JavaBean properties, shown in Table 11.4, “DataSource Configuration Properties”, so there are get and set methods for each of these properties.
Table 11.4. DataSource Configuration Properties
| Property | Type | Description |
|---|---|---|
| serverName | String | PostgreSQL™ database server host name |
| databaseName | String | PostgreSQL™ database name |
| portNumber | int | TCP port which the PostgreSQL™ database server is listening on (or 0 to use the default port) |
| user | String | User used to make database connections |
| password | String | Password used to make database connections |
The pooling implementations require some additional configuration properties, which are shown in Table 11.5, “Additional Pooling DataSource Configuration Properties”.
Table 11.5. Additional Pooling DataSource Configuration Properties
| Property | Type | Description |
|---|---|---|
| dataSourceName | String | Every pooling DataSource must have a unique name. |
| initialConnections | int | The number of database connections to be created when the pool is initialized. |
| maxConnections | int | The maximum number of open database connections to allow. When more connections are requested, the caller will hang until a connection is returned to the pool. |
Example 11.1, “DataSource Code Example” shows an example of typical application code using a pooling DataSource.
Example 11.1. DataSource Code Example
Code to initialize a pooling DataSource might look like this:
Jdbc3PoolingDataSource source = new Jdbc3PoolingDataSource();
source.setDataSourceName("A Data Source");
source.setServerName("localhost");
source.setDatabaseName("test");
source.setUser("testuser");
source.setPassword("testpassword");
source.setMaxConnections(10);
Then code to use a connection from the pool might look
like this. Note that it is critical that the connections
are eventually closed. Else the pool will “leak” connections and
will eventually lock all the clients out.
Connection con = null;
try {
con = source.getConnection();
// use connection
} catch (SQLException e) {
// log error
} finally {
if (con != null) {
try { con.close(); } catch (SQLException e) {}
}
}