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