February 28, 2021
Hot Topics:

Databases and Tomcat

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

Obtaining the Data Source Connection

When you are using resource references in your application, you must use JNDI to look up and access the resource. You will have to import the javax.naming package to use JNDI. Note that the JNDI methods shown below can throw a javax.naming.NamingException error.

The name you defined in the <res-ref-name> element is the JNDI name you must use in your code. In order to resolve the resource associated with this name, you must obtain the JNDI context for your Web application. Use the javax.naming.Context.lookup() method to look up the data source and obtain a database connection as shown in the following code:

Context init = new InitialContext();
Context ctx = (Context) init.lookup("java:comp/env");
DataSource ds = (DataSource) ctx.lookup("jdbc/conversion");
Connection con = ds.getConnection();

Use this Connection object just like a normal JDBC connection. Listing 9.2 shows the simple database example rewritten to use the JNDI data source shown in the previous examples.

Listing 9.2 Using a DataSource Object in DataSourceRates.java

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

public class DataSourceRates extends HttpServlet
  private DataSource dataSource;

  public void init(ServletConfig config) throws ServletException {
    try {
      Context init = new InitialContext();
      Context ctx = (Context) init.lookup("java:comp/env");
      dataSource = (DataSource) ctx.lookup("jdbc/conversion");
    catch (NamingException ex) {
      throw new ServletException(
        "Cannot retrieve java:comp/env/jdbc/conversion",ex);

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

    try {
      synchronized (dataSource) {
        con = dataSource.getConnection();
      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 - To recap, to run the example in Listing 9.2, you must have defined the <resource-ref> element in WEB-INF/web.xml and the <ResourceParams> element in <CATALINA_HOME>/conf/server.xml. Furthermore, you must place your JDBC JAR file in the directory <CATALINA_HOME>/common/lib so the Tomcat class loader can load the driver correctly. You will need to stop and restart Tomcat in order for your changes to the server.xml file to be recognized.

In Listing 9.2, all the data source lookup code is added to the init() method to avoid the costly JNDI operations for every HTTP request. Because the dataSource instance variable is potentially shared across multiple threads, access to the variable must be from within a synchronized block.

The example in Listing 9.2 is an improvement over Listing 9.1, but it still has problems. The intermingling of HTML presentation code and database access code is less than ideal. You will now be shown how to refactor the example by using the Java design pattern (or idiom) called a Data Access Object.

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