Solved

connection pooling

Posted on 2000-03-31
4
724 Views
Last Modified: 2008-03-03
can anyone please explain the below 2 concepts elaborately?

1.Connection Pooling

2.Servlet Chaining
0
Comment
Question by:sumabala
  • 3
4 Comments
 
LVL 16

Expert Comment

by:heyhey_
ID: 2672838
Java & Internet Glossary

http://www.mindprod.com/jgloss.html
0
 
LVL 7

Expert Comment

by:Ravindra76
ID: 2673311

Connection pooling
http://developer.java.sun.com/developer/onlineTraining/Programming/JDCBook/conpool.html
http://webdevelopersjournal.com/columns/connection_pool.html
www-4.ibm.com/software/webservers/appserv/doc/v30/ae/web/doc/whatis/iccpool.html
www.serverpages.com/Java_Server_Pages/Java_APIs/Connection_Pooling/

What is connection pooling?

Connection pooling lets you control and reduce the resources used by your Web-based applications. Web-based applications accessing data servers incur
higher and less predictable overhead than non-Web applications, because Web users connect and disconnect more frequently. Often, more resources are
spent connecting and disconnecting than are spent during the interactions themselves. Users interactions are typically shorter, due to the "surfing" nature of
the Internet. Users are often from outside the company (Internet, rather than intranet), which make usage volumes higher and more difficult to predict.
Connection pooling spreads the connection overhead across several user requests by establishing a pool of connections which user servlets can use. Put
another way, each user request incurs the overhead of only a fraction of the cost of a connect/disconnect. After the initial resources are spent to produce
the connections in the pool, additional connect/disconnect overhead is insignificant because the existing connections are reused repeatedly.
The servlets use the connection pool as follows: When a user makes a request over the Web to a servlet, the servlet uses an existing connection from the
pool, meaning the user request does not incur the overhead of a data server connect. When the request is satisfied, the servlet returns the connection to the
connection pool for use by other servlets. The user request therefore does not incur the overhead of a data server disconnect. It is important to note that the
servlet does not explicitly access a connection pool. Instead, the servlet uses a DataSource object to get a connection, and the connection pooling services
are automatically provided through the DataSource object. Use of the DataSource object is discussed under Connection pooling implementation.
Connection pooling also lets you control the number of concurrent connections to a data server product. This is very useful if the data server license
agreement limits you to a certain number of users. You could create a pool for the data server and set the maximum connections parameter (discussed under
Performance features) for the connection pool equal to the maximum number of users permitted by your data server product license. This approach does not
guarantee compliance if you connect to the data server with other programs that bypass connection pooling.

Connection pooling implementation

JDBC 1.0 defines the Java APIs for access to relational databases. With the introduction of JDBC 2.0, the APIs have been split into two parts:

   1.The JDBC 2.0 Core API contains evolutionary improvements, but has been kept small and focused like the JDBC 1.0 API in order to promote ease of
     use. Code written for the 1.0 API continues to work with the 2.0 API. The 2.0 API classes remain in the java.sql package.
   2.The JDBC 2.0 Standard Extension API defines specific kinds of additional functionality when vendors are ready to provide the functionality and
     programmers are ready to use the functionality. IBM has implemented these new classes and interfaces in new packages. You can use the new
     classes and interfaces in your applications by using the following import statements:

     import com.ibm.db2.jdbc.app.stdext.javax.sql.*;
     import com.ibm.ejs.dbm.jdbcext.*;

Connection pooling is defined as part of the JDBC 2.0 Standard Extension API. Another part of the Standard Extension API provides for the use of the Java
Naming and Directory Interface (JNDI) and DataSource objects as an alternative to using DriverManager objects to access relational data servers. IBM has
implemented these extensions in Application Server Version 3.0. These extensions are explained using code fragments that show:

   1.How things are done the "old" way - how relational data servers are accessed by a servlet using the JDBC 1.0 APIs. Call this the "reference model."
   2.How relational data servers are accessed by a servlet the "new" way - using the new JDBC 2.0 Standard Extension APIs.

