Select statement returns 0 rows : impossible

Question

I need a quick help on this one, thanks!

I'm trying to check if a member exists in the database with this code:

public boolean memberExists(int id) {
   try {
      open();
      PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM client WHERE id = ?");
      pstmt.setInt(1,id);
      boolean _res = pstmt.executeQuery().getRow() > 0;
      close();
      return _res;
    }
    catch (SQLException e){ System.out.print(e); }
       return false;
    }
}

The method open() gets a new connection and the close() method closes the connection. Pretty straightforward.

The thing that drives me nuts is that it always returns 0 rows. Even the query SELECT * FROM client returns 0 rows.

I used sqlitebrowser.exe to inspect my database and the exact same queries returns the right number of rows.

I think that I already ran into this problem before, but I don't remember what it was exactly. I feel like I'm missing something.

Also, in my code, I can INSERT INTO the database, but not SELECT. This is really strange.

The open() method.

public DBConnector open() {
    boolean _exists = new File(path).exists();
    try {
        conn = DriverManager.getConnection("jdbc:sqlite:" + path);
        if (!_exists) create();
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }
    return this;
}

The problem is that you are calling `getRow()` which does something else than you think it does. Instead of `pstmt.executeQuery().getRow() > 0`, use `pstmt.executeQuery().next()` - `next()` will return `false` if there are no rows. — Jesper, Nov 13 '17 at 14:57
@Kayaman thanks for your comment! Much appreciated! This forum needs more people like you! — Bird, Nov 13 '17 at 16:55
@Kayaman You must be a great teacher! Do you fail all your students? Whew... — Bird, Nov 13 '17 at 16:57

Arnaud · Accepted Answer · 2017-11-13T15:03:39.377

1

From ResultSet.getRow() :

Returns: the current row number; 0 if there is no current row

This is the current row number, not the number of rows in the resultset.

You have several ways to get the row count, see :

How do I get the size of a java.sql.ResultSet ?

edited Nov 13 '17 at 15:03

answered Nov 13 '17 at 14:58

Arnaud

17,229
3
31
44

1

Thanks a lot! I saw this method in some code above (in the same file) `someCodeRelatedToARequest.getRow()` and I went for the same strategy haha! Thanks again :) – Bird Nov 13 '17 at 17:06
@Bird that's known as [Cargo cult programming](https://en.wikipedia.org/wiki/Cargo_cult_programming) – Kayaman Nov 13 '17 at 17:32
@Kayaman I guess everyone does that when they begin coding haha (including you). Thanks for the link. I didn't know it had a name. – Bird Nov 13 '17 at 17:35

Select statement returns 0 rows : impossible

1 Answers1