游标状态无效 - 从 sql 表中检索整数值时没有当前行

Question

我一直在搜索很多关于这个，但没有什么能解决我的问题，我试图从名为"USERS"的sql表中获取注册用户的id(即INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY(，当调用int _id = rs2.getInt("UserID");时，它会给我一个异常：Invalid cursor state - no current row.这是我到目前为止所拥有的：

在 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
}

在 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=? ";

用户型号.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

例外：

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

有人可以指导我做什么吗？谢谢

Answer 1

executeQuery之后，光标位于第一行之前。因此，您还无法访问任何数据。首先，您需要调用next()将光标移动到第一行。

所以它应该是：

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
}

还有一个异常处理的提示：不要用枯燥的打印语句来填充代码，并且除了非常小的消息之外不处理异常，而是重新抛出异常：

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

这样，您就会收到一条完整的错误消息，包括所有嵌套的异常，并且还会得到引发异常的行。