O'Reilly logo

Java Programming with Oracle JDBC by Donald Bales

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Servlet Connection Strategies

From a programmer’s perspective, a servlet has three stages to its life cycle. They are defined by the following three methods, or types of methods:

init( )

This method is normally used to perform any initialization that should take place only once in the lifetime of the servlet. The init( ) method is invoked automatically before any of the servlet’s doXXX( ) methods can be called.

doXXX( )

The various do methods -- doGet( ), doDelete( ), doPost( ), and doPut( ) -- are called as needed by web users to satisfy their dynamic content and form processing needs.

destroy( )

This method is called just before the servlet container removes the servlet from memory, which typically happens when the servlet container itself is shutting down.

Given the life cycle described here, you have four strategies for making a database connection. The differences between these strategies hinge on when the connection is made and on whether connections are shared between servlets. The four strategies are:

Per-transaction connection

You load the Oracle JDBC driver in the servlet’s init( ) method, open a connection at the beginning of each doXXX( ) method, and close that connection at the end of each doXXX( ) method.

Dedicated connection

You use a combination of the init( ) and destroy( ) methods, whereby you load the driver and open a connection in the init( ) method, and then close that connection in the destroy( ) method. As a result, the servlet uses one connection that remains open during the servlet’s entire lifetime and is shared by all users of the servlet.

Session connection

You load the Oracle JDBC driver in the init( ) method, but you don’t open a connection until the beginning of the first doXXX( ) method. You then store that connection in an HTTP Session object, from which it can be retrieved and used by other doXXX( ) method calls invoked by the same user session.

Cached connection

You use a connection pool to minimize the total number of connections that are open at any one time. At the beginning of each doXXX( ) method, you allocate a connection from the connection pool for use while the method executes then return that connection to the pool at the end of the doXXX( ) method.

Let’s start a more detailed examination of these methods by looking first at the per-transaction connection strategy.

A Per-Transaction Connection

The per-transaction connection strategy is the kind of connection that most CGI programs use, and it’s the least efficient of the four strategies. The Oracle JDBC driver is loaded once in the init( ) method. While the servlet is in operation, a new database connection is created at the beginning of each doXXX( ) method and is closed at the end of each doXXX( ) method. This model for managing connections is inefficient, because database connections are costly to create in terms of both response time and system resources. As a result, connecting to a database is a time-consuming process for the servlet. In addition, because connection creation is a costly process for the database, frequent connecting and disconnecting will impact the response time of other database users. Regardless of all this, there may be cases where such an approach is justified. Example 4-1 shows a servlet that uses a per-transaction connection.

Example 4-1. A one-connection-per-transaction servlet

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class TransactionConnectionServlet extends HttpServlet {

  public void init(ServletConfig config)
   throws ServletException {
    super.init(config);
    try { 
      // Load the driver
      Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(  );
    }
    catch (ClassNotFoundException e) {
      throw new UnavailableException(
       "TransactionConnection.init(  ) ClassNotFoundException: " + 
       e.getMessage(  ));
    }
    catch (IllegalAccessException e) {
      throw new UnavailableException(
       "TransactionConnection.init(  ) IllegalAccessException: " + 
       e.getMessage(  ));
    }
    catch (InstantiationException e) {
      throw new UnavailableException(
       "TransactionConnection.init(  ) InstantiationException: " + 
       e.getMessage(  ));
    }
  }

  public void doGet(
   HttpServletRequest request, HttpServletResponse response) 
   throws IOException, ServletException {
    
    response.setContentType("text/html");
    PrintWriter out = response.getWriter(  );
    out.println("<html>");
    out.println("<head>");
    out.println("<title>A Per Transaction Connection</title>");
    out.println("</head>");
    out.println("<body>");

    Connection connection = null;
    try {
      // Establish a connection
      connection = DriverManager.getConnection(
       "jdbc:oracle:thin:@dssw2k01:1521:orcl", "scott", "tiger");
    }
    catch (SQLException e) {
      throw new UnavailableException(
       "TransactionConnection.init(  ) SQLException: " + 
       e.getMessage(  ));
    }

    Statement  statement = null;
    ResultSet  resultSet = null;
    String     userName  = null;  
    try { 
      // Test the connection
      statement = connection.createStatement(  );
      resultSet = statement.executeQuery(
       "select initcap(user) from sys.dual");
      if (resultSet.next(  ))
        userName = resultSet.getString(1);
    }
    catch (SQLException e) {
      out.println(
       "TransactionConnection.doGet(  ) SQLException: " + 
       e.getMessage(  ) + "<p>");
    }
    finally {
      if (resultSet != null) 
        try { resultSet.close(  ); } catch (SQLException ignore) { }
      if (statement != null) 
        try { statement.close(  ); } catch (SQLException ignore) { }
    }

    if (connection != null) {
      // Close the connection
      try { connection.close(  ); } catch (SQLException ignore) { }
    }

    out.println("Hello " + userName + "!<p>");
    out.println("You're using a per transaction connection!<p>");
    out.println("</body>");
    out.println("</html>");
  }

  public void doPost(
   HttpServletRequest request, HttpServletResponse response)
   throws IOException, ServletException {
    doGet(request, response);
  }
}

