March 8, 2021
Hot Topics:

Databases and Tomcat

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

Data Access Objects (DAOs)

A Data Access Object (DAO) encapsulates access to a database so that data manipulation code can be separated out from other business logic and data presentation code. Good use of DAOs will simplify the design and development of Web applications and reduce the cost of ongoing maintenance and upgrades.

DAOs are a Java design pattern where all the database access code is encapsulated in supporting Java classes. Changes to the database details such as the table schema (column names and types) will usually only affect the DAO and not the Java code using the DAO. Combining the DAO with a Tomcat data source provides a good solution to using a database with Tomcat.

Listing 9.3 shows a JSP that uses a Java Bean DAO to display a table of currency exchange rates.

Listing 9.3 The rates.jsp Page Using a DAO

<jsp:useBean id="dao" class="converters.ConversionDAO" scope="page" >
  <jsp:setProperty name="dao" property='*'/>
<% dao.updateRate(); %>
 <HEAD><TITLE>Currency Conversion Rates</TITLE></HEAD>
  <H1>Conversion Rates</H1>
  GBP to USD rate = <%= dao.selectRate("GBP","USD") %><BR>
  EUR to USD rate = <%= dao.selectRate("EUR","USD") %><BR>
  EUR to GBP rate = <%= dao.selectRate("EUR","GBP") %><BR>
  <H1>Update Rate</H1>
        <TD>Convert from:</TD>
        <TD><SELECT NAME='src'>
        <TD><SELECT NAME='dst'>
        <TD COLSPAN='3'><INPUT TYPE='text' NAME='rate'></TD>
    <P> <INPUT TYPE='submit' VALUE='Set new Rate'> </P>

The first thing that should strike you about Listing 9.3 is the absence of complex Java code and any database access code. The beginning of the page creates the DAO as a Java Bean and sets the properties of the DAO that match the HTTP request parameters by using the element

<jsp:setProperty name="dao" property='*'/>

The DAO has been designed so that the properties reflect the three columns of the underlying Exchange table (src, dst, and rate). If the HTTP request parameters include these three properties, the DAO's updateRate() method will update the underlying Exchange table with a new exchange rate. The line

<% dao.updateRate(); %>

will update an existing exchange rate with parameters supplied with the HTTP request.

The main part of the JSP uses the DAO bean to display the exchange rates for three currencies (EUR, GBP, and USD) and displays a simple form for updating a conversion rate. Filling in a new conversion rate and submitting the form will invoke this JSP to update the database (using the <% dao.updateRate(); %> action coded at the top of the page).

The DAO is not complicated, as shown in Listing 9.4.

Listing 9.4 The ConversionDAO.Java Data Access Object

package converters;

import java.io.*;
import java.sql.*;
import javax.naming.*;
import javax.sql.*;
import java.util.*;

public class ConversionDAO
  private Connection con;
  private PreparedStatement select;
  private PreparedStatement update;
  private String src;
  private String dst;
  private double rate;

  public ConversionDAO() throws SQLException, NamingException
    Context init = new InitialContext();
    Context ctx = (Context) init.lookup("java:comp/env");
    DataSource ds = (DataSource) ctx.lookup("jdbc/conversion");
    con = ds.getConnection();
    select = con.prepareStatement("SELECT rate FROM Exchange WHERE src = ? AND dst = ?");
    update = con.prepareStatement("UPDATE Exchange SET rate = ? WHERE src = ? AND dst = ?");

  public String getSrc() { return src;}
  public String getDst() { return dst;}
  public double getRate() { return rate;}
  public void setSrc(String src) { this.src = src;}
  public void setDst(String dst) { this.dst = dst;}
  public void setRate(double rate) { this.rate = rate;}

  public double selectRate(String src, String dst) throws SQLException
    ResultSet results = null;
    try {
      results = select.executeQuery();
      if (!results.next())
        throw new SQLException("Missing Exchange rate data row");
      return results.getDouble(1);
    finally {
      try { results.close(); } catch (Exception ex) {}

  public int updateRate() throws SQLException
    if (src==null || dst==null)
      return 0;
    return update.executeUpdate();

  public void close()
    try { select.close(); } catch (Exception ex) {}
    try { update.close(); } catch (Exception ex) {}
    try { con.close(); } catch (Exception ex) {}

For simplicity, the examples in Listings 9.3 and 9.4 exclude any error-handling code. In Chapter 12, you will be shown how to use Web application error pages and other error-handling techniques that will enable you to enhance the JSP to deal gracefully with any underlying database or JNDI problems.

Note - You will need to deploy any DAO classes to the WEB-INF/classes directory together with the JSPs in your Web application.

An alternative technique for accessing a database from within a JSP is to use a custom tag to encapsulate the database access (see Chapter 11, "JSP Expression Language and JSTL").

That's it. You now know how to work with DAOs and data sources to access a JDBC database.

Security Considerations

Your main security problem concerns the coding of plain-text passwords in the server.xml file. You must ensure that only authorized users (ideally only the Tomcat administrator) can read the server.xml file.

One minor headache occurs if you configured Tomcat to use a security manager (see Chapter 14, "Access Control"). If you are accessing a database with a security manager, you will need to add appropriate permission lines to the <CATALINA_HOME>/conf/catalina.policy file to allow the Web application to connect to the database. You will need to add the following lines to the catalina.policy file (assuming the MySQL database is running on localhost using the default 3306 port):

grant codeBase "file:${catalina.home}/webapps/database/WEB-INF/classes/- {
  permission java.net.SocketPermission "localhost:3306", "connect";

If you use a different database, you will need to amend the database hostname and port number specified for the java.net.SocketPermission permission to match your database server configuration.

Tomcat does not use a security manager by default, so you will normally not have permission problems with database access.

Page 5 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