In addtion to all above I want to add this but I am not very sure about this and need inputs of other.
In my view ResultSet does not hold any data rather It points to cursor in database and it makes a round trip to database whenever resultset.next() is called so there is no limit of resulset if there is any then it would be DB dependant
what is alternative to ResultSet which can hold more table of data
As far as I know there is no alternative of ResultSet even if you use CachedRowSet then also you need resultSet to populate it.
finally I got one statement to validate my sentence
Standard JDBC receives the result sets of queries one row at a time. Each row costs a round trip to the database. This feature associates with each statement object an integer called its row prefetch setting. JDBC fetches that number of rows at a time from result sets associated with the statement.