Getting java.lang.NullPointerException when while(rs.next()) is encountered second time

Question

String[] Starttime= new String[5000];
String[] Endtime = new String[5000];

int st=0;
int et=0;

while(rs.next()) {           
    ResultSet rs_second=stmt.executeQuery("*Select Query*");

    while (rs_second.next()) {
        if(condition){
            rs_second.previous();
            Endtime[et]=rs_second.getString("rec_datetime");
            rs_second.next();
            et=et+1;
        }
        else if(condition){
            Starttime[st]=rs_second.getString("rec_datetime");
            st=st+1;
        }
    }
    System.out.println();
    System.out.println(st);
    System.out.println(et);

    for(int i=0;i<st;i++) {
        Date Start=new SimpleDateFormat("MM/dd/yyyy HH:mm:ss").parse(Starttime[i]);
        Date End=new SimpleDateFormat("MM/dd/yyyy HH:mm:ss").parse(Endtime[i]);
        long diff = End.getTime() - Start.getTime();
        long diffSeconds = diff / 1000 % 60;
        long diffMinutes = diff / (60 * 1000) % 60;
        long diffHours = diff / (60 * 60 * 1000) % 24;
        long diffDays = diff / (24 * 60 * 60 * 1000);

        System.out.println(diffDays+" Days "+diffHours +" Hours "+diffMinutes+" Minutes "+diffSeconds+" Seconds");
    }
}

When the above code is executed, at first the code runs absolutely fine, but when accessing rs.next() again. its showing:

java.lang.NullPointerException
    at oracle.jdbc.driver.ScrollableResultSet.next(ScrollableResultSet.java:344)
    at triage.Triage.main(Triage.java:84)

I tried to execute rs.next() separately, and it contains 17 records. Tried with everything but not able to debug the exception.

This question is not a duplicate of What is a nullpointerexception and how do I fix it In this case I cannot debug why it's coming, since the code is running absolutely fine once and when while(rs.next()) is encountered second time, null pointer exception is caused.

do you get the exception on the rs.next() row? How do you create rs? Do you use the same statement? — Veselin Davidov, Mar 28 '18 at 14:56
Did you disable auto-commit, or did you leave auto commit enabled? Are you using the same `Statement` object as the one that created the first result set? Exactly which version of the Oracle JDBC driver are you using? — Mark Rotteveel, Mar 28 '18 at 15:03
Based on the answer, this seems like it might be a useful reference question, although it seems to be in dire need of a [mcve] (you probably don't need / shouldn't post an example where you construct a database from scratch, or actually you can probably just query a default table, but you should at least post a complete code sample that does your select query). — Bernhard Barker, Mar 28 '18 at 15:15
@Dukeling The fact a `NullPointerException` occurs suggests a bug in the version of the Oracle JDBC driver used by the OP. The JDBC specification itself requires a `SQLException` to be thrown when calling `next()` on a closed result set. There are already a number of questions addressing the problem of interleaved result set use and execution of other statements on the same statement, eg https://stackoverflow.com/questions/935511/how-can-i-avoid-resultset-is-closed-exception-in-java and https://stackoverflow.com/questions/25898865/jdbc-resultset-is-closed-in-while-loop — Mark Rotteveel, Mar 28 '18 at 15:17

score 1 · Accepted Answer · answered Mar 28 '18 at 15:00

1

You haven't included how you get the first resultset. If you get the error there maybe you should?

My guess is that you use the same statement to get the second result set and since in java you can't have more than one open result set per statement when you go in the while loop for the first time you reuse the statement and close the first result set.

The Statement API says:

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

So try to create a new statement object in the while loop and use that one

answered Mar 28 '18 at 15:00

Veselin Davidov

7,031
1
15
23

`Connection con=DriverManager.getConnection(Url,UserID,Pass);` `Statement stmt= con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);` `ResultSet rs=stmt.executeQuery("Select query");` `ResultSetMetaData rsmd = rs.getMetaData();` – Aman Malhotra Mar 28 '18 at 15:02
yes the issue is resolved now.. I was using the same statement by mistake for the other resultset too.. Thank you so much @Veselin.. :) – Aman Malhotra Mar 28 '18 at 15:07
That's what I meant. You use the same statement object ("stmt") and when you call a second query with it you effectively close the first result set. That's why you get the null pointer on the second iteration - the first result set is closed there. Create a scond statement for the second query – Veselin Davidov Mar 28 '18 at 15:07

Getting java.lang.NullPointerException when while(rs.next()) is encountered second time

1 Answers1