The "old" way: The reference model using the JDBC 1.0 APIs (which will still work under JDBC 2.0 and Application Server 3.0) is based on the code
fragments shown in the following steps.

   1.Load the driver for a specific relational database product. The specific driver class should be available from the Web administrator. This is typically
     done once in the init() method of the servlet.

     Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");

   2.Use the static getConnection() method of the DriverManager class to get a JDBC Connection to the relational database product, again using
     parameters for a specific database product. The subprotocol, database, userid, and password information should be available from the Web
     administrator. This is done for each client request made to the servlet, typically in the doGet() or doPost() method. (The subprotocol and database
     information are combined into what is called the database URL, shown as "jdbc:subprotocol:database" below.)

     Connection conn =
       DriverManager.getConnection("jdbc:subprotocol:database",     // database URL
                                   "userid",
                                   "password");

   3.Given the connection, do the necessary data server interactions for each client request. This is typically done in the doGet() or doPost() method.
   4.At the end of each client request, free the connection resource. This is typically done at the end of the doGet() or doPost() method.

     conn.close();

The "new" way: As noted in the Connection pooling discussion, the reference model (the "old" way) will probably be inefficient in Web-based applications
due to data server connections being frequently created and dropped. However, Application Server Version 3.0 supports the JDBC 2.0 Standard Extension
APIs and implements connection pooling. Therefore, servlets running under Application Server Version 3.0 can be coded to make efficient use of data server
connection resources. The coding concepts are shown in the code fragments in the following steps.

   1.Using JNDI, get access to the relevant naming service. A naming service is a way to store things - Java objects for example - as well as a way to
     retrieve them. The first part of this process is to create a Hashtable object that will hold the parameters necessary to access the naming service. This
     is typically done once in the init() method of the servlet.

     Hashtable parms = new Hashtable();

   2.Next, put the parameter information into the Hashtable object. The parameters should be available from the Web administrator. This is typically done
     once in the init() method of the servlet.

     parms.put(Context.INITIAL_CONTEXT_FACTORY, "com.ibm.ejs.ns.jndi.CNInitialContextFactory");

   3.Given the relevant parameters, get the naming context. The naming context will allow you to access things in the naming service. This is typically done
     once in the init() method of the servlet.

     Context ctx = new InitialContext(parms);

   4.Then, using the JNDI naming context, get a DataSource object. The DataSource object is defined in the JDBC 2.0 Standard Extension APIs. This is
     typically done once in the init() method of the servlet.

     DataSource ds = (DataSource)ctx.lookup(source);

     This is an example of how a naming service is used - specify a name and retrieve (in this case) a Java object from the service. In this code fragment,
     the source object is a string containing the context and logical name for the lookup. For example, the source object might be something like
     "jdbc/sample." The Java object retrieved is a DataSource object stored in the "jdbc" context with the logical name of "sample." The DataSource object
     is created by a Web administrator and stored in the naming service using the WebSphere Administrative Console. In the code fragment above, the
     string value of the source object can be read from an external property file, making the servlet more portable. Alternatively, the Web administrator
     could give you the context and logical name for you to use as an explicit string argument in the lookup() method. Vendor specific data server
     information and site specific information (such as the database name) are therefore hidden from the programmer. In the code fragment above, the ds
     object is local to the init() method and is not available to other methods. In actual servlets, the ds object would first be defined as an instance variable
     outside of all methods, and then fully initialized within the init() method, making it available for use in all servlet methods. In the code fragment above,
     the class name DataSource is shown before the ds variable (making it a local variable) in order to more clearly identify the type of the ds variable for
     the sake of the example.
   5.The DataSource object is a "factory" that is used to get an actual JDBC Connection object (a DataSource object is roughly comparable to the
     DriverManager introduced with JDBC 1.0). Getting the connection is done for each client request made to the servlet, typically in the doGet() or
     doPost() method.

     conn = ds.getConnection("userid", "password");

   6.Given the connection, do the necessary data server interactions for each client request. This is typically done in the doGet() or doPost() method. You
     can continue to use the same coding as with JDBC 1.0, or you can use some of the new JDBC 2.0 Standard Extension APIs.
   7.At the end of each client request, free the connection resource. This is typically done at the end of the doGet() or doPost() method.

     conn.close()

