Buupu Buupu - 6 months ago 9
Java Question

How to stop trying to retrieve rows if they do not exist?

After looking into it this seems quite common but I didn't find an answer that was really appropriate for what I was looking for.

I have this method which will returns the first 5 rows. However, when the rows are empty it pops up with


java.sql.SQLException: Illegal operation on empty result set.


per empty row.

How would I stop this code trying to return results when there are no more rows?

public Appointment viewUpcomingAppointmentsDB(int i) throws SQLException {
Connection myConn = DriverManager.getConnection(dBPath, dBUsername, dbPassword);
Statement myStmt = myConn.createStatement();
Appointment ap1 = new Appointment();
ResultSet myRs = myStmt.executeQuery(
"select a.AppointmentID, a.Time, a.Date, d.SName, p.SName from Appointment as a" +
" Inner join Doctor as d on d.DoctorID = a.DoctorID Inner join Patient as p on " +
"p.PatientNInsurance = a.PatientNInsurance where Date(a.date) >= DATE(NOW()) order by Date");

int k = 0;
if (myRs.next()) {
while (k < i) {

}
myRs.next();
k++;
}

ap1.setAppointmentID(myRs.getString("AppointmentID"));
ap1.setDate(myRs.getString("Date"));
ap1.setTime(myRs.getString("Time"));
ap1.setPatientName(myRs.getString("p.SName"));
ap1.setDoctorName(myRs.getString("d.SName"));

myStmt.close();
myConn.close();
return ap1;
}


Thanks in advance for any help!

Answer

You are calling next() twice in the loop. Don't.

if (myRs.next()) {  // <-- Advances to the next row
    while (k < i) { // <-- What is this loop supposed to do?

    }
    myRs.next();    // <-- Advances to the next row, but that
                    //     row is never used. Delete this line.
    k++;
}

To retrieve up to i (bad name, use rowsToRead or something like that), use a combined for loop:

for (int row = 1; row <= i && myRs.next(); row++) {
    // process row here
}

That loop will exit after i rows, or when ResultSet exhausts all available rows, whichever comes first.