NullPointerException At Statement stmt = conn.createStatement();

[JiaPei Jen]

I am trying to use a connection pool to retrieve
data from a database. I have succeeded in putting InitialContext, DataSource, Statement,
ResultSet in "one servlet". And I am able to display the output in the browser. It means that the table is created in the database, the connection pool is properly configured, DataSource is referenced in the web.xml, and mapping in the web.xml has been taken care of.
I ran into problem when I tried to put
IntialContext and DataSource in one class
(DBConnection.java) that specializes in providing Connection object (conn). And another class
(VerificationServlet.java) with Statement and
ResultSet in it references the provided Connection object. (DBConnection.java and VerificationServlet.java are in a package 'cptest'.)
The error message I got is
"java.lang.NullPointerException at
cptest.VerificationServlet.doGet(VerificationServlet.java:25)
and the 25th line of the VerificationServlet is
Statement stmt = conn.createStatement();
I am lost. I do not know what went wrong.
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 ]

[David O'Meara]

It looks like you've forgotten to get the Connection from the DataSource.

[David O'Meara]

Oops, my bad.
What happens if conn = ds.getConnection() throws an exception? You aren't even checking for it.

[JiaPei Jen]

The exception is caught in the code snippet shown below:

         try 
         {
            conn = ds.getConnection();
         }
         catch ( SQLException sqlEx )
         {
            System.out.println( "cannot get JDBC connection: " + sqlEx ); 
         }

Somehow, the Connection (conn) is not passed from DBConnection.java to VerificationServlet.java. Just cannot figure out what wrong with my code.

[David O'Meara]

If an exception is thrown, you hide the exception and return null. Are you sure there is no exception being thrown?
This would cause a NullPointer every time.