January 21, 2021
Hot Topics:

Databases and Tomcat

  • By Sams Publishing
  • Send Email »
  • More Articles »

Direct JDBC Database Access

Any JDBC-compliant database can be used with Tomcat provided the necessary supporting classes are available. If the JDBC driver for a database is provided as a JAR file, this JAR file must be added to the <CATALINA_HOME>/common/lib directory; otherwise, the Tomcat 4.1 class loader will not be able to load the driver.

Note - The restriction of placing JDBC driver JAR files in <CATALINA_HOME>/common/lib applies to the Tomcat 4.1 beta release. Under Tomcat 4.0, you may also store the JAR files in the WEB-INF/lib or <CATALINA_HOME>/lib directory.

A Simple Database Servlet

The first example program shown in Listing 9.1 is a simple servlet that uses a database to display the exchange rate for converting UK pounds sterling (GBP) to US dollars (USD).

Listing 9.1 The Simple Database Program DatabaseRates.java

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

public class DatabaseRates extends HttpServlet
  public void doGet(HttpServletRequest request,
 HttpServletResponse response)
  throws ServletException, IOException
    PrintWriter out = response.getWriter();
    Connection con = null;
    out.println ("<HTML><HEAD><TITLE>Conversion Rates");
    out.println ("</TITLE></HEAD><BODY>");
    out.println("<H1>Conversion Rates</H1>");

    try {
      con = DriverManager.getConnection("jdbc:mysql://localhost/test","root","secret");
      PreparedStatement pstmt = con.prepareStatement("SELECT rate FROM Exchange WHERE src = ? and dst = ?");
      ResultSet results = pstmt.executeQuery();
      if (!results.next())
        throw new SQLException("Missing Exchange rate data row");
      double rate = results.getDouble(1);
      out.println("GBP to USD rate = "+rate+"<BR>");
      results = pstmt.executeQuery();
      if (!results.next())
        throw new SQLException("Missing Exchange rate data row");
      rate = results.getDouble(1);
      out.println("EUR to USD rate = "+rate+"<BR>");
    catch (Exception ex)
      out.println("<H2>Exception Occurred</H2>");
      if (ex instanceof SQLException) {
        SQLException sqlex = (SQLException) ex;
        out.println("SQL state: "+sqlex.getSQLState()+"<BR>");
        out.println("Error code: "+sqlex.getErrorCode()+"<BR>");
    finally {
      try { con.close(); } catch (Exception ex) {}
    out.println ("</BODY></HTML>");

Note - The error handling in Listing 9.1 and all other examples in this chapter is designed to aid development and debugging. Chapter 12, "Error Handling," discusses techniques for incorporating user-friendly error handling for a live application.

Although the example in Listing 9.1 works and is thread-safe, there are a large number of problems:

  • The JDBC driver, database, username, and password are hard-coded into the program.

  • Every HTTP request must open a new connection to the database.

  • The database access code and the HTML presentation code are inextricably intermixed within the servlet.

As previously discussed, hard-coded database details always reduce portability of code and should be avoided if at all possible.

Opening a database connection for every request will result in slower performance and may overload the database server because creating new client connections is a resource-intensive operation. In addition, as you have already been shown, the mixing of business logic and data presentation in a single class is a sign of poor system design and makes code maintenance a nightmare. Each of the problems just identified can be resolved using standard tools and design techniques as discussed later in this chapter.

A Bad Example Servlet

Before studying the correct approach to database access using data sources, a common technique suggested by some online tutorials and textbooks will be examined and rejected due to the complications inherent in the approach.

In order to improve performance, you might think of moving the database connection code into the HttpServlet.init() method as follows:

public class DatabaseRates extends HttpServlet
  implements SingleThreadModel
  Connection con;
  PreparedStatement pstmt ;
  public void init() throws ServletException
try {
      con = DriverManager.getConnection("jdbc:mysql://localhost/test","root","secret");
      pstmt = con.prepareStatement("SELECT rate FROM Exchange WHERE src = ? and dst = ?");
    catch (SQLException ex) {
      throw new ServletException("Cannot create database connection",ex);

This is a technique you should not adopt. By attempting to improve performance in this manner, you create a whole range of other potential problems:

  • The Connection and PreparedStatement instance variables are not multithread–safe, and the servlet must now implement the SingleThreadModel, which will degrade servlet performance and scalability.

  • The database connection is kept open for the lifetime of the servlet instance and this may affect database performance and scalability. If you adopt the same approach for many servlets, you will use up large numbers of database connections for servlets that are idle for a lot of the time. You may even have to reconfigure your database to support an unusually large numbers of connections.

  • A database may time out a connection that is kept open for too long or one that remains idle for specified period of time. If this happens, you will have to add code to your servlet to deal with a closed connection, further complicating the logic of your servlet.

You can address these problems by using the servlet init() method to cache the exchange rate in an instance variable when the servlet is first accessed. All subsequent accesses will use the cached rate rather than access the database. But this approach cannot handle the real-world situation where the exchange rate varies over time. To solve this problem, the servlet must periodically update the cached exchange rate from the master value stored in the database. You then have the problem of deciding on a suitable algorithm for updating the cached exchange rate and adding additional database access code. This adds complications to the servlet that can be avoided simply by using connection pooling.

Page 2 of 6

This article was originally published on April 1, 2003

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date