Note: The use of the close() method above looks identical to the reference model (using JDBC 1.0), but unlike the reference model, a real Connection object is
not closed, so there is no close overhead. Instead, the Connection object is returned to the connection pool for reuse. In a similar fashion, the earlier
getConnection() method on the DataSource object probably did not go through the overhead of creating a new Connection object, but instead returned an
already existing Connection object from the connection pool. (A new Connection object is created only if the connection pool does not have an available
Connection object.)
In environments other than Application Server Version 3.0 there might be no automatic connection pooling mechanism underneath a DataSource object, so
every getConnection() on a DataSource object and the related close() might in fact go through the overhead of creating a new connection and closing a
connection. Within Application Server Version 3.0, however, using a DataSource object to get a Connection object automatically provides the efficiencies of
connection pooling.

Performance features

Creating a new connection for the pool is a high overhead task and the new connection will use resources on the data server machine. Therefore, Application
Server Version 3.0 seeks to maximize the probability that a servlet can get an existing connection from the pool. At the same time, Application Server Version
3.0 must minimize the idle connections in the pool, because they are a significant waste of resources. Finally, orphan connections must be returned to the pool
and made available for reuse by other servlets (an orphan connection is a connection owned by a servlet that has become unresponsive, perhaps because
of an unexpected problem).
Application Server Version 3.0 performs these minimizing and maximizing tasks based on pool parameters set in the WebSphere Administrative Console. See
the WebSphere Administrative Console online help for a discussion on setting these parameters and facilities for monitoring pool performance. Some of the
parameters include:

     maximum connection pool size - the maximum number of connections (in-use, idle, and orphaned) that can be in the pool. If maximum connections has
     been reached and all connections are in-use, then an additional servlet request will wait for a connection to be released by other servlets. If a
     connection is not made available after a short time (specified in the connection timeout parameter), the servlet will get a
     com.ibm.ejs.dbm.jdbcext.ConnectionTimeoutException (a subclass of SQLException).
     idle timeout - how long an idle connection can remain in the pool before being removed to free resources.
     orphan timeout - how long a connection can be owned by a servlet before being considered orphaned.
     connection timeout - how long a request for a connection will wait until an exception is thrown. This can happen if the maximum connection limit is
     met, the pool is empty of available connections, and no free connections are returned to the pool within the time specified by the connection timeout
     parameter.
     minimum connection pool size - the number of connections that will be in the pool when the pool is first created. A busy site will probably have an
     actual number of connections in the pool well above the minimum (up to the maximum allowed). As user requests drop to zero, the number of
     connections in the pool may be reduced by the Application Server Version 3.0 optimization process to a number below the minimum connections.

If the servlet is going to use the connection to communicate with the data server multiple times over an extended period and there is no activity on the
connection, there is a chance that the connection might be considered orphaned and taken away form the servlet. (This is a "preemption" feature that can be
configured by an administrator through the WebSphere Administrative Console.) You may want to add code to the servlet to handle this possibility. See How
servlets use connection pooling for details.
Note that the servlet does not access the connection pool directly. As discussed Connection pooling implementation, the servlet requests a connection through
a DataSource object and the connection pooling is provided automatically. A DataSource object might have several associated connection pools, and the
specific pool used depends on the userid parameter specified in the DataSource object getConnection() method. You may want to work with the Web
administrator to design connection pools suitable for the needs of specific servlets or classes of servlets.
See Connection pooling implementation and How servlets use connection pooling for coding samples.

How servlets use connection pooling