When the servlet shown in Example 4-1 is loaded into a servlet container, the init( ) method is called before any of the doXXX( ) method requests are processed. This is standard behavior for any servlet. In this servlet, TransactionConnectionServlet, the init( ) method first passes the config object on to its parent class. Next, it loads the Oracle driver using the Class.forName().newInstance( ) method. Using this form of the Class.forName( ) method guarantees you compatibility with noncompliant JVMs but at the cost of having to catch two additional exception types: IllegalAccessException and InstantiationException . As the servlet operates, whenever a doGet( ) or doPost( ) method is called, a new database connection is opened, the database is queried as needed, and the connection is closed. This is simple, and often effective, but can be an inefficient method for managing connections.

Our next method, a dedicated connection, is somewhat more efficient, so let’s take a look at it.

A Dedicated Connection

Of the four strategies, the dedicated connection is the most costly in terms of the number of simultaneous database connections. Remember that a dedicated connection is opened when a servlet is initialized and closed when the servlet is destroyed. A dedicated connection remains open during the entire lifetime of a servlet and is dedicated to just that one servlet.

There are three drawbacks to a dedicated connection:

  • You need a database connection for every JDBC servlet instance that is active in your servlet container. This may not really be that much of a drawback, because Oracle claims that its database is very efficient at handling many simultaneous connections.

  • Since the connection will be shared with every user of the servlet, a transaction cannot span multiple calls to the servlet’s doXXX( ) methods. This means that you cannot provide a user with several forms in a row, using several servlets, and commit all the user’s database changes after the last of those forms has been filled out. You instead have to commit a user’s input for each form as it is submitted.

  • Because the Oracle Connection class’s methods are synchronized, only one invocation of any given method is allowed at any one time. You will experience a processing bottleneck when multiple invocations of the doXXX( ) methods attempt to use the connection at the same time. The doXXX( ) methods will have to wait their turn for access to the Connection class’s synchronized methods.

Example 4-2 shows a sample servlet that uses a dedicated connection.

Example 4-2. A dedicated connection servlet

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class DedicatedConnectionServlet extends HttpServlet {
  Connection connection;
  long       connected;

  public void init(ServletConfig config)
   throws ServletException {
    super.init(config);
    try { 
      // Load the driver
      Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(  );
    }
    catch (ClassNotFoundException e) {
      throw new UnavailableException(
       "DedicatedConnection.init(  ) ClassNotFoundException: " + 
       e.getMessage(  ));
    }
    catch (IllegalAccessException e) {
      throw new UnavailableException(
       "DedicatedConnection.init(  ) IllegalAccessException: " + 
       e.getMessage(  ));
    }
    catch (InstantiationException e) {
      throw new UnavailableException(
       "DedicatedConnection.init(  ) InstantiationException: " + 
       e.getMessage(  ));
    }

    try {
      // Establish a connection
      connection = DriverManager.getConnection(
       "jdbc:oracle:thin:@dssw2k01:1521:orcl", "scott", "tiger");
      connected = System.currentTimeMillis(  );
    }
    catch (SQLException e) {
      throw new UnavailableException(
       "DedicatedConnection.init(  ) SQLException: " + 
       e.getMessage(  ));
    }
  }

  public void doGet(
   HttpServletRequest request, HttpServletResponse response) 
   throws IOException, ServletException {

    response.setContentType("text/html");
    PrintWriter out = response.getWriter(  );
    out.println("<html>");
    out.println("<head>");
    out.println("<title>A Dedicated Connection</title>");
    out.println("</head>");
    out.println("<body>");
    
    Statement statement = null;
    ResultSet resultSet = null;
    String    userName  = null;  
    try { 
      // test the connection
      statement = connection.createStatement(  );
      resultSet = statement.executeQuery(
       "select initcap(user) from sys.dual");
      if (resultSet.next(  ))
       userName = resultSet.getString(1);
    }
    catch (SQLException e) {
     out.println(
      "DedicatedConnection.doGet(  ) SQLException: " + 
      e.getMessage(  ) + "<p>");
    }
    finally {
      if (resultSet != null) 
        try { resultSet.close(  ); } catch (SQLException ignore) { }
      if (statement != null) 
        try { statement.close(  ); } catch (SQLException ignore) { }
    }
    out.println("Hello " + userName + "!<p>");
    out.println(
     "This Servlet's database connection was created on " + 
     new java.util.Date(connected) + "<p>");
    out.println("</body>");
    out.println("</html>");
  }

  public void doPost(
   HttpServletRequest request, HttpServletResponse response)
   throws IOException, ServletException {
    doGet(request, response);
  }

  public void destroy(  ) {
    // Close the connection
    if (connection != null)
      try { connection.close(  ); } catch (SQLException ignore) { }
  }
}

When the servlet shown in Example 4-2 is loaded into a servlet container, the init( ) method is invoked. The init( ) method then loads the Oracle JDBC driver. All this occurs before any doXXX( ) method requests are processed. So far, this sequence of events is the same as that for the servlet named TransactionServlet in Example 4-1. In this case, though, the init( ) method also attempts to connect to the database. If the init( ) method cannot load the Oracle JDBC driver and establish a connection, it will throw an UnavailableException. This will manifest itself as a 503 error in the user’s browser.

The doGet( ) method shown in Example 4-2 uses the database connection to retrieve the login user’s username from the database. It then displays that username in the user’s browser along with the date and time that the connection was established. The database connection will persist and can be used by other doXXX( ) methods until the servlet is destroyed. You can verify this by executing the servlet, waiting several minutes, and then executing it again. You’ll notice that the servlet displays the same initial connection time no matter how many times you execute it. This connection time indicates how long the connection has been open.

When the servlet is unloaded from the servlet container, the destroy( ) method is invoked. The destroy( ) method in turn closes the dedicated connection.

