公司新闻     |      2021-04-13 10:46

Improved Performance with a Connection Pool by Hans Bergsten

If you have heard anything about Servlets you know that one of the advantages over CGI is that a Servlet can keep information between requests and share common resources. This article describes one common use of this feature, namely a database connection pool.
September 01, 1999

A dynamic web site generates HTML pages from information stored in a database. Each request for a page results in a database access. But connecting to a database is a time consuming activity since the database must allocate communication and memory resources as well as authenticate the user and set up the corresponding security context. The exact time varies, of course, but it is not unusual to see connection times of one or two seconds. Establishing the connection once and then use the same connection for subsequent requests can therefore dramatically improve the performance of a database driven web application. Since Servlets can keep information between requests, a database connection pool is a straight forward solution.

A Servlet is a Java class. A Servlet Engine (native to the web server or a separate add-on module) loads the class in the Java Virtual Machine (JVM) process and creates one instance of the Servlet class at start-up or the first time the Servlet is requested. Each request for the Servlet is then executed in a separate thread using the same Servlet instance. The Servlet remains resident between requests and can keep persistent data in instance variables or in separate helper objects, such as connection pool objects.

The Java Database Connect API (JDBC) is supported by all major database vendors as well as many small databases. To access a database through JDBC you first open a connection to the database, resulting in a Connection object. A Connection object represents a native database connection and provides methods for executing SQL statements. The database connection pool described in this article consists of manager class that provides an interface to multiple connection pool objects. Each pool manages a set of JDBC Connection objects that can be shared by any number of Servlets.

The database connection pool class, DBConnectionPool, provides methods to get an open connection from the pool, return a connection to the pool, release all resources and close all connections at shutdown. It also handles connection failures, such as time-outs, communication failures, etc. and can limit the number of connections in the pool to a predefined max value.

The manager class, DBConnectionManager, is a wrapper around the DBConnectionPool class that manages multiple connection pools. It loads and registers all JDBC drivers, creates DBConnectionPool objects based on properties defined in a properties file, maps connection pool names to DBConnectionPool instances, keeps track of connection pool clients to shut down all pools gracefully when the last client is done. The rest of this article describes each class in detail, starting with the DBConnectionPool class. You will also see an example of how a Servlet is using the connection pool. The complete source code for the DBConnectionManager and the DBConnectionPool is also available here. The DBConnectionPool classThe DBConnectionPool class represents a pool of connections to one database. The database is identified with a JDBC URL. A JDBC URL consists of three parts: the protocol identifier (always jdbc), the driver identifier (e.g. odbc, idb, oracle, etc.) and the database identifier (the format is driver specific). As an example, jdbc:odbc:demo, is the JDBC URL for a database named demo accessed through the JDBC-ODBC bridge driver. The pool also has a name used by the clients and optionally a user name and password and a max connection limit. If you develop a web application where all users can execute some database operations but others are restricted to authorized users, you can define one pool for the general user and another pool for the restricted group using the same JDBC URL but different user names and passwords. ConstructorThe DBConnectionPool constructor takes all values described above as its parameters: public DBConnectionPool(String name, String URL, String user, String password, int maxConn) { = name; this.URL = URL; this.user = user; this.password = password; this.maxConn = maxConn; } It saves all parameter values in its instance variables. Get an open connection from the poolThe DBConnectionPool class provides two methods for checking out a connection. They both return an existing Connection if one is available, otherwise they create a new Connection. If no Connection is available and the max number of connections have been reached, the first method returns null but the other waits until an existing Connection is checked in. public synchronized Connection getConnection() { Connection con = null; if (freeConnections.size() > 0) { // Pick the first Connection in the Vector // to get round-robin usage con = (Connection) freeConnections.firstElement(); freeConnections.removeElementAt(0); try { if (con.isClosed()) { log("Removed bad connection from " + name); // Try again recursively con = getConnection(); } } catch (SQLException e) { log("Removed bad connection from " + name); // Try again recursively con = getConnection(); } } else if (maxConn == 0 || checkedOut < maxConn) { con = newConnection(); } if (con != null) { checkedOut++; } return con; } All Connection objects in the pool are kept in a Vector, named freeConnections, when they are checked in. If there is at least one Connection in the Vector getConnection() picks the first one. As you will see later, Connections are added to the end of the Vector when they are checked in so picking the first ensures an even utilization to minimize the risk that the database disconnects a connection due to inactivity.