Java MySQL NullPointerException on query

Question

Basically I have a problem with getting a ResultSet from a database. The errors occur around Line 34 (?) in this code block but I've marked it

ResultSet rs = caq.executeQuery("SELECT * FROM ProjectScore"); //error goes here

I get a null pointer exception on runtime and this is the output from the catch statement below:

null, calculating average score failed

java.lang.NullPointerException

[Ljava.lang.StackTraceElement;@1de2b1

The interesting thing is that I use the Connection Exactly the same in the class at the end and I receive no errors there. Copying the statement to the first class doesn't work either so I assume it's something else. I think that's all I have, any help is appreciated :)

import java.awt.*;
import java.awt.event.*;
import java.sql.*;

import javax.swing.*;

public class MainWindow extends JFrame implements ActionListener{
    //.......................
    private int [] averageScore;

    //References
    private LogInWindow liw;
    private NewUserWindow nuw;
    private ScoreWindow sw;
    private boolean isAnotherWindowOpen = false;
    private boolean isLoggedIn = false;
    private ConnectionAndQueries caq;

    public MainWindow(ConnectionAndQueries caq) throws SQLException{
        this.caq = caq;

        //.................................

        //Middle
        averageScore = new int [9];
        calculateAverageScore();
        setTable();

        //............................
    }

    private void calculateAverageScore() throws SQLException{
        try{
            ResultSet rs = caq.executeQuery("SELECT * FROM ProjectScore"); //error goes here
            int [] count = new int [9];
            int [] totalScore = new int [9];

            while(rs.next()){
                int itemID = rs.getInt("itemID");

                count[itemID]++;
                totalScore[itemID] += rs.getInt("Score");
            }

            for(int i = 0; i < 9; i++){
                averageScore[i] = totalScore[i] / count[i];
            }
        }
        catch (Exception e) {
            System.out.print(e.getMessage());
            System.out.println(", calculating average score failed");
            System.out.println(e.toString());
            System.out.println(e.getStackTrace().toString());
        }
    }
}




//next class

import java.sql.*;

public class ConnectionAndQueries {
    private static Connection connection;
    private static Statement statement;
    private MainWindow mw;

    public ConnectionAndQueries() throws ClassNotFoundException, SQLException{
        mw = new MainWindow(this);
        connect();
    }

    public static void connect() throws ClassNotFoundException, SQLException{
        try{
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://zzzzzzzzzz", "yyyy",     "xxxx"); //dont think im allowed to give that info
            statement = connection.createStatement();
        } catch (Exception e) {
            System.out.println("Connecting to the database failed");
        }
    }

    public ResultSet executeQuery(String query) throws SQLException {
        return statement.executeQuery(query);
    }

    public int executeUpdate(String update) throws SQLException {
          return statement.executeUpdate(update);
    }

    public static void main(String [] args) throws ClassNotFoundException, SQLException{
        ConnectionAndQueries caq = new ConnectionAndQueries();
    }
}




//another class which uses the connection class, and works.

import java.awt.*;
import java.awt.event.*;
import java.sql.*;

import javax.swing.*;

public class LogInWindow extends JFrame implements ActionListener{
    //........................

    //References
    private MainWindow mw;
    private ConnectionAndQueries caq;

    public LogInWindow(MainWindow mw, ConnectionAndQueries caq){
        this.mw = mw;
        this.caq = caq;

        //......................
    }

    public void actionPerformed(ActionEvent e) {
        if(e.getSource() == logIn){
            String usn = usernameField.getText();
            String pwd = passwordField.getText();

            try {
                ResultSet rs = caq.executeQuery("SELECT * FROM ProjectCustomer");

                while(rs.next()){
                    if(rs.getString("username").equals(usn)){
                        if(rs.getString("passwrd").equals(pwd)){
                            logInSuccess(usn);
                            mw.userLoggedIn(usn);
                            quit();
                        }
                    }
                }

                if(mw.isUserLoggedIn() == false)
                    logInFailed();

            } catch (Exception e2) {
                System.out.print(e2.getMessage());
                System.out.println(", error at log in");
                System.out.println(e2.toString());
            }
        }

        else if(e.getSource() == quit){
            quit();
        }
    }

    //............................
}

Answer 1

Problem is because of the constructor.

public ConnectionAndQueries() throws ClassNotFoundException, SQLException{

mw = new MainWindow(this);

connect();

}

Here from the constructor of Mainwindow, its makding call to calculateAverageScore, which is executing a query with the ConnectionAndQueries. As connect() is not called so far, connection and statement is null. And so getting null pointer exception.

Try, calling connect() before mw = new MainWindow(this); statement.

public ConnectionAndQueries() throws ClassNotFoundException, SQLException{

connect();

mw = new MainWindow(this);

}

But for sure code refactoring is required as suggested by Gorkamorka.

Answer 2

When this constructor is called, it sends a reference of itself to MainWindow.

public ConnectionAndQueries() throws ClassNotFoundException, SQLException{
    mw = new MainWindow(this);
    connect();
}

However, as this reference is sent while the object being referenced has not yet been constructed (that is, the constructor has not yet finished) the reference can only be null at this point.

One way to monkey-fix this would be move the code from the constructor to the main method, below new ConnectionAndQueries(). However, I strongly recommend refactoring the program structure to separate the database connections and operations into one or more separate classes, as well as decoupling the UI code from the database code.

Answer 3

Looks like your ConnectionAndQuery object is null.

I would check this for nullness upon construction, not when you try and use it. This will capture the error much earlier (and at a much more useful time). It's also possible (since error reporting isn't clear - see below) that this:

catch (Exception e) {
    System.out.println("Connecting to the database failed");
}

is capturing an error but leaving the statement object uninitialised (null). I would check nullness of the statement as a precondition or better still throw that originating exception such that the client code has to deal with a broken ConnectionAndQuery object.

This

System.out.println(e.getStackTrace().toString());

isn't very helpful, btw. I would rather do:

e.printStackTrace();

since it'll dump the complete stack trace, and dump it to stderr (conventional destination for errors). At the moment you're calling toString() on the array of stack frames, and this doesn't really do anything useful.