The dedicated connection strategy yields an improvement in response time efficiency over the per-transaction connection strategy because the connection is already open, but it requires many more simultaneous database connections. This is because you must have a dedicated connection for every servlet that accesses the database. In even a small application, this can be hundreds of connections.

The next strategy we will discuss -- the session connection strategy -- improves response time by removing the bottleneck of a single connection object. It also resolves the transaction boundary problem. However, all this is still at the cost of many simultaneous database connections.

A Session Connection

If your servlet is part of a larger application that calls for a connection that is dedicated to a particular user, then a session connection is your best option. The session connection strategy is similar to that used for an application client -- the connection is opened at the beginning of the program and closed when the application is closed. In the case of servlets, a connection is established the first time a particular user calls a servlet requiring a connection. The connection then remains open until the user’s session expires.

For example, suppose you are writing a servlet that is part of a human resources application. Due to the highly confidential nature of HR data, and because you need to keep an audit trail of who makes changes to the data, you may decide that you cannot use a dedicated connection as we did in the previous section. Remember that a dedicated connection is shared by all users of a servlet. In this case, to ensure that each session gets its own connection, you can open a connection for a given username and store that connection in an HTTP session object. The session object itself will be available from one HTTP transaction to the next, because a reference to it will be stored and retrieved by your browser using cookies. This functionality is handled automatically by the HttpServlet class as per the servlet API specification. Since the reference for the database connection will be stored in the user’s session object, the connection will be available to all servlets invoked by the user’s session. Example 4-3 demonstrates one way to implement a session connection strategy.

Example 4-3. A session connection servlet

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class Login extends HttpServlet {

  public void init(ServletConfig config)
   throws ServletException {
    super.init(config);
    try { 
      // Load the driver
      Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(  );
    }
    catch (ClassNotFoundException e) {
      throw new UnavailableException(
       "Login init() ClassNotFoundException: " + e.getMessage(  ));
    }
    catch (IllegalAccessException e) {
      throw new UnavailableException(
       "Login init() IllegalAccessException: " + e.getMessage(  ));
    }
    catch (InstantiationException e) {
      throw new UnavailableException(
       "Login init() InstantiationException: " + e.getMessage(  ));
    }
  }

  public void doGet(
   HttpServletRequest request, HttpServletResponse response) 
   throws IOException, ServletException {

    response.setContentType("text/html");
    PrintWriter out = response.getWriter(  );
    out.println("<html>");
    out.println("<head>");
    out.println("<title>Login</title>");
    out.println("</head>");
    out.println("<body>");
    
    HttpSession session = request.getSession(  );
    Connection connection = 
     (Connection)session.getAttribute("connection");
    if (connection == null) {
      String userName = request.getParameter("username");
      String password = request.getParameter("password");
      if (userName == null || password == null) { 
        // Prompt the user for her username and password
        out.println("<form method=\"get\" action=\"Login\">");
        out.println("Please specify the following to log in:<p>");
        out.println("Username: <input type=\"text\" " + 
         "name=\"username\" size=\"30\"><p>");
        out.println("Password: <input type=\"password\" " + 
         "name=\"password\" size=\"30\"><p>");
        out.println("<input type=\"submit\" value=\"Login\">");
        out.println("</form>");
      }
      else { 
        // Create the connection
        try {
          connection = DriverManager.getConnection(
           "jdbc:oracle:thin:@dssw2k01:1521:orcl", userName, password);
        }
        catch (SQLException e) {
          out.println("Login doGet() " + e.getMessage(  ));
        }
        if (connection != null) {
          // Store the connection
          session.setAttribute("connection", connection);
          response.sendRedirect("Login");
          return;
        }
      }
    }
    else {
      String logout = request.getParameter("logout");
      if (logout == null) {
        // Test the connection
        Statement statement = null;
        ResultSet resultSet = null;
        String    userName  = null;  
        try { 
          statement = connection.createStatement(  );
          resultSet = statement.executeQuery(
           "select initcap(user) from sys.dual");
          if (resultSet.next(  ))
            userName = resultSet.getString(1);
        }
        catch (SQLException e) {
          out.println("Login doGet() SQLException: " + e.getMessage(  ) + "<p>");
        }
        finally {
          if (resultSet != null) 
            try { resultSet.close(  ); } catch (SQLException ignore) { }
          if (statement != null) 
            try { statement.close(  ); } catch (SQLException ignore) { }
        }
        out.println("Hello " + userName + "!<p>");
        out.println("Your session ID is " + session.getId(  ) + "<p>");
        out.println("It was created on " + 
         new java.util.Date(session.getCreationTime(  )) + "<p>");
        out.println("It was last accessed on " + 
         new java.util.Date(session.getLastAccessedTime(  )) + "<p>");
        out.println("<form method=\"get\" action=\"Login\">");
        out.println("<input type=\"submit\" name=\"logout\" " + 
         "value=\"Logout\">");
        out.println("</form>");
      }
      else {
        // Close the connection and remove it from the session
        try { connection.close(  ); } catch (SQLException ignore) { }
        session.removeAttribute("connection");
        out.println("You have been logged out.");
      }
    }
    out.println("</body>");
    out.println("</html>");
  }

  public void doPost(
   HttpServletRequest request, HttpServletResponse response)
   throws IOException, ServletException {
    doGet(request, response);
  }
}

As in the previous examples, the init( ) method is called before any of the doXXX( ) method requests are processed. In this servlet, the init( ) method loads the Oracle JDBC driver using the Class.forName().newInstance( ) method. If the Login servlet cannot load the Oracle JDBC driver, it throws an UnavailableException.

