[ Team LiB ] Previous Section Next Section

Recipe 21.3 Using a DataSource in a Servlet with Tomcat


You want to use a DataSource that you have configured with Tomcat.


Use the JNDI API classes to obtain the DataSource, then access a database connection from that DataSource.


Use classes from the javax.naming package to access the configured DataSource. For example, use a javax.naming.InitialContext object to look up a DataSource that has been bound as a JNDI object.

The javax.naming package is a part of the Java Platform Standard Edition 1.3 and 1.4.

Example 21-4 instantiates a javax.sql.DataSource instance variable in its init( ) method, which the servlet container calls when it creates a servlet instance. In Tomcat, JNDI objects are stored under the root level specified by the "java:comp/env" string.

Example 21-4. Using a DataSource in a servlet
package com.jspservletcookbook;           

import java.sql.*;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.*;

import javax.servlet.*;
import javax.servlet.http.*;

public class DbServlet extends HttpServlet {

    DataSource pool;
  public void init( ) throws ServletException {
      Context env = null;
          env = (Context) new InitialContext( ).lookup("java:comp/env");

          //Look up a DataSource, which represents a connection pool
          pool  = (DataSource) env.lookup("jdbc/oracle-8i-athletes");

          if (pool == null)
              throw new ServletException(
              "'oracle-8i-athletes' is an unknown DataSource");
      } catch (NamingException ne) { 
           throw new ServletException(ne.getMessage( ));



  public void doGet(HttpServletRequest request, 
    HttpServletResponse response)
    throws ServletException, java.io.IOException {
      String sql = "select * from athlete";

      Connection conn = null;

      Statement stmt = null;

      ResultSet rs = null;

      ResultSetMetaData rsm = null;
      //Start building the HTML page
      java.io.PrintWriter out = response.getWriter( );
      "<html><head><title>Typical Database Access</title></head><body>");

      out.println("<h2>Database info</h2>");
      out.println("<table border='1'><tr>");
          //Get a Connection from the connection pool
          conn = pool.getConnection( );

          //Create a Statement object that can be used to execute
          //a SQL query
          stmt = conn.createStatement( );
          //execute a simple SELECT query
          rs = stmt.executeQuery(sql);

          //Get the ResultSetMetaData object so we can dynamically
          //display the column names in the ResultSet
          rsm = rs.getMetaData( );

          int colCount =  rsm.getColumnCount( );
          //print column names in table header cells
          for (int i = 1; i <=colCount; ++i){
              out.println("<th>" + rsm.getColumnName(i) + "</th>");
          //while the ResultSet has more rows...

          while( rs.next( )){
              //Print each column value for each row with the 
              //ResultSet.getString( ) method
              for (int i = 1;  i <=colCount; ++i)
                  out.println("<td>" + rs.getString(i) + "</td>");


      } catch (Exception e){
          throw new ServletException(e.getMessage( ));
      } finally {
              //When a Statement object is closed, any associated
              //ResultSet is closed
              if (stmt != null)
                  stmt.close( );
              //VERY IMPORTANT! This code returns the Connection to the 
              if (conn != null)
                  conn.close( );
            } catch (SQLException sqle){ }

Example 21-4 gets a DataSource by using the address configured in Tomcat (Recipe 21.2; jdbc/oracle-8i-athletes) in a JNDI lookup. This code looks like this:

env = (Context) new InitialContext( ).lookup("java:comp/env");

//Look up a DataSource, which represents a connection pool
pool  = (DataSource) env.lookup("jdbc/oracle-8i-athletes");

The code then obtains a database connection from the connection pool by calling the DataSource object's getConnection( ) method. It is very important to call the Connection object's close( ) method when the servlet is finished with it, because this method call returns the shared Connection to the pool.

Requesting the servlet of Example 21-4 in a browser creates output that looks just like Figure 21-1.

Chapter 23 on the JSTL shows how to use a JSP to access a database with a DataSource configuration.

See Also

The JDBC specification: http://java.sun.com/products/jdbc/download.html; Recipe 21.1 on accessing a database from a servlet without a connection pool; Recipe 21.2 on configuring a DataSource on Tomcat; Recipe 21.4-Recipe 21.6 on configuring and using DataSource with servlets and JSPs on WebLogic; Recipe 21.7 and Recipe 21.8 on calling stored procedures from servlets and JSPs; Recipe 21.9 on converting a java.sql.ResultSet object to a javax.servlet.jsp.jstl.sql Result; Recipe 21.10 and Recipe 21.11 on using transactions in servlets and JSPs; Recipe 21.12 on finding out information about a ResultSet.

    [ Team LiB ] Previous Section Next Section