Buupu Buupu - 1 year ago 40
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) {



return ap1;

Thanks in advance for any help!

Answer Source

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.

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.