Why i can't read the next rows in ResultSet

Question

I have a problem i'm trying to read another row in my ResultSet but dunno why can't my loop just end at rs.next(). Yes, i got more than 1 rows in tblPracownicy. I check ResultSet content with while(rs.next())system.out.println(rs.getRow(1)) before DO and it look good i got that number of result as much i got rows in tblPracownicy. But in DO loop i didn't know why it won't cycle the loop. here is the code:

ResultSet rs2 = stat.executeQuery("select _id, cykl_id, CyklDzien, CyklData, DataZatrudnienia, DataZwolnienia from tblPracownicy");
            //wygeneruj harmonogramy dla pracowników
            long prac_id = rs2.getLong(1);
            int offset;
            Cykl cykl = null;

            do
            { //pracownicy
                //odnajdź i pobierz cykl dla pracownika prac_id
                if (cykl == null || cykl.cykl_id != rs2.getLong(2))
                    for (Cykl c : cykle)
                        if (c.cykl_id == rs2.getLong(2)) 
                        {
                            cykl = c;
                            break;
                        }
                //ustaw offset cyklu na dzień 1.szy
                GregorianCalendar gc_cykl = new GregorianCalendar();
                gc_cykl.setTime(rs2.getDate(4));
                gc_harm = new GregorianCalendar(rok, miesiac-1, 1);
                //uwzględnij startowy dzień cyklu = CyklDzien
                gc_cykl.add(GregorianCalendar.DAY_OF_MONTH, -rs2.getInt(3)+1);
                offset = (int) ((gc_harm.getTimeInMillis() - gc_cykl.getTimeInMillis()) / (3600000*24)) % cykl.dlugosc;
                //przelicz offset na dodatni
                if (offset < 0) offset = cykl.dlugosc + offset;

                GregorianCalendar gc_zatr = new GregorianCalendar(new Integer(rs2.getString(5).substring(0, 4)), new Integer(rs2.getString(5).substring(5, 7)), new Integer(rs2.getString(5).substring(8)));
                GregorianCalendar gc_zwol = null;
                //if (!(rs2.getString(6) == null || rs2.getString(6).equals("")))
                if ((rs2.getString(6) != null && !rs2.getString(6).equals("")))
                {
                    gc_zwol = new GregorianCalendar(new Integer(rs2.getString(6).substring(0, 4)), new Integer(rs2.getString(6).substring(5, 7)), new Integer(rs2.getString(6).substring(8)));
                }
                //definiuj zmiany pracownika na cały miesiąc
                for (int dzien=1; dzien <= max; dzien++)
                { //dni
                    //w dni miesiąca kiedy pracownik nie jest zatrudniony wstawić dni wolne
                    gc_harm.set(rok, miesiac, dzien);
                    if (gc_harm.before(gc_zatr) || (gc_zwol != null && gc_harm.after(gc_zwol)))  //jesli przed zatrudnieniem lub po zwolnieniu
                    {   //wpisz dzien wolny = niekasowalne godziny 'xx - xx' o id = 0
                        stat.executeUpdate("insert into tblZmiany (Harmonogram_id, dzien, pracownik_id, godziny_id) "
                                + "values (" + id + ", " + dzien + ", " + prac_id + ", " + 0 + ");");
                    }else{
                        //wpisz zmianę
                        stat.executeUpdate("insert into tblZmiany (Harmonogram_id, dzien, pracownik_id, godziny_id) "
                                + "values (" + id + ", " + dzien + ", " + prac_id + ", " + cykl.godziny[offset] + ");");
                    }
                    offset++;
                    if (offset >= cykl.dlugosc) offset = 0;
                }
            }while (rs2.next());

Can you please say what exactly goes wrong? What do you mean by "won't cycle the loop" ? — kornero, Feb 13 '12 at 12:21

adarshr · Answer 1 · 2012-02-13T12:27:47.207

3

You should use a normal while loop, not a do ... while one.

while(rs2.next()) {
    // do your stuff
}

If you use a do ... while loop, the body of the loop gets executed first and then the condition gets checked. Which will fail if you don't get any results back from the database.

Bear in mind that the ResultSet.next() call won't just check the condition. It actually advances the cursor. Something like how the Iterator.next() works.

So, on the first iteration when the do block executes, the cursor is pointing nowhere (it hasn't yet advanced as you haven't invoked the next() on it) which causes the behaviour you're seeing.

edited Feb 13 '12 at 12:27

answered Feb 13 '12 at 12:19

adarshr

61,315
23
138
167

The big disadvantage of `do.. while` is that it executes atleast once and in your case it tries to read the next record of last record... – Fahim Parkar Feb 13 '12 at 12:21
OK previously I got while and i started loop from the second element and got same problem, I'm trying to make rs.beforeFirst but i got error " the ResultSet is TYPE_FORWARD_ONLY soo I've changed to do..while. – Bulit Feb 13 '12 at 12:34
Be sure that the only time you call `rs.next()` is within the `while(rs.next())` line. People generally tend to do things like `System.out.println("Has next?" + rs.next());` while debugging which actually creates problems! – adarshr Feb 13 '12 at 12:40
kornero - In the second cycle loop rs courser is set after last record and that's why the loop ends, but i dunno why it got last when it should go to the next record. @adarshr Yes i know this – Bulit Feb 13 '12 at 12:40

score 1 · Accepted Answer · answered Feb 22 '12 at 23:50

ok problem solved, the two query cause "interference" in working of ResultSet.

if (gc_harm.before(gc_zatr) || (gc_zwol != null && gc_harm.after(gc_zwol)))  //jesli przed zatrudnieniem lub po zwolnieniu
                {   //wpisz dzien wolny = niekasowalne godziny 'xx - xx' o id = 0
                    stat.executeUpdate("insert into tblZmiany (Harmonogram_id, dzien, pracownik_id, godziny_id) "
                            + "values (" + id + ", " + dzien + ", " + prac_id + ", " + 0 + ");");
                }else{
                    //wpisz zmianę
                    stat.executeUpdate("insert into tblZmiany (Harmonogram_id, dzien, pracownik_id, godziny_id) "
                            + "values (" + id + ", " + dzien + ", " + prac_id + ", " + cykl.godziny[offset] + ");");
                }

Now i got:

if (gc_harm.before(gc_zatr) || (gc_zwol != null && gc_harm.after(gc_zwol)))  //jesli przed zatrudnieniem lub po zwolnieniu
                    {   //wpisz dzien wolny = niekasowalne godziny 'xx - xx' o id = 0
                        listaZapytan[x++] = "insert into tblZmiany (Harmonogram_id, dzien, pracownik_id, godziny_id) "
                                + "values (" + id + ", " + dzien + ", " + prac_id + ", " + 0 + ");";
                        System.out.println(listaZapytan[x-1]);

                    }else{
                        //wpisz zmianę
                        listaZapytan[x++] = "insert into tblZmiany (Harmonogram_id, dzien, pracownik_id, godziny_id) "
                                + "values (" + id + ", " + dzien + ", " + prac_id + ", " + cykl.godziny[offset] + ");";
                        System.out.println("cykl.godziny[" + offset + "] = " + cykl.godziny[offset]);
                        System.out.println(listaZapytan[x-1]);

Why i can't read the next rows in ResultSet

2 Answers2