JDBC PreparedStatement throws NullPointerException

Question

Could anybody please enlighten me why prepared statement returns null? Here is the code that queries the database:

package beans;
import java.sql.*;
public class ConnectToDB {
private Connection connect;

private final String url = "jdbc:mysql://localhost/";
private final String DBuser = "root";
private final String DBpass = "root";
private final String DBname = "reservation";

private final String Driver = "com.mysql.jdbc.Driver";
public ConnectToDB(){
    try{
        Class.forName(this.Driver);
        this.connect = DriverManager.getConnection(this.url+this.DBname, this.DBuser, this.DBpass);
    }catch(  ClassNotFoundException | SQLException e){ e.printStackTrace(); }
}
public Connection getConnection(){
    return this.connect;
}

private String get_user_info(String username,int index){
    /*
     * user_info[0] = user_id;
     * user_info[1] = username;
     * user_info[2] = password;
     * user_info[3] = firstname;
     * user_info[4] = middle_name;
     * user_info[5] = lastname;
     * user_info[6] = client_rights;
     * 
     */
    String user_info[] = new String[7];
    PreparedStatement pstmt = null;
    ResultSet rset = null;
    String query = null;
    try{
        query = "Select * from user where username = ?";
        pstmt = this.connect.prepareStatement(query);
        pstmt.setString(1, username);
        rset = pstmt.executeQuery();
        while(rset.next()){
            int user_id = rset.getInt("user_id");
            user_info[0] = String.valueOf(user_id);
            user_info[1] = rset.getString("username");
            user_info[2] = rset.getString("password");
            user_info[3] = rset.getString("firstname");
            user_info[4] = rset.getString("middle_name");
            user_info[5] = rset.getString("lastname");
            user_info[6] = rset.getString("user_rights");
        }
    }catch(SQLException e){ e.printStackTrace(); }
    finally{
        try{
            pstmt.close();
            rset.close();
        }catch(SQLException e){ e.printStackTrace(); }
    }
    return user_info[index];
}

public int getUserId(String username){
    String user_id_from_db = get_user_info(username, 0);
    int user_id = Integer.parseInt(user_id_from_db);
    return user_id;
}

public String getUsername(String username){ return get_user_info(username, 1); }
public String getPassword(String username){ return  get_user_info(username, 2); }
public String getFirstname(String username){ return get_user_info(username, 3); }
public String getMiddlename(String username){ return get_user_info(username, 4); }
public String getLastname(String username){ return  get_user_info(username, 5); }
public String getUserRights(String username){ return get_user_info(username, 6); }

public boolean userExists(String username){
    boolean queryStatus = false;
    if(username.equalsIgnoreCase(getUsername(username)))
        queryStatus = true;
    else
        queryStatus = false;
    return queryStatus;
}   
}

Then here is the code that calls the query:

<jsp:useBean id="user" class="beans.ConnectToDB" scope="session" />
<jsp:useBean id="aes" class="beans.AES" scope="session" />

String getUsername = request.getParameter("username");
        String getPassword = request.getParameter("password");  

        final String passphrase = "#asdf@1234#";    
        byte[] password_byte = getPassword.getBytes();  
        byte[] passphrase_byte = passphrase.getBytes();
        byte[] encrypt_password = aes.encrypt(password_byte, passphrase_byte);      

        if((getUsername != null && !getUsername.isEmpty()) || (getPassword != null && !getPassword.isEmpty())){
            String username_from_db = user.getUsername(getUsername);
            String password_from_db = user.getPassword(getUsername);

            byte[] pass_db_byte = password_from_db.getBytes();
            byte[] encrypted_pass_db = aes.encrypt(pass_db_byte, passphrase_byte);

            if(getUsername.equalsIgnoreCase(username_from_db) && encrypt_password.equals(encrypted_pass_db)){
                response.sendRedirect("home_page.jsp");
            }
        }
        else{ response.sendRedirect("index.jsp"); }

When I call getUsername(String username) method it returns null here is the thrown exception:

org.apache.jasper.JasperException: An exception occurred processing JSP page /authenticate_user.jsp at line 29
26:             byte[] encrypt_password = aes.encrypt(password_byte, passphrase_byte);      
27:             
28:             if((getUsername != null && !getUsername.isEmpty()) || (getPassword != null && !getPassword.isEmpty())){
29:                 String username_from_db = user.getUsername(getUsername);
30:                 String password_from_db = user.getPassword(getUsername);
31: 
32:                 byte[] pass_db_byte = password_from_db.getBytes();


Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:568)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:470)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

root cause

java.lang.NullPointerException
beans.ConnectToDB.get_user_info(ConnectToDB.java:61)
beans.ConnectToDB.getUsername(ConnectToDB.java:72)
org.apache.jsp.authenticate_005fuser_jsp._jspService(authenticate_005fuser_jsp.java:110)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

Answer 1

The exception you catch is caused by a previously one. An exception is thrown in the first lines of the try/catch block:

    pstmt = this.connect.prepareStatement(query);
    pstmt.setString(1, username);
    rset = pstmt.executeQuery();

So no value is set to rset before the finally block begins executing, and a new exception is raised as you try to work with a null value. Change your catch to capture all exceptions and you will find the root cause.

And of course, when doing work with a finally or catch block, mind that if an exception has been thrown then not all code in the try block will have been executed, so maybe some variables are not defined.

Note that if you are using Java 7, you have available the try with resources: http://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html

Also, Exception thrown in catch and finally clause

Answer 2

You posted too much stuff.

All you need is this:

beans.ConnectToDB.get_user_info(ConnectToDB.java:61)

Open up ConnectToDB.java in a text editor, got to line 61, and inspect all the object references on that line. One of them is null because you didn't initialize it properly or assumed that it would always be non-null when you got it. Figure out which one and initialize it properly. Problem solved.

I don't think this is a good solution. If you have JSPs, you have a servlet/JSP engine, which should have a JNDI database connection pool and naming service. You should set that up and externalize your database connection parameters. They don't belong in your code. A pool will manage connections better than you will.

You aren't closing your resources properly, either. They should be closed in a finally block, in reverse order of creation, wrapped in individual try/catch blocks. I would write a static utility method that could be called to do it.

package persistence;

public class DatabaseUtils {
    private DatabaseUtils() {}

    // Similar for ResultSet and Connection
    public static void close(Statement st) {
        try {
            if (st != null) {
                st.close();
            }
        } catch (Exception e) {
            // Log the exception
        }
    }
}