When a user executes the servlet’s doGet( ) method, the following sequence of events occurs:

  1. A request object is implicitly passed as part of the HttpServlet class’s normal functionality.

  2. The doGet( ) method then uses the HttpServletRequest object’s getSession( ) method to get the current HttpSession object. If no current HttpSession object exists, the getSession( ) method automatically creates a new one.

  3. The doGet( ) method invokes the HttpSession object’s getAttribute( ) method in order to get the Connection object for the session. If no Connection object exists, getAttribute( ) returns a null. If a Connection object does exist, control goes to step 7.

  4. If the doGet( ) method sees that the Connection object is null, it will then check to see whether the user has passed a username and password as parameters of an HTML form.

  5. If username and password values are found, the doGet( ) method uses those passed values to log into the database and create a new database connection. Because this is a sample program, control is then redirected back to the Login servlet to show the user its HttpSession information.

  6. If no username and password parameters are found, the doGet( ) method creates an HTML form to prompt the user for that information. When the user enters the username and password into the form and then submits it, the Login servlet is called once again.

  7. If a Connection object does exist for the session, the doGet( ) method tests to see if the user has passed a parameter named logout as part of an HTML form.

  8. If a logout parameter has been passed, the doGet( ) method closes the database connection, removes the reference to that connection from the session object, and displays a logged out verification message.

  9. If a connection exists, and no logout parameter has been passed, the doGet( ) method uses the connection to retrieve the database username from the database. It then displays information about the user’s session.

If you were to code a doPost( ) method for the Login servlet, you’d have to add the same session connection code to that method as I’ve implemented for the doGet( ) method. For that matter, any doXXX( ) method that requires database access would require this session connection code.

Creating a session-bound wrapper for connections

With the servlet shown in Example 4-3, a user’s database connection remains open until that user submits a form containing a parameter named logout to the servlet. That’s all well and good, but what happens when the user forgets to log out before closing her browser? Or when the session times out? The answer, unfortunately, is that the connection will not be closed. It will remain open until the Oracle process monitor recognizes that the session is gone, at which point the Oracle process monitor closes the connection. This is terribly inefficient! Fortunately, there is an elegant solution to this problem. By using the HttpSessionBinding interface, you can wrap a connection object in a session-bound object that is notified when the session expires. The session-bound object can then in turn close the connection. Example 4-4 shows a wrapper class for a connection. This wrapper class is named SessionConnection.

Example 4-4. A session-bound wrapper class for a connection

import java.sql.*;
import javax.servlet.http.*;

public class SessionConnection 
 implements HttpSessionBindingListener {

  Connection connection;

  public SessionConnection(  ) {
    connection = null;
  }

  public SessionConnection(Connection connection) {
    this.connection = connection;
  }

  public Connection getConnection(  ) {
    return connection;
  }

  public void setConnection(Connection connection) {
    this.connection = connection;
  }

  public void valueBound(HttpSessionBindingEvent event) {
    if (connection != null) {
      System.out.println("Binding a valid connection");
    }
    else {
      System.out.println("Binding a null connection");
    }
  }

  public void valueUnbound(HttpSessionBindingEvent event) {
    if (connection != null) {
      System.out.println(
       "Closing the bound connection as the session expires");
      try { connection.close(  ); } catch (SQLException ignore) { }
    }
  }
}

The SessionConnection class shown in Example 4-4 holds a connection and implements the HttpSessionBindingListener interface. When you create a new Connection object, you also need to create a new SessionConnection object. You then store your new Connection object in that SessionConnection object. Then, when a session expires, the HttpSession object notifies the SessionConnection object that it is about to be unbound. This notification happens because the SessionConnection class implements the HttpSessionBindingListener interface. In turn, the SessionConnection object closes the database connection so it’s not left hanging in an open state after the session has ended.

Using the session bound wrapper class

Creating the SessionConnection class is not enough. You also need to code your servlet to use that class when managing connections. Example 4-5 shows a modified version of the Login servlet shown earlier. It can now use the SessionConnection class. The servlet has been renamed SessionLogin and uses a SessionConnection object to manage connections.

