Invalid cursor state - no current row when retrieving integer value from sql table

Question

I have been searching a lot about this but nothing solved my problem, I am trying to get the id (that is INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ) of a registered user from sql table called "USERS", when calling int _id = rs2.getInt("UserID"); it throws me an exception : Invalid cursor state - no current row. here is what I have so far:

in Servlet.java:

try{
    stmt = conn.prepareStatement(UAppConstants.SELECT_USERID_BY_NAME);
    System.out.println("1");
    stmt.setString(1, _username);
    System.out.println("2");
    ResultSet rs2 = stmt.executeQuery();
    System.out.println("3");
    int _id = rs2.getInt("UserID"); //exception here
    System.out.println("the registered user id is" +  _id);

}catch (SQLException e) {
        System.err.println(e.getMessage()); // print error
}

in UAppConstants.java:

public final String CREATE_USERS_TABLE = "CREATE TABLE USERS (UserID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY,username varchar(10) NOT NULL,password varchar(8) NOT NULL,description varchar(50) DEFAULT NULL,nickname varchar(20) NOT NULL,photo varchar(100) DEFAULT NULL)";
public final String INSERT_USER_STMT = "INSERT INTO USERS (username,password,nickname,description,photo) VALUES (?,?,?,?,?)";
public final String SELECT_USERID_BY_NAME = "SELECT UserID FROM USERS WHERE username=? ";

UserModel.java:

public class User {
private String Username, Password, Description, Photo,Nickname;//user "schema"
private int UserID;


public User(String name, String pass, String nick,String desc,String photo ) {
    Username = name;
    Password = pass;
    Description = desc;
    Photo = photo;
    Nickname = nick;
}



/* Getters & Setters */

public int getUserID() {
    return UserID;
}

public void setUserID(int uid) {
    this.UserID = uid;
}

//the rest of getters and setters

Exception:

INFO: Reloading Context with name [/ExampleServletv3] is completed
        1
        2
        3
        Invalid cursor state - no current row.

can someone direct me what to do? thank you

Answer 1

After executeQuery, the cursor is positioned before the first row. So you cannot access any data yet. First, you need to call next() to move the cursor to the first row.

So it should be:

stmt = conn.prepareStatement(UAppConstants.SELECT_USERID_BY_NAME);
stmt.setString(1, _username);
ResultSet rs2 = stmt.executeQuery();
if (rs2.next()) {
    int _id = rs2.getInt("UserID");
    // do something with the id
} else {
    // user not found
}

And a tip for exception handling: Instead of plastering your code with dull print statements and not handling an exception except for a very minimal message, rethrow the exception:

try {
    // code
} catch (SQLException e) {
    throw new RuntimeException(e);
}

That way you get a full error message including all the nested exception and you also get the line where the exception was thrown.