用于连接Java和MySQL的代码的JDBC结果集 [英] JDBC result set for code connecting Java and MySQL

问题描述

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

I am trying to connect to a MySQL database using Java. I want to grab all the entries for two row from two rows in a database and put them into the class ExpertScore. I then want to put the newly created ExpertScore objects into an array of ExpertScore objects. After this I want to run them through a method that takes an array of ExpertScore objects as input. However, I run the code and get this error. Some debugging indicates that I think the problem results from the count of the result object.

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新手.

Does anyone know what is going on here? Sorry I am new to programming and particularly new to JDBC.

推荐答案

您需要先调用resultcount.next()，然后再调用resultcount.getInt(0);

You need to call resultcount.next() before calling resultcount.getInt(0);

并且最好在finally子句中关闭结果集.通常是您要使用的结构.

And it's good practice to close your result sets in a finally clause. Here is generally the structure you would want to use.

//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) {
  }
}

}

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

Pro Tip: For the common "close this resource an ignore exceptions" pattern, create utility methods:

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) {}
    }
}

这篇关于用于连接Java和MySQL的代码的JDBC结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！