I'm using Hibernate 3.1 to assist in an ETL scenario with a database.

The jist of my task is to perform a "select * from table" type query and process all of the results one at a time [details irrelevant]. That's what I'd do if I were using straight JDBC, anyway. The table has thousands of records; I can't afford to have them all in memory at once. I've read from the Hibernate documentation that the .iterate() call will result in N+1 queries! I want an O(1) algorithm (preferably exactly one query). The lazy fetching information I've read in the docs seem to apply to related tables but not the table (or "Enity") itself.

The main thing I'm trying to gain by using Hibernate in the first place is for it to map ResultSets to my objects (in a straight-forward manner). All SQL is legacy (and complicated!) and I don't need Hibernate figuring it out.

We've been able to do this with scroll() method and clearing session or evicting objects as we go.

is this what you are looking for...
http://www.hibernate.org/hib_docs/v3/reference/en/html/performance.html#performance-sessioncache

http://ibatis.apache.org/

iBATIS might be better suited to your application.

Thanks for the tip at that URL. I looked into ScrollableResults but the docs didn't clearly say that the get(int):Object method is going to give me the mapped class of the ResultSet. I thought it would give me an array of primitive types for the ResultSet. The docs could use more clarity here.

I would love to use iBatis in this scenario; it is a perfect fit. Unfortunately, my project leader insists that we not use software that in any way overlaps with Hibernate's capabilities.