1� When I get a ResultSet from a select query with, for example, 20 rows. How many rows are loaded in memory at a time. I've heard I won't have the 20 rows in memory at a time, but in that case, is JDBC accessing the DB again and again to get the same cursor in order to get the next row? In that case, how many rows reads each time? Isn't it a bottleneck to access DB so many times?
This depends entirely on the JDBC driver. Is it a bottleneck? Possibly. But something has to take the strain. If you have a large result set you are liable to run out of memory in your client app if you return all the results in one go.
2� What are the handicaps of using a PreparedStatement over using a simple Statement? I've read PreparedStatment is slower if I use it only once, as its only advantage is when using the same PreparedStatement many times (for example, in a loop), but the creation of a PreparedStatement is slower. Is there any other handicap than that? Is there any other advantage?
None, that I'm aware of. I'd be surprised if it were noticably slower. Where did you hear this? PreparedStatements have the considerable advantage that you can bind variables, so safegard your code against SQL injection attacks, plus avoid all those pesky formatting issues normal Statements have.
(Ah, that's the problem with long answers! A faster typer always gets there first

)
[ September 01, 2008: Message edited by: Paul Sturrock ]