Example 4-5. An HttpSessionBindingListener session connection servlet

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class SessionLogin extends HttpServlet {

  public void init(ServletConfig config)
   throws ServletException {
    super.init(config);
    try { 
      // Load the driver
      Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(  );
    }
    catch (ClassNotFoundException e) {
      throw new UnavailableException(
       "Login init() ClassNotFoundException: " + e.getMessage(  ));
    }
    catch (IllegalAccessException e) {
      throw new UnavailableException(
       "Login init() IllegalAccessException: " + e.getMessage(  ));
    }
    catch (InstantiationException e) {
      throw new UnavailableException(
       "Login init() InstantiationException: " + e.getMessage(  ));
    }
  }

  public void doGet(
   HttpServletRequest request, HttpServletResponse response) 
   throws IOException, ServletException {

    response.setContentType("text/html");
    PrintWriter out = response.getWriter(  );
    out.println("<html>");
    out.println("<head>");
    out.println("<title>Login</title>");
    out.println("</head>");
    out.println("<body>");
    
    HttpSession session = request.getSession(  );
    SessionConnection sessionConnection = 
     (SessionConnection)session.getAttribute("sessionconnection");
    Connection connection = null;
    if (sessionConnection != null) {
      connection = sessionConnection.getConnection(  ); 
    }
    if (connection == null) {
      String userName = request.getParameter("username");
      String password = request.getParameter("password");
      if (userName == null || password == null) { 
        // Prompt the user for her username and password
        out.println("<form method=\"get\" action=\"SessionLogin\">");
        out.println("Please specify the following to log in:<p>");
        out.println("Username: <input type=\"text\" " + 
         "name=\"username\" size=\"30\"><p>");
        out.println("Password: <input type=\"password\" " + 
         "name=\"password\" size=\"30\"><p>");
        out.println("<input type=\"submit\" value=\"Login\">");
        out.println("</form>");
      }
      else { 
        // Create the connection
        try {
          connection = DriverManager.getConnection(
           "jdbc:oracle:thin:@dssw2k01:1521:orcl", userName, password);
        }
        catch (SQLException e) {
          out.println("Login doGet() " + e.getMessage(  ));
        }
        if (connection != null) {
          // Store the connection
          sessionConnection = new SessionConnection(  );
          sessionConnection.setConnection(connection);
          session.setAttribute("sessionconnection", sessionConnection);
          response.sendRedirect("SessionLogin");
          return;
        } 
      }
    }
    else {
      String logout = request.getParameter("logout");
      if (logout == null) {
        // Test the connection
        Statement statement = null;
        ResultSet resultSet = null;
        String    userName  = null;  
        try { 
          statement = connection.createStatement(  );
          resultSet = statement.executeQuery(
           "select initcap(user) from sys.dual");
          if (resultSet.next(  ))
           userName = resultSet.getString(1);
        }
        catch (SQLException e) {
          out.println("Login doGet() SQLException: " + e.getMessage(  ) + "<p>");
        }
        finally {
          if (resultSet != null) 
            try { resultSet.close(  ); } catch (SQLException ignore) { }
          if (statement != null) 
            try { statement.close(  ); } catch (SQLException ignore) { }
        }
        out.println("Hello " + userName + "!<p>");
        out.println("Your session ID is " + session.getId(  ) + "<p>");
        out.println("It was created on " + 
         new java.util.Date(session.getCreationTime(  )) + "<p>");
        out.println("It was last accessed on " + 
         new java.util.Date(session.getLastAccessedTime(  )) + "<p>");
        out.println("<form method=\"get\" action=\"SessionLogin\">");
        out.println("<input type=\"submit\" name=\"logout\" " + 
         "value=\"Logout\">");
        out.println("</form>");
      }
      else {
        // Close the connection and remove it from the session
        try { connection.close(  ); } catch (SQLException ignore) { }
        session.removeAttribute("sessionconnection");
        out.println("You have been logged out.");
      }
    }
    out.println("</body>");
    out.println("</html>");
  }

  public void doPost(  
   HttpServletRequest request, HttpServletResponse response)
   throws IOException, ServletException {
    doGet(request, response);
  }
}

The first notable change in this servlet, with respect to the Login servlet shown in Example 4-3, is that it uses a SessionConnection object as an attribute of the HttpSession object. You can see in the doGet( ) method that instead of getting a Connection object directly from an HttpSession object, this servlet gets a SessionConnection object from an HttpSession object. If the SessionConnection object is valid (i.e., it is not initialized to null), an attempt is then made using that object’s getConnection( ) method to get a connection object. If no connection object exists, the doGet( ) method creates one. It then creates a new SessionConnection object in which to store the newly created Connection object. The SessionConnection object in turn is stored in the HttpSession object.

The SessionConnection class shown in Example 4-4 contains several System.out.println( ) method calls you can use for debugging purposes. If you compile the SessionConnection.java and SessionLogin.java files, place them into service on your servlet container, and set your servlet container’s session timeout to a reasonably small period -- such as two minutes -- you can see the HttpSessionBindingListener interface in action.

As you can see from these last few examples, using the session connection strategy can add a significant amount of code to your servlet. If you don’t need a connection dedicated to a user, then you are better off using a cached connection. Let’s talk about that next.

A Cached Connection

A cached connection, or pooled connection as it is sometimes called, is the most efficient connection strategy. A separate Connection Manager object is created in the servlet container that manages a pool of cached connections (you’ll see an example Connection Manager implementation shortly). When your servlet requires a connection, it asks Connection Manager for a connection. Connection Manager then finds an unused connection, or creates a new connection if necessary, and passes that back for the servlet to use. The servlet returns the connection to the cache when it is no longer needed.

Connection Manager allocates connections, which are all made using a pool username and password, as needed by the servlets in the servlet container. Rather than close the connections when they are returned to Connection Manager, they are placed in a cache in an open state until another servlet requires them. There are several connection-caching products on the market for Java. Later, in Chapter 7, I will show Oracle’s connection-caching implementation. But since I can’t dissect them to help you get a better understanding of how they work, I’ve put together a connection-caching tool of my own for you to examine. This tool consists of the following components:

  • A class to wrap cached connections

  • A class to load drivers and create connections

  • A class to manage cached connections

The following sections show and describe each of these classes. Following the class descriptions are examples of servlets that use the classes to implement a cached connection strategy.

A class to wrap cached connections

For each connection, my caching tool needs to keep track of not only the Connection object itself, but also the following two pieces of information:

  • The time the connection was last used

  • Whether the connection is currently in use

To accomplish this objective, I’ve created a wrapper class named CachedConnection, which is shown in Example 4-6.

Example 4-6. The CachedConnection class to wrap cached connections

import java.sql.*;

public class CachedConnection {
  private boolean    inUse;
  private Connection conn;
  private long       lastUsed;
  private String     baseName;

