Recipe 21.3 Using a DataSource in a Servlet with Tomcat

Problem

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

Solution

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

Discussion

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;
        
      try{
           
          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( ));

      }//try
      

    }

  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
      response.setContentType("text/html");
      java.io.PrintWriter out = response.getWriter( );
      out.println(
      "<html><head><title>Typical Database Access</title></head><body>");

      out.println("<h2>Database info</h2>");
      out.println("<table border='1'><tr>");
        
      try{ 
          
          //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>");
          }
            
          out.println("</tr>");
          
          //while the ResultSet has more rows...

          while( rs.next( )){
                
              out.println("<tr>");
               
              //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>");
                
               out.println("</tr>");

          }//while

      } catch (Exception e){
            
          throw new ServletException(e.getMessage( ));
            
      } finally {
            
          try{
              
              //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 
              //pool
              if (conn != null)
                  conn.close( );
                
            } catch (SQLException sqle){ }
            
    }
          out.println("</table></body></html>");
      
     }//doGet
}

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.

Recipe 21.3 Using a DataSource in a Servlet with Tomcat

Problem

Solution

Discussion

Example 21-4. Using a DataSource in a servlet

See Also