NullPointerException in jsp but not in console

Question

RecupData my class returns a NullPointerException only in the web. I connect to a pgsql db 8.3.7 - the script works well in 'console' syso - but a test web NullPointerException is thrown

Here is the method that throws the exception:

/**
 * 
 */
package com.servlet.pgsql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 */
public class RecupData {

    /**
     * Connection connection
     */

    static String SqlSinRef     = "SELECT * FROM \"SINref\""; // view in pgsql - contain only 1 column
    static Connection connection;
    static Statement statement;



    /**
     * cette méthode sert pour toutes les connections de la classe RecupData
     */
    public static Statement state() {

        try{
           connection = UniqueConnection.getInstance();
           statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        }
        catch(Exception e) {    e.printStackTrace();    }

        return statement;
    }


    public static String htmlListeRefDossier() {

        ResultSet resultat;

        String listeHtml    =   "<form>\n" +
                                "\t<select>\n";     

        try {
            resultat = state().executeQuery(SqlSinRef);
            while(resultat.next()) {
                listeHtml = listeHtml+"\t\t<option>"+resultat.getString(1)+"</option>\n";   }
        }
        catch (SQLException e) {
            listeHtml = listeHtml+"sql exception sortie";
            e.printStackTrace();
        }
        catch (NullPointerException e) {
            listeHtml = listeHtml+"null pointer exception -sortie";
            e.printStackTrace();
        }


        listeHtml = listeHtml+"\t</select>\n</form>";
        return listeHtml;

    }


    /*
     * valable just pour le dev
    * method main only for test in console
     */
    public static void main(String[] args) throws SQLException {

        System.out.println(htmlListeRefDossier());

    }
    /*
     * A SUPPRIMER  
     */

} // fin class

this is UniqueConnection :

public class UniqueConnection {

  private volatile static Connection connect;

  public static Connection getInstance(){

    if(connect == null){
      synchronized(Connection.class){
        try {  connect = DriverManager.getConnection(url, user, passwd);  }
        catch(SQLException e) { e.printStackTrace(); }
      }
    }
    return connect; 
  } 

}

could you take a look? Thank you!

Answer 1

Look here:

public static Statement state() {

    try{
       connection = UniqueConnection.getInstance();
       statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    }
    catch(Exception e) {    e.printStackTrace();    }

    return statement;
}

Your exception handling is poor. If UniqueConnection.getInstance() or connection.createStatement() throws an exception, then the code will immediately jump into the catch block, hereby untouching the statement variable which thus remains null. As you're just printing the stacktrace to the stdout and continuing the code flow instead of throwing a fullworthy exception, the code continues into the return statement line. Because statement is still null, you'll get a NullPointerException whenever you attempt to invoke a method on the returned value.

resultat = state().executeQuery(SqlSinRef); // state() returns null, so NPE!

You need to read the stacktrace which is printed inside the catch clause for the real cause of the problem, not the one which is caused by the NPE. Or better yet, throw a real exception so that the code will immediately stop instead of continuing the flow and working with variables which are possibly null.

Fix all your catch clauses which are just swallowing the exception and printing the trace to throw a real exception instead:

catch(SomeException e) { throw e; }

Or, better, just remove them and use the throws clause in the method declaration instead. You should only use try block whenever you can handle the exception in a sensible manner (i.e. taking an alternative path, displaying an error screen to the enduser and aborting the code, etc; just printing the trace and continuing with null variables definitely isn't a proper alternative path), or whenever you would like to use finally block to close and release expensive resources.

My bet is that it's actually a SQLException: no suitable driver, because the loading of the driver failed with a ClassNotFoundException org.postgresql.Driver and you suppressed this exception on the same manner.

---

Unrelated to the problem: there are more serious problems with your code.

• You've declared expensive database resources such as connection, statement and resultset as static variables of the class. This is not threadsafe! It'll work fine in an application which uses them once and then immediately shuts down. But it will fail miserably in an application which uses them multiple times during the run, such as a web application.

• You do not want to use an "unique connection" throughout the web application's lifetime. The average database will automatically close it whenever it's been opened for a too long time. You need to acquire and close the connection, statement and resultset in the shortest possible scope. If you want to improve connecting performance, you should be using a connection pool instead.

• Your data access layer is extremly tight coupled with the presentation layer. You're building some piece of HTML inside a database access class instead of inside a JSP file.

Fix it accordingly. You can find some kickoff examples in the answer(s) of the following questions:

• How to manage db connections on server?
• How often should Connection, Statement and ResultSet be closed in JDBC?
• retrieve dropdown list from mysql database and insert to database in jsp
• How to avoid Java code in JSP files?

Answer 2

I assume that here statement will be null:

statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                       ResultSet.CONCUR_READ_ONLY);

I guess this has something to with the synchronized statement because servlets and jsp should be generally be build a a thread-safe manner

Please try in initialize your connection within your RecupData but without any synchronized and static statement and look if this works - May also be a issue to your Classloader that quite different in some app servers.