The basic concepts are discussed in Connection pooling implementation. All servlets using connection pooling will follow the numbered steps illustrated in the
fully working code sample ConnPoolTest.java. You may want to display the code sample while reading the following steps:

   1.Import JDBC packages and IBM implemented extensions:
     Import the JDBC 2.0 Core classes, the IBM implementation of the JDBC 2.0 Standard Extension classes, and the JNDI naming classes. These are the
     classes needed for data server access.
   2.Create the initial naming context:
     The servlet will use the naming service to get access to a DataSource object placed there by a Web administrator. The Web Administrator will provide
     the parameters needed to create the naming context. This step needs to be done only once, in the init() method.
   3.Get a DataSource object:
     The DataSource object is a "factory" to be used by all requests to get a connection. Note that the code sample uses a String object named source to
     do a lookup to get a DataSource object. The text string would have a context part and a logical name part. A typical string might look something like
     "jdbc/sample" where the context is "jdbc" and the logical name is "sample." This information can be supplied by the Web administrator and the
     information identifies the DataSource object placed in the naming service for use by the servlet programmer. Use the context and logical name
     appropriate for your servlet. This step needs to be done only once, in the init() method.
   4.Get a Connection object using the DataSource factory:
     This step is done for every request, usually in the doGet() or doPost() method. The connection is automatically a connection from the connection pool.
     Each individual client request gets its own connection.
   5.Interact with the data server:
     This step is done for every request, usually in the doGet() or doPost() method. The servlet interacts with the data server - retrieving data, updating
     data, and so on - using methods of the connection object and objects derived from the connection. You can use the JDBC 2.0 Core APIs or JDBC 2.0
     Standard Extension APIs.
     If you use the data connection for more than one data server interaction within the same user request, and the request extends over a period of time
     approaching the maximum age parameter, then there is a chance that the connection will be considered an orphan connection and be taken away
     from the servlet. (This is a "preemption" feature that can be configured by an administrator through the WebSphere Administrative Console.) You may
     then get a ConnectionPreemptedException. You may want to add code to handle this specific exception. One possible response could be to use the
     DataSource object to get another connection. However, with proper connection pool tuning and servlet design, this should not be a problem with most
     servlets.
   6.Close the connection:
     This step is done for every request, usually in the doGet() or doPost() method. The servlet invokes the close() method on the connection, which does
     not actually close the connection but simply frees it and returns it to the connection pool for use by another servlet. Note that the close is done in a
     finally block, so you are assured that the close will take place regardless of whether the data server interactions are successful or cause an
     exception. Such coding will reduce the possibility of orphan connections.
   7.Prepare and send the response:
     This step is done for every request, usually in the doGet() or doPost() method. The servlet prepares and returns the response to the user request. In
     this step you will probably not be using any JDBC APIs.

Coding considerations

     You will need ejs.jar, db2java.zip, and ujc.zip in your CLASSPATH in order to import the packages and successfully compile the sample servlet, as
     well as any servlets you write. You will also need servlet.jar in your CLASSPATH for servlet related classes. These files are in the lib directory under
     under the Application Server root install directory.
     Given a DataSource object ds, there are two versions of the getConnection() method that you can use to get a connection:

     Connection conn = ds.getConnection();

     Connection conn = ds.getConnection(useridString, passwordString);

     In the first version, the default database userid and password are used. In the second version, you specify explicit character strings for the userid
     and password.
     You may want to code for the possibility of a ConnectionPreemptedException when using a connection. This exception can happen if your servlet
     does not use a connection for an extended period, so that the connection is considered to be "orphaned" and is taken away from your servlet.
     You may want to code for the possibility of a ConnectionTimeoutException when getting a connection. This exception can happen if the pool is empty
     when the connection request is made, and no free connections are returned to the pool within the time specified by the connection timeout parameter.
     Note that the sample servlet reads in some information from an external property file, making the servlet more portable. You may want to write your
     own servlets to promote portability.


0
 
LVL 7

Expert Comment

by:Ravindra76
ID: 2673319

  http://java.sun.com (anything about Java : downloads + guide)
  http://www.servletsource.com  (this deals with servlets)
  http://www.coolservlets.com
  http://www.servletforum.com
0
 
LVL 7

Accepted Solution

by:
Ravindra76 earned 50 total points
ID: 2676677


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 class

The 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.

Constructor

The DBConnectionPool constructor takes all values described above as its
parameters:

