The driver implements a standard JDBC connection pool. Set UseConnectionPooling to enable the pool. The following sections show how to configure and use them.
Working with Pooled Connections
Just as you would interact with a non-pooled connection, you use standard JDBC objects to get and close connections. But, in this case, the Connection object retrieved is a handle for the physical connection owned by the connection pool. When the connection is closed, instead of the connection being destroyed, the handle is returned to the pool, where it is available for the next connection request.
You must explicitly close the connection for it to be returned to the pool.
Configuring the Connection Pool
In addition to UseConnectionPooling, set the following connection properties to control the connection pool:
- PoolMaxSize: Define the maximum number of connections that can be open at any given time.
- PoolIdleTimeout: Set a limit to how long connections can remain open and idle. If this limit is exceeded, the connection is returned to the pool.
- PoolWaitTime: Set a limit to how long new connection requests should wait for a connection to become available. If this limit is exceeded, the request throws an error. By default, connection requests wait forever for a connection to become available.
Connection Pooling with SQLDataSource
To use the default method for pooling connections, instantiate the SQLDataSource with UseConnectionPooling:
SQLDataSource sqlDataSource = new SQLDataSource(); sqlDataSource.setURL("jdbc:sql:UseConnectionPooling=true;user=myuser;password=mypassword;Server=localhost;Database=Northwind;");
JDBC Connection Pooling
If you would like to establish a pooled connection using the the JDBC ConnectionPoolDataSource interface, instantiate the SQLConnectionPoolDataSource with UseConnectionPooling:
SQLConnectionPoolDataSource sqlPoolDataSource = new SQLConnectionPoolDataSource(); sqlPoolDataSource.setURL("jdbc:sql:UseConnectionPooling=true;user=myuser;password=mypassword;Server=localhost;Database=Northwind;");
Closing the Connection Pool
On JRE 1.3 or higher, the connection pool itself automatically closes when the application stops running. You can manually close the connection pool by invoking the close method of the DataSource object.