resultset.first(); doesnt return it to the first row

Question

this is one of my button behaviors. my problem is resultset.first(); doesnt return the cursor to the first row. this is my code

public void Menu3_next( JTextField jTextField1, JTextField jTextField2, JComboBox jComboBox1, JComboBox jComboBox2){
    Connect();    
    try{
        statement = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        resultset = statement.executeQuery("select * from Menu3");
        resultset.first();
        while(resultset.next()){
            jTextField1.setText(String.format("%s", resultset.getString("idMenu3")));
            jTextField2.setText(String.format("%s", resultset.getString("Name")));
        }
        String query1 = String.format("select * from Menu1");// where idMenu1 = '%s'",strBrand);
        resultset = statement.executeQuery(query1);
        resultset.first();
        while(resultset.next()){
            jComboBox1.addItem(String.format("%s", resultset.getString("Brand")));
        }
        String query2 = String.format("select * from Menu2");// where idMenu= '%s'",strPart);
        resultset = statement.executeQuery(query2);
        resultset.first();
        while(resultset.next()){
            jComboBox2.addItem(String.format("%s", resultset.getString("Part")));
        }
    }
    catch(SQLException ex){
        System.out.println(ex);
    }
}

TIA Guys. when i click the button it must show the next, and next and next, until the last row. but it doesnt. it just show the last row.

Is there any reason to skip the first row? You're calling `resultset.first()` and then `resultset.next()` so the first accessed result is the second row. — Tom, Sep 06 '14 at 14:51

score 1 · Answer 1 · answered Sep 06 '14 at 14:52

The while (resultset.next()) { ... } idiom expects the cursor is before the first row. When you call resultset.first(), the cursor moves to the first row, and then resultset.next() will move the cursor forward again, effectively skipping the first row.

Comment out all the resultset.first() statements to fix, for example:

resultset = statement.executeQuery("select * from Menu3");
//resultset.first();
while (resultset.next()) {
    jTextField1.setText(String.format("%s", resultset.getString("idMenu3")));
    jTextField2.setText(String.format("%s", resultset.getString("Name")));
}

still the same. it still get the last row and display that. btw thanks:3 — Joorj Pagarigan, Sep 06 '14 at 16:25

score 0 · Answer 2 · edited Jul 29 '20 at 18:27

0

I didn't read the question thoroughly enough and my answer covered another issue I believe... As pointed out in other answers, your issue is with the use of first()and next() but I do believe the point I made is still valid.

When you don't include an explicit ORDER BY the order of the rows in the set returned will be arbitrary. The solution to your problem is most likely to specify the order in the query:

SELECT * FROM Menu3 ORDER BY ...

Also, using select * is considered a bad practice, see Bad habits to kick : using SELECT * / omitting the column list for reasons why (also Why is SELECT * considered harmful).

edited Jul 29 '20 at 18:27

Community

1
1

answered Sep 06 '14 at 14:49

jpw

44,361
6
66
86

So the solution for skipped result set entries is an `ORDER BY` statement? Sounds interesting. Can you explain that? – Tom Sep 06 '14 at 14:54
@Tom No, of course it isn't, and I never claimed that, did I? My answer was meant to point out that rows might not appear in a specific order, and thus it can be perceived as rows being skipped if the first row in a set was expected to appear later. After re-reading the question it seems that the issue here is the use of `first()` and `next()`. The `ORDER BY` still matters in my opinion. – jpw Sep 06 '14 at 15:02
im using order by and not using first() but still the same :3 – Joorj Pagarigan Sep 06 '14 at 16:28

Mark Rotteveel · Answer 3 · 2014-09-06T17:01:29.303

There is no need to call first() here. After you execute a query the ResultSet is positioned before the first row. Subsequent calls the next() will move it forward until there are no more rows.

Your current code has the following steps:

Produce ResultSet (positioned before first row)
Call first() (positioned on first row)
Call next() in loop (positioned on second, third, etc row)
- You do something with that row (the second, third, etc, but not the first)

By calling first() and subsequent calls to next(), you are skipping the first row.

You seem to have the idea that the ResultSet is the same object during the entire method: it isn't, each call to executeQuery produces a new ResultSet. And as matter of correctly closing resources, you should start using try-with-resources with JDBC resources to make sure the ResultSet, Statement and Connection is closed correctly after you are done with them.

As to your second problem: you use setText, setText replace the entire content of the field. So of course it will only show the last row.

PS On stackoverflow you should stick to one problem per question.

resultset.first(); doesnt return it to the first row

3 Answers3