JDBC result set for code connecting Java and MySQL

Question

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)

This is my code that leads to the error:

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();
     } 

}
}

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

Answer 1

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

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