Looking For Connection Bean For Connection Pool

[JiaPei Jen]

I am looking for a connection bean that provides connection object to a database from a connection pool. I am using Tomcat. The connection pool has been configured in the $TOMCATHOME\config\server.xml file and JNDI is referenced in the web.xml file.
I know some people use http://www.bitmechanic.com/projects/jdbcpool/
and are happy with it. But, my machine cannot unzip the .tar file that is downloadable from the bitmechanic. Therefore, I am unable to go for that option.
I have come up with my own simple DBConnecion.java to pass a connection to a controller servlet (VerificationServlet.java). But, I got NullPointerException because it is a 'null' connection object. And I need help. My code is shown below:
DBConnection.java:

package cptest;
import java.sql.Connection;
import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class DBConnection 
{
   public static Connection getDBConnection() throws SQLException
   {
      Connection conn = null;
      try
      {
  
         InitialContext ctx = new InitialContext();
         DataSource ds = ( DataSource ) ctx.lookup( "java:comp/env/jdbc/MySQLDB" );
         try 
         {
            conn = ds.getConnection();
         }
         catch ( SQLException sqlEx )
         {
            System.out.println( "cannot get JDBC connection: " + sqlEx ); 
         }
      }
      catch ( NamingException nEx )
      {
         nEx.printStackTrace();
      }
      return conn;
   }
}
 

VerificationServlet.java:

package cptest;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class VerificationServlet extends HttpServlet
{
   public void doGet( HttpServletRequest request, HttpServletResponse response )
               throws ServletException, IOException
   {
      response.setContentType( "text/html" );
      PrintWriter out = response.getWriter();
      try
      {
         Connection conn = DBConnection.getDBConnection();
         try
         {
            Statement stmt = conn.createStatement();
            String query = "SELECT * FROM test";
            ResultSet rs = stmt.executeQuery( query );
            while ( rs.next() )
            {
               out.println( rs.getString( "user_ID" ) + "     " + rs.getString( "user_password" ) + "<p>" );
            }
            rs.close();
            stmt.close();
         }
         catch ( SQLException e )
         {
            out.println( "JDBC error: " + e );
         }
            conn.close();
      }    
      catch ( SQLException sqlEx )
      {
         out.println( "could not get JDBC connection: " + sqlEx );
      }
   }
}

[ July 11, 2003: Message edited by: JiaPei Jen ]