  public CachedConnection(  ) { 
    conn     = null;
    inUse    = false;
    lastUsed = System.currentTimeMillis(  );
    baseName = "Database";
  }

  public CachedConnection(Connection conn, boolean inUse) { 
    this.conn     = conn;
    this.inUse    = inUse;
    this.lastUsed = System.currentTimeMillis(  );
    this.baseName = "Database";
  }

  public CachedConnection(Connection conn, boolean inUse, String baseName) { 
    this.conn     = conn;
    this.inUse    = inUse;
    this.lastUsed = System.currentTimeMillis(  );
    this.baseName = baseName;
  }

  public Connection getConnection(  ) { 
    return conn; 
  }

  public void setConnection(Connection conn) { 
    this.conn = conn; 
  }

  public boolean getInUse(  ) {
    return inUse;
  }

  public boolean isInUse(  ) {
    return inUse;
  }

  public void setInUse(boolean inUse) {
    if (!inUse)
      lastUsed = System.currentTimeMillis(  );
    this.inUse = inUse;
  }

  public String getBaseName(  ) {
    return baseName;
  }

  public void setBaseName(String baseName) {
    this.baseName = baseName;
  }

  public long getLastUsed(  ) {
    return lastUsed;
  }
}

A CachedConnection object has the following four attributes:

inUse

A boolean that keeps track of whether the connection is in use. A value of true indicates that the connection has been checked out by a servlet. A value of false indicates that the connection is available.

conn

A JDBC Connection object that is cached in the pool.

lastUsed

A long that holds the time the connection was last checked out. This is used by the management class to determine when to close and remove from the cache connections that have not been used in a predetermined period of time.

baseName

A String object that holds the name of the pool to which this connection belongs. This allows you to manage several different connection pools simultaneously.

The CachedConnection class’s isInUse( ) method is a function you can use in a logical statement to check if the connection is in use. The rest of the methods are getter-setter methods for the class.

A class to load drivers and create connections

The next class in my connection caching tool is a class to manage the loading of JDBC drivers and the creation of connections. This class is named Database, and it’s shown in Example 4-7.

Example 4-7. The database class to manage driver loading and connection creation

import java.sql.*;
import java.util.*;

public class Database {
  private static boolean verbose  = false;

  public static final Connection getConnection(String baseName) {
    Connection conn = null;
    String driver   = null;
    String url      = null;
    String username = null;
    String password = null;
    try {
      ResourceBundle resb = ResourceBundle.getBundle(baseName);
      driver              = resb.getString("database.driver");
      url                 = resb.getString("database.url");
      username            = resb.getString("database.username");
      password            = resb.getString("database.password");
      Class.forName(driver);
    }
    catch(MissingResourceException e) {
      System.err.println("Missing Resource: " + e.getMessage(  ));
      return conn;
    }
    catch(ClassNotFoundException e) {
      System.err.println("Class not found: " + e.getMessage(  ));
      return conn;
    }
    try {
      if (verbose) {
        System.out.println("baseName=" + baseName);
        System.out.println("driver=" + driver);
        System.out.println("url=" + url);
        System.out.println("username=" + username);
        System.out.println("password=" + password);
      }

      conn = DriverManager.getConnection(url, username, password);
    }
    catch(SQLException e) {
      System.err.println(e.getMessage(  ));
      System.err.println("in Database.getConnection");
      System.err.println("on getConnection");
      conn = null;
    }
    finally {
      return conn;
    }
  }

  public static void setVerbose(boolean v) {
    verbose = v;
  }
}

Database is a utility class that employs the use of a static variable and two static methods that allow you to call the methods without instantiating the class. The attribute verbose is a boolean that controls the output of diagnostics to standard out. The getConnection( ) method takes a String argument named baseName, which identifies a properties file on the local filesystem. This properties file must be generated before invoking the getConnection( ) method, and in it you should place the connection properties that you want each new connection to have. The following is a hypothetical example of a properties file:

database.driver=oracle.jdbc.driver.OracleDriver
database.url=jdbc:oracle:thin:@dssw2k01:1521:orcl
database.username=scott
database.password=tiger

In my solution, the pool name is used as the properties filename, so each pool can have its own, distinct set of connection properties. All connections in a given pool share the same set of properties.

A class to manage cached connections

The final piece of my connection-caching solution is a class to manage cached connections, doling them out to servlets as they are needed. The CacheConnection class, shown in Example 4-8, does this.

Example 4-8. The CacheConnection class to manage cached connections

import java.io.*;
import java.sql.*;
import java.util.Vector;

public class CacheConnection {
  private static boolean verbose           = false;
  private static int     numberConnections = 0;
  private static Vector  cachedConnections = new Vector(  );
  private static Thread  monitor           = null;
  private static long    MAX_IDLE          = 1000*60*60;

  synchronized public static Connection checkOut(  ) {
    return checkOut("Database");
  }

