Reusing Database Objects (Java Servlet Programming)

In the introduction, we mentioned that the servlet life cycle allows for extremely fast database access. After you've used JDBC for a short time, it will become evident that the major performance bottleneck often comes right at the beginning, when you are opening a database connection. This is rarely a problem for most applications and applets because they can afford a few seconds to create a Connection that is used for the life of the program. With servlets this bottleneck is more serious because we are creating and tearing down a new Connection for every page request. Luckily, the servlet life cycle allows us to reuse the same connection for multiple requests, even concurrent requests, as Connection objects are required to be thread safe.

9.3.1. Reusing Database Connections

A servlet can create one or more Connectionobjects in its init() method and reuse them in its service(), doGet(), and doPost() methods. To demonstrate, Example 9-4 shows the phone lookup servlet rewritten to create its Connection object in advance. It also uses the HtmlSQLResult class from Example 9-3 to display the results. Note that this servlet uses the Sybase JDBC driver.

Example 9-4. An improved directory servlet

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

public class DBPhoneLookupReuse extends HttpServlet {

  private Connection con = null;

  public void init(ServletConfig config) throws ServletException {
    super.init(config);
    try {
      // Load (and therefore register) the Sybase driver
      Class.forName("com.sybase.jdbc.SybDriver");
      con = DriverManager.getConnection(
        "jdbc:sybase:Tds:dbhost:7678", "user", "passwd");
    }
    catch (ClassNotFoundException e) { 
      throw new UnavailableException(this, "Couldn't load database driver");
    }    
    catch (SQLException e) { 
      throw new UnavailableException(this, "Couldn't get db connection");
    }    
  }

  public void doGet(HttpServletRequest req, HttpServletResponse res)
                               throws ServletException, IOException {
    res.setContentType("text/html");
    PrintWriter out = res.getWriter(); 

    out.println("<HTML><HEAD><TITLE>Phonebook</TITLE></HEAD>");
    out.println("<BODY>");

    HtmlSQLResult result = 
      new HtmlSQLResult("SELECT NAME, PHONE FROM EMPLOYEES", con);
     
    // Display the resulting output
    out.println("<H2>Employees:</H2>");
    out.println(result);
    out.println("</BODY></HTML>");
  }

  public void destroy() {
    // Clean up.
    try {
      if (con != null) con.close();
    }
    catch (SQLException ignored) { }
  }
}

9.3.2. Reusing Prepared Statements

With a little care, you can speed servlet performance even more by creating other database-related objects ahead of time. The PreparedStatementobject is an ideal candidate because it can precompile a SQL statement. This usually saves only a few milliseconds, but if your site gets a few hundred thousand hits a day, that can add up pretty quickly.

Note, however, that sharing objects other than connections poses a problem. Servlets must be thread safe, and accessing a PreparedStatement might require three or four method calls. If one thread calls the clearParameters() method of PreparedStatement right before another thread calls execute(), the results of execute() will be disastrous. Also, there's the limitation that a Statement can support only one query (and any associated result sets) at a time. The solution is to synchronize the sections of your code that use shared objects, as discussed in Chapter 3, "The Servlet Life Cycle" and shown here:

Unfortunately, this solution is not without drawbacks. Entering a synchronization block on some platforms takes extra time, and synchronized objects can be used by only one thread at a time. However, some servlets already require a synchronization block, and in these cases the drawback is less of an issue. A good rule of thumb, then, is to create your connections ahead of time, along with any frequently used objects (such as PreparedStatement objects) that can be quickly used inside preexisting synchronization blocks.

For servlets written using the SingleThreadModel interface, these issues do not apply. On the other hand, you will have a number of copies of your servlet loaded at once, which could be just as detrimental to performance.


9.2. The JDBC API		9.4. Transactions

9.3. Reusing Database Objects

9.3.1. Reusing Database Connections

Example 9-4. An improved directory servlet

9.3.2. Reusing Prepared Statements