resultSet.next() throwing null pointer exception

Question

I'm working in java using the JDBC to perform queries on a database. For some reason, this code:

public static void checkAllFlights() {
    String SQLStatement = "SELECT Flight.FlightID,"
                            +"(CASE WHEN (SUM(NumSeats) > 0) THEN SUM(NumSeats) ELSE 0 END)"
                            +"AS Booked_Seats,"
                            +"(CASE WHEN (MaxCapacity-SUM(NumSeats) > 0) THEN MaxCapacity-SUM(NumSeats) ELSE MaxCapacity END) AS Available_Seats,"
                            +"MaxCapacity"
                            +"FROM Flight LEFT JOIN FlightBooking ON Flight.FlightID = FlightBooking.FlightID"
                            +"GROUP BY Flight.FlightID, Flight.MaxCapacity"
                            +"ORDER BY Flight.FlightID";

    try {
        dbAccess.getConnection();
        ResultSet resultSet = dbAccess.runSimpleQuery(SQLStatement);
        System.out.println("FlightID "+"Booked_Seats "+"Available_Seats "+"Max_Capacity");      
        while (resultSet.next()) {
            int ID = resultSet.getInt(1);
            System.out.println(ID);
        }
        DBAccess.disconnect();
    } catch (Exception e) {
        e.printStackTrace(System.err);
    } 
}

is throwing me the error:

java.lang.NullPointerException
        at databases2.Databases2.checkAllFlights(Databases2.java:245)
        at databases2.Databases2.main(Databases2.java:26)

(line "245" refers to the "while (resultSet.next())")

resultSet.next() and resultSet.close() on their own also produce the same error.

I've used essentially the same code further up to cycle over a resultset and it worked fine and I've run the SQL directly and that returns the correct results so I'm confused as to why the resultSet.next() could be throwing a null pointer exception?

The runSimpleQuery method:

public ResultSet runSimpleQuery(String sql)
        throws Exception {

    try {
        // Create a statement variable to be used for the sql query
        statement = connection.createStatement();

        // Perform the update
        return statement.executeQuery(sql);
    } catch (SQLException e) {
        // Problem encountered
        statement = null;
        return null;
    }

Update: After stepping through it, it does appear that runSimpleQuery is returning null which is something it shouldn't be doing unless an sql exception is throw....

Update: Solved. Whilst the query was right, apparently I messed up my string concatenation so that it didn't include spaces where it should and it worked when testing because I was an idiot and tested the query by copy-pasting from code rather than pulling the actual variable out of the debugger....

Solution 1

Have you checked connection object? is it returning proper connection ? it might be possible that connection is null..and it caught in catch block and returning null. You should debug that .. by printing the exception in catch block ..so that you may come to know the exact reason for this null

Solution 2

I ran into the same issue and I discovered that if other thread uses the same connection the ResultSet will throw a NullPointerException after the other sql is executed.

sys.scheduler 06:28:00,017 [SchedulerReloader] ERROR - java.lang.NullPointerException
sys.scheduler 06:28:00,018 [SchedulerReloader] ERROR - com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7009)

My process is a long running process with 200k rows... I will use a particular connection for this and discard it after the process is done.

Solution 3

Debug your runSimpleQuery method, it returns null, that's why resultset.getNext() throw NullPointerException