how to reset the result set to the first row after looping through a while loop

Question

My code has two loops, my outer loop should loop through all the rows of the inner loop for the first row of outer loop, and the for the second row of the outer loop, it should loop through all the rows of the inner row.

int y1,y2;
float t = 0,s1,s2;

while(rm.next())
{
    int currentCol = 0;

    cellNumber = new jxl.write.Number (currentCol++, currentRow, index, integerFormat);
    index++;
    sheet.addCell ( cellNumber );

    cellLabel = new Label(currentCol++, currentRow, rs.getString("Name"));
    sheet.addCell ( cellLabel );


    y1 = rm.getInt("Year");

    System.out.println("Year 1: " +y1);


    cellNumber = new jxl.write.Number (currentCol++, currentRow, y1 , integerFormat);
    sheet.addCell ( cellNumber );



    s1 = rm.getFloat("Total_Price");
    System.out.println("S1: "+s1);

    while (rs.next())
    {
        y2 = rs.getInt("Year");
        System.out.println("Year 2: " +y2);

        s2 = rs.getFloat("Total_Price");
        System.out.println("S2: " +s2);

        if(y1==y2)
        {
            t = s1+s2;    
            System.out.println("Toatl Sales:" +t);

            cellNumber = new jxl.write.Number (currentCol++, currentRow, t , priceformat);
            sheet.addCell ( cellNumber );

        }


    } 
    int a = rs.getFetchDirection();
    System.out.println("Direction: " +a);

    rs.setFetchDirection(ResultSet.FETCH_REVERSE);


    cellNumber = new jxl.write.Number (currentCol++, currentRow, s1 , priceformat);
    sheet.addCell ( cellNumber );




    currentRow++;

}

May or may not be possible: http://stackoverflow.com/q/17450766/499922 — billc.cn, Aug 07 '15 at 15:09

ka4eli · Answer 1 · 2015-08-07T15:12:42.173

19

Use this:

resultSet.beforeFirst()

From docs:

Throws: SQLException - if a database access error occurs; this method is called on a closed result set or the result set type is TYPE_FORWARD_ONLY

edited Aug 07 '15 at 15:12

answered Aug 07 '15 at 15:03

ka4eli

5,294
3
23
43

I am getting the following error, Aug 07, 2015 10:00:40 AM tsidb.SalesUI AnuualSalesCustButtonActionPerformed SEVERE: null java.sql.SQLException: Incorrect cursor type – Anusha Aug 07 '15 at 15:09
Do I need to the set the FetchDirection? – Anusha Aug 07 '15 at 15:20
I may need to set the direction of the resultset. and set it to the first row? – Anusha Aug 07 '15 at 15:24
I can't understand what you ask. – ka4eli Aug 07 '15 at 15:27
I want to reset the resultset, so that it starts again from the first row for the inner loop. – Anusha Aug 07 '15 at 15:33
so, use beforeFisrt() – ka4eli Aug 07 '15 at 15:50
But that is not working in my case and giving me an error – Anusha Aug 07 '15 at 15:51

score 2 · Answer 2 · answered Sep 10 '20 at 10:18

2

Use

resultSet.beforeFirst();

But you have to create the Statement this way:

Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

So that it is traversable and not one direction only.

answered Sep 10 '20 at 10:18

Renis1235

4,116
3
15
27

score 1 · Answer 3 · answered Aug 07 '15 at 15:03

1

Here's what you're looking for:

rm.beforeFirst()

Official documentation:

Moves the cursor to the front of this ResultSet object, just before the first row. This method has no effect if the result set contains no rows.

answered Aug 07 '15 at 15:03

cbender

2,231
1
14
18

Thank you,But I am getting the following error when I try this. – Anusha Aug 07 '15 at 15:08
Aug 07, 2015 10:00:40 AM tsidb.SalesUI AnuualSalesCustButtonActionPerformed SEVERE: null java.sql.SQLException: Incorrect cursor type – Anusha Aug 07 '15 at 15:09
How are you getting the data after you do `beforeFirst()` you need to use `.next()` otherwise use the `.first()` method instead. – cbender Aug 07 '15 at 15:12
I am using rs.next() to get the data. Its the while condition of my inner loop. – Anusha Aug 07 '15 at 15:14
Are you using `beforeFirst()` on `rs` or `rm`? – cbender Aug 07 '15 at 15:15
I am using beforeFirst() on rs. – Anusha Aug 07 '15 at 15:17

how to reset the result set to the first row after looping through a while loop

3 Answers3

Linked