public DBConnectionPool(String name, String URL, String user,
String password, int maxConn) {
this.name = 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 pool

The 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.

Before returning the Connection to the client, the isClosed() method is used
to verify that the connection is okay. If the connection is closed, or an
exception is thrown, the method calls itself again to get another connection.

If no Connection is available in the freeConnections Vector the method
checks if the max connection limit is specified, and if so, if it's been reached.
A maxConn value of 0 means "no limit". If no limit has been specified or the
limit has not been reached, the method tries to create a new Connection. If
it's successful, it increments the counter for the number of checked out
connections and returns the Connection to the client. Otherwise it returns
null.

The newConnection() method is used to create a new Connection. This is a
private method that creates a Connection based on if a user name and a
password have been specified or not.

private Connection newConnection() {
Connection con = null;
try {
if (user == null) {
con = DriverManager.getConnection(URL);
}
else {
con = DriverManager.getConnection(URL, user, password);
}
log("Created a new connection in pool " + name);
}
catch (SQLException e) {
log(e, "Can't create a new connection for " + URL);
return null;
}
return con;
}

The JDBC DriverManager provides a set of getConnection() methods that
takes a JDBC URL plus other parameters, for instance a user name and a
password. The DriverManager uses the URL to locate a JDBC driver matching
the database and opens a connection.

The second getConnection() method takes a time-out parameter, with a
value in milliseconds for how long the client is willing to wait for a connection.
It's implemented as a wrapper around the first getConnection() method:

public synchronized Connection getConnection(long timeout) {
long startTime = new Date().getTime();
Connection con;
while ((con = getConnection()) == null) {
try {
wait(timeout);
}
catch (InterruptedException e) {}
if ((new Date().getTime() - startTime) >= timeout) {
// Timeout has expired
return null;
}
}
return con;
}

The local startTime variable is initialized with the current time. A while loop
first try to get a connection. If it fails, wait() is called with the the number of
milliseconds we are prepared to wait. wait() returns either when another
thread calls notify() or notifyAll(), as you will see later, or when the time has
elapsed. To figure out if wait() returned due to a time-out or a notification,
the start time is subtracted from current time and if the result is greater than
the time-out value the method returns null. Otherwise getConnection() is
called again.

Return a connection to the pool

The DBConnectionPool class also provides a method for returning a
connection to the pool. The freeConnection() method takes the returned
Connection object as its parameter.

public synchronized void freeConnection(Connection con) {
// Put the connection at the end of the Vector
freeConnections.addElement(con);
checkedOut--;
notifyAll();
}

The Connection is added to the end of the freeConnections Vector and the
counter for number of checked out connections is decremented. The
notifyAll() is called to notify other clients waiting for a connection.

Shutdown

Most Servlet Engines provide some method for graceful shutdown. The
database connection pools need to be notified about this event so that all
connections can be closed correctly. The DBConnectionManager class is
responsible for coordinating the shutdown but it's the DBConnectionPool class
that closes all connections in the pool. The release() method is called by the
DBConnectionManager.

public synchronized void release() {
Enumeration allConnections = freeConnections.elements();
while (allConnections.hasMoreElements()) {
Connection con = (Connection) allConnections.nextElement();
try {
con.close();
log("Closed connection for pool " + name);
}
catch (SQLException e) {
log(e, "Can't close connection for pool " + name);
}
}
freeConnections.removeAllElements();
}

This method loops through the freeConnections Vector and closes all
Connections. When all Connections have been closed they are removed from
the Vector.

The DBConnectionManager
class

The DBConnectionManager class is implemented according to the Singleton
pattern described in many design books. A Singleton is a class with just one
instance. Other objects can get a reference to the single instance through a
static method (class method).

Constructor and getInstance()

The DBConnectionManager constructor is private to prevent other objects to
create instances of the class.

private DBConnectionManager() {
init();
}

Clients of the DBConnectionManager calls the getInstance() method to get a
reference to the single instance.

static synchronized public DBConnectionManager getInstance() {
if (instance == null) {
instance = new DBConnectionManager();
}
clients++;
return instance;
}

The single instance is created the first time this method is called and a
reference is then kept in the static variable named instance. A counter for
the number of DBConnectionManager clients is incremented before the
reference is returned. This counter is later used to coordinate the shutdown
of the pools.

Initialization

The constructor calls a private method called init() to initialize the object.

private void init() {
InputStream is = getClass().getResourceAsStream("/db.properties");
Properties dbProps = new Properties();
try {
dbProps.load(is);
}
catch (Exception e) {
System.err.println("Can't read the properties file. " +
"Make sure db.properties is in the CLASSPATH");
return;
}
String logFile = dbProps.getProperty("logfile",
"DBConnectionManager.log");
try {
log = new PrintWriter(new FileWriter(logFile, true), true);
}
catch (IOException e) {
System.err.println("Can't open the log file: " + logFile);
log = new PrintWriter(System.err);
}
loadDrivers(dbProps);
createPools(dbProps);
}

The getResourceAsStream() method is a standard method for locating an
external file and open it for input. How the file is located depends on the
class loader but the standard class loader for local classes searches for the
file in the CLASSPATH, starting in the directory where the class file is located.
The db.properties file is a file in the Properties format containing key-value
pairs that define the connection pools. The following common properties can
be defined:

drivers
A space separated list of JDBC driver class names
logfile
The absolute path for a log file


Another set of properties are used for each pool. The property name starts
with the name of the connection pool:

<poolname>.url
The JDBC URL for the database
<poolname>.maxconn
The max number of connections in the pool. 0
means no limit.
<poolname>.user
The user name for the pool
<poolname>.password
The corresponding password


The url property is mandatory but all the others are optional. The user name
and the matching password must be valid for the database defined by the
URL.

Below is an example of a db.properties file for a Windows platform, with one
pool for an InstantDB database and one pool for an MS Access database
accessed through an ODBC Data Source Name (DSN) demo.

drivers=sun.jdbc.odbc.JdbcOdbcDriver jdbc.idbDriver
logfile=D:\\user\\src\\java\\DBConnectionManager\\log.txt

idb.url=jdbc:idb:c:\\local\\javawebserver1.1\\db\\db.prp
idb.maxconn=2

access.url=jdbc:odbc:demo
access.user=demo
access.password=demopw

Note that the backslashes (\) in a Windows path must be duplicated since a backslash in
a properties file is also used as an escape character.

The init() method creates a Properties object and loads the db.properties file.
It then reads the logfile property. If a log file hasn't been specified a file
named DBConnectionManager.log in the current directory is used instead. As
a last resort, System.err is used for log messages.

The loadDrivers() method loads and registers all JDBC drivers specified by the
drivers property.

private void loadDrivers(Properties props) {
String driverClasses = props.getProperty("drivers");
StringTokenizer st = new StringTokenizer(driverClasses);
while (st.hasMoreElements()) {
String driverClassName = st.nextToken().trim();
try {
Driver driver = (Driver)
Class.forName(driverClassName).newInstance();
DriverManager.registerDriver(driver);
drivers.addElement(driver);
log("Registered JDBC driver " + driverClassName);
}
catch (Exception e) {
log("Can't register JDBC driver: " +
driverClassName + ", Exception: " + e);
}
}
}

loadDrivers() uses a StringTokenizer to split the drivers property value into a string for
each driver class name and and then loops through all class names. Each class is loaded
into the JVM and an instance is created. The instance is then registered with the JDBC
DriverManager and added to a private Vector. The drivers Vector is used at shutdown
to deregister all drivers from the DriverManager.

Next the DBConnectionPool objects are created by the private createPools()
method.

private void createPools(Properties props) {
Enumeration propNames = props.propertyNames();
while (propNames.hasMoreElements()) {
String name = (String) propNames.nextElement();
if (name.endsWith(".url")) {
String poolName = name.substring(0, name.lastIndexOf("."));
String url = props.getProperty(poolName + ".url");
if (url == null) {
log("No URL specified for " + poolName);
continue;
}
String user = props.getProperty(poolName + ".user");
String password = props.getProperty(poolName + ".password");
String maxconn = props.getProperty(poolName + ".maxconn", "0");
int max;
try {
max = Integer.valueOf(maxconn).intValue();
}
catch (NumberFormatException e) {
log("Invalid maxconn value " + maxconn + " for " +
poolName);
max = 0;
}
DBConnectionPool pool =
new DBConnectionPool(poolName, url, user, password, max);
pools.put(poolName, pool);
log("Initialized pool " + poolName);
}
}
}

An Enumeration of all property names is created and scanned for property names ending
with .url. When such a property is found, the pool name is extracted, all properties for
the corresponding connection pool are read and a DBConnectionPool object is created
and saved in an instance variable named pools. pools is a Hashtable, using the pool name
as the key and the DBConnectionPool object as the value.

Get and return a connection

The DBConnectionManager provides the getConnection() and freeConnection()
methods used by the clients. All of them take a pool name parameter and relay the call
to the corresponding DBConnectionPool object.

public Connection getConnection(String name) {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != null) {
return pool.getConnection();
}
return null;
}

