java - 连接Java和MySQL的代码的JDBC结果集

Question

这个问题已经有答案了:

ResultSet exception - before start of result set (6 个回答)

已关闭 5 年前。

我正在尝试使用 Java 连接到 MySQL 数据库。我想从数据库中的两行中获取两行的所有条目，并将它们放入 ExpertScore 类中。然后我想将新创建的 ExpertScore 对象放入 ExpertScore 对象数组中。之后，我想通过一个将 ExpertScore 对象数组作为输入的方法来运行它们。但是，我运行代码并收到此错误。一些调试表明我认为问题是由结果对象的计数引起的。

java.sql.SQLException: Before start of result set
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
 at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:841)
 at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2672)
 at ZScoreCalc.main(ZScoreCalc.java:106)

这是我的代码，导致错误:

public static void main(String[] args) throws InstantiationException, IllegalAccessException, ClassNotFoundException{
     System.out.println("MySQL Connect Example.");
     Connection conn = null;
     String url = "jdbc:mysql:*";    // server address
     String dbName = "QA";   //table name
     String driver = "com.mysql.jdbc.Driver"; // jdbc driver
     String userName = "*";   //username
     String password = "*";    //password
     Class.forName(driver).newInstance();
     try {
      conn = DriverManager.getConnection(url+dbName,userName,password);
      System.out.println("Connected to the database");

      for(int j=1; j<11; j++){
      String query = "select * from CONSUMER_EXPERT_SCORE where CONSUMER_EXPERT_ID="+j;
      String queryforcount = "select count(*) from CONSUMER_EXPERT_SCORE where CONSUMER_EXPERT_ID="+j;
      PreparedStatement pscount = conn.prepareStatement(queryforcount);
      ResultSet resultcount = pscount.executeQuery();
      int count = resultcount.getInt(0);
      PreparedStatement ps = conn.prepareStatement(query);
      ResultSet result = ps.executeQuery();
      int i=0;
      ExpertScore[] allUsers=new ExpertScore[count];
      while(result.next()){
       double expert=result.getDouble(3);
       int id=result.getInt(2);
       ExpertScore current=new ExpertScore(id, j, expert);
       allUsers[i]=current;
       i++;
      }
      ZScoreCalc scrCalc = new ZScoreCalc();
         scrCalc.Z_Calc(allUsers);
         scrCalc.print();
      }

     } catch (Exception e) {
      e.printStackTrace();
     } 

}
}

有人知道这是怎么回事吗？抱歉，我是编程新手，尤其是 JDBC。

Answer 1

在调用 resultcount.getInt(0); 之前，您需要先调用 resultcount.next()

在finally 子句中关闭结果集是一个很好的做法。这通常是您想要使用的结构。

//in general use a structure like this:
ResultSet rs = null;
PreparedStatemetn pStmt = null;
try {

 pStmt = conn.prepareStatement("Select * from foo");
 rs = pStmt.executeQuery();
 while (rs.next()) {
    String data = rs.getString(1);
 }
}
catch(Exception e){
 //handle exception
}
finally {
  try {
     if (rs != null) rs.close();
     if (pStmt != null) pStmt.close();
  }
  catch (Exception ignored) {
  }
}

}

专业提示:对于常见的“关闭此资源并忽略异常”模式，创建实用程序方法:

class DbUtil {
    public void closeQuietly(ResultSet rs) {
      try {
         if (rs != null) rs.close();
      }
      catch (Exception ignored) {}
    }

    public void closeQuietly(Statement stmt) {
      try {
         if (stmt != null) stmt.close();
      }
      catch (Exception ignored) {}
    }
}