ResultSet异常-开始前

提问者：小点点

ResultSet异常-开始前

    public static User getUser (User user) throws Exception {
            String sql = "SELECT * FROM login WHERE userID = '" + user.getuserID() + "'";
            //is FROM Correct?
            PreparedStatement pStatement = conn.prepareStatement(sql);
            ResultSet Rset = pStatement.executeQuery(sql);
            Rset.beforeFirst();
            Rset.next();

            int userID = Rset.getInt("userID");
            String email = Rset.getString("email");
            User userReturn = new User (userID, email);
            return userReturn;
        }

Trace: java.sql.SQLException: Operation not allowed for a result set of type ResultSet.TYPE_FORWARD_ONLY.
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.result.ResultSetImpl.beforeFirst(ResultSetImpl.java:426)
    at DaoLayer.daoImplementation.getUser(daoImplementation.java:28)
    at DaoLayer.DaoTest.testGetUser(DaoTest.java:26)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
    at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
    at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
    at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
    at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:89)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:41)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:542)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:770)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:464)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:210)

共3个答案

匿名用户

通过pStatement.ExecuteQuery()更改pStatement.ExecuteQuery(sql)

此外，userid是int，因此不需要在查询中使用单引号'（但这不是异常的根）

我建议在rset.next()上使用if语句，如果查询没有返回任何结果，那么在获取值时可能会导致异常。

使用如下代码

public static User getUser (User user) throws Exception {
        String sql = "SELECT * FROM login WHERE userID = " + user.getuserID();
        //is FROM Correct?
        PreparedStatement pStatement = conn.prepareStatement(sql);
        ResultSet Rset = pStatement.executeQuery();
        if(Rset.next())
        {
             int userID = Rset.getInt("userID");
             String email = Rset.getString("email");
             User userReturn = new User (userID, email);
             return userReturn;
        }
        return null;

    }

匿名用户

那么，您的JDBC/SQL可能需要一些更新。

但最重要的是。发生的情况如下:

    ResultSet Rset = pStatement.executeQuery(sql); //Executes the query
    Rset.beforeFirst(); //Moves the cursor backwards before the first row (!)
    Rset.next(); //Goes to the next row

当游标为forward_only时，第二行抛出一个Exeption。加上:你根本不需要那个电话！

这应该能解决你眼前的问题:

    ResultSet Rset = pStatement.executeQuery(sql);
    if(Rset.next()) {
      int userID = Rset.getInt("userID");
      String email = Rset.getString("email");
      User userReturn = new User (userID, email);
      return userReturn;
    } else {
      //Deal with no user found
    }

现在谈谈一般情况:

你在准备陈述真是太好了。但是正如Bashir已经指出的那样:如果您使用executeQuery(sql)（它是class语句的成员），那么您将丢弃预编译语句。
使用PreparedStatement时-同时使用绑定参数。这使得您的代码对于SQL注入和
使用try-with-resource确保正确关闭所有内容。查找连接泄漏或打开的游标是一个很容易避免的麻烦。

最后，您可能会得到如下所示的结果:

public static User getUser (User user) throws Exception {
    String sql = "SELECT * FROM login WHERE userID = ?;
    try(PreparedStatement pStatement = conn.prepareStatement(sql)) {
      pStatement.setInt(1, user.getuserID());
      try(ResultSet Rset = pStatement.executeQuery()) {  
        if(Rset.next()) {
          int userID = Rset.getInt("userID");
          String email = Rset.getString("email");
          User userReturn = new User (userID, email);
          return userReturn;
        }
      }
    } catch(SQLException e) {
      //Deal with the exception
      e.printStackTrace();
    }
    //Error or no user found
    return null;
}

匿名用户

创建标准ResultSet时，只能使用rs.Next()方法迭代结果行。因此不允许rs.BeforeFirst()。而且在您的代码中也不是必需的。创建ResultSet时，光标会自动放置在第一行之前。因此，只使用if（rs.next（））{。。。}。当rs.next()在第一次调用后返回false时，这意味着ResultSet为空。