InfoCenter Home >
4: Developing applications >
4.2: Building Web applications >
4.2.4: Accessing data >
4.2.4.2: Obtaining and using database connections >
4.2.4.2.1: Accessing data with the JDBC 2.0 Optional Package APIs >
4.2.4.2.1.2: Tips for using connection pooling

4.2.4.2.1.2: Tips for using connection pooling

Most best practices have been documented elsewhere in Related information. The following are additional items that have not been explicitly called out:

Obtain and close connection in the same method. An application should obtain and close its connection in the method that requires the connection. This keeps the application from holding resources not being used and leaves more available connections in the pool for other applications. In addition, this practice removes the temptation to use the same connection in multiple transactions, which, by default, is not allowed. This practice does not cost the application much in performance, because the Connection object is from a pool of connections, where the overhead for establishing the connection has already been incurred. Lastly, make sure to declare the Connection object in the same method as the getConnection() call in a servlet; otherwise, the Connection object works as if it is a static variable (see "Worst Practices" later in this article for problems with this).

If you opened it, close it. All JDBC resources that have been obtained by an application should be explicitly closed by that application. The product tries to clean up JDBC resources on a connection after the connection has been closed. However, this behavior should not be relied upon, especially if the application might be migrated to another platform in the future.

For servlets, obtain DataSource in the init() method. For performance reasons, it is usually a good idea to put the JNDI lookup for the datasource into the init() method of the servlet. Because the datasource is simply a factory for connections that does not typically change, retrieving it in this method ensures that the lookup happens only once.

Worst practices

The following are some very common problems with applications that should be avoided, because they most often result in unexpected failures:

Do not close connections in a finalize() method. If an application waits to close a connection or other JDBC resource until the finalize() method, the connection is not closed until the object that obtained it is garbage-collected. This leads to problems if the application is not very thorough about closing its JDBC resources, such as ResultSet objects. Databases can quickly run out of the memory required to store the information about all of the JDBC resources it currently has open. In addition, the pool can quickly run out of connections to service other requests.

Do not declare connections as static objects. It is never recommended that connections be declared as static objects. If a connection is declared as static, the same connection might get used on different threads at the same time. This causes a great deal of difficulty, within both the product and the database.

In servlets, do not declare Connection objects as instance variables. In a servlet, all variables declared as instance variables act as if they are class variables. For example, in a servlet with an instance variable

Connection conn = null;

this variable acts as if it were static. In this case, all instances of the servlet use the same Connection object. This is because a single servlet instance can be used to serve multiple Web requests in different threads.

In CMP beans, do not manage data access. If a Container Managed Persistence (CMP) bean is written so that it manages its own data access, this data access may be part of a global transaction. Generally, if specialized data access is required, use a BMP session bean.

Go to previous article: Creating datasources with the WebSphere connection pooling API Go to next article: Handling data access exceptions

 

 
Go to previous article: Creating datasources with the WebSphere connection pooling API Go to next article: Handling data access exceptions