public Connection getConnection(String name, long time) {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != null) {
return pool.getConnection(time);
}
return null;
}

public void freeConnection(String name, Connection con) {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != null) {
pool.freeConnection(con);
}
}

Shutdown

Finally, the DBConnectionManager has a method called release(). This method is used
for graceful shutdown of the connection pools. Each DBConnectionManager client must
call the static getInstance() method to get a reference to the manager. As you could see
above, a client counter variable is used in this method to keep track of the number of
clients. The release() method is called by each client during shutdown and the client
counter is decremented. When the last client calls release(), the DBConnectionManager
calls release() on all DBConnectionPool objects to close all connections.

public synchronized void release() {
// Wait until called by the last client
if (--clients != 0) {
return;
}

Enumeration allPools = pools.elements();
while (allPools.hasMoreElements()) {
DBConnectionPool pool = (DBConnectionPool) allPools.nextElement();
pool.release();
}
Enumeration allDrivers = drivers.elements();
while (allDrivers.hasMoreElements()) {
Driver driver = (Driver) allDrivers.nextElement();
try {
DriverManager.deregisterDriver(driver);
log("Deregistered JDBC driver " + driver.getClass().getName());
}
catch (SQLException e) {
log(e, "Can't deregister JDBC driver: " +
driver.getClass().getName());
}
}
}

