Avoiding skipping a row By next() Method of ResultSet

Question

This is a simple code print some rows from a Database. but When I execute this nothing is print on screen. I figured that the problem is rs.next() method is skipping a row. So How can I avoid that or Reset the Position of rs.next() method?

 String searchword = Searchtf.getText();
 try {
        Class.forName("com.mysql.jdbc.Driver");

        java.sql.Connection con = DriverManager.getConnection(url, db_username, db_password);

        java.sql.Statement stat = con.createStatement();

        String searchQuery = "SELECT * FROM user WHERE Name LIKE '" + searchword + "' ";

java.sql.ResultSet rs = stat.executeQuery(searchQuery);

        if (rs.next()) {
            while (rs.next()) {
                System.out.print(rs.getString("idUser") + " ,");
                System.out.print(rs.getString("Name") + " ,");
                System.out.print(rs.getString("Email") + " ,");
                System.out.println(rs.getString("country") + " .");
            }
        } else {
            JOptionPane.showMessageDialog(null, "Not Found");
        }
  } catch (Exception ex) {
        ex.printStackTrace();
    }

Answer 1

First, stop building SQL like that - use parameterized SQL and a PreparedStatement. Your current code is vulnerable to SQL injection attacks.

Basically, don't call rs.next() twice in a row (first in the if then in the while)... you can easily do that by converting your while loop into a do/while loop:

if (rs.next()) {
    do {
        System.out.print(rs.getString("idUser") + " ,");
        System.out.print(rs.getString("Name") + " ,");
        System.out.print(rs.getString("Email") + " ,");
        System.out.println(rs.getString("country") + " .");
    } while (rs.next());
}

Or just have the while loop, with a separate variable to detect that you've seen some results:

bool anyResults = false;
while (rs.next()) {
    anyResults = true;
    System.out.print(rs.getString("idUser") + " ,");
    System.out.print(rs.getString("Name") + " ,");
    System.out.print(rs.getString("Email") + " ,");
    System.out.println(rs.getString("country") + " .");
}
if (!anyResults) {
    JOptionPane.showMessageDialog(null, "Not Found");
}

(Additionally, you should use try-with-resources to close your ResultSet etc, and just printing a stack trace to stdout is almost never the appropriate way to handle exceptions...)

Answer 2

No no need to use :

 if (rs.next()) {

The while is enough, this make the result two times. instead you can use :

boolean b = false;
while (rs.next()) {
   b = true;
   ...
}

if(!b) {
  JOptionPane.showMessageDialog(null, "Not Found");
}

---

You have to use PreparedStatement instead, to avoid any SQL Injection or syntax error.

Answer 3

The API docs for ResultSet explain about the next() method:

Moves the cursor forward one row from its current position. A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.

Every time you call the next() method you are moving the cursor to the next row. This happens in your code (two times) here:

if (rs.next()) {
            while (rs.next()) { 

One way to avoid the problem is to use the method beforeFirst(). According to the API:

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.

Then, every time you use somewhere in your code "rs.next()" you are moving the cursor one position (to the next row) but if after using rs.next() many times you need to start reading from the first row you can simply use rs.beforeFirst() and when you call the next time rs.next() the cursor will start from the beginning of your result set.

Also you do not need to use the if statement in your code:

if (rs.next()) {

because the while you use is enough.

Answer 4

rs.next() will increment cursor in if so if resultset has only one row returned by query then while will move cursor again and no data will get print.Use do..while loop instead.

if (rs.next()) {
            do {
                System.out.print(rs.getString("idUser") + " ,");
                System.out.print(rs.getString("Name") + " ,");
                System.out.print(rs.getString("Email") + " ,");
                System.out.println(rs.getString("country") + " .");
            }while (rs.next());
        } 

Answer 5

Thanks for every answers. I found that putting rs.relative(-1) also does the job. But above answers are well coded and better than mine. So thanks all. I'm a newbie to programming I will consider all your advice in my coding. Thanks

if (rs.next()) {
     rs.relative(-1);
        while (rs.next()) {
            System.out.print(rs.getString("idUser") + " ,");
            System.out.print(rs.getString("Name") + " ,");
            System.out.print(rs.getString("Email") + " ,");
            System.out.println(rs.getString("country") + " .");
        }
 }

Answer 6

Hey it is skipping row because u r doing rs.next() in the if clause and then again in while. So the first iteration which while is doing is actually the second row. Best option will be in if clause u should write if(rs.first())