  synchronized public static Connection checkOut(String baseName) {
    boolean          found  = false;
    CachedConnection cached = null; 

    if (verbose) {
      System.out.println("There are " + 
       Integer.toString(numberConnections) + 
       " connections in the cache");
      System.out.println("Searching for a connection not in use...");
    }
    for (int i=0;!found && i<numberConnections;i++) {
      if (verbose) {
        System.out.println("Vector entry " + Integer.toString(i));
      }
      cached = (CachedConnection)cachedConnections.get(i);
      if (!cached.isInUse() && cached.getBaseName(  ).equals(baseName)) {
        if (verbose) {
          System.out.println("found cached entry " + 
           Integer.toString(i) + 
           " for " + baseName);
        }
        found = true;
      }
    }
    if (found) {
      cached.setInUse(true);
    }
    else {
      if (verbose) {
        System.out.println("Cached entry not found ");
        System.out.println("Allocating new entry for " + baseName);
      }
      cached = new CachedConnection(
       Database.getConnection(baseName), true, baseName);
      cachedConnections.add(cached);
      numberConnections++;
    }

    if (monitor == null) {
      monitor = new Thread( 

       new Runnable(  ) { 
         public void run(  ) { 
           while(numberConnections > 0) { 
             runMonitor(  ); 
           } 
           monitor = null; 
           if (verbose) {
             System.out.println("CacheConnection monitor stopped"); 
           }
         } 
       } 
      );
      monitor.setDaemon(true);
      monitor.start(  );
    }
    return cached.getConnection(  );
  }

  synchronized public static void checkIn(Connection c) {
    boolean          found  = false;
    boolean          closed = false;
    CachedConnection cached = null; 
    Connection       conn   = null;
    int              i      = 0;

    if (verbose) {
      System.out.println("Searching for connection to set not in use...");
    }
    for (i=0;!found && i<numberConnections;i++) {
      if (verbose) {
        System.out.println("Vector entry " + Integer.toString(i));
      }
      cached = (CachedConnection)cachedConnections.get(i);
      conn = cached.getConnection(  );
      if (conn == c) {
        if (verbose) {
          System.out.println("found cached entry " + Integer.toString(i));
        }
        found = true; 
      }
    }
    if (found) {
      try { 
        closed = conn.isClosed(  );
      }
      catch(SQLException ignore) {
        closed = true;
      }
      if (!closed) 
        cached.setInUse(false);
      else {
        cachedConnections.remove(i);    
        numberConnections--;
      }
    }
    else if (verbose) {
      System.out.println("In use Connection not found!!!");
    }
  }

  synchronized private static void checkUse(  ) {
    CachedConnection cached = null; 
    Connection       conn   = null;
    int              i      = 0;
    long             now    = System.currentTimeMillis(  );
    long             then   = 0;

    for (i=numberConnections-1;i>-1;i--) {
      if (verbose) {
        System.out.println(
         "CacheConnection monitor checking vector entry " + 
         Integer.toString(i) + 
         " for use...");
      }
      cached = (CachedConnection)cachedConnections.get(i);
      if (!cached.isInUse(  )) {
        then = cached.getLastUsed(  );
        if ((now - then) > MAX_IDLE) {
          if (verbose) {
            System.out.println("Cached entry " + 
             Integer.toString(i) + 
             " idle too long, being destroyed");
          }
          conn = cached.getConnection(  );
          try { conn.close(  ); } catch (SQLException e) { 
          System.err.println("Unable to close connection: " + 
           e.getMessage(  )); }
          cachedConnections.remove(i);    
          numberConnections--;
        }
      }
    }
  }

  private static void runMonitor(  ) {
    checkUse(  );
    if (numberConnections > 0) {
      if (verbose) {
        System.out.println("CacheConnection monitor going to sleep");
      }
      try {
        // 1000 milliseconds/second x 60 seconds/minute x 5 minutes 
        monitor.sleep(1000*60*5);
      }
      catch (InterruptedException ignore) {
        if (verbose) {
          System.out.println(
           "CacheConnection monitor's sleep was interrupted");
        }
      }
    }
  }

  public void finalize(  ) throws Throwable {
    CachedConnection cached = null; 
    for(int i=0;i<numberConnections;i++) {
      cached = (CachedConnection)cachedConnections.get(i);
      if (cached.getConnection(  ) != null) {
        if (verbose) {
          System.out.println(
           "Closing connection on Vector entry " + 
           Integer.toString(i));
        }
        try { 
          cached.getConnection().close(  ); 
        } 
        catch(SQLException ignore) { 
          System.err.println("Can't close connection!!!"); 
        }
      }
    }
    numberConnections = 0;
  }

  public static void setVerbose(boolean v) {
    verbose = v;
  }
}

This sample caching object is quite lengthy, but I figure you want a working example, and this is what it takes to get one. Let’s start dissecting this class. To begin with, notice that the CacheConnection class has several static attributes, and that all the methods are static as well. That’s because this utility class, like the Database class in Example 4-7, is never intended to be instantiated in a servlet. The attributes in the class are:

verbose

A boolean used throughout the class’s methods to turn diagnostic output on or off. Diagnostic output is written to the standard output device.

numberConnections

An integer to keep track of the number of open connections in the cache.

cachedConnections

A Vector object to contain the actual cache of connections.

monitor

A Thread object that runs independently of the CacheConnection object to manage the removal of unused connections in the cache.

MAX_IDLE

A long to hold the maximum time, in milliseconds, that an idle connection should remain in the cache.

Now that you’re familiar with the CacheConnection class’s attributes, let’s look at the class’s methods. In the discussion that follows, I’ll work my way down from the top of the class listing and discuss each method in turn.

At the top of the listing, you’ll find a pair of overloaded checkOut( ) methods. The first checkOut( ) method allocates a database connection from a default pool, while the second allocates a connection from a pool specified by name. The default pool name used by the first checkOut( ) method is “Database”. To allocate a connection from that default pool, the first checkOut( ) method simply calls the second checkOut( ) method, passing “Database” as the pool name parameter. The second checkOut( ) method does all the real work. It looks in the cache for a free connection with the corresponding pool name. If such a connection exists, it is flagged as in use, and returned as the method’s result. Otherwise, if no connection exists in the specified pool, a new connection is created, placed into the cache, flagged as in use, and returned as the method’s result. Before returning any connection, the checkOut( ) method checks if a monitor Thread object exists, creating one if necessary. I’ll cover the function of this monitor Thread object shortly.

