the workings of ResultSet.getFetchSize()

[Faisal Khan]

Hello,
I have a method which looks something like this:

protected ResultSet queryResultsRS( String database, String sqlQuery ) throws SQLException {
  Connection conn = connect(database);
  Statement stmt = conn.createStatement();
  ResultSet resultSet   =  stmt.executeQuery(sqlQuery);
  System.out.println(resultSet.getFetchSize());
  return(resultSet);
}

The validate method does the following to return an int:

return( queryResultsRS("userDB","SELECT ClientID, CorporateID, LoginCounter 
FROM user_info WHERE EmailAddress = '"+emailAddress+"' 
AND LoginPassword LIKE BINARY '"+password+"' AND Enabled='1';").getFetchSize() );

(broken up the lines for display)
In the UserBean, there is a method isUserValid() and that checks whether validate returns greater than 0.
Unfortunately, whether the user is valid or not, the result is always 0. I have another method called queryResultsAL() which returns an ArrayList of results, if I subsitute the above code with following:

return( queryResultsAL("userDB","SELECT ClientID, CorporateID, LoginCounter FROM user_info 
WHERE EmailAddress = '"+emailAddress+"' 
AND LoginPassword LIKE BINARY '"+password+"' AND Enabled='1';").size() );

Then this works (unfortunately also returns greater than 0 when no username and password are specified but there will be validations to ensure empty strings never reach that code).
I am somewhat confused about the workings of getFetchSize() which I thought will return how many results are retrieved by the query.
Can any one help me to understand this and correct my understanding.
Regards
Faisal
[ February 10, 2004: Message edited by: Faisal Khan ]

[Peyton McCullough]

I'm not exactly sure what getFetchSize() does. Though I do know of another method to count the number of results:

ResultSet rs = executeQuery ( "SELECT * FROM people WHERE bugbites='15'" );
rs.last();
int num_rows = rs.getRow();

Maybe that will aid you.

[Joe Nguyen]

Faisal,
GetFetchSize() returns the number of records that database fetch to your applicaton at a time. By default, the fetch size is 10. Let's assume that your query result contains 100 records. It would takes 10 round trips to transfer 100 records to your application (10 x 10 = 100). GetFetchSize() does not return the number of records in the resultset. If you need to ensure that there is at least one record.
return resultset.next();
Otherwise, loop and cound
while(result.next){
rowCount++
}
You would be in better shape when using PreparedStatement since much overhead is reduced.
Hope it help

[Faisal Khan]

Thanks guys for the feedback and clarification of getFetchSize().