java - What is the idiomatic way to iterate over a result set without loading all results at once using Hibernate? -

suppose have query object this:

final sqlquery query = createquery(); // org.hibernate.sqlquery // hibernatetransaction wrapper around hibernate's transaction, session , // sessionfactory classes  // corresponding method: private sqlquery createquery(hibernatetransaction t) {     final sqlquery query = t.fullsqlquery(my_query_string);     query.addscalar("column1", hibernate.long);     query.addscalar("column2", hibernate.long);     query.setresulttransformer(new aliastobeanresulttransformer(mydto.class));     return query; } 

the sqlquery object has method called iterate(). problem if try this:

iterator<mydto> iterator = query.iterate(); while(iterator.hasnext()) {     mydto dto = iterator.next();     // ... } 

i exception iteration not supported. if use query.list() method list<mydto> nice if query returns million rows gets slow , runs out of memory.

my question idiomatic way using hibernate fetch row @ time , lazily iterate on them? in example try process data in table row row.

i thinking using clustered indexes , query 1000 rows once , next 1000 based on last id of previous 1000 (so thinking pagination) using myisam tables not supporting .

you can use setmaxresults() , setfirstresult() methods sqlquery class. allow emulate both offset , limit sql clauses.

if wish load results rownum = 0 rownum = 50, instance, use setfirstresult(0) , setmaxresults(50) , on.

source: hibernate javadocs