The next method, checkIn( ) , is used by a servlet to return a connection to the cache when it is no longer needed. Besides returning the connection to the cache, checkIn( ) verifies that the connection is still open. This check is performed to allow a servlet to close a connection should a catastrophic error occur. If the connection is no longer open, the CachedConnection object that holds the connection is removed from the cache. By closing a bad connection and then returning it to the cache, a servlet can permanently remove that connection from the cache, thereby preventing another servlet from using it.

The CacheConnection class’s checkUse( ) method is called by the runMonitor( ) method, which is in turn called by the monitor thread. The purpose of the checkUse( ) method is to close any connections that have been idle longer than the time period specified by the MAX_IDLE attribute. The MAX_IDLE attribute specifies the maximum idle time in milliseconds. In Example 4-8, I’ve specified a value that results in a maximum idle time of 60 minutes. If you set the MAX_IDLE attribute to a lower value, such as 1000*60*2, or two minutes, you can easily watch the monitor thread close idle connections.

The runMonitor( ) method invokes checkUse( ) to check the cache for idle connections. The runMonitor( ) method then puts the Thread object to sleep for five minutes. After the sleep interval, the runMonitor( ) method awakens, and the cycle repeats. When there are no connections remaining in the cache, the monitor thread terminates.

The setVerbose( ) method allows you to control the display of debugging output. Calling setVerbose( ) with an argument of true puts the CacheConnection class, as well as all cached CachedConnection objects, into verbose mode. You’ll have to activate this from one of your servlets by calling CacheConnection.setVerbose(true). This causes the CacheConnection object to execute the various System.out.println( ) calls coded within its methods. The resulting debug output is written to your servlet container’s error log or to your monitor screen, depending on how your servlet container is configured. Call setVerbose( ) with an argument of false to turn verbose mode off.

The final method is finalize( ) (pun intended). When the servlet container is closed, the finalize( ) method sweeps through the cache and closes any open connections.

A servlet that uses cached connections

Now that you understand the mechanics of the connection cache, let’s put it to use. Example 4-9 shows a servlet that implements a cached connection strategy using the three classes just described. The servlet’s name is CachedConnectionServlet.

As you read through the code for CachedConnectionServlet, note that there are three significant differences between it and the SessionLogin servlet you should look for:

  1. The servlet turns on our Connection Manager’s verbose output mode with a call to the CacheConnection.setVerbose( ) method.

  2. The servlet allocates a cached connection by calling the CacheConnection.checkOut( ) method. Here, the code is quite compact when compared to the lengthy code required to manage a session connection.

  3. The servlet returns the checked-out connection by calling the checkIn( ) method.

In many respects, the cached connection strategy is very similar in implementation in the servlet to the per-transaction strategy, except this time, we’ve reduced the cost of opening and closing connections by reusing them.

Example 4-9. A cached connection servlet

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class CachedConnectionServlet extends HttpServlet {

  public void doGet(
   HttpServletRequest request, HttpServletResponse response) 
   throws IOException, ServletException {

    response.setContentType("text/html");
    PrintWriter out = response.getWriter(  );
    out.println("<html>");
    out.println("<head>");
    out.println("<title>Cached Connection Servlet</title>");
    out.println("</head>");
    out.println("<body>");

    // Turn on verbose output
    CacheConnection.setVerbose(true);

    // Get a cached connection
    Connection connection = CacheConnection.checkOut(  );
  
    Statement  statement  = null;
    ResultSet  resultSet  = null;
    String     userName   = null;  
    try { 
      // Test the connection
      statement = connection.createStatement(  );
      resultSet = statement.executeQuery(
       "select initcap(user) from sys.dual");
      if (resultSet.next(  ))
       userName = resultSet.getString(1);
    }
    catch (SQLException e) {
      out.println("DedicatedConnection.doGet(  ) SQLException: " + 
       e.getMessage(  ) + "<p>");
    }
    finally {
      if (resultSet != null) 
        try { resultSet.close(  ); } catch (SQLException ignore) { }
      if (statement != null) 
        try { statement.close(  ); } catch (SQLException ignore) { }
    }

    // Return the conection
    CacheConnection.checkIn(connection);

    out.println("Hello " + userName + "!<p>");
    out.println("You're using a cached connection!<p>");
    out.println("</body>");
    out.println("</html>");
  }

  public void doPost(
   HttpServletRequest request, HttpServletResponse response)
   throws IOException, ServletException {
    doGet(request, response);
  }
}

This last connection strategy provides the response time efficiency of session connections, while at the same time reduces the number of simultaneous database connections to an on-demand minimum. In practice, I’ve seen a caching implementation like this handle a web site with more than 1,000 hits a day without ever having more than two simultaneous connections open. Now that’s a drastic improvement in the number of simultaneous connections used when compared to the other three strategies.

Unfortunately, this strategy does not enable you to create transactions that span more than one doXXX( ) method invocation. The reason you can’t create such transactions is that you have no guarantee of getting the same connection object from one doXXX( ) method call to the next.

So of the four connection strategies I’ve discussed in this chapter, which method should you choose? Let’s discuss that next.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required