When all DBConnectionPool objects have been released, all JDBC drivers are
deregistered.

Example of a Servlet using
the connection pool

The Servlet API defines a Servlet's life cycle like this:

1.Servlet is created then initialized (the init() method).
2.Zero or more service calls from clients are handled (the service()
method).
3.Servlet is destroyed then garbage collected and finalized (the destroy()
method).

A Servlet using the connection pool described in this article typically performs the
following actions in these methods:

1.in init(), calls DBConnectionManager.getInstance() and saves the
reference in an instance variable.
2.in service(), calls getConnection(), performs all database operations,
and returns the Connection to the pool with freeConnection().
3.in destroy(), calls release() to release all resources and close all
connections.

The following is an example of a simple Servlet using the connection pool classes this
way.

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class TestServlet extends HttpServlet {
private DBConnectionManager connMgr;

public void init(ServletConfig conf) throws ServletException {
super.init(conf);
connMgr = DBConnectionManager.getInstance();
}

public void service(HttpServletRequest req, HttpServletResponse res)
throws IOException {

res.setContentType("text/html");
PrintWriter out = res.getWriter();
Connection con = connMgr.getConnection("idb");
if (con == null) {
out.println("Can't get connection");
return;
}
ResultSet rs = null;
ResultSetMetaData md = null;
Statement stmt = null;
try {
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT * FROM EMPLOYEE");
md = rs.getMetaData();
out.println("<H1>Employee data</H1>");
while (rs.next()) {
out.println("<BR>");
for (int i = 1; i < md.getColumnCount(); i++) {
out.print(rs.getString(i) + ", ");
}
}
stmt.close();
rs.close();
}
catch (SQLException e) {
e.printStackTrace(out);
}
connMgr.freeConnection("idb", con);
}

public void destroy() {
connMgr.release();
super.destroy();
}
}

Class loader considerations

Most Servlet Engines use a special class loader for Servlets to be able to automatically
reload a Servlet when it is modified. This class loader handles all class files located in a
special directory, typically named servlets. A common mistake among new Servlet
developers is to also put helper classes, such as the connection pool classes described
here, in the servlets directory. This doesn't work at all when static variables are involved
since the special class loader creates a separate class instance for each Servlet. To
avoid this problem you should place all helper classes in the regular CLASSPATH used
by the Servlet Engine. How you set the CLASSPATH varies between Servlet Engines
so read the documentation for the product you use.

Resources

Source code for the classes described in this article:
DBConnectionManager.java

Servlet-enabled Products:
http://jserv.java.sun.com/products/java-server/servlets/environments.html

Sun's JDBC Pages:
http://java.sun.com/products/jdbc/jdbc.drivers.html

Sun's Servlet Pages:
http://java.sun.com/products/java-server/servlets/

Sun, Sun Microsystems, Java, and JDBC are trademarks or registered
trademarks of Sun Microsystems, Inc. in the United States and other
countries.

Read more Java articles like this one ==>

Read more technical articles like this one ==>



Servlets Chaining:-

http://jserv.javasoft.com/products/java-server/documentation/webserver1.0.2/servlets/filter.html Servlets chanin

http://www.servlets.com/jsp/examples/index.html  



0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now