Connection Pools - JSP

When we discussed the use of DataSources in Tomcat, we mentioned that the mechanism used by Tomcat involved connection pooling. In this section, we will discuss how connection pooling works.

In our early examples using the DriverManager class, every request resulted in a new connection to the database being created and destroyed. This can be OK for very lowtraffic sites, or sites with minimal database interaction, but this will very quickly become a performance bottleneck on high-traffic sites.

One very common solution to this problem is the concept of connection pooling.A connection pool is essentially a pool of active, connected database connections that can be used by your Web application when necessary.This would result in connections being taken from the pool when needed and then returned to the pool when they are finished with, avoiding the unnecessary process of constantly creating and destroying connections. Figure shows the basic architecture of a connection pool. In the diagram, some of the connections are in use, whereas others are free. Either way, they are all permanently connected to the database. Application servers all provide connection pooling out of the box.You have also seen how it can be configured to work in Tomcat with the use of the DataSource.

In the J2EE architecture,connection pools work with javax.sql.DataSource bjects, as shown in Figure

Connection pool architecture in a Web application.

Connection pool architecture in a Web application.

Connection pool architecture using DataSources.

Connection pool architecture using DataSources.

To fully understand Figure it is important to understand the various interfaces that are involved.Three unfamiliar interfaces are used to implement connection pooling:

javax.sql.ConnectionPoolDataSource javax.sql.PooledConnection javax.sql.ConnectionEventListener

The ConnectionPoolDataSource Interface

The ConnectionPoolDataSource in Figure is very similar to a javax.sql.DataSource, except that it is used for pooled connections. It has an overloaded method:

public PooledConnection getConnection() public PooledConnection getConnection(String user, String password)

When a connection is returned by a ConnectionPoolDataSource, it is a PooledConnection.This is the next important interface.The PooledConnection is a connection to the database.A ConnectionPoolDataSource will typically have a number of them live and ready, waiting to be used.They can be seen in Figure

The PooledConnection Interface

The javax.sql.PooledConnection interface has the following methods:

java.sql.Connection getConnection() void close() void removeConnectionEventListener(ConnectionEventListener listener) void addConnectionEventListener(ConnectionEventListener listener)

The method that might surprise you in this interface is the getConnection() method.

You might be thinking that this is the connection, so why have a getConnection() method? If you notice, the method returns a java.sql.Connection. It is this object that actually gets returned to the client application. This is what is happening:When the client requests a connection from the data source, a PooledConnection is retrieved from the connection pool by the ConnectionPoolDataSource.The java.sql.Connection reference is then obtained from the PooledConnection using the getConnection() method and sent to the client. The client can then interact with this connection as they would any other connection. The important thing from the client point of view is that they can interact with a connection from a connection pool in exactly the same way as they would interact with a connection from a straightforward data source. To access a connection from a connection pool using a JSP it will be exactly the same as if you were using any other data source. Assuming you are using the tag libraries, you would have something like the following in your page:

<sql:query dataSource=”jdbc/address” var=”addresses”> SELECT * FROM AddressList</sql:query>

The dataSource attribute above contains a JNDI lookup to a DataSource object as before.The fact that it might be implementing connection pooling is transparent to the client. As discussed, one of the key benefits of this architecture is that the client does not need to know how the connections are being implemented. Application servers provide visual interfaces for setting up connection pools. Figure shows the administration interface for WebLogic 7.The minimum and maximum numbers of connections are set up from here.

Web Logic connection pool management screen.

Web Logic connection pool management screen.

Other application servers provide a similar interface to enable these settings to be configured.

The ConnectionEventListener Interface

The last of the three interfaces that are involved in connection pooling is the javax.sql.ConnectionEventListener.This interface is used under the covers of the connection pool implementation to enable the application to keep track of which connections are checked back in. The interface has two methods:

public void connectionClosed (ConnectionEvent evt) public void connectionErrorOccurred (ConnectionEvent evt)

The client has the reference to a java.sql.Connection object.When close() is called on this object, the connectionClosed (ConnectionEvent evt) method is called on a Connection EventListener, and the PooledConnection is then returned to the pool to be used by other clients.Therefore, it is very important that connections are closed, otherwise you can end up with connection pools constantly running out